Burns Michael CS440 Homework #1 10.17: Design a relational database schema for this database application. Student Relation: {ssn, snum, sname, sc_addr, sc_phone, sp_addr, sp_phone, bdate, sex, class, major_code, minor_code, prog} Functional Dependencies: ssn, snum sname, sc_addr, sc_phone, sp_addr, sp_phone, bdate, sex, class, major_code, minor_code, prog Department Relation: {dname, dcode, doffice, dphone, dcollege} Functional Dependencies: dname, dcode doffice, dphone, dcollege Course Relation: {cnum, cname, cdesc, credit, level, c_dep} Functional Dependencies: cnum, cname cdesc, credit, level, c_dep Section Relation: {sec_num, name, semester, year, sec_course} Functional Dependencies: sec_num Iname, semester, year, sec_course Grade Record Relation: {ssn, sec_num, grade} Functional Dependencies: ssn, sec_num grade
10.18: Prove or disprove the following rules: a. W Y, X Z = WX Y IR2 (AUGMENTATION): If W Y then WX YX This also means that WX Y and WX X True b. X Y, Y Z = X Z IR1 (RELFECTIVE): If Y Z then Y Z IR3 (TRANSITIVE): If X Y and Y Z then X Z True c. X Y, X W, WY Z = X Z IR5 (UNION): If X Y and X W then X WY IR3 (TRANSITIVE): If X WY and WY Z then X Z True d. XY Z, Y W = XW Z Relation: Student(sname, ssn, age, drivers_license) W: drivers_license X: Sname Y: ssn Z: age Functional Dependencies: Sname, ssn age ssn drivers_license ASSUMPTION: Sname, drivers_license age sname and ssn are needed to get the age, the drivers_license is not sufficient. False e. X Z, Y Z = X Y Relation: student(ssn, Sname, age) X: ssn Y: Sname Z: age Functional Dependencies: Ssn age Sname age ASSUMPTION: ssn Sname There is no functional dependency between ssn and Sname. False
X Y, XY Z = X Z Relation: Reservations(ssn, age, boatid, resv_date) Functional Dependences: ssn age ssn, boatid date ASSUMPTION: ssn date ssn and the boatid needed to get the date field. False f. X Y, Z W = XZ YW IR2 (AUGMENTATION): X Y = XZ YZ and Z W = ZY WY IR3 (TRANSITIVE): So if XZ YZ and YZ WY then XZ YW True g. XY Z, Z X = Z Y Relation: Student(ssn, sname, bdate) Functional Dependencies: ssn, sname bdate bdate ssn ASSUMPTION: bdate sname bdate does not provide ssn, only sname. False X h. Y, Y Z = X YZ IR1 (RELFECTIVE): X Y means that X is a of Y IR1 (RELFECTIVE): Y Z means that Y is a of Z This means that X has to be a subset of YZ or X YZ True i. XY Z, Z W = X W IR3 (TRANSITIVE): XY Z and Z W so XY W Relation: Student(ssn, sname, age, class(fresh, soph, junior, senior)) Functional Dependencies: ssn, sname age age class ASSUMPTION: ssn class ssn and sname are needed to get the age. From age you can the class. False
10.20: Calculate the closures ssn+ and dnum+ with respect to G: Funcitonal Dependenices: ssn ename, bdate, address, dnum dnum dname, dmgr_ssn ssn+: {ssn, ename, bdate, address, dnum, dname, dmgr_ssn} dnum+: {dnum, dname, dmgr_ssn} 10.30: Given the relation and the functional dependencies, try to normalize the relation and give the keys for that normalized relation: Relation: Courses(course_no, sec_no, offering_dept, credit_hours, course_level, instructor_ssn, semester, year, days_hours, room_no, no_of_students) Functional Dependencies: course_no offering_dept, credit_hours, course_level course_no, sec_no, semester, year days_hours, room_no, no_of_students, instructor_ssn room_no, days_hours, semester, year instructor_ssn, course_no, sec_no Normalization (breaking up into two tables, I also got rid of the third functional dependency because it was transitive and creating a redundancy problem): Relation1: Courses(course_no, offering_dept, credit_hours, course_level) Functional Dependencies: course_no offering_dept, credit_hours, course_level Relation2: Course2(course_no, sec_no, semester, year, days_hours, room_no, no_of_students, instructor_ssn) Functional Dependencies: course_no, sec_no, semester, year days_hours, room_no, no_of_students, instructor_ssn
10.31: Given the functional dependencies, if you do a natural join of the two relations what will be the key? Is it in 2NF? 3NF? Key: I# Functional Dependencies: I# O#, Odate, Cust#, total_amount, I#, qty_ordered, total_price, Discount% O# odate, Cust#, total_amount O#, I# qty_ordered, total_price, discount% 2NF: Not in 2NF, removing O# and you can still get qty_ordered, total_price, and discount% using just I#. 3NF: It cannot be in 3NF since it is not in 2NF, which is a superset.