Database Systems Ch. 3
Conditions for a table to be a relation
1) each column must have a name. Within one table, each column name must be unique 2) within one table, each row must be unique 3) within each row, each value in each column must be single valued. Multiple values of the content represented by the column are not allowed in any rows of the table 4) all values in each column must be from the same (predefined) domain 5) order of columns is irrelevant 6) order of rows is irrelevant
Foreign key
a column in a relation that refers to a primary key column in another (referred) relation
Primary key
a column whose value is unique for each row. It is underlined
Composite primary key
a primary key that is composed of multiple attributes
Entity integrity constraint
a relational database rule that states that all primary keys columns must have values
Mapping unary M:N relationship
another relation is created to represent the M:N relationship itself. This new relation has two foreign keys, both of them corresponding to the primary key of the relation representing the entity involved in the unary M:N relationship. Each of the foreign keys is used as part of the composite primary key of the new relation
Mapping M:N relationships
another relation is created to represent the M:N relationship itself. This new relation has two foreign keys, corresponding to the primary keys of the two relations representing the two entities involved in the M:N relationship. The two foreign keys form the composite primary key of the new relation
Mapping entities with derived attributes
derived attributes are NOT mapped as part of the relational schema
Column
known as a field or attribute
Row
known as a record or instances
Mapping 1:1 relationships
mapped in much the same way as 1:M relationships
Relational schema
once database requirements are collected and visualized as an ER diagram, the next step in creating a relational database is to map (convert) the ER diagram into a logical database model
Relation
the main construct in the relational database model. It is known as a relational table
Mapping entities with multivalued attributes
the multivalued attribute is mapped as separate relation that has a column representing the multivalued attribute and a foreign key column referring to the primary key of the relation resulting from the entity itself
Mapping unary 1:M relationships
the relation mapped from an entity involved in a 1:M unary relationship contains a foreign key that corresponds to its own primary key
Mapping 1:M relationships
the relation mapped from the entity on the M side of the 1:M relationship has a foreign key that corresponds to the primary key of the relation mapped from the 1 side of the 1:M relationship
Mapping unary relationships
unary relationships in ER diagrams are mapped into a relational schema in the same way as binary relationships, using a foreign key in cases of 1:M and 1:1 relationships and using a new relation with two foreign keys in the case of an M:N relationship