Answered step by step
Verified Expert Solution
Question
1 Approved Answer
1 Building a Database for a non-Governmental Educational Foun- dation (nGEF) [40 Points] A recent meeting among the board of directors of a non-Governmental Educational
1 Building a Database for a non-Governmental Educational Foun- dation (nGEF) [40 Points] A recent meeting among the board of directors of a non-Governmental Educational Foundation (GEF) has been concluded with the decision of collecting and storing information pertaining to all organizations under nGEF and their employees. The information will be used primarily for their emergency notification system among other things. An expert (you!) has been hired to design their database given the following description of entities and relations: . Every organization has a unique name (e.g. New York University, Student Center, etc., address within nGEF, and contact numbers. An organization can be classified as University, Company, Vendor, or Activities Center. Teras A&M University, Media for Children, Java U Cafe, and Dou Recreational Center are examples of each category, respectively. nGEF records the majors, product line, cuisine, and types of activities for the above categories, respectively. Every employee has a name, contact number, and employee ID. An employee may work for several organizations, but at least for one. For example, employee X may work for the Recreation Center in the morning while for the Community Center in the afternoon. Accordingly, for every employee, we must record the start and end times at each organization. An employee is either a long-term or a short-term hire and is either local or expatriate. Note that for local hires, nGEF records the end date of their contracts. An employee may be appointed as a manager. A manager manages one or more organiza- tion(s). For example, the Recreation Center and Community Center may be managed by the same manager. Each organization should be managed by at most one manager. 2pts (a) Consider the works for relationship between the entities organization and employee. Correct the errors (if any) in the following E.R. Diagram. You may either use the thin/thick 'arrow' notation of the book, or the M:N notation. Organization Works for Employee 15pts (b) Draw the E.R. diagram for nGEF's database as described above. Make sure to indicate primary and partial keys, cardinality constraints, weak entities, and participation constraints where applicable. Please state any assumptions that you make. 3pts (c) State whether the organization ISA hierarchy has (a) covering and/or (b) overlapping constraints. Explain briefly. 5pts (d) State whether the employee ISA hierarchy has (a) covering and/or (b) overlapping con- straints. Explain briefly. 15pts (e) Write SQL statements to create tables corresponding to your E.R. diagram. Make sure to indicate primary keys and foreign keys (if any). Do not specify on delete clauses or check constraints
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started