What are the functional dependencies

Assignment Help Database Management System

Reference no: EM132345588

Normalization Exercise 1:

For each of the following tables
• Convert to a group of tables in first normal form
• Show the primary key of each table
• Show the foreign key of each table (and what table it points to)
Student(StNum,StName,SocSecNum,age, clubs, awards)
Faculty(FacNum,SocSecNum, rank, committees, papers-written)

Consider the following table - Where each project has many employees and each employee works on many projects. Sample data is shown.

ProjectInfo(PrjNum,PrjName,budget,EmpNum,EmpName,HrsWorked)

P22 Cyclone 50000 E1001 Joe 12

P22 Cyclone 50000 E2002 Pat 50

P21 IMB 20000 E3003 Ed 40

P21 IMB 20000 E2002 Pat 30

P21 IMB 20000 E1001 Joe 70

a) What problems do you see keeping all this data in one table?

b) what are the functional dependencies?

c) what is the key? (minimum group of attributes that determine all other attributes)

d) what are the partial dependencies? Is it in 2ndN.form?

Normalization Exercise 2:

Consider the following table- With some sample data shown(assume each order can contain various numbers of different products, but is placed by one customer on one date), and
• determine the functional dependencies
• determine the primary key
• determine if there are any partial dependencies
• convert to 2nd N.F. Show all keys (primary and foreign)

Order(OrdNum,date,CustNum,prodNum,num-ordered,unit-price,total-price)

O23 1/1/08 C22 P21 5 7.00 35.00

O23 1/1/08 C22 P25 3 21.00 63.00

O24 1/1/08 C22 P23 3 7.00 21.00

O25 2/1/08 C44 P21 11 7.00 77.00

O25 2/1/08 C44 P28 4 99.00 396.00

025 2/1/08 C44 p25 1 21.00 21.00

Normalization Exercise 3:

Consider the following table involving course info and the grades given in those courses, and
• determine the functional dependencies
• determine the primary key
• determine if there are any partial dependencies
• convert to 2nd N.F. Show all keys (primary and foreign)

Courses(Cnum,Cname,credits,stuNum,stName,stAge,semester,grade)

Normalization Exercise 4:

Given the follow table T( A,B,C,D,E,F,G)

Suppose we have the following dependencies:
A+B---> C; A+B--->F; A--> D; A--->E; B--> G

a) What is the primary key? (the minimum attributes that determine all the other attributes)

b) What are the partial dependencies?

c) Convert to set of tables in 2nd N.F. Indicate primary and foreign keys.

Normalization Exercise 5:

Consider the following table (with each order going to a single customer and shipped from one warehouse)

Order(ordNum,date, warehouseNum, warehouseLoc, custNum, custName)

Or55 1/1/07 w5 NY C55 Acme

Or66 1/1/07 w3 WC C66 IBM

Or77 4/4/07 w5 NY C77 Intel

Or88 4/12/07 w3 WC C55 Acme

a) What are the functional dependencies of the order table?

b) What is the primary key?

c) Why must it be in 2nd Normal Form?

d) Is it in 3rd Normal Form? Explain (any dependencies not involving candidate keys?)

e) Convert to 3rdN.Form. Indicate primary and foreign keys.

Normalization Exercise 6:

Consider the following table with the given dependencies.
T(A,B,C,D,E,F,G) E->G; B->C,A; D->A,B,C,E,F,G

a) What is the primary key?

b) Why must it be in 2NF? (Why can't there be any partial dependencies)

c) Is it in 3NF (any dependencies involving attributes that are not candidate keys)?

d) Convert to a set of tables in 3NF. Indicate primary and foreign keys.

Normalization Exercise 7:

Consider the following table and dependencies

T(A,B,C,D,E,F,G,H) D+E---> A,B,C; D-->F;E-->G,H;H-->G

a) What is the key?_______________

b) Are there any partial dependencies? List them!

c) Is T is 2nd Normal form?

d) Convert to a group of tables in 2nd N.F

e) Are the tables in part C in 3rdN.Form? Explain

f) Convert to 3rd N. Form.

Normalization Exercise 8:

Consider the table involving a chain of bookstores, books and publishers
• each branch can sell a book at what ever Selling-price they want
• each book has a (preset, fixed) list price
• each book is published by a single publisher

(BranchNum, BranchAddr, BkNum, Tittle, PubNum, PubName, List-Price, InStock, list-Price, Selling-Price)

a) What are the functional dependencies?

b) If we keep everything in this one table what is the primary key? _______________

c) Is the table in 2nd N.F.? ________

d) List any dependencies which involve part of the primary key (partial dependencies) ?

e) Convert to a set of tables in 2nd N.F. (show the primary key of each table)

f) Is the table in 3rd N.F? _______

g) List any dependencies that don't involve the candidate keys

h) Convert to a set of tables in 3rd N.F. Show all primary and foreign keys

Attachment:- Normalization.rar

Reference no: EM132345588

Questions Cloud

Why is it vital to organizational success : What are the three vertical integration of a business and why is it vital to organizational success?

About the empirical realities of women in prison : What is the media image of women in prison? What do the readings and videos reveal about the empirical realities of women in prison?

What is the present value of the payments : You have just won the Strayer Lottery jackpot of $11,000,000. You will be paid in 26 equal annual installments beginning immediately. If you had the money now.

Write creatively or engage in sociological observations : Write creatively or engage in sociological observations. How are women presented in television-film in 2018? What sociological factors might explain presence.

What are the functional dependencies : What are the functional dependencies and Convert to a set of tables in 3NF. Indicate primary and foreign keys - Convert to a set of tables in 3rd N.F

What are some of the lessons learned : Identify a company and discuss the key to their effective leadership and work motivation. What are some of the lessons learned?

What stresses and strains have been placed on these women : What stresses and strains have been placed on these women and their families due to underemployment? Give specific examples

What are the ways provider tries to make a service : What are the ways provider tries to make a service tangible to the consumer?

Instrumental model of corporate management : What is the instrumental model of corporate management? What is the social contract model of corporate management?

User Account

All Pages