Welcome to lesson two of module ten on schema conversion. I'm going to start with two practical questions about the difference between the ERD notation and the relational data model. What is the most prominent difference between ERD notation and the relational data model? What conversion rules involve this difference? Lesson two clarifies your understanding of the entity relationship model in a relational data model. You will learn rules that convert from an ERD to a table design. Applying these rules will clarify your understanding of the differences between the models and help you implement ERDs in relational DBMSes. The objectives in this lesson involve specific concepts and skills for the first step of logical database design. You should be able to apply each rule to an ERD and use the rules in the specified order. After getting some practice with the rules, you should clarify your understanding of the differences between the entity relationship model and the relational data model. Conversion from an ERD to a table design is important because of industry practice. Database design tools support varied notations for ERDs. It is common practice to use a database design tool as an aid in developing an ERD. Because most commercial DBMSes use the relational model, you must convert an ERD to relational tables to implement your database design. Even if you use a database design tool to perform normalization, you should still have the basic understanding of the conversion process. Understanding the conversion rules improves your understanding of the entity relationship model, and helps you avoid the common error of using foreign keys in an ERD. This lesson covers four conversion rules. The rules should be applied in the order noted in the diagram. The entity type rule converts each entity type into a table. The primary key of the entity type, if not weak, becomes the primary key of the table. The attributes of the entity type become columns in a table. This rule should be applied first, before the relationship rules. The one-to-many relationship rule converts relationships into foreign keys. A foreign key should be placed into the child table. The child table corresponds to the entity type near the crow's foot symbol. The many-to-many relationship rule converts each many-to-many relationship into a table with a combined primary key. The attributes of the many-to-many relationship become columns in a table. The identification dependency rule is the most subtle. It adds components to the primary key of a table. Examples in the remainder of this lesson will give you practice with the rules on small ERDs. With some practice, the rules will become easy to use. Lesson three provides practice on larger ERDs. Let's apply the entity type and one-to-many relationship rules to the small ERD. We first apply the entity type rule, and then the one-to-many relationship rule. Note that the result shows abbreviated CREATE TABLE statements to emphasize the major points. The entity type rule converts the course in offering entity types into the course in offering tables along with the primary key and other columns of each table. The one-to-many relationship rule converts the has relationship into the CourseNo FOREIGN KEY column in the offering table. Note that the offering table is a child table corresponding to the entity type, nears the crow's foot symbol. Because the has relationship is mandatory for offering entities, the CourseNo column should have a NOT NULL constraint. This small ERD requires the many-to-many relationship rule. Note that the result shows abbreviated CREATE TABLE statements to emphasize the major points. The entity type rule converts the OFFERING and STUDENT entity types into the offering in Student tables, along with the primary key and other columns of each table. The many-to-many relationship rule converts the enrolls in relationship into the Enrollment table. The Enrollment table is a combined primary key consisting of student number and offer number. Both primary key components are also foreign keys. Note that the name change, enrolls in to enrollment, is optional. Typically table names are nouns, so renaming was used. Not null constraints are not used because a primary key constraint implies that null values are not allowed. This small ERD requires the identification dependency rule. The table design is identical to example two, with the many-to-many relationship, but the derivation is different. The table design contains three tables, student, offering, and enrollment, with the same columns and constraints. The entity type rule is applied three times for the student, offering and enrollment tables. The one-to-many relationship rule is applied twice to create foreign keys, student number and offer number, in the enrollment table. The identification dependency rule is applied twice to add student number and offer number as components of the primary key in the enrollment table. Now, let's wrap up lesson two about conversion rules. This lesson demonstrated four basic rules converting an ERD into a table design. You learned about the actions of each rule along with the order of applying new rules. Several small examples were shown to depict usage of the rules. In the answer to the opening question, the most prominent difference between the entity relationship model and the relational model is the named relationships versus foreign keys. Two conversion rules, the one-to-many relationship rule and the many-to-many relationship rule involve this essential difference. The one-to-many relationship rule converts a one-to-many relationship into a foreign key in a child table. The many-to-many relationship rule converts a many-to-many relationship into a table with a combined primary key, consisting of two foreign keys. Lesson three solidifies your understanding of the conversion rules with larger examples. You should also work practice and graded problems to master the conversion process.
