CS 338 Computer Applications in Business: Database Assignment #4 – Normalization
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CS 338 Computer Applications in Business: Database
Assignment #4 – Normalization
Introduction
Assignments are essential in learning the material and preparing for exams. You can ask for help but make sure you write the answers in your own words. We will check for copies. You should submit your work to the dropbox on Learn, by 1pm on Nov 30th, 2022. Late submission will NOT be accepted.
Questions
1. Determine whether each of the following relations is in BCNF): (a) R1=(ABCD), AB >CD, B>C, and A is the only candidate key.
(b) R2=(ABCD), AB >D, C >D, and A and C are the only two candidate keys.
(c) R3=(ABCD), AB >C, CD >AC, and (C, D) is the only candidate key.
2. Let us say R = (EFABCX) and has the following set of functional dependencies F: F = { E → F, AB → X, BC → A, AX → B }.
(a) What are the candidate keys of R? Hint: attribute closure.
(b) Is R in BCNF?
3. When designing database in practice, the user and client sometimes cannot properly explain the functional dependencies. They can only show how data is recorded in file and you will have to fish out the necessary information yourself like in this exercise. The following table records information about the bank employees and their branches.
(a) Identify the proper functional dependencies in the table given above.
(b) Decompose the table into BCNF.
4. An animal clinic maintains health history of each pet as shown in the following report. Based on the sample information presented,
(a) Identify the proper functional dependencies in the table
PetHealth(pet_id,pet_name,pet_type,pet_age, owner, visitdate,procedure_no,procedure_name)
(b) Identify the primary key and explain why the PetHealth table is NOT in BCNF.
(c) Decompose the table into BCNF.
5. Consider the following relational schema and set F of functional dependencies: R(ABCDEFG), F = { E → C, G → AD, B → E, C → BF }.
Decompose R into BCNF. Check whether your decomposition is lossless and preserves all functional dependencies.
6. Given the following relation schemes and set of FDs: Marks(Name, SID, Course, Grade)
FDs = {SID → Name
Name → SID
(Name, Course) → Grade
(SID, Course) → Grade}.
(a) Find all the candidate keys for the relation.
(b) Indicate which FD(s) violates BCNF.
(c) Find a BCNF decomposition for the given relation.
(d) Is your decomposition lossless?
(e) Does your decomposition preserve all functional dependencies?
7. Consider the relation PLAYER with relational schema
PLAYER (Player-no, Player-name, Team, Teamcolor, Coach-no,
Coach-name, Player-position, Team-captain) and set of functional dependencies as follows;
F = { FD1: Player-no → Player-name, Player-position, Team
FD2: Coach-no → Coachname
FD3: Team → Team-color, Coach-no, Team-captain }
Decompose the table into BCNF. Check whether your decomposition is lossless and preserves all functional dependencies.
8. Consider the following relation:
New_Vehicle_Sale(VIN, SalesRep_Name, Date_sold, SalesRep#, Commision, Discount) Assume that a car may be sold by multiple salesmen and hence (VIN, SalesRep#) is the primary key. Additional dependencies are:
FD1: Date_sold →Discount
FD2: SalesRep# → Commission, SalesRep_Name
FD3: VIN → Date_sold
Decompose the table into BCNF. Check whether your decomposition preserves all functional dependencies.
Marking (40 points)
Each question is worth 5 points and is marked according to the following scheme:
• Excellent (no mistake or very small one): 5 points;
• Good (some mistakes): 4 points;
• Ok (lots of mistakes): 3 points;
• Poor (mostly wrong but has some merit): 1 point;
• Wrong (totally off or no answer): 0 point.
Submission
The assignment file format should be DOC/DOCX (word document), PDF, TXT (text file), or JPG/JPEG (picture). It should be uploaded to the drop box on Learn: Submit -> Dropbox -> Assignment #4. The submission deadline is Nov 30th, 1pm.
2022-11-29