Here is Your Sample Download Sample 📩
Introduction
The above ER model shows the generalization-specialization hierarchy for the given motor-vehicle scenario. In this design, generalized classes hold the common attributes and specialized attribute holds the attributes which are unique for that class (Saha Bagui & Earp, 2022). As Car and Truck have a common attribute chassis number, so another level of generalization is achieved through an intermediate class Axle vehicle.
For the given ER diagram, the relational database model in terms of table schema is shown in the following. It also specifies the primary key and foreign key for each table (Elmasri, 2018).
This relationship is in 1NF if we consider C1, C2, … C5 are atomic.
This relationship is not in 2NF, as we have composite primary key {C1, C3} and C2 is partially dependent on PK. (Patni et al., 2022)
So 2NF Representation is as follows
R1> {C1 (PK), C2}
R2 > {C1,C3, C4, C5}
Now R1 is also in 3NF as no transitive functional dependency is present there
R2 is not in 3 NF as {C1,C3} à C5 is a transitive dependency as {C1,C3} à C4 and C4 à C5
R2 can be decomposed into R3 and R4 as follows:
R3 à {C1,C3, C4}
R4 à {C4, C5}
The final 3NF representation of the given relationship is shown in the following
|
Table Name |
Attribute Details |
|
EMPLOYEE |
empno (PK), name, hire_date |
|
FULL TIME EMPLOYEE |
empno(FK: ref àEmployee), fempno (PK), salary |
|
PART TIME EMPLOYEE |
empno(FK: ref àEmployee),pempno (PK), type |
|
FACULTY |
fempno(FK: ref àFULL TIME EMPLOYEE) (PK), degree, subjectofinterest |
|
STAFF |
fempno(FK: ref àFULL TIME EMPLOYEE), Classification |
|
TEACHING |
pempno(FK: ref àPART TIME EMPLOYEE) (PK), stipend |
|
CASUAL FACULTY |
pempno(FK: ref àPART TIME EMPLOYEE), hourly rate |
|
Table Name |
Attributes |
|
Monuments |
Mid, Name, istouristinterest, isreligious, ishistoric |
|
Hotels |
Hid, name, category, noofroom |
|
Tours |
Tid, name, tariff, timing, stay |
|
Hotel Available |
Hid (FK: ref àHotels), Mid (FK: ref à Monuments) |
|
Tours Covered |
Tid (FK: ref àTours), Mid (FK: ref à Monuments) |
REFERENCES
Elmasri, R. (2018). Data Management Fundamentals: Database Management System. Encyclopedia of Database Systems, 784–785.
Patni, J. C., Sharma, H. K., Tomar, R., & Katal, A. (2022). Database management system: An evolutionary approach. Chapman & Hall/CRC Press.
Saha Bagui, S., & Earp, R. W. (2022). The basic ER diagram: A data modeling schema. Database Design Using Entity-Relationship Diagrams, 71–105.
Fill The Form