g. Are the relations in your answer in f) in Boyce Codd Normal Form (BCNF)? (5 p

Question

g. Are the relations in your answer in f) in Boyce Codd Normal Form (BCNF)? (5 points) h. If not in BCNF, Normalize the relations in f to BCNF. Provide justification/s or assumptions for each step in your normalization process. List all primary keys List all foreign keys (5 points) (5 points) (5 points) i. j. Section - II a. Based on your normalization in section-I, draw the entity relationship diagram using the b. Identify and include any cardinalities that you believe exist, based on the information c. Does your database design exhibit database integrity? Justify your answer/s. (10 points) Crow's foot notation. (10 points) provided. (5 points) Section III Provide the SQL syntax for Data Definition Language to create the database you have designed. Include the MsSQL Server database for the database you designed. (10 points)

Explanation / Answer

Answer is as follows :

According to the Chegg Guidelines, We are allowed to answer only first three parts of section I, so please repost others.It would be helpful

Functional Dependecies :

So here,

Part a)

Partial Dependency : Partial Dependency is that , if a relation x -> y than y is depedennt on the part of x not on the full combination of x. Where x is super key or candidate key.

So in the given example Project Name depend Upon Project Number

and Employee Name, Job class depends upon Employye Number.

But according to given schema the candidate key is (Projcet No, Employee No.)

So the above two defined relations, they are depend upon part of candidate key, not on the full candidate key.

So , on the above relations partial dependency exist.

Transitive Dependency : Transitive dependency is that if x -> y and y -> z than x -> z.

So in given relation

Project Name is deopend on Project Number i.e Project Number -> Project Name

Employee Name, Job class depends upon Employye Number. i.e. Employee Number -> Employee Name, Job Class

But Project Number,JobClass -> Charge/Hour, Hours Build

So there exist Employee Number -> Job Class and Job Class -> Charge/Hour, Hours Build

So also Employee Number -> Charge/Hour, Hours Build

that leads to transitive dependency.

Part b)

Multivalued Dependecy : Multivalued Dependency is that where more than value are present for one record.

So in this there are multiple record of same employees.For e.g. In same table employye number 105 come two times with same details of Emoloyee Name and Job Class.

That leads to Multivalued Dependency.

Part c)

From above the generated candidate key is:

{Projecrt Number , Employee Number }+ -> {Projecrt Number ,Project Name, Employee Number ,Employee Name, Job Class, Charge/Hour, Hours Build , Total Hours}

One more keys is there : {Projecrt Number , Employee Number, Job Class}

{Projecrt Number , Employee Number, Job Class }+ -> {Projecrt Number ,Project Name, Employee Number ,Employee Name, Job Class, Charge/Hour, Hours Build , Total Hours}

if there is any query please ask in comments...

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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