Identify determinants from fds

Assignment Help Database Management System

Reference no: EM132677060

Objective: Assess your understanding of Normalization process to avoid database modification anomalies

Skills needed: given a non-normalized Relation (Table) and a set of functional dependencies FDs, analyze the FDs to normalize the relation up to 3NF or BCNF to avoid anomalies:

1- Identify determinants from FDs
2- Identify types of FDs and draw FDD for a relation.
3- Identify types of FDs required to be eliminated from a table to be in the desired NF 4- Identify Primary Keys in the relations based on FDs
5- Identify the attribute to remove from one relation to a new relation 6- Identify the Foreign Keys when partitioning the relation
7- Write SELECT queries to reconstruct the original relation from the normalized relations

Requirements & Submission:

1. You need to submit One PDF file contains your work to complete the tasks described below.
2. You must include your name at the beginning of the pdf file

Tasks:
The following XyzMaintenance table is used by a XyzHotel to keep track of room inspections, fixes, and information of staff who worked on these inspections.

RoomNo	RoomFeatures	InspectionDate	inspNo	StaffNo	StaffName	Specialty	StaffPhone	RoomIssues	StaffNotes
225	32" TV, Fridge, 2 Queen Beds, Microwave	03/02/15	1	16	John Doe	Carpenter	333-123-4444	Front door lock	Lock fixed
		06/015/15	1	12	Tracy C.	Electrician	666-525-5555	TV not working	TV power line replaced
		06/015/15	2	12	Tracy C.	Electrician	666-525-5555	None
126	45" TV, Fridge , 1 King Bed, Coffee	04/11/15	1	12	Tracy C.	Electrician	666-525-5555	Bathroom lights not working	Bathroom light bulbs replaced
126	45" TV, Fridge , 1 King Bed, Coffee	04/11/15	2	16	John Doe	Carpenter	333-123-4444	Bed headboard unstable	Headboard fixed
115	32" TV, 2 Queen Beds	03/02/15	1	10	Mike K.	Plummer	333-123-6789	No Hot water	Heating element changed

Task 1
a) This table is susceptible to modification anomalies. Provide examples of insertion, deletion and update anomalies.
b) List The Functional Dependencies of the relation schema of the table XyzMaintenance (RoomNo, RoomFeatures, InspectionDate, InspNo, StaffNo, StaffName, Specialty, StaffPhone, RoomIssues, StaffNotes)
c) Draw FDD (Functional Dependency Diagram) of the relation XyzMaintenance showing the type of each Functional Dependency (Partial, Full, Transitive).
d) what are the Keys of the relation and their type (Primary, candidate)

Task 2 Apply the normalization process Up to 3NF on XyzMaintenance .
State why the relation is not in each normal form first, then apply the normalization process and Explain your steps using relation notations only (examples are not required) and show the keys in each new relation.

Reference no: EM132677060

Questions Cloud

What will Khushboo basis be at the end of the current year : What will Khushboo's basis be at the end of the current year and how much income will Khushboo recognize from these transactions

Analysis of a local small and medium enterprise : Analysis of a local small and medium enterprise (SME). Explain to me how you understand its competitive strategy

How much income does Feela recognize : How much income does Feela recognize from the distribution and what is her basis in her interest in Alchemy after the distribution

Benchmarking data supports strategic planning : Benchmarking data supports strategic planning for the healthcare administrator.

Identify determinants from fds : Identify types of FDs required to be eliminated from a table to be in the desired NF 4- Identify Primary Keys in the relations based on FDs

Are the recruitment and training costs still justified : Using this week's course readings and supplemental readings, summarize (1-2 paragraphs) the importance of reviewing ROI for investments in human resources.

Different societies have different moral values : Cultural relativism comes out of the observation that different societies have different moral values.

What is Ryan recognized gain or loss on the contribution : Blake and Ryan form the Poole Partnership. Blake contributes cash of $15,000. What is Ryan recognized gain or loss on the contribution

How your organization communicates internally and externally : Describe in detail how your organization communicates internally and externally. Then develop a comprehensive communications plan which will ensure.

User Account

All Pages