Database — Design: Logical Design (Part 6)
The logical design is about mapping of entities, relationships, and multi-valued attributes into a logical schema.
Wish you already came along the last part Database — Modeling : Entity Relationship Diagram (ERD) (Part 5)
We are going to walk through defined steps to map our conceptual schema (described by ER model) we have just created into logical schema (described by relational model).
When we sketched our ER diagram, we showed that some kind of relationship exists between our entities, but to get closer to actually building this in a database, we need to translate these relationships (along with others like multi-valued attributes) in our database.
All the relationships we have identified are going to be implemented in the database by either creating a new table, or by just creating a new column, or maybe there are some other options we are going to cover here.
So, here are the steps:
1. Mapping of Regular Entity Types
For each entity, create a table that includes all of its simple attributes. Then, choose the primary key, if it’s composite, then a set of simple attributes will together form the primary key.
The composite column will be decomposed into separate simple columns.
2. Mapping of Weak Entity Types
For each weak entity, create a table that includes all of it’s simple attributes. And include a foreign key points to the primary key of the owner entity, where the foreign key and partial key will be the primary key of the weak entity.
A partial key uniquely identify a weak entity for a given owner entity.
3. Mapping of 1:1 Relationship Types
There are three ways:
- Foreign Key approach: Choose the primary key from either one of the entities, and make a foreign key in the other entity referencing the primary key of the first one.
- Merged relation option: Merge both entities, since every row in any entity will have a corresponding row in the other entity.
- Cross-reference or relationship relation option: Create a third table, that has two foreign keys from the primary keys of both entities (overkill!).
4. Mapping of 1:M Relationship Types
Considering the example we have been using, where each employee works for only one department, while a department can have more than one employee.
Now, in order to map this relationship, we add a foreign key in the employee table (many side), which in turn will point to the primary key of the department table (1 side).
Also include any simple attribute of the 1-M relationship in the many side.
Recursive Relationship
Don’t let the recursive relationship trick you!. You would treat it like any other relationship between one entity and another.
In our example, we had a one-to-many recursive relationship called supervision between the employee entity and itself.
In other cases, you may have a many-to-many recursive relationship, or something else.
The same thing goes here, just add a foreign key in the many side (which is the employee table), which points to the primary key of the 1 side (which is also the employee table).
Now, if an employee is supervised by another employee, SUPERSSN will be assigned to the supervisor employee’s id. Otherwise, it’s given null.
More About Foreign Keys
- Foreign key’s values must exist in the primary key at any time. That’s it, a foreign key can’t have a value that’s not in the primary key it refers to.
- Foreign key can have different values, and could be not unique.
- Foreign key may be primary key but, one foreign key can’t be, two foreign keys can; composite primary keys.
- Foreign key may have different name from primary key.
- Foreign key’s data type must match with data type of the primary key.
- Foreign key may contain NULL (based on rules defined by the user).
5. Mapping of M:N Relationship Types
A good example for this relationship, is every employee can work on one or more project, and every project can have one or more employee involved in this project. So, it’s many from both sides.
Now, in order to map this relationship, we create a new table, this table exists only to connect the employee and project tables. You start by adding two foreign keys, each one will point to a primary key of one of the two tables. The two foreign keys together will form the primary key of the new table.
There may be an employee called Adam who works on Project A, and Project B. And another employee who’s working on Project A. But, you can’t have duplicates; meaning, you can’t have two rows with the same employee, and the same project.
Simple Attributes of M:N Relationship
If there are simple attributes related to the M-N relationship, you add them into the new table created. As an example, here we have Hours, that is every employee who works on a specific project has a specific working hours.
6. Mapping of Multi-valued Attributes
Remember? A multi-valued attribute is a set of different values.
As an example, a department may have different locations, thus it will have different location values for each department.
So, we create a new table that has a foreign key points to the primary key of department, and another column represent the multi-valued attribute (one-to-many relationship). This is done for each multi-valued attribute.
The foreign key and the multi-valued attribute together will form the primary key of the new table.
If the multi-valued attribute is a composite attribute; consists of more than one attribute. You decompose the composite attribute into simple ones.
7. Mapping of N-ary Relationship Types
“What if you have a relationship that connects more than two tables?”. It’s almost the same solution as we did in M:N relationship.
Create a new table, that has the foreign keys from the primary keys of all participating entities, the foreign keys together will form the primary keys of the new entity. Also include any simple attribute of that relationship in the new table.
Relational Integrity Constraints
There are some constraints we can define and they are enforced by the DBMS to keep your data valid and meaningful across all of your tables. There are three main constraints:
- Unique Key Constraints: A unique column or a group of columns that uniquely identify each row can’t be duplicated (although they could be null).
- Entity Integrity Constraints: A primary key uniquely identifies each row, can’t be duplicated, and can’t take null.
- Referential Integrity Constraints: A foreign key … (see above).
Deleting rows from the M-side table in one-to-many relationship, or, deleting rows from the linking tables in many-to-may relationship doesn’t violate the referential integrity constraint.
Wrapping Up
What we are going to do next is to take what we have implemented further, organize the tables, make our database easier to work with and more reliable. This process is called “Normalization”, and there are 3 steps to achieve it.
References:
