Sp24_Assignment5_Solution

docx

School

Oklahoma State University *

*We aren’t endorsed by this school

Course

5663

Subject

Computer Science

Date

Apr 3, 2024

Type

docx

Pages

5

Uploaded by SuperOtterPerson63

Report
MSIS 5663 – Advanced Data Wrangling – Spring 2024 Assignment 5 (5 points – Points indicated for each question) Problem 1 : (3 points) (a) Convert the following EER Diagram into a set of 3NF relations and (b) justify that each table is in 3NF. Describe each relation as follows (Example): TableA (att1 , att2 , att3,…) Primary Key (att1, att2) Foreign Key att7 references TableB (batt1) … Alternate Key(s): att4 Solution: 1) (0.3 points) Create Tables for all strong entities; note their primary key (underlined) and other attributes. Table Primary Key Other attributes Staff staffNo name, extensionTelNo ParkingLot parkingLotName Location, capacity, noOfFloors Space spaceNo 2) (0.3 points) Implement 1:1 binary relationship. We have Staff and Space with a 1:1 relationship, with optional participation on both sides. Following our rules, we would, designate either Staff or Space as parent entity and place its primary key as a foreign key in the other entity. We choose to StaffNo as foreign key in the Space table. Table Primary Key Other attributes Staff staffNo name, extensionTelNo ParkingLot parkingLotName Location, capacity, noOfFloors Space spaceNo staffNo Foreign key staffNo references Staff(staffNo) 3) (0.4 points) Implement 1:* binary relationship. We have ParkingLot and Space with a 1:* relationship. Following our rules, we would put the primary key of ParkingLot (parent) as a foreign key in Space (child). Table Primary Key Other attributes Staff staffNo name, extensionTelNo ParkingLot parkingLotName Location, capacity, noOfFloors Space spaceNo staffNo, parkingLotName ParkingLotName is (PK)
Foreign key ParkingLotName references parkingLot (parkingLotName) Foreign key staffNo references Staff(staffNo) 4) (0.5 points) Implement (Mandatory, OR) specialization of Space – Covered, Uncovered. Based on our rules, we will create two tables CoveredSpace and UncoveredSpace containing all the attributes (and foreign keys) of the superclass Space. Superclass Space is no longer needed. The primary key of each subclass will the same as the Superclass, Space. Each subclass may also have its own attributes. Table Primary Key Other attributes Staff staffNo name, extensionTelNo ParkingLot parkingLotName Location, capacity, noOfFloors CoveredSpace spaceNo staffNo, parkingLotName, monthlyRate Foreign key ParkingLotName references parkingLot (parkingLotName) Foreign key staffNo references Staff(staffNo) UncoveredSpace spaceNo staffNo, parkingLotName Foreign key ParkingLotName references parkingLot (parkingLotName) Foreign key staffNo references Staff(staffNo) 5) (0.5 points) Implement *:* relationship between CoveredSpace and Staff. Based on our rules, we will create a new table (we call it StaffCoveredSpace) whose primary key is the primary key of the two parent tables, i.e., (staffNo, spaceNo), and contains the attributes belonging to the relationship (dateRequired, visitorVLicenceNo) Table Primary Key Other attributes Staff staffNo name, extensionTelNo ParkingLot parkingLotName Location, capacity, noOfFloors CoveredSpace spaceNo staffNo, parkingLotName, monthlyRate Foreign key ParkingLotName references parkingLot (parkingLotName) Foreign key staffNo references Staff(staffNo) UncoveredSpace spaceNo staffNo, parkingLotName Foreign key ParkingLotName references parkingLot (parkingLotName) Foreign key staffNo references Staff(staffNo) StaffCoveredSpace (staffNo , spaceNo ) dateRequired, visitorVLicenceNo Foreign key staffNo references Staff(staffNo) Foreign key spaceNo references CoveredStaff(spaceNo)
6) (0.5 points) Describe each of the final relations (tables) as follows: TableA (att1 , att2 , att3,…) Primary Key (att1, att2) Foreign Key att7 references TableB (batt1) … Alternate Key(s): att4 7) (0.5 points) The final set of tables in 5) have to be checked for 3NF . 1) Staff: StaffNo is the primary key -> name, extensionTelNo. It is in 1NF (no repeating groups). It is in 2NF because there can be no partial dependency with a single attribute primary key. It is in 3NF because name does not determine extensionTelNo. 2) ParkingLot: parkingLotName is the primary key -> Location, capacity, noOfFloors. It is in 1NF (no repeating groups). It is in 2NF because there can be no partial dependency with a single attribute primary key. It is in 3NF because none of the non-key attributes (Location, capacity, noOfFloors) determine the other non-key attributes. 3) CoveredSpace: spaceNo is the primary key -> staffNo, parkingLotName, monthlyRate. It is in 1NF (no repeating groups). It is in 2NF because there can be no partial dependency with a single attribute primary key. It is in 3NF because it has only one non-key attribute monthlyRate (the other non-key attributes are foreign keys). 4) UncoveredSpace: spaceNo is the primary key -> staffNo, parkingLotName. It is in 1NF (no repeating groups). It is in 2NF because there can be no partial dependency with a single attribute primary key. It is in 3NF because it has no non-key attributes (the other non-key attributes are foreign keys). 5) StaffCoveredSpace: (staffNo, spaceNo) is the primary key -> dateRequired, visitorVLicenceNo. It is in 1NF (no repeating groups). It is in 2NF because dateRequired, visitorVLicenceNo depend on the entire composite primary key (staffNo, spaceNo). It is in 3NF because dateRequired, visitorVLicenceNo do not determine each other (the other non-key attributes are foreign keys).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Problem 2 : (2 points) Convert the following EER Diagram into a set of 3NF relations and (b) justify that each table is in 3NF. Solution: 1) (0.2 points) Create Tables for all strong entities; note their primary key (underlined) and other attributes. Table Primary Key Other attributes Book ISBN Title, edition, yearPublished BookCopy copyNo status Borrower borrowerNo Name, address BookLoan loanNO dateOut, dateReturned 2) (0.3 points) Implement 1:* binary relationships. (a) borrowerNo of Borrower (parent) becomes foreign key in BookLoan (child) (b) copyNo of BookCopy (parent) becomes foreign key in BookLoan (child) (c) ISBN of Book (parent) becomes foreign key in BookCopy (child) Table Primary Key Other attributes Book ISBN Title, edition, yearPublished BookCopy copyNo Status, ISBN Foreign key ISBN references Book(ISBN) Borrower borrowerNo Name, address BookLoan loanNO dateOut, dateReturned, borrowerNo, copyNo Foreign key borrowerNo references Borrower(borrowerNo) Foreign key copyNo references BookCopy(copyNo) 3) (0.5 points) Implement (Mandatory, And) specialization of BookToLoan and BookToSell. Applying our rules, we need a single table with type discriminators (flags) to distinguish each subclass tuple. That is, the BookCopy table will have all the attributes (loanPeriod, dateNotForLoan, sellingPrice) and two type discriminators pLoan and pSell. If the book copy is only for loan, then pLoan = 1, and pSell = 0. If the book copy is only for sale, then pSell = 1 and pLoan = 0. If the book copy can be loaned or sold, then both pLoan and pSell will be 1. Table Primary Key Other attributes Book ISBN Title, edition, yearPublished
BookCopy copyNo Status, ISBN, loanPeriod, dateNotForLoan, sellingPrice, pLoan, pSell Foreign key ISBN references Book(ISBN) Borrower borrowerNo Name, address BookLoan loanNO dateOut, dateReturned, borrowerNo, copyNo Foreign key borrowerNo references Borrower(borrowerNo) Foreign key copyNo references BookCopy(copyNo) 4) (0.5 points) Describe each of the final relations (tables) as follows: TableA (att1 , att2 , att3,…) Primary Key (att1, att2) Foreign Key att7 references TableB (batt1) … Alternate Key(s): att4 5) (0.5 points) In your own words, justify why EACH table in step 5 is in 1NF, 2NF and 3NF. Students need to show that each table has no partial or transitive dependency just as in question 1.

Related Documents

NP_EX365_2021_CT8c_FelippeFigueiredo_Report_1.xlsx
Naputi, Rickyjoseph DISN141 WK4 .docx
Assignment 1 Solution.docx
CSD203Inclassassignment4.pdf
Lab11_R copy.docx
Lab 1.docx
mccaffery_a20.docx
AE-201-Q2.pdf
asst3.pdf
a1_solutions.pdf
asst2-soln.pdf
mccaffery_a12.docx
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS