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