CMPT 354 Study Guide - Quiz Guide: Third Normal Form, Functional Dependency
Document Summary
Identify (using bold face or some other way) the foreign keys and any other constraints in each table you define. Q2: normalize the following to bcnf (15 points) Studentinfo(studno, studname, major, advisor, courseno, ctitle, instrucname, instruclocn, grade) given the following functional dependencies: studno -> studname, courseno -> ctitle,instrucname, instrucname -> instruclocn, studno,courseno,major -> grade, studno,major -> advisor, advisor -> major. To help you get started, the studentinfo relation has been decomposed into the following relations: Q3: normalize the following to bcnf (15 points) Lending(bname, assets, bcity, loan#, cname, amount) given the following functional dependencies: bname -> assets, bcity, loan# -> amount, bname. Write an sql specification with primary and foreign key constraints to create tables for relations: student, course, and enrolled. Suppose you execute the following sql statements in sequence. For each of the following rules/implications, determine whether it holds (i. e. , yes or no). If yes, provide a formal proof; otherwise, give a counterexample.