CIS336 Lab 1 Normal Forms and Entity Relationship Diagrams

Description

In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will next construct a series of dependency diagrams, transforming the evolving data model from First Normal Form (1NF), to Second Normal Form (2NF), and finally to Third Normal Form (3NF). When the model has reached 3NF, you will construct the Entity Relationship Diagram (ERD) depicting the logical design of the database. Your ERD will use Crow’s Foot notation to denote the relationships between tables.
Sample Exercise, With Solution.
Before completing your lab, please review this example problem. The diagram below is a partial depiction of a business spreadsheet for a retail store operation. The store sells books. Books have a 13-digit International Standard Book Number (ISBN), a title, a publisher, and a unit price. Invoices track sales of books. An Invoice contains one or many line items, with each line item reflecting the sale of one or more copies of a specific book. Every publisher has a company name, and a publisher code.
[table]
InvNum, ISBN13, InvDate, BookTitle, PubCode, PubName, Qty, unitPrice
1022, 9781291940336, 2015-03-09, MYSQL Functions ,1001 ,Lulu.com ,3, 4.99
1022, 9780321833877, 2015-03-09, MySQL Fifth Edition, 1029, Addison-Wesley, 5, 33.95
1022 ,9781890774820 ,2015-03-09 ,Murach’s MySQL 2nd Edition ,1032, “Murach, Mike & Associates Inc”, 2, 48.95
1249, 9781449374020, 2015-02-22, MySQL Cookbook: Solutions for Database Developers and Administrators ,1118, O’Reilly Media Incorporated, 9, 50.59
1249, 9781449325572, 2015-02-22, PHP & MySQL: The Missing Manual, 1118, “O’Reilly Media, Incorporated”, 6, 29.95
1249, 9781890774790, 2015-02-22, Murach’s PHP and MySQL 2nd Edition, 1032, “Murach, Mike & Associates Inc”, 1, 48.95
[/table]
BookSales
Using the BookSales table structure shown above, draw the dependency diagram, and show all dependencies, full, partial, and transitive.
Discussion: A composite primary key consisting of InvNum + ISBN13 can be constructed, ensuring that all rows are unique. All remaining attributes are shown to be dependent (at least partially) on this composite key, so the table is 1NF. Further analysis shows that InvDate is dependent on only part of the key (InvNum), and that unitPrice, PubCode, and BookTitle also depend on part of the key (ISBN13). A transitive relationship is also revealed: PubName truly depends on PubCode, which in turn depends on ISBN13.
Using the answer to exercise 1, remove all partial dependencies and draw the new dependency diagrams. For each new table created, specify its normal form (1NF, 2NF, 3NF).
Discussion: To achieve 2NF, we must remove partial dependencies. This is done by decomposing into three tables, and three dependency diagrams at this stage. Notice that the Transitive relationship has not yet been addressed.
Using the answer to exercise 3, remove all transitive dependencies, and draw the new dependency diagrams. For each new or revised table, specify its normal form.
Discussion: The transitive relationship has been removed by creating the Publishers table, in which PubName is fully dependent upon PubCode. Pubcode also persists as a dependent attribute and Foreign Key in Books. All tables are now in 3NF, and have been given meaningful names reflecting the entities they represent. LINEITEMS represents the collection of line items for all invoices. Invoices represents the collection of Invoices for all customer orders. Books makes up the list of all books available for sale (whether they have ever been ordered or not). Each book has a publisher, and the publisher code and Name reside in the Publishers table.
Draw the ERD for exercise 3, using Crow’s Foot notation.
Student Exercise
The student exercise for this lab is similar to the sample exercise presented above.
Consider the following spreadsheet containing information about customers, their shipping and billing addresses, and the countries corresponding to each of those addresses. A customer may be associated with zero to many addresses. A customer may have 0 or 1 default billing address. A customer may have 0 or 1 default shipping address. Each address may be associated with 0 or 1 countries, while each country may be associated with 0 to many addresses.
[table]
cust_id, fname, lname, def_bill_addr_id, def_ship_addr_id, addr_id, street_addr, city, state, zip, country_code, country_name
202, John, Smith, 1096, 2039, 1096, 123 Happy Ave., Orlando, FL, 32801, 76, United States
202, John, Smith, 1096, 2039, 2039, 2024 Shorline Dr., Seattle, WA, 98101, 76, United States
202, John, Smith, 1096, 2039, 8053, 100 N. Kent Ave. St., James, WA, 54364, 34, Jamaica
175, Hilda, Yeager, 2172, 2172, 3879, 48 Spatzel Ct., Dusseldorf, DD, 5111, 29, Germany
175, Hilda, Yeager, 2172, 2172, 3921, 162 Rue Moritz, Paris, PR, 75001, 28, France
321, Siri, Apple, 1881, 1881, 1881, 1 Infinite Loop, Cupertino, CA, 95014, 76, United States
[/table]
Using the example solution as a guide, perform the following steps:
Using the CustomerAddressCountry spreadsheet show above, draw the dependency diagram, and show all dependencies, full, partial, and transitive. Paste your solution into the answer sheet, and label it, Answer #1.
Using the answer to exercise 1, remove all partial dependencies and draw the new dependency diagrams. For each new table created, specify its normal form (1NF, 2NF, 3NF). Paste your solution into the answer sheet, and label it, Answer #2.
Using the answer to exercise 3, remove all transitive dependencies, and draw the new dependency diagrams. For each new or revised table, specify its normal form. Paste your solution into the answer sheet, and label it, Answer #3.
Draw the ERD for exercise 3, using Crow’s Foot notation. Paste your solution into the answer sheet, and label it, Answer #4.
SCREENSHOTS
SOLUTION
PAYMENT
ENTIRE COURSE
The solution includes a single word document.
Attachments [Move over files to preview content of those files]
CIS336_Lab_1.zip (48.39 KB)
CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams.docx
Price: $12
Buy Now
Checkout
Added to cart
Buy More Save More
Buy at least TWO items & save up to 30% OFF your ENTIRE order!
Rack up instant rebates in your shopping cart. Simply add items to your cart, and see the savings add up.
Discounts will automatically be applied on eligible orders.
CIS336 Lab 1 Normal Forms and Entity Relationship Diagrams – $12.00
CIS 336 Lab 2 The Expanded Entity Relationship Diagram – $12.00
CIS336 Lab 3 Building the Physical – $12.00
CIS336 Lab 4 Introduction to Select Insert Update and Delete Statements – $12.00
CIS336 Lab 5 Joining Tables – $12.00
CIS336 Lab 6 Group Functions and Subqueries – $15.00
CIS336 Lab 7 Working with Views – $15.00
Add to Cart
Checkout
Added to cart
FLASH SALE
$69
$90
Save $21
CIS336 Entire Course
Get Entire Course
You May Also Like:
CIS 336 Entire Course
CIS336 Lab 2 The Expanded Entity Relationship Diagram
CIS336 Lab 3 Building the Physical
CIS336 Lab 4 Introduction to Select Insert Update and Delete Statements
CIS336 Lab 5 Joining Tables
CIS336 Lab 6 Group Functions and Subqueries
CIS336 Lab 7 Working with Views