# Database

Topics: Database normalization, Transitive dependency, Third normal form Pages: 4 (492 words) Published: May 6, 2013
Question 1
Figure P5.1&2 The Dependency Diagrams for Problems 1 and 2 INV_NUM
SALE_DATE
VEND_CODE
PROD_DESCRIPTION
Problem 1 Solution
Transitive Dependency
PROD_NUM
VEND_NAME NUM_SOLD PROD_PRICE
Partial dependency
Partial dependencies
Problem 2 Solution
INV_NUM PROD_NUM NUM_SOLD
INV_NUM SALE_DATE
3NF
3NF
VEND_CODE
PROD_DESCRIPTION
PROD_NUM
VEND_NAME
PROD_PRICE
2NF (Contains a
transitive dependency)
Transitive Dependency
Relational schema: 1NF(INV_NUM, PROD_NUM, SALE_DATE, PROD_DESCRIPTION, VEND_CODE, Relational schema: 3NF(INV_NUM, PROD_NUM, NUM_SOLD)
Relational schema: 3NF(INV_NUM, SALE_DATE)
Relational schema: 2NF(PROD_NUM, PROD_DESCRIPTION, VEND_CODE, VEND_NAME) VEND_NAME, NUM_SOLD, PROD_PRICE)

Page 3|
Using the results of Problem 3, draw the Crow’s Foot ERD.
Figure P5.4 The Invoicing ERD and Its (Partial) Relational Diagram INV_NUM
INV_DATE
INV_NUM
INVOICE
PROD_NUM
NUM_SOLD
PROD_NUM
PROD_DESCRIPTION
PROD_PRICE
VEND_CODE
VEND_CODE
VEND_NAME
LINE
PRODUCT
VENDOR
Crow’s Foot Invoicing ERD
Invoicing Relational Diagram, Sample Attributes
1
1
1
M
M
M

Figure P5.1&2 The Dependency Diagrams for Problems 1 and 2 Problem 1 Solution
INV_NUM PROD_NUM SALE_DATE PROD_DESCRIPTION VEND_CODE VEND_NAME NUM_SOLD PROD_PRICE Partial dependency
Transitive Dependency Partial dependencies
Relational schema: 1NF(INV_NUM, PROD_NUM, SALE_DATE, PROD_DESCRI PTION, VEND_CODE, VEND_NAME, NUM_SOLD, PROD_PRICE) Problem 2 Solution
INV_NUM PROD_NUM NUM_SOLD
3NF
INV_NUM SALE_DATE
3NF
Relational schema: 3NF(INV_NUM, PROD_NUM, NUM_SOLD)
Relational schema: 3NF(INV_NUM, SALE_DATE) 2NF (Contains a transitive dependency) PROD_NUM PROD_DESCRIPTION PROD_PRICE VEND_CODE VEND_NAME
Transitive Dependency Relational schema: 2NF(PROD_NUM, PROD_DESCRIPTION, VEND_CODE, VE ND_NAME) 156
Chapter 5 Normalization of Database Tables 3. Using the table structures you created in Problem 2, remove all transitive dependencies,...