Discuss about the normalization

Assignment Help Database Management System
Reference no: EM131289564

MMIS 630

Normalization

Part 1

Use the following table to answer questions 1 and 2:

Table: BOOK-DETAIL

BookID**

GenreID

GenreDesc

Price

1

1

Gardening

25.99

2

2

Sports

12.99

3

1

Gardening

10.00

4

3

Travel

14.99

5

2

Sports

17.99

**Primary key

1.      What, if any normalization error is present in the table?

a.       None

b.      First Normal Form

c.       Second Normal Form

d.      Third Normal Form

2.      Displaythe modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

Use the following table to answer questions 3 and 4:

Table: BOOKS

Book-Code**

Title

Price

Pub-Code**

Publisher

City

0180

Shyness

7.65

BB

Bantam Books

Boston

0189

Kane and Able

5.55

PB

Pocket Books

New York

0200

The Stranger

8.75

BB

Bantam Books

Boston

0378

The Dunwich Horror

19.75

PB

Pocket Books

New York

079X

Smokescreen

4.55

PB

Pocket Books

New York

**Primary Key

3.      What, if any normalization error is present in the table?

a.       None

b.      First Normal Form

c.       Second Normal Form

d.      Third Normal Form

4.      Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

Use the following table to answer questions 5 and 6:

Table: PRODUCT

ProductID**

Sizes Available

Price

1

Small, Medium

15.99

2

Small, Medium, Large

13.99

3

Small

22.99

4

Small

17.50

5

Large, Extra Large

19.99

**Primary key

5.      What, if any normalization error is present in the table?

a.       None

b.      First Normal Form

c.       Second Normal Form

d.      Third Normal Form

6.      Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

Use the following table to answer questions 7 and 8:

Table: PURCHASE-DETAIL

CustomerID**

StoreID**

StoreLocation

1

1

Los Angeles

1

3

San Francisco

2

1

Los Angeles

3

2

New York

4

3

San Francisco

*Primary key

7.      What, if any normalization error is present in the table?

a.       None

b.      First Normal Form

c.       Second Normal Form

d.      Third Normal Form

8.      Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

Use the following table to answer questions 9 and 10:

Table: BOOK-LOCATION

BookID**

BranchID**

Quantity-on-Hand

1

5W

4

1

3E

6

2

5W

3

3

5W

5

3

2S

4

**Primary key

9.      What, if any normalization error is present in the table?

a.       None

b.      First Normal Form

c.       Second Normal Form

d.      Third Normal Form

10.  Display the modified table or tables that would correct the normalization error, if one is present. Be sure to indicate primary (**) and foreign (*) keys.

Part 2

 Use the following table to answer questions 11 through 16.

Order ID

Order Date

Customer ID

Customer Name

Product ID

Product Desc.

Product Price

Quantity Ordered

1006

10/24/10

2

Value Furniture

7

 

5

 

4

Dining Table

Writers Desk

Entertain Center

800.00

 

325.00

 

650.00

2

 

2

 

1

1007

10/25/10

6

Furniture Gallery

11

 

4

4 Drawer Dresser

Entertain Center

500.00

 

650.00

4

 

3

 

Business rules:

1.      The OrderID is a unique number given to each order

2.      OrderIDs are not reused.

3.      The CustomerID is a unique number given to each customer

4.      CustomerIDs stay with the customer forever

5.      CustomerIDs are not reused

6.      ProductID is a unique number given to each product the company sells.

7.      ProductIDs stay with the product for as long as the product is sold by the company

8.      ProductIDs are not reused.

The questions:

11.  What errors prevent the table displayed above from being first normal form compliant? Be specific, identifying all columns that create the normalization error by name.

12.  Bring the table into first normal form compliance without loss of any data. Identify primary and foreign keys (when present) for all tables. Give each table its own name.

13.  What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant? Be specific, identifying all columns and tables that create the normalization error by name.

14.  Bring the table(s) into second normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process. Give each table a its own name.

15.  What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant? Be specific, identifying all columns and tables that create the normalization error by name.

16.  Bring the table(s) into third normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process. Give each table its own name.

Reference no: EM131289564

Explain the problems associated with data redundancy

Assignment 5: Logical Design, Part II, Explain the problems associated with data redundancy as it pertains to effectively using information for reporting and analysis.

Differentiate between the interaction types and styles

Explain the conceptual model employed in the design of these types. Describe the analogies and concepts these monitors expose to users, including the task-domain objects use

Database management challenge than relational database

What is it about a 200 MB video or audio file which makes it so much more of database management challenge than relational database the same size?

Implement the query find total number of orders submitted

Find the total number of orders submitted in 2015. List all distinct customer IDs (O_C_ID) who submitted the largest number of orders in 2014. Find the total number if orders

Write a script that deletes the gettoplovendors procedure

Use the Management Studio to write a script that deletes the GetToplOVendors procedure and the ApClrObjectsEx assembly. Check that the procedure and assembly exist before de

Find the enames of managers who manage the departments

Find the enames of managers who manage the departments with the largest budget. Find the managerids of managers who manage only departments with budgets greater than $1,000,00

Front-end and back-end validation

What are the advantages and disadvantages of front-end and back-end validation and in which specific situations would one technique of validation be preferred over the other?

Prepare a use case diagram for the case study

Case Study based on an Interview contained in a separate document (Please see the details in page 221 of your text book). Carefully read this case study. You are required to

Reviews

Write a Review

 
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd