For each of the following EER diagrams from Chapter 3: I. Transform the diagram into a...
For each of the following EER diagrams from Chapter 3:
I. Transform the diagram into a relational schema that shows referential integrity constraints (see Figure 4-5 for an example of such a schema).
II. For each relation, diagram the functional dependencies (see Figure 4-23 for an example).
III. If any of the relations are not in 3NF, transform them to 3NF.
a. Figure 3-6b
b. Figure 3-7a
c. Figure 3-9
d. Figure 3-10
e. Figure 3-11
Reference figure 4-5
Reference figure 4-23
Step-by-Step Solution
Referential integrity:
• Referential integrity is supported by RDBMS (Relational Database Management System).
• Relational database is basically a collection of the tables where every table has a specific key assigned to it which is known as primary key.
• The values in the each row of a table are unique. Two tables are related to each other with the help of foreign key.
• Referential integrity is a special feature of RDBMS, which ensures the data entered by the user is valid and consistent.
• It is done with the help of primary and foreign key, which links the tables together and it checks the whether the values are correctly synchronized.
Functional dependency:
• An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.
• In a table one attribute is functionally dependent on another attribute to take one value.
Third normal form:
• For the third normal form the first and basic requirement is that the model should be in the first and second normal form.
• The value of any non-primary key attributes will not depend on any other non-primary key attributes.
o If any non-primary key attributes are depend on any other non-primary key attribute then it should moved or deleted.
o It is termed as transitive dependency.
Note:
• In the diagrams, to indicate primary and foreign keys notational conventions are used.
o The attributes noted with underlined name are primary key values.
o The attributes noted with italics letters are foreign key values.
(a) Transforming the figure 3-6b from the textbook to its relational schema.
• The diagram given below describes the relation amongst various types of vehicles. These are of different parameters in the vehicle segments.
• The Enhanced E-R model is transformed into three relations. They are VEHICLE, CAR, and TRUCK and it is shown below along with its referential integrity and normal form.
Explanation:
In the relation VEHICLE ,
• The attributes are VehicleID, Price, Make, Model, and EngineDisplacement.
• Here, VehicleID is the primary key.
• The values provided for each attribute is unique to show referential integrity.
Functional dependency in VEHICLE relation:
• Price, Make, Model, and EngineDisplacement are functionally dependent on VehicleID.
Normal form:
• The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
• The attributes are CVehicleID and NoOfPassengers.
• CVehicleID is the foreign key to refer the VEHICLE relation.
Functional dependency in CAR relation:
• NoOfPassengers is functionally dependent on CVehicleID.
Normal form:
• The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
In the relation TRUCK ,
• The attributes of the relation are TVehicleID, CabType and Capacity.
• TVehicleID is the foreign key, which is used to refer VEHICLE relation.
Functional dependency in TRUCK relation:
• CabType and Capacity are functionally dependent on TVehicleID.
Normal form:
• The attributes Cabtype and Capacity are not dependent on TVehicleID. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
(b) Transforming the figure 3-7a from the textbook to its relation.
• The diagram given below describes the relation between the responsible physician and patient. It shows the inter-dependency between both.
• The Enhanced E-R model is transformed into five relations. They are RESPONSIBLE PHYSICIAN, PATIENT, OUTPATIENT, RESIDENT PATIENT, and BED and it is shown below along with its referential integrity and normal form.
Explanation:
In the relation RESPONSIBLE PHYSICIAN ,
• PhysicianID is the attribute and it acts as the primary key.
• There is no functional dependency.
Normal form:
• The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.
In the relation PATIENT ,
• PatientID, PatientName, AdmitDate, and PhysicianID are the attributes.
• PatientID is the primary key.
• PhysicianID is the foreign key to provide referential integrity for relations PATIENT and RESPONSIBLE PHYSICIAN.
• PatientName, AdmitDate, and PhysicianID are functionally dependent on PatientID.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation OUTPATIENT ,
• OPatientID and CheckbackDate are the attributes.
• OPatientID is the primary key.
Functional dependency in OUTPATIENT relation:
• CheckBackDate is functionally dependent on OPatientID.
Normal form:
• Other attributes does not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation RESIDENT PATIENT ,
• RPatientID, DateDischarged and BedID are the attributes.
• RPatientID is the primary key.
• BedID is the foreign key which refers BED table to get the BedID.
Functional dependency in RESIDENT PATIENT relation:
• DateDischarged and BedID is functionally dependent on RPatientID.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation BED ,
• BedID is the attribute and it acts as the primary key.
• There is no functional dependency.
(c) Transforming the figure 3-9 from the textbook to its relation.
• The diagram given below describes the relation between the parts manufactured and the parts purchased.
• They are also associated with the supply of the parts and its supplier.
• The Enhanced E-R model is transformed into five relations. They are PART, SUPPLIES, MANUFACTURED PART, PURCHASED PART, and SUPPLIER and it is shown below along with its referential integrity and normal form.
Explanation:
In the relation PART ,
• The attributes are PartNo, Description, Location, Manufactured and Purchased.
• PartNo is the primary key attribute for the relation.
Functional dependency in PART relation:
• Description, Location, Manufactured and Purchased are functionally dependent on PartNo.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation MANUFACTURED PART ,
• The attributes are MPartNo and RoutingNumber.
• MPartNo is the primary key attribute.
Functional dependency in MANUFACTURED PART relation:
• RoutingNumber is functionally dependent on MPartNo.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation PURCHASED PART ,
• PPartNo is the foreign key and it refers PartNo to get the value.
• Two tables are related to each other with the help of foreign key.
• Hence, FOREIGN KEY REFERENCES statement enforces the integrity constraints.
Normal form:
• There is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation SUPPLIES ,
• PPartNo, SupplierID and UnitPrice are the foreign key attributes.
• SupplierID refers the SUPPLIER relation.
Functional dependency in SUPPLIES relation:
• SupplierID and UnitPrice are functionally dependent on PPartNo.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation SUPPLIER ,
• SupplierID is the primary key attribute.
Normal form:
• There is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
(d) Transforming the figure 3-10 from the textbook to its relation.
• The diagram given below shows the relation between the different types of person amongst each other.
• The Enhanced E-R model is transformed into nine relations. They are PERSON, EMPLOYEE, FACULTY, STAFF, ALUMNUS, DEGREE, STUDENT, UNDERGRADUATE, and GRADUATE and it is shown below along with its referential integrity and normal form.
Explanation:
In the relation PERSON ,
• The attributes are SSN, Name, Gender, Address and DateOfBirth.
• SSN is the primary key attribute for the relation.
Functional dependency in PERSON relation:
• Name, Gender, Address and DateOfBirth are functionally dependent on SSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation EMPLOYEE ,
• The attributes are ESSN, Salary and DateHired.
• ESSN is the primary key attribute for the relation.
Functional dependency in EMPLOYEE relation:
• Salary and DateHired are functionally dependent on ESSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation FACULTY ,
• The attributes are FSSN and Rank.
• FSSN is the primary key attribute for the relation.
Functional dependency in FACULTY relation:
• Rank is functionally dependent on FSSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation STAFF ,
• The attributes are FSSN and Rank.
• FSSN is the primary key attribute for the relation.
• Position is functionally dependent on SSSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation ALUMNUS ,
• ASSN is the primary key attribute for the relation.
In the relation DEGREE ,
• The attributes are ASSN, Degree, Year, Date and Designation.
• Degree is the primary key attribute for the relation.
• ASSN is the foreign key attribute which refers ALUMNUS relation.
Functional dependency in DEGREE relation:
• Degree and Year are functionally dependent on ASSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation STUDENT ,
• The attributes are STSSN and MajorDept.
• STSSN is the primary key attribute for the relation.
Functional dependency in STUDENT relation:
• MajorDept is functionally dependent on STSSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation UNDERGRADUATE ,
• The attributes are UGSSN and ClassStanding.
• UGSSN is the primary key attribute for the relation.
Functional dependency in UNDERGRADUATE relation:
• ClassStanding is functionally dependent on UGSSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
In the relation GRADUATE ,
• The attributes are GRSSN and TestScore.
• GRSSN is the primary key attribute for the relation.
Functional dependency in GRADUATE relation:
• TestScore is functionally dependent on GRSSN.
Normal form:
• Other attributes do not depend on each other. Hence, there is no transitive dependency.
• Therefore, the relation is in third normal form (3NF).
The diagram given below describes the dependency amongst salesperson, customer, product, user and supplier. It shows a long chain. The diagram is as given below:
Explanation:
• The ER diagram is transformed into relations such as:
o SALESPERSON
o TERRITORY
o DOES BUSINESS IN
o CUSTOMER
o NATIONAL CUSTOMER
o REGULAR CUSTOMER
o ORDER
o ORDER LINE
o PRODUCT
o PRODUCT LINE
o RAW MATERIAL
o USES
o SUPPLIES
o SUPPLIE
o VENDOR
o PRODUCED IN
o WROK CENTER
o EMPLOYEE
o MANAGEMENT EMPLOYEE
o UNION EMPLOYEE
o SKILL
o HAS SKILL (EXPERTISE)
o WORKS IN
• Referential integrity is shown in the above diagram.
• All the relations are in 3NF. Since, there is no transitive dependency and no repeated attributes in the relations.
Most questions answered within 3 hours.
-
Calculating the space time for parallel reactions. m-Xylene is reacted over a ZSM-5 zeolit...
-
Determine Vo and ID for the networks of Fig. 2.160.FIG. 2.160
-
The truck travels along a circular road that has a radius of 50 m at a speed of 4 m/s. F...
-
A state legislature enacted a statute that required any motorcycle operator or passenger...
-
A 1024 × 1024 8-bit image with 5.3 bits/pixel entropy [computed from its histogram using E...
-
In Problem 3.3, we estimated the equationwhere we now report standard errors along with th...
-
In each of the following cases, deduce the nature of the light that is consistent with the...
-
Solve Example 20.5 such that the x, y, z axes move with curvilinear translation, Ω = 0 in...
-
In Fig. 6.43, if i = cos 4t and v = sin 4t, the element is:(a)a resistor(b) a capacitor(c)...
-
Sketch vo for each network of Fig. 2.181 for the input shown.FIG. 2.181
-
(Supplement B) Computing and Reporting Cash Flow Effectsof Sale of Plant and EquipmentDuri...
-
A 350-mL spherical flask contains 0.075 mol of an ideal gas at a temperature of 293 K. Wha...