Database Design

The projects table contains information about all the different projects. Each project is given a unique project number as well as a project name. The city attribute denotes the city where the project is being implemented. The employees table stores information about all the employees. Each employee is given a unique employee ID. All the attributes are self-explanatory. The workon table links employees with the projects that they work on. Note that each employee can work on multiple projects while each project could have more than one employee work on it The tables are shown below with some sample records (note that sample records are just samples and they do not represent all the possible scenarios.) projects project number project name citv Chicago San Dicgo super jet emplovees emplovee ID last name first name work phone number work location Main Building ones Zink Smith Doe Jil1 Jack John 8585551212 8585551213 South Campus 8585551214 Main Building 8585551215 North Campus workon employee ID project number Please solve the following: Identify a candidate key for each table If you were obligated to use the city column of the projects table as a candidate key, what restrictions would you have to put on the table to make it work? a) b) c) Can the work phone number attribute of the employees table be a candidate key? Under what circumstances it can and under what circumstances it can t? d) What referential integrity can be declared on each table? State None"if not applicable. 2. (15 Points) Consider the following description: The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches (offensive, defensive, and physical training coaches). During the season, each team plays two games (home and visitor) against each of the other teams. Given those conditions, do the following, draw the ERDs to represent the JCBC database using Chen s notation Your ER diagram should include all the essential attributes for each entity to support the application. You should use Chen's notation You should also include cardinalities on your ER diagram and indicate primary keys as well Wri te any unspecified requirements, and any assumptions that you need to make the specification complete if necessary