Central Plane University uses a single relation RECRUITING to store its data. Fo
ID: 3822593 • Letter: C
Question
Central Plane University uses a single relation RECRUITING to store its data. For each recruiter in the Central Plan University, relation RECRUITING lists the number of recruited students from a particular city. It also lists the population of cities and states from where the Central Plan University recruits students. Relation RECRUITING has the following columns:
Recruiter’s identifier, a unique value for each recruiter
Recruiter’s name
Recruiter’s status identifier (each recruiter has only one status)
Recruiter’s status description
Name of a city from where the recruiter recruits
(A recruiter can recruit from multiple cities and the same city can have more than one recruiter recruiting from it. There can be more than one city with the same name, but within one state each city has a different name.)
State where the city is located
Population of the state
Population of the city
Number of recruits a particular recruiter recruited from a particular city.
Sample data set is provided below:
1. Please identify all non-trivial functional dependencies of the RECRUITING relation.
2. Is the RECRUITING relation defined above in the First Normal Form (1NF)? Please provide an explanation for your answer.
3. Please illustrate how you would normalize this relation to Second Normal Form (2NF). What type of functional dependency(ies) do you need to identify to convert this relation to 2NF?
4. Please convert the 2NF form you have achieved in section c to Third Normal Form (3NF). Please make sure to illustrate the tables. What type of functional dependency(ies) do you need to identify to convert this relation to 3NF?
RecruiterIDRecruiter’s identifier, a unique value for each recruiter
RecruiterNameRecruiter’s name
StatusIDRecruiter’s status identifier (each recruiter has only one status)
StatusNameRecruiter’s status description
CityName of a city from where the recruiter recruits
(A recruiter can recruit from multiple cities and the same city can have more than one recruiter recruiting from it. There can be more than one city with the same name, but within one state each city has a different name.)
StateState where the city is located
StatePopulationPopulation of the state
CityPopulationPopulation of the city
NoofRecruitsNumber of recruits a particular recruiter recruited from a particular city.
City Recruiter ID RecruiterName StatusID Status City State State Noof Recruits Population Population Katy Internal Portland ME 1,350,000 70,000 RI Full Time Internal Grand MI 9,900,000 190,000 Katy Full Time Abra Internal Rockford IL 12,900,000 340,000 Part Time R3 Jana CN Contractor Spokane WA L6,800,000 210,000 8 R3 Jana CN Contractor Portland OR 3,900,000 600,000 30 R3 Jana CN Contractor Eugene OR 3,900,000 360,000 20 Maria R4 Internal Rockford IL 12,900,000 340,000 Full Time Internal rand MN 5,400,000 ll,000 Maria R4 Full Rapids Time Contractor Grand MI 9,900,000 190,000 33 Rapids Please identify all non-trivial functional dependencies of the RECRUITING relationExplanation / Answer
2)The RECRUITING relation is in first normal form because each column in the realtion has a single value.
each column has its single value.
each row is identified by primary key( RecruuiterID).
3)This table is in 1st normal form.
There should be no subset of data that comes with multiple rows in a table. if it occurs then place that data into different table.
Define relationships among the new tables and the base table using foreign key.
4)to convet a table into a third normal firstly is must be in second normal form.
remove all non-key column from that table that is not dependent on the primary key.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.