An excellent read:
https://www.tutorialcup.com/dbms/er-model-into-tables.htm
Covers almost everything.
SUMMARY
The basic rules for converting the ER diagrams into tables are:
- Convert all the Entities in the diagram to tables: All the entities represented in the rectangular box in the ER diagram become independent tables in the database.
- All single-valued attributes of an entity is converted to a column of the table: All the attributes, whose value at any instance of time is unique, are considered as columns of that table.
- The key attribute in the ER diagram becomes the Primary key of the table.
- Declare the foreign key column, if applicable: attribute COURSE_ID in the STUDENT entity is from COURSE entity. Hence add COURSE_ID in the STUDENT table and assign it a foreign key constraint. COURSE_ID and SUBJECT_ID in LECTURER table form the foreign key column. Hence by declaring the foreign key constraints, the mapping between the tables are established.
- Any multi-valued attributes are converted into the new table: A hobby in the Student table is a multivalued attribute. Any student can have any number of hobbies. So we cannot represent multiple values in a single column of STUDENT table. We need to store it separately, so that we can store any number of hobbies, adding/ removing/deleting hobbies should not create any redundancy or anomalies in the system. Hence we create a separate table STUD_HOBBY with STUDENT_ID and HOBBY as its columns. We create a composite key using both the columns.
- Any composite attributes are merged into the same table as different columns: Address is a composite attribute. It has Door#, Street, City, State, and Pin. These attributes are merged into STUDENT table as individual columns.
- One can ignore derived attribute since it can be calculated at any time: In the STUDENT table, Age can be derived at any point in time by calculating the difference between DateOfBirth and the current date. Hence we need not create a column for this attribute. It reduces the duplicity in the database.
some of the special cases
Converting Weak Entity:
A weak entity is also represented as a table. All the attributes of the weak entity form the column of the table. But the key attribute represented in the diagram cannot form the primary key of this table. We have to add a foreign key column, which would be the primary key column of its strong entity. This foreign key column along with its key attribute column forms the primary key of the table.
Representing 1:1 relationship
We have LECTURER teaches SUBJECT relation. It is a 1:1 relation. i.e.; one lecturer teaches only one subject. We can represent this case in two ways
-
Create a table for both LECTURER and SUBJECT. Add the primary key of LECTURER in the SUBJECT table as a foreign key. It implies the lecturer name for that particular subject.
-
Create a table for both LECTURER and SUBJECT. Add the primary key of SUBJECT in LECTURER table as a foreign key. It implies the subject taught by the lecturer.
In both the case, the meaning is same. The foreign key column can be added in either of the tables, depending on the developer’s choice.
Representing 1:N relationship
Consider SUBJECT and LECTURER relation, where each Lecturer teaches multiple subjects. This is a 1: N relation. In this case, the primary key of LECTURER table is added to the SUBJECT table. i.e.; the primary key at 1 cardinality entity is added as foreign key to N cardinality entity
Representing M:N relationship
Consider the example, multiple students enrolled for multiple courses, which is M:N relation. In this case, we create STUDENT and COURSE tables for the entities. Create one more table for the relation ‘Enrolment’ and name it as STUD_COURSE. Add the primary keys of COURSE and STUDENT into it, which forms the composite primary key of the new table.
Both the participating entities are converted into tables, and a new table is created for the relation between them. Primary keys of entity tables are added into the new table to form the composite primary key. We can add any additional columns if present as an attribute of the relation in ER diagram.
