HW4

docx

School

Rochester Institute of Technology *

*We aren’t endorsed by this school

Course

230

Subject

Computer Science

Date

Apr 3, 2024

Type

docx

Pages

7

Uploaded by HighnessValor13716

Report
ISTE-230 Introduction to Database & Data Modeling Homework # 4 – Normalization DUE: Tuesday, February 20 th Name: _____Aedan Lahiff________________________________________ All assignments will be graded with regard to the standards that were discussed in class, which can be found in the Standards Content area. Submit this document edited to include your answers, for the six tasks, to the HW#4 Assignment Folder by the stated deadline. Task #1 (12 points) MUSIC(title , artist, numGrpMembers, year, producer, producerURL, category, categorySales, media , mediaPrice) Business Rules: 1. Each “album” (CD) is uniquely identified by its title. Note that, for the rest of the business rules, the “Title” attribute of MUSIC refers to the name of the “album”. 2. An artist may either be a single person or a band made up of multiple members (the count being recorded in NumGrpMembers, which can be 1). 3. Each album has one release year. 4. Each album is produced by one music production company (producer). 5. Each producer has one company URL. 6. A specific album has only one artist. 7. Each album is classified into one music category (Rock, Country, etc.) 8. Each category is associated with one category sales value, which is the year-to-date sales for that given category. 9. For convenience, the music company sells all of its music at the same price based on the media type. For example, all cassettes are $9.99, all CDs are $16.99, etc. List ALL functional dependencies for the MUSIC relation above, according only to the business rules listed. Use the format A B. Then, for each functional dependency denote with a ‘Y’ or ‘N’ if the respective functional dependency causes 2NF or 3NF violation in the MUSIC relation. Functional Dependencies 2NF violation? 3NF violation? Artist -> NumGrpMembers N Y Title -> Artist Y N Title -> Year Y N Title -> Producer Y N Title -> Category Y N Producer -> ProducerURL N Y Category -> CategorySales N Y ISTE-230 – Introduction to Database and Data Modeling Page 1 of 7 ©Elissa Weeden, Jim Habermas 2020
Media -> MediaPrice Y N Task #2 (9 points) For the relation below, determine the highest normal form the relation is in, the reason, and if necessary normalize the relation, and all resulting relations, through BCNF. Use proper relational notation and include reference statements for any foreign keys. Q1( a, b , c, d ) Functional Dependencies: a, b c, d c d YOUR ANSWER: The highest normal form is 2NF because it satisfies all the properties of 2NF It violates 3NF because c -> d, c and d are both non key attributes Q2(c , d) Q1(a, b , c ) Task #3 (12 points) For the relation below, determine the highest normal form the relation is in, the reason, and if necessary normalize the relation, and all resulting relations, through BCNF. Use proper relational notation and include reference statements for any foreign keys. Q2(a, b , c, d) Functional dependencies: a, b c, d a c b d YOUR ANSWER: The highest normal form is 1NF because it has a singular and simple attribute It violates 2NF because there is a partial dependency where the composite key is treated individually Q2( a, b ) Q3(a , c) Q4(b , d) ISTE-230 – Introduction to Database and Data Modeling Page 2 of 7 ©Elissa Weeden, Jim Habermas 2020
Task #4 (17 points) Given the E-R diagram, the resulting relation, and the functional dependencies below, normalize the ITEM relation and resulting relations through BCNF. Be sure to use proper relational notation and reference statements for foreign keys. Resulting Relation: ITEM(itemID , itemName, name, street, city, state, zipcode, cost, retailPrice, color1, color2, notes, shelfQty, perishable, returnable, description) Functional Dependencies: itemID è itemName, name, street, city, state, zipcode, cost, retailPrice, color1, color2, notes, shelfQty, perishable, returnable, description name è street, city, state, zipcode YOUR ANSWER (Final set of relations normalized to BCNF): ITEM( itemID , itemName, name , cost, retailPrice, notes, shelfQty, perishable, returnable, description) INFO(name , street, city, state, zipcode) COLOR( itemID , color ) ISTE-230 – Introduction to Database and Data Modeling Page 3 of 7 ©Elissa Weeden, Jim Habermas 2020
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
Task #5 (23 points) Given the relation and functional dependencies below, normalize the SALE relation and resulting relations through BCNF. Be sure to use proper relational notation and reference statements for foreign keys. SALE(invoice#, item#, custID, custName, custAddress, itemName, itemPrice, itemQtyPurch, salesPerson#, salesPersonName, subtotal, tax, totalDue) Functional Dependencies: invoice#, item# custID, custName, custAddress, itemName, itemPrice, itemQtyPurch, salesPerson#, salesPersonName, subtotal, tax, totalDue item# itemName, itemPrice invoice# custID, custName, custAddress, salesPerson#, salesPersonName, subtotal, tax, totalDue custID custName, custAddress salesPerson# salesPersonName YOUR ANSWER (Final set of relations normalized to BCNF): SALE(Invoice#, Item#, CustName, CustAddress, ItemName, ItemPrice, ItemQtyPurch, ISTE-230 – Introduction to Database and Data Modeling Page 4 of 7 ©Elissa Weeden, Jim Habermas 2020
Salesperson#, SalespersonName, Subtotal, Tax, TotalDue) SALE(Invoice#) mei BILL(Invoice#) SALE(Item#) mei ITEM_INFO(Item#) SALE(CustID) mei CUST_INFO(CustID) BILL(Invoice#, CustID, CustName, CustAddress, Salesperson#, SalespersonName, Subtotal, Tax, TotalDue) ISTE-230 – Introduction to Database and Data Modeling Page 5 of 7 ©Elissa Weeden, Jim Habermas 2020
BILL(CustID) mei CUST_INFO(CustID) BILL(Saleperson#) mei SALES(Salesperson#) ITEM_INFO(Item#,ItemNam e, ItemPrice) CUST_INFO(CustID,CustNam e, CustAddress) SALES( Salesperson#, SalespersonName) SALE( Invoice#, Item# , ItemQtyPurch) BILL(Invoice# , CustID , Salesperson# , Subtotal, Tax, TotalDue) ITEM_INFO(Item# , ItemName, ItemPrice) CUST_INFO(CustID , CustName, CustAddress) SALES(Salesperson# , SalespersonName) ISTE-230 – Introduction to Database and Data Modeling Page 6 of 7 ©Elissa Weeden, Jim Habermas 2020
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
Task #6 (27 points) Given the relation and functional dependencies below, normalize the relation and resulting relations through BCNF. Be sure to use proper relational notation and reference statements for foreign keys. A(1, 2, 3, 4 , 5, 6, 7, 8, 9, 10) Functional Dependencies: 1, 2, 3, 4->5, 6, 7, 8, 9, 10 1->5, 6 5->1,6 2,3->7,8 7->8 4->9,10 9->10 10->9 YOUR ANSWER (Final set of relations normalized to BCNF): A( 1, 2, 3, 4 ) B(1 , 5, 6) C(2, 3 , 7 ) D(7 , 8) E(4 , 9 ) F(9 , 10) ISTE-230 – Introduction to Database and Data Modeling Page 7 of 7 ©Elissa Weeden, Jim Habermas 2020

Related Documents

Screen Shot 2024-03-26 at 2.53.45 PM.png
Task 5 Presentation.pdf
Screen Shot 2024-03-26 at 2.55.32 PM.png
Lab01_Report_Template.docx
Lab02_Report_Template.docx
Lab04_Report_Template.docx
PE6.docx
HW2.docx
hpe6-a72_5.pdf
HPE6-A72-demo.pdf
Lab2 (1).pdf
Practice2_Spring_2024.docx
Recommended textbooks for you
Text book image
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Systems Architecture
Computer Science
ISBN:9781305080195
Author:Stephen D. Burd
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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781305082168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Systems Architecture
Computer Science
ISBN:9781305080195
Author:Stephen D. Burd
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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Fundamentals of Information Systems
Computer Science
ISBN:9781305082168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS