Assignment3a - Pages 372-6: Exercises 10.17 Design a...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
Pages 372-6: Exercises 10.17 Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. FD1: {Ssn} {Sname, Snum, Sc_addr, Sc_phone, Bdate, Sex, Class, Major_code, Minor_code, Prog} FD2: {Snum} {Snum} {Sname, Ssn, Sc_addr, Sc_phone, Bdate, Sex, Class, Major_code, Minor_code, Prog} FD3: {Dname {Dcode, Doffice, Dphone, Dcollege} FD4: {Dcode} {Dname, Doffice, Dphone, Dcollege} FD5: {Cnum} {Cname, Cdesc, Credit, Level, Cdept} FD6: {Sec_course, Semester, Year, Sec_num} {lname} FD7: {Sec_course, Semester, Year, Sec_num, Ssn} {Grade} The key attributes of FD1 and FD2 are STUDENT attributes and either Ssn or Snum can be the primary key for the relation. The key attributes of FD3 and FD4 are DEPARTMENT attributes and either Dname or Dcode can be the primary key of that relation. FD5 is for course attributes, FD6 has the SECTION attributes and FD7 has the GRADE attributes. SSN Snum Sname Sc_addr Sc_phone Bdate Sex Class Major_code Minor_code Prog Dept_code Dept_name Doffice Dphone Dcollege
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Cnum Cname Cdesc Credit Level Cdept Sec_course Sec_num Year Semester lname Sec_course Semester Year Sec_num Ssn Grade Pages 372-6: Exercises 10.20
Background image of page 2
Consider the relation schema EMP_DEPT in Figure 10.3(a) and the following set G of functional dependencies on EMP_DEPT: G = {Ssn {Ename, Bdate, Address, Dnumber} , Dnumber {Dname, Dmgr_ssn} }. Calculate the closures {Ssn} + and {Dnumber} + with respect to G. {Ssn} + ={Ssn, Ename, Bdate, Address, Dnumber, Dname, Dmgr_ssn} {Dnumber} + ={Dnumber, Dname, Dmgr_ssn} Pages 372-6: Exercises 10.28 Consider the following relation: A B C Tuple# 10 b1 c1 #1 10 b2 c2 #2 11 b4 c1 #3 12 b3 c4 #4 13 b1 c1 #5 14 b3 c4 #6 a. Given the previous extension (state), which of the following dependencies may hold in the above relation? If the dependency cannot hold, explain why by specifying the tuples that cause the violation. i. A B, ii. B C, iii. C B, iv. B A v. C A b. Does the above relation have a potential candidate key? If it does, what is it? If it does not, why not? Pages 372-6: Exercises 10.32 Consider the following relation:
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
CAR_SALE(Car#, Date_sold, Salesman#, Commision%, Discount_amt) Assume that a car may be sold by multiple salesmen and hence {CAR#, SALESMAN#} is the primary key. Additional dependencies are: Date_sold Discount_amt and Salesman# Commission% Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely?
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/07/2011 for the course MIS unknown taught by Professor Unknown during the Fall '09 term at New York Institute of Technology-Westbury.

Page1 / 9

Assignment3a - Pages 372-6: Exercises 10.17 Design a...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online