All Tutorials will be E-mailed immediately (automatically) after the Payment and can also be Downloaded by clicking on Tutorial Bucket.
Please Check your Spam Email if you do not get the Tutorial.
Click Here to Submit New Syllabus (If course has changed or if you need help with any other course)
Shopping Cart
Your shopping cart is empty!
Categories
CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams
$9.00

CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams

This Tutorial was purchased 1 times & rated No rating by student like you.

 

This Tutorial contains following Attachments

  • CIS 336 n iLab 1 Normal Forms and Entity Relationship Diagrams.docx

CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams

Introduction
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.

InvNum ISBN13 InvDateBookTitlePubCodePubNameQtyunitPrice
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
BookSales

1. Using the BookSales table structure shown above, draw the dependency diagram, and show all dependencies, full, partial, and transitive.

Figure 1 -solution 1
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. 
2. 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).

Figure 2- solution 2
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.

3. 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.

Figure 3 – Solution 3
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.

4. Draw the ERD for exercise 3, using Crow’s Foot notation.

End of Sample Exercise.


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.
cust_idfnamelnamedef_bill_addr_iddef_ship_addr_idaddr_idstreet_addr city state zip country_codecountry_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 34 Jamaica
175 Hilda Yeager 2172 2172 3879 48 Spatzel Ct. Dusseldorf 5111 29 Germany
175 Hilda Yeager 2172 2172 3921 162 Rue Moritz Paris 75001 28 France
321 Siri Apple 1881 1881 1881 1 Infinite Loop Cupertino CA 95014 76 United States

Using the example solution as a guide, perform the following steps.
1. 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.
2. 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.
3. 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.
4. Draw the ERD for exercise 3, using Crow’s Foot notation. Paste your solution into the answer sheet, and label it, Answer #4.

Write a review

Order Id


Order Id will be kept Confidential
Your Name:


Your Review:
Rating:   A   B   C   D   F  

Enter the code in the box below:



Related Tutorials
$9.00

This Tutorial was purchased 4 times & rated A+ by student like you.

(TCO 6) Which of the following keywords is used to remove a row in a table? (TCO 6) When using the ALTER TABLE...DROP COLUMN command, which of the following is NOT correct? (TCO 6) Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store t..
$9.00

This Tutorial was purchased 3 times & rated No rating by student like you.

(TCO 8) If a Cartesian join is used to link table A which contains five rows to table B which contains eight rows, there will be _______ rows in the results. (TCO 8) Which of the following statements will produce the value 21.1? (TCO 8) Which of the following format elements will display ..
$8.99

This Tutorial was purchased 3 times & rated A by student like you.

(TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? (TCO 7) Which of the following SQL statements will display the profit generated by each book currently stored in the BOOKS table? (You do not need to see the table structure for this ..
$9.00

This Tutorial was purchased 2 times & rated No rating by student like you.

(TCO 2) Explain what a composite attribute is (TCO 1) Data constitutes the building blocks of (TCO 1) Successful database design is first and foremost based on _____________ requirements. (TCO 1) The lowest level in a database hierarchy is the ____________ or value as it is often refe..
$7.49

This Tutorial was purchased 3 times & rated A by student like you.

(TCO 3) Normalization works through a series of stages called normal forms. Typically _________ stages must be completed before a table can be considered normalized. (TCO 3) The conflicts between design efficiency, information requirements, and processing speed are often resolved through ..
$32.00

This Tutorial was purchased 23 times & rated A+ by student like you.

CIS 336 Final Exam 4 Sets of Answers ..
$11.00

This Tutorial was purchased 10 times & rated A+ by student like you.

1. (TCO 7) For every order, list the order number, order date, part number, part description, and item class for each part that makes up the order. 2. (TCO 8) Write a query that will display the customer number, last name, and first name for every customer I whose first name begins with the l..
$11.00

This Tutorial was purchased 4 times & rated A by student like you.

1. (TCO 2) Explain what physical independence means. (Points : 15) 2. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the..
$11.00

This Tutorial was purchased 8 times & rated A+ by student like you.

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) A relational DBMS provides protection of the _______________________ through security, control, and recovery facilities. (Points : 4) ..
$11.00

This Tutorial was purchased 6 times & rated A by student like you.

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4) elemental linked hierarchical relational 2. (TCO 1) Data constitutes the building blocks of _____________. (Points : 4) information processing applications programming 3. (TC..
$9.00

This Tutorial was purchased 6 times & rated A+ by student like you.

CIS-336: Lab7: Working with Views LAB OVERVIEW Lab 7 will introduce the concept of database views. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the OM database tables. The lab will ..
$7.00

This Tutorial was purchased 1 times & rated A by student like you.

CIS 336 Week 6 Quiz     Question 1.                  (TCO 6) Which of the following keywords can be used to change the size, datatype, or default value of an existing column?   ..
$9.00

This Tutorial was purchased 4 times & rated A by student like you.

CIS336: Lab 6: Group Functions and Subqueries LAB OVERVIEW Scenario and Summary Lab 6 will introduce the concept of group functions and subqueries to meet more complex report requirements. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local co..
$7.00

This Tutorial was purchased 3 times & rated A+ by student like you.

CIS 336 Week 5 Quiz       Question 1.                  (TCO 8) The ____ set operator in SQL combines rows from two queries and returns only the values that are common between the tw..
$9.00

This Tutorial was purchased 6 times & rated A by student like you.

CIS336: Lab5: Joining Tables LAB OVERVIEW Scenario and Summary Lab 5 will introduce the concept of multi-table JOINS in order to work with data in two or more related tables simultaneously. This lab may be completed using either DeVry’sOmnymbus EDUPE-APP lab environment, or a local copy o..
$7.00

This Tutorial was purchased 1 times & rated No rating by student like you.

CIS 336 Week 4 Quiz         Question 1.                  (TCO 7) Which of the following types of columns will automatically right-align the data when it is displayed? &..
$9.00

This Tutorial was purchased 3 times & rated No rating by student like you.

CIS336: Lab 4: Introduction to Select, Insert, Update, and Delete Statements LAB OVERVIEW Lab 4 will introduce the various aspects of the SQL select statement and the methods of retrieving data from the database tables. This lab will also introduce the fundamentals of updating and deleting ..
$7.00

This Tutorial was purchased 1 times & rated No rating by student like you.

CIS 336 Week 3 Quiz     Question 1.                  (TCO 3) When designing a database, you should _____   Question 2.          ..
$10.00

This Tutorial was purchased 8 times & rated A+ by student like you.

CIS-336: Lab 3: Building the Physical LAB OVERVIEW Scenario and Summary The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to downl..
$6.00

This Tutorial was purchased 2 times & rated A by student like you.

CIS 336 Week 2 Quiz                                               ..
$9.00

This Tutorial was purchased 3 times & rated A by student like you.

CIS336 Lab 2: The Expanded Entity Relationship Diagram LAB OVERVIEW Scenario and Summary This lab introduces the next step in creating a data model, the Entity Relationship Diagram (ERD). You will be given a business scenario for a University Medical Center, which is a small community hos..
$9.00

This Tutorial was purchased 1 times & rated No rating by student like you.

CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams Introduction 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 (..
$25.00

This Tutorial was purchased 3 times & rated A by student like you.

This project is designed to touch all aspects of the fundamental concepts of database design and logical data modeling covered during the class. Each student will be responsible for designing, developing, and demonstrating the functionality of a database created based on a set of business specifi..
$15.00

This Tutorial was purchased 8 times & rated A by student like you.

CIS 336 Final Exam       Question 1. 1. (TCO 1) A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions?   Question 2. 2. (TCO 1) A relati..
Tags:
CIS 336 © 2017 All Rights Reserved.Powered by:Ash Tutorial