data base help please show work. (50%) Conduct normalization analysis on CLYSTMS

Question

data base help please show work.

(50%) Conduct normalization analysis on CLYSTMS by listing the FDs and the highest normal form of each relation. "You must use h9q1_template.docx ' which i have picture of below the question . Complete the task by filling in the row "Normalization Analysis."

(2) Consider the relation Student(StudentId, StudentLastName, StudentFirstName, Phone, FacultyId) with obvious meanings of the columns. StudentId and FacultyId are the unique identifier for a student and a faculty respectively.

Provide two sets of FDs and assumptions such that:

the first set of assumptions make the relation in BCNF.

the second set of assumptions make the relation violating 3NF.

List the candidate keys in both cases.

(3) Consider the following relation

R(A,B,C,D) {AB->C, B->D}

(a) Show all candidate keys.

(b) What is the highest normal form (up to BCNF)? Why?

(c) If it is not in BCNF, can you losslessly decompose R into component relations in BCNF while preserving functional dependencies?

(4) Consider the following relation

R(A,B,C,D) {AB->C, BC->AD}

(a) Show all candidate keys.

(b) What is the highest normal form (up to BCNF)? Why?

(c) If it is not in BCNF, can you losslessly decompose R into component relations in BCNF while preserving functional dependencies?

Clipboard Font Paragraph Swimmer (Swimmerld, FName, LName, Phone, Email, JoinTIme, CurrentLevelid, Main CTL ld Main CT Since Swimmerld Candidate Kes Foreign Keys Level Levelld Nullable Attributes Notes [1] The relationship WM Caretaker" is implemented as two attributes ain Main CT Id and Main CT Since. It is also acceptable to use a separate relation to implement the relationship. [2] CurrentLevelld is a derived column that can be obtained from the table LevelHistory. Normalization Analysis 2 Caretaker (CT ld. FName. LName. Phone. Email) Candidate Keys [1] CT ld Foreign Keys Nullable Attributes Notes Normalization Analysis Highest NF OtherCaretaker(OC ld, Swimmerld, CT ld. Since Candidate Keys [11 OC Id. [21 ISwimmerld. CT Idl Foreign Keys Nullable Attributes [1] A surrogate key, OC ld, is created as the primary key. This is optional Notes Normalization Analysis FD Highest NF Level( Levelld, Level, Description) Candidate Keys [1] Levelld, [2] Level Foreign Keys Possibly Description, depending on assumptions made Nullable Attributes Notes Normalization Analysis FD: Highest NF

Explanation / Answer

2) Given table is : Student(StudentId, StudentLastName, StudentFirstName, Phone, FacultyId).

Set of suumtions that are in BCNF:

{studentId-->StudentLastName, StudentFirstName, Phone ; FacultyId---> Phone} which satisfies both 3NF and BCNF

3)

Given relation R{A,B,C,D} And sets are{AB-->C, B-->D}

a) take a subset (AB)+ ={A,C,B,D}. None of its subset can determine all attribute, So AB will be candidate key.

A and B are can’t be derived from any other attribute . So primary keys are: {AB}.

b)Hence primary keys are: {A,B} and non-primary keys are : {C,D}

The relation R is in 1st normal form because there is no multivalued attributes.
The relation is not in 2nd normal form because AB-->C is in 2nd normal form but B-->D is not in 2nd normal form.

Therefore heighest NF is 1NF.

c) By decomposing R into component relations in BCNF

{AB-->C, BC-->D}

4) Given relation is R(A,B,C,D) {AB->C, BC->AD}

a) Take a subset (AB)+ ={A,C,B,D}. None of its subset can determine all attribute, So AB will be candidate key. A is derived from BC since BC is a superkey. Therefore canditate keys are: {AB,BC}.

b)Hence primary keys are: {A,B,C} and non-primary keys are :{D}

The relation R is in 1st normal form because there is no multivalued attributes.
The relation is not in 2nd Normal form because BC-->AD is partial dependency.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.