Answered step by step
Verified Expert Solution
Link Copied!

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

image text in transcribed

image text in transcribed

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_step_2

Step: 3

blur-text-image_step3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Knowledge Discovery In Databases

Authors: Gregory Piatetsky-Shapiro, William Frawley

1st Edition

ISBN: 0262660709, 978-0262660709

More Books

Students also viewed these Databases questions