Download Understanding the Entity-Relationship Model in Database Design and more Study notes Design in PDF only on Docsity!
The Entity-Relationship Model
- After completing this chapter, you should be able to . explain the three phases of database design, Why are multiple phases useful? . evaluate the significance of the Entity-Relationship Model (ER model) for DB design, . enumerate the basic constructs of the ER model, . develop ER diagrams (schemas in the ER model) for a given application, . translate ER models into equivalent (as far as possible) relational models.
The Entity-Relationship Model
166
Overview
1. Database Design Overview
2. Basic ER Constructs
3. Kinds of Relationships (Cardinalities)
4. Keys, Weak Entities
5. Translation into the Relational Model
Database Design (1)
- Overall goal of DBMS usage: Efficiently develop programs to support given real-world tasks.
- These programs need to store data persistently.
- To develop these programs, apply proven methods of software engineering—specialized to support data-intensive programs.
Database Design Database Design is the process of developing a database schema for a given application. DB design is a subtask of the overall software engineering effort.
Database Design (2)
168
- The specification of programs and data is intertwined: . The schema should contain the data needed by the programs. . Programs are often easy to develop once the structure of the data to be manipulated has been specified.
- Data, however, is an independent resource: . Typically, additional programs will be developed later based on the collected data. . Also, ad-hoc queries will be posed against the DB.
Database Design (5)
Three Phases of DB Design
O 1 Conceptual Database Design.^ Produces the initial model of the mini world in a conceptual data model (e.g., in the ER model).
O 2 Logical Database Design.^ Transforms the conceptual schema into the data model supported by the DBMS (e.g., the relational model).
O 3 Physical Database Design.^ Design indexes, table distribution, buffer size, etc., to maximize performance of the final system (subject of “Datenbanken II ”).
Database Design (6)
172
- Why multiple design phases? . Partition the problem, attack one sub-problem after the other. For example, during conceptual design there is no need to worry about performance aspects or limitations of the specific SQL dialect of the RDBMSs. . DBMS features do not influence the conceptual design and only partially influence the logical design. Thus, the conceptual design work is not invalidated, if a different DBMS is used later on.
Example (1)
- ER schema in graphical notation:
ER schema in graphical notation:
Instructor
Name Phone
Course
No (^) Title
teaches
. This mini world talks about instructors and courses. . Instructors teach courses. . Instructors have a name and a phone number. . Courses are numbered and have titles.
Example (2)
174
A possible state of this mini world
Grust
DB
XML
RRR R
RRRR
RRRR
R
llll llll llll l ssgggggggg Phone
kkWWWWWWWW Name
g^ Nogg^ gg^33 ggg Title W++
WWWWWW
WW
g^ Nogg^ gg^33 ggg Title W++
WWWWWW
WW
Instructor Course
The Entity-Relationship Model
Overview
1. Database Design Overview
2. Basic ER Constructs
3. Kinds of Relationships (Cardinalities)
4. Keys, Weak Entities
5. Translation into the Relational Model
Basic ER Model Concepts (1)
178
- Entities: . An object in the mini world about which information is to be stored. Examples: persons, books, courses. Note: entities do not have to correspond to objects of physical existence. Entities may also represent conceptual objects like, e.g., vacations. . The mini world that has to be modelled can contain only a finite number of objects. . It must be possible to distinguish entities from each other, i.e., objects must have some identity. Examples: entity book identified by ISBN number, entity vacations identified by travel agency booking number.
Basic ER Model Concepts (2)
- Attribute: . A property or feature of an entity (or relationship, see below). Example: the title of this course entity is “Foundations of Databases.” . The value of an attribute is an element of a data type like string, integer, date. These values have a printable representation (which entities have not).
Basic ER Model Concepts (3)
180
- Relationship: . A relation—not in the strict relational model sense—between pairs of entities (a binary relationship). Example: Grust (an entity) teaches (a relationship) the course “Foundations of Databases” (an entity).
ER Diagrams (3)
- Relationships may have attributes, too:
Student (^) solved Exercise
points
. This models the fact that a number of points is stored for every pair of a student X and an exercise Y such that X submitted a solution for Y.
Graphical Syntax
184
O 1 An ER diagram contains
- boxes, diamonds, ovals, plus interconnecting lines.
O 2 Boxes, diamonds, and ovals are each^ labelled^ by a string.
- Box labels are unique in the entire diagram.
- Oval labels are unique for a single box or diamond.
- Diamond labels are unique for a pair of connected boxes.
O 3 Interconnecting lines are only allowed between
- box—diamond, box—oval, diamond—oval.
O 4 A diamond has exactly two connecting lines to boxes. There may be any number of connections to ovals.
O 5 An oval has exactly one connecting line.
ER Example
Modelling a Mini World: Define an ER Diagram
- Information about researchers in the database field is to be stored.
- For each researcher, his/her last name, first name, e-mail ad- dress, and homepage URI are relevant.
- Researchers are affiliated with universities and assume a cer- tain position (e.g., professor, lecturer).
- Relevant university information are the name, homepage URI, and country.
- Researchers publish articles (of a given title) in journals.
The Entity-Relationship Model
186
Overview
1. Database Design Overview
2. Basic ER Constructs
3. Kinds of Relationships (Cardinalities)
4. Keys, Weak Entities
5. Translation into the Relational Model
Cardinalities (3)
- The ER model introduces the (min, max) notation to specify an interval of possible participations in an relationship:
E 1 (m 1 , n 1 ) (^) R (m 2 , n 2 ) E 2
. An entity of type E 1 may be related to at least m 1 and at most n 1 entities of type E 2. . Likewise, m 2 is the minimum number and n 2 is the maximum number of E 1 entities to which an E 2 entity is related
Cardinalities (4)
190
- Extensions: . “∗” may be used as maximum if there is no limit. . (0, ∗) means no restriction at all (general relationship).
Cardinalities (5)
Marriage
Man (0,1) is married to (0,1) Woman
“A man can be married to at most one woman and vice versa.”
Airport Locations
Airport (1,1) lies in (0,*) Country
“An airport lies in exactly one country. A country may have arbitrarily many airports (and maybe none at all).”
Cardinalities (6)
192
Derive cardinalities from verbal specifications “Besides normal customers, the database may contain customers who have not yet ordered anything.” Order ( , ) from ( , ) Customer
Derive cardinalities from verbal specifications “An order can contain several products.” Order (^ ,^ ) for (^ ,^ ) Product
Common Cases (2)
- Many-to-many relationships: . Both maximum cardinalities are ∗ (the minimum cardinalities are 0 or 1): Many-to-many relationship
Student (0,)^ takes (0,) Course
. This is the most general/least restrictive case of a relationship. . When translated into the relational model, the representation of many-to-many relationships requires an extra table.
Common Cases (3)
196
- One-to-many relationships: . Maximum cardinality 1 on the “many” side and ∗ on the “one” side: One-to-many relationship
Instructor (0,*) teaches (1,1) Course
“One instructor teaches many courses, but each course is run by exactly one instructor.”
. One-to-many relationships do not require an extra table in an equivalent representation in the relational model.
Common Cases (4)
- One-to-one relationships: . Maximum cardinality 1 on both sides: One-to-one relationship
Employee (0,1) is head of (1,1) Department
“Each department has exactly one department head, some employees are the head of one department.”
- Note how mandatory or optional participation in an relationship determines the minimum cardinalities.
Cardinalities: Alternative Notations (1)
198
- Widespread variants of notations for cardinalities: . Leave particpiation unspecified: Cardinalities are either many-to-many (N:M), one-to-many (1:N), or one-to-one (1:1).
One-to-many relationship
Instructor 1 teaches (^) N Course
Keys (1)
ER Key A key K of an entity type Ea^ is an attribute of E which uniquely identifies the entities of this type. No two different entities share the same value for the key attribute. Composite keys are allowed.
E
K
A 1
A 2
aOnly entity types can have key attributes.
Keys (2)
202
- The translation of ER schemas into relational schemas requires the declaration of ER keys.
- If there is no natural key, add artificial identifiers (e.g., integers, remember attributes EMPNO, DEPTNO from Chapter 1) which then represent the entities.
Weak Entities (1)
- In many schemas, some entities describe a kind of detail that cannot exist without a master (or owner) entity. In such a case,
O 1 there is a^ relationship with cardinality^ (1,^ 1)^ on the detail entity side, and in addition O 2 the^ key of the master is inherited and becomes part of the key of the detail entity.
Invoice has Position
Inv No
Date
Inv No
Pos
(1, ∗) (1, 1)
Weak Entities (2)
204
- Without a specific ER construct for this case, we would require the following additional constraint: . If two entities are in “has” relationship, . then their attribute “Inv No” are required to have identical values. For example, invoice #12 cannot have position 2 in invoice #42 as detail.
- Such constraints occur if an entity does not have a key by itself, but it is only unique in the context of some other (master) entity.
