last updated 20-sep-12
Goal of design is to generate a formal specification of the database schema
E-R Model is not SQL based.
The E-R Model: The enterprise the database represents is viewed as set of
Mapping #1: Each entity type always corresponds to a relation
Mapping #2: The attributes of a relation contains at least the attributes of an entity type
Problem: Recall that the entity type can have multi- valued attributes.
Possible solution: Use several rows to represent a single entity
Problems with this solution:
so, the resulting relation must be further transformed--> Normalization
Relationship: connects two or more entities into an association/relationship
Relationship Type: set of similar relationships
Entity types and most relationship types in the E-R model are mapped to relations (relational model)
Relationship Types may also have attributes in the E-R model.
Relationships tend to be verbs; attributes of relationships are again nouns
Problem: recursive relationships can relate elements of same entity type
e.g., the ReportsTo relationship type relates two elements of the Employee entity type:
We do not have distinct names for the roles
It is not clear who reports to whom
Solution: the role name of relationship type need not be same as name of entity type from which participants are drawn
Mapping #7: If the cardinality is 1-many or 1-1 of a recursive relationship, then a second attribute of the same domain as the key may be added to the entity relation to establish the relationship. Attributes of the relationship can also be added to the entity relation, but may be a good reason to create a separate relation with the attributes and keys of the entities.
Mapping #8: for many-many recursive relationships, you create a relation including the attributes of the relation but with the primary keys of the entity included twice, one for each role.
Contains the following features:
Role names, Ri, and their corresponding entity sets. Roles must be single valued (the number of roles is called its degree)
Attribute names, Aj, and their corresponding domains. Attributes in the E-R model may be set or multi-valued.
Key: Minimum set of roles and attributes that uniquely identify a relationship
Relationship: <e1, …en; a1, …ak>
Attributes of corresponding relation are
Candidate key of corresponding table = candidate key of relation
Except when there are set/multi- valued attributes
Example: Teaching (CrsCode, SectNo, Id, RoomNo, TA)
Each role of relationship type produces a foreign key in corresponding relation.
The foreign key references the table corresponding to the entity type from which role values are drawn.
CREATE TABLE WorksIn ( Since DATE, -- attribute Status CHAR (10), -- attribute ProfId INTEGER, -- role (key of Professor) DeptId CHAR (4), -- role (key of Department) PRIMARY KEY (ProfId), -- since a professor works in at most one department FOREIGN KEY (ProfId) REFERENCES Professor (Id), FOREIGN KEY (DeptId) REFERENCES Department )
CREATE TABLE Sold ( Price INTEGER, -- attribute Date DATE, -- attribute ProjId INTEGER, -- role SupplierId INTEGER, -- role PartNumber INTEGER, -- role PRIMARY KEY (ProjId, SupplierId, PartNumber, Date), FOREIGN KEY (ProjId) REFERENCES Project (Id), FOREIGN KEY (SupplierId) REFERENCES Supplier (Id), FOREIGN KEY (PartNumber) REFERENCES Part (Number) )
If, for a particular participant entity type, each entity participates in at most one relationship, its corresponding role is a foreign key relationship type.
The representation in an E-R diagram is an arrow.
Relational model representation: the key of the relation corresponding to the entity type is the key of the relation corresponding to the relationship type
Supertypes and subtypes can be represented as separate relations.
We need a way of identifying each subtype entity with its (unique) related supertype entity
Redundancy is eliminated if the IsA hierarchy is not disjoint
If every entity participates in at least one relationship, a participation constraint holds:
Inclusion dependency: Every professor works in at least one dep’t.
in relational model: (easy)
FOREIGN KEY Id REFERENCES WorksIn (ProfId)
General case (not so easy):
CREATE ASSERTION ProfsInDepts CHECK ( NOT EXISTS ( SELECT * FROM Professor P WHERE NOT EXISTS ( SELECT * FROM WorksIn W WHERE P.Id = W.ProfId ) ) )
Example (here you can’t use the foreign key in Professor)
If every entity participates in exactly one relationship, both a participation and a key constraint hold:
CREATE TABLE Professor ( Id INTEGER, …… PRIMARY KEY (Id), -- Id can’t be null FOREIGN KEY (Id) REFERENCES WorksIn (ProfId) --all professors participate )
Alternate solution if both key and participation constraints apply: merge the tables representing the entity and relationship sets