
Quick Contact
DBMS Tutorial
- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
Reduction of ER-Diagram into Table
This topic will describe how to reduce the Entity-Relationship (ER) diagram into Tables.
Example
An enterprise has different departments. Each department has supervisors and partly one employee. Employees have to be assigned to at least one, however, possibly more departments. At least one employee is authorized to a project, but an employee can be on holiday and not authorize to any project. The essential information areas are the name of the departments, projects, supervisors, and employees, including the supervisor and employee-no and the specific project-no.
Solution
We now informally represent the steps of an algorithm for Entity-Relationship to relational mapping. Entity-Relationship diagram for a company database is shown below:
Now we can convert this to a similar schema
The COMPANY relational schema display below can be derived from the Entity-Relationship schema by following these steps. We illustrate every step by using the use of examples from the COMPANY schema
Employee
FNAME | MINIT | LNAME | SSN | BDATE | ADD | SEX | SAL | SUPERSSN | DNO |
Department
DNAME | DNUMBER | MGRSSN | MGRSTARTDATE |
Dept Locations
DNUMBER | DLOCATION |
Project
PNAME | PNUMBER | PLOCATION | DNUM |
Works on
ESSN | PNO | HOURS |
Dependent
ESSN | DEPENDENT_NAME | SEX | BDATE | RELATIONSHIP |
Steps for Reduction of Entity-Relationship Diagram into Tables
Step1
Create a relation R that contains all the simple attributes of E for each regular entity type E. In the Entity-Relationship schema, provide only the simple element attributes of a composite attribute.
In this example, we generate the relation EMPLOYEE, PROJECT, and DEPARTMENT.
The foreign key and relationship attributes are not protected yet; they will be inserted during subsequent steps. These sometimes are known as ‘Entity Relations.’
Step2
Create a relation R, and contain all simple attributes of W as attributes of R, for each weak entity type W in the entity-relationship schema with owner entity type E. Also, provide as foreign key attributes of R in the primary key attribute (s) of the relation (s) that correlates to the owner entity type (s); the primary key of R is the consolidation of the partial key of the weak entity type W if any and the primary key (s) of the owner (s).
In this example, contain the primary key of the EMPLOYEE relation, which correlates to the owner entity type-as a foreign key attribute of DEPENDENT.
Step3
Identify the relation S and T that corresponds to the entity type participating in R, for every binary relationship one to one relation type R in the entity-relationship schema. It is always good to select an entity type with total participation in R in the appearance of S. Also, to contain all the simple attributes (or simple elements of composite attributes) of the one to one relationship type R as an attribute of S.
An alternative mapping of one to one relationship type is feasible by combining the relationship and the two entity types and into a single location. This is particularly suitable when the entity types do not participate in any other relationship type and when both participations are TOTAL.
Step4
Recognize the association that define the participating entity type at the N-side of the relationship technique for every regular (strong) binary association one to many relationship type R. It contains any simple attributes (simple elements of composite attributes) of the one to many relationship types as attributes of S.
Step5
Create a new relation S to define R for each binary relation many to many relationship type R. It contains any simple attributes of the many relationship types (simple elements of composite attributes) as attributes of S and also contains as a foreign key attribute in S the primary keys of the relation that defines the participating entity type and there combination will structure the primary keys of S.
Step6
Create a new relation R that contains an attribute correlating to A plus the primary key attribute K (as a foreign key in R) of the relationships that describe the entity type or relationship type, which has A as an attribute. For each multivalued attribute A, and if the multivalued attribute is complex, we contain its easy elements. The primary key of R is the mixture of A and K.
Step7
Create a new relation S to symbolize R, for each n-ary relationship type R n>2. It contains any simple attributes of the n-ary relationship type (simple elements of composite attributes) as attributes of S and also contains as a foreign key attribute in S the primary keys of the relation that define the participating entity types. The primary key of S is generally a combination of all the foreign keys that define the relations describing the participating entity types.
Apply now for Advanced DBMS Course