Converting an E-R Model into a Relational Model

Speaker: We create an E-R model of a system as a first step toward creating a database. The E-R model is a representation of the system that humans can understand and that they can relate to the system being modeled. The next step is to convert the E-R model into a form that is closer to something that the DBMS can translate into a database. One potential problem has to do with many-to-many relationships. Their complexity makes it very difficult to maintain data consistency as data is added to, changed or deleted from a database table. As a result, it usually makes sense to break down what was a many-to-many relationship in the E-R model into two one-to-many relationships between the two original entities and a new intersection entity that lies between them.

Entities in the E-R model are transformed into tables in the corresponding relational model. Consider the example of students in courses that we looked at in the previous segment. The first step is to create a new table named, for example, Stu-Course. Then establish a one-to-many link from student to Stu-Course. Add a second one-to-many link from course to Stu-Course. This eliminates the problems associated with the original many-to-many relationship. Assuming student ID is the primary key of the student table and course ID is the primary key of the course table, the Stu-Course table would have only two attributes. The student ID of the students taking courses and the course ID of the courses the students are taking. In Stu-Course, student ID and course ID are foreign keys and together, they make up the primary key of Stu-Course. There would be a record in Stu-Course for every course that a particular student is taking. There would also be a record in Stu-Course for every student that is taking a particular course.

Once an accurate relational model has been created with no many-to-many relationships remaining, it’s possible to build a database using SQL. However, it’s first wise to consider whether there are any constraints on the characteristics of records that you want to add to a database table or whether there are any business rules that must be obeyed. In the next segment, we’ll look at constraints and business rules.