Normalize the following – draw [or write them in the standard format] the set of
ID: 3906174 • Letter: N
Question
Normalize the following – draw [or write them in the standard format] the set of tables that puts these into 3NF.
The sample data provided below for a student-tutor scheduling database is included in one table. Put the data in this table into 3rd normal form (3NF).
Functional Dependencies are:
SessionID
StudentID
StudentName
Date
Topic
TutorID
Room
Book
TutEmail
S1
St1
Smith
2/6
FIN
Tut1
629
Deumlich
tut1@fhbb.ch
S2
St1
Smith
3/7
SQL
Tut3
631
Kroenke
Tut3@fhbb.ch
S4
St4
Jones
2/9
FIN
Tut1
629
Deumlich
tut1@fhbb.ch
S3
St2
Jackson
2/10
ACC
Tut3
632
Deumlich
Tut3@fhbb.ch
S5
St2
Jackson
3/1
SQL
Tut5
621
Kroenke
Tut5@fhbb.ch
The Gill Art Gallery wishes to maintain data on the sales of their paintings. The art gallery buys and sales paintings so it is conceivable that they could sale the same painting twice. Normalize the Gill Art Gallery Sales Database as represented in sample data below.
CustName
CustPhone
ArtistId
ArtistName
ArtTitle
ArtMedium
SaleDate
SalePrice
Tom
206-214-8899
03
Channing
Laugh with Teeth
Oil
9/17/2020
7000.00
John
449-643-6255
15
Frings
South toward the Sea
Water Color
5/11/2021
1800.00
Tom
206-214-8899
03
Channing
At the Movies
Acrylic
4/12/2020
5550.00
Jackson
515-776-7453
15
Frings
South toward the Sea
Water Color
8/11/2022
9500.00
Identify functional dependencies. {These are your business rules – in this case, you will want to make those rules by looking the data – some questions include – How will you identify customers, works of arts and their artists? - Can artists have more than one work of art? Can customers buy more than one work of art?
Customer [custno, cust_street, cust_cit, cust_prov, cust_pstlcd, cust_phone]
Normalize the listing – with just attribute names into 3NF.
For the following, answer whether Tables are in 3NF ? Highlight in yellow either Yes or No. If not, explain why? State the normalization rule that is violated. Rules can be:
A) No multi-valued attribute
B) Not dependent on the whole primary key
C) Dependencies between non-key attributes.
1) Employee (ssn, Name, Salary, Address, ListOfSkills)
Yes, No. Rule: ________________________________________
2) Vehicle (LicensePlate, Brand, Model, PurchasePrice, Year, OwnerSSN, OwnerName)
Yes, No Rule _______________________________________
3) Employee (ssn, Name, Salary, did)
Yes, No. Rule: __________________________________________
4) Customer (Cust_Id, Name, Salesperson, Region) where Salesperson determines Region.
Yes, No. Rule: ____________________________________
5) Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo -> ItemName
Yes, No. Rule: _______________________________________
SessionID
StudentID
StudentName
Date
Topic
TutorID
Room
Book
TutEmail
S1
St1
Smith
2/6
FIN
Tut1
629
Deumlich
tut1@fhbb.ch
S2
St1
Smith
3/7
SQL
Tut3
631
Kroenke
Tut3@fhbb.ch
S4
St4
Jones
2/9
FIN
Tut1
629
Deumlich
tut1@fhbb.ch
S3
St2
Jackson
2/10
ACC
Tut3
632
Deumlich
Tut3@fhbb.ch
S5
St2
Jackson
3/1
SQL
Tut5
621
Kroenke
Tut5@fhbb.ch
Explanation / Answer
1. No, Rule A is violated as ListOfSkills is multivalued attribute.
2, No Rule C) Dependencies between non-key attributes. i.e. OwnerSSN and OwnerName
3. Yes. no rule is violated assuming employee can belong to only one department.
4. No. Rule C Dependencies between non-key attributes. i.e. SalesPerson and Region
5. No Rule B Not dependent on the whole primary key . because of partial dependency ItemNo -> ItemName
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.