Database Systems Lecture 14 Chapter 5 The Relational

Database Systems Lecture # 14 Chapter # 5 The Relational Data Model and Relational Database Constraints

Candidate key n n n In some instances, an entity will have more than one attribute that can serve as a primary key. Any key or minimum set of keys that could be a primary key is called a candidate key. Once candidate keys are identified, choose one, and only one, primary key for each entity. Choose the identifier most commonly used by the user as long as it conforms to the properties listed above for the primary key. Candidate keys which are not chosen as the primary key are known as alternate keys.

Candidate key n n An example of an entity that could have several possible primary keys is Employee. Let's assume that for each employee in an organization there are three candidate keys: Employee ID, Social Security Number, and Name.

Candidate key n n n Name is the least desirable candidate. While it might work for a small department where it would be unlikely that two people would have exactly the same name, it would not work for a large organization that had hundreds or thousands of employees. Moreover, there is the possibility that an employee's name could change because of marriage. Employee ID would be a good candidate key as long as each employee were assigned a unique identifier at the time of his/her hiring. Social Security would work best since every employee is required to have one before being hired.

Composite Keys n n n Sometimes it requires more than one attribute to uniquely identify an entity. A primary key that made up of more than one attribute is known as a composite key. Figure 1 shows an example of a composite key. Each instance of the entity Work can be uniquely identified only by a composite key composed of Employee ID and Project ID. Employee ID Project ID Hours_Worked 1 1 200 1 2 120 2 3 142 2 6 24 3 5 54 3 9 25 Example of Composite Key

Primary Key Migration n n Dependent entities (weak entities), entities that depend on the existence of another entity for their identification, inherit the entire primary key from the parent entity (strong entity). Every entity within a generalization/specialization hierarchy inherits the primary key of the root generic entity.

Define Key Attributes n n Once the keys have been identified for the model, it is time to name and define the attributes that have been used as keys. There is no standard method for representing primary keys in ER diagrams. For this document, the name of the primary key followed by the notation (PK) is written inside the entity box. Entities with Key Attributes

Validate Keys and Relationships n Basic rules governing the identification and migration of primary keys are: ¨ Every entity in the data model shall have a primary key whose values uniquely identify entity instances. ¨ The primary key attribute cannot be optional (i. e. , cannot have null values). ¨ The primary key cannot have repeating values. This is known as the No Repeat Rule. ¨ Entities with compound (composite) primary keys cannot be split into multiple entities with simpler primary keys. This is called the Smallest Key Rule. ¨ Two entities may not have identical primary keys with the exception of entities within generalization hierarchies. ¨ The entire primary key must migrate from parent entities to child entities and from supertype, generic entities, to subtypes, category entities.

Foreign Keys n n n A foreign key is an attribute that completes a relationship by identifying the parent entity. Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity. Every relationship in the model must be supported by a foreign key.

Identifying Foreign Keys n n Every dependent and category (subtype) entity in the model must have a foreign key for each relationship in which it participates. Foreign keys are formed in dependent and subtype entities by migrating the entire primary key from the parent or generic entity. If the primary key is composite, it may not be split. Foreign keys attributes are indicated by the notation (FK) beside them

Primary Key Foreign Key (implements 1: N relationship between customer and order) Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M: N relationship between order and product)

Transforming ER Diagrams into Relations Mapping Regular Entities to Relations Simple attributes: E-R attributes map directly onto the relation 2. Composite attributes: Use only their simple, component attributes 3. Multivalued Attribute - Becomes a separate relation with a foreign key taken from the superior entity 1.

Figure 5 -8: Mapping a regular entity (a) CUSTOMER entity type with simple attributes (b) CUSTOMER relation

Figure 5 -9: Mapping a composite attribute (a) CUSTOMER entity type with composite attribute (b) CUSTOMER relation with address detail

Figure 5 -10: Mapping a multivalued attribute (a) Multivalued attribute becomes a separate relation with foreign key (b) 1–to–many relationship between original entity and new relation

Transforming EER Diagrams into Relations (cont. ) Mapping Weak Entities ¨ Becomes a separate relation with a foreign key taken from the superior entity ¨ Primary key composed of: n Partial identifier of weak entity n Primary key of identifying relation (strong entity)

NOTE: the domain constraint for the foreign key should NOT allow null value if DEPENDENT is a weak entity Foreign key Composite primary key

Summary n n Primary and foreign keys are the most basic components on which relational theory is based. Each entity must have an attribute or attributes, the primary key, whose values uniquely identify each instance of the entity. Every child entity must have an attribute, the foreign key, that completes the association with the parent entity. Transforming Entities into Relations