4.1 The steps involved in the design of relational database are
1) Developing the conceptual model – Drawing the ER/ EER diagram 2) Transforming the Conceptual model into a set of relations – relational schema and 3) Normalizing the relations.
Once ER / EER model has been developed for the application , ER/ EER diagram can be mapped into a relational model . ER/ EER diagram consists of the following components.
1) Entities (Strong , Weak) with attributes
2) Attributes may be Single, Simple, composite or multi valued attributes 3) Relationships with cardinality ratios (1:1, 1:N, N: M) 4) Ternary relationship
5) In the case of EER diagram, concept of generalization, categorization and aggregation 4.2 Translation of EER diagram into a set of relations includes the translation of 1) Strong Entity
2) Weak Entity
3) Multi valued attributes
4) Unary relationship
5) Binary relationships (1:1 and 1:M)
6) M: N relationships
7) Ternary relationship
8) Associative entity
9) Generalization / Specialization
10) Categorization (union) and
The transformation includes the translation of all the above components in the following steps 4.2.1 Translation of Entity
Strong Entity :
Each strong entity in the ER / EER diagram will be a relation on translation. The attributes of the entities will be automatically the attributes of the relation. One of the attributes which has unique and single value will be the primary key of the relation. Weak Entity:
Each weak entity will also be translated into a relation. The attributes will be the attributes of the relation By definition the existence of weak entity depends upon the other strong entity. Known as Owner entity This characteristic is reflected in fixing the primary key of the relation corresponding to the weak entity. The primary key will be a COMPOSITE key with one of the attributes of the weak entity and the primary key of the owner entity. Example:
Relation corresponding to strong entity “ Car” is
CAR ( Regd_no , year, make, model, engine type, colour )
Primary key of “ CAR” relation is Regd_no
Relation corresponding to weak entity “ Service ” is Service ( Regd_No , date , charge, type of service, parts changed ) Primary key of “ Service ” relation is ( Regd_no, date) It has been assumed that all the attributes are simple.
4.2.2 Translation of Multi valued attribute:
If any one of the attributes is a multi valued, it will create an additional entity with a composite primary key. For example if the entity “ CAR” has a an attribute “ extra fitting” which is a multi valued one, on translation a new relations “ Car Fittings” will be created as Car Fitting ( Regd_no, Extra fitting)
Relation corresponding to an entity will have only primary key. The foreign key in a relation is due to the relationship between the entities. 4.2.3 Mapping Unary Relationships
Employee Employee is a recursive (unary ) relationship. One Employee manages many Employees means that one of the Employee is a manager. On translation recursive foreign key will be introduced in the same relation Employee. The foreign key , Employee_id , may be renamed as Manager_id to provide clarity.
Employee( Employee_id, Emp_name, date of birth, address, gender, salary, date_join, Manager_id)
4.2.4 Translation of one to one 1: 1 relationship. When the relationship between two entities has 1:1 cardinality ratio, on translation, one of the relations will have a foreign key, which is the primary key of the other relation. In the figure , one manager manages the Branch and one branch has only one manager. There are two possible ways of translating the 1:1 relationship and correspondingly foreign key will be introduced. Placing...