MEC Database Fundamentals Physical Design Part 3 1-to-many, 1-to-1 – – Adding a primary key This is a good time to add something about keys and keys. candidate surrogate Implementing relationships shown in the data model Unary, one-to-many relationship The EMPLOYEE table example has an example of two people with the same name. This is a good time to remind student why attributes, such as a person’s name, can’t be used as the primary key. This is a situation when the name of the foreign key cannot match the primary key because they’re in the same table. One-to-one relationship Note that the one-to-one relationship example in this presentation does not fit with the auto repair shop database used in many examples. This is just a example using LOCATION and EMPLOYEE tables. MEC Database Fundamentals Physical Design Part 4 Many-to-many – – Implementing a many-to-many relationship The example data model is based on the auto repair model. However, the REPAIR DETAIL entity is not shown initially and an INVENTORY entity has been added. Associative entity This is an opportunity to discuss what weak entities are and identifying versus relationships. MEC Database Fundamentals Physical Design Part 5 Multivalued attribute – – Implementing a multivalued attribute. This is another opportunity to discuss what weak entities are. Parent and child entities Concatenated (composite) primary key MEC Database Fundamentals Test Your Knowledge – This presentation asks questions about terminology and how to interpret the data models presented. Questions & Answers: What is the data model term that has this definition? person, place, thing, or event about “A which we need to keep information.” ENTITY What is a field called if it is used to link to a primary key field in another table? FOREIGN KEY A field (or fields) that uniquely identifies each row in a table is called a PRIMARY KEY? CANDIDATE KEY would also be a valid answer.
MEC Database Fundamentals – Physical Design Part 3 – 1-to-many, 1-to-1 Adding a primary key This is a good time to add something about candidate keys and surrogate keys. Implementing relationships shown in the data model Unary, one-to-many relationship The EMPLOYEE table example has an example of two people with the same name. This is a good time to remind student why attributes, such as a person’s name, can’t be used as the primary key. This is a situation when the name of the foreign key cannot match the primary key because they’re in the same table. One-to-one relationship Note that the one-to-one relationship example in this presentation does not fit with the auto repair shop database used in many examples. This is just a example using LOCATION and EMPLOYEE tables. MEC Database Fundamentals – Physical Design Part 4 – Many-to-many Implementing a many-to-many relationship The example data model is based on the auto repair model. However, the REPAIR DETAIL entity is not shown initially and an INVENTORY entity has been added. Associative entity This is an opportunity to discuss what weak entities are and identifying versus relationships. MEC Database Fundamentals – Physical Design Part 5 – Multivalued attribute Implementing a multivalued attribute. This is another opportunity to discuss what weak entities are. Parent and child entities Concatenated (composite) primary key MEC Database Fundamentals – Test Your Knowledge This presentation asks questions about terminology and how to interpret the data models presented. Questions & Answers: What is the data model term that has this definition? “A person, place, thing, or event about which we need to keep information.” ENTITY What is a field called if it is used to link to a primary key field in another table? FOREIGN KEY A field (or fields) that uniquely identifies each row in a table is called a PRIMARY KEY? CANDIDATE KEY would also be a valid answer.