Description
1 Building a Database for Concordia Foundation [40 Points] A recent meeting among the board of directors of Concordia Foundation has been concluded with the decision of collecting and storing information pertaining to all organizations in CF 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. Sherbrooke University, Student Center, etc.), address within CF, and contact numbers. • Organization are classified as either a University, Company, Vendor, or Activities Center. Texas A&M University, Al-Jazeera for Children, Java Cafe, and Concord Recreational Center are examples of each category, respectively. CF records the majors, product line, cuisine, and types of activities for the above categories, respectively. • One organization has many projects but one project is only assigned to one organizations. each project has id , name , start date and end date • 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, CF records the end date of their contracts. • An employee may be appointed as a manager. A manager manages one or more organizations. • Each organization should be managed by at most one manager. • One project has many employees and each employee is a member of one project at least. Also, the number of hours worked by the employee on a single project are recorded. Page 2 2pts (a) Consider the assigned relationship between the entities organization and project. 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. 15pts (b) Draw the E.R. diagram for CF’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 constraints. 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. 2 Extending CF’s Database [30 Points] Concordia foundation wishes to conduct some events in different organizations. There are four types of events, seminars, food festivals, dance festivals, and product release events. There are employee visitors for each event. The name and contact number of visitors should be stored for future event notifications. 15pts (a) Extend the E.R. diagram from Exercise 1 to include event and visitor entities and the associate relationship. Clearly indicate primary and partial keys, cardinality constraints, weak entities (if applicable), and participation constraints. 15pts (b) Write SQL statements to create tables corresponding to your new E.R. diagram. Make sure to indicate primary keys, and foreign keys (if any). Do not specify on delete clauses or check constraints. Page 3 3 Building a Database for Concordia Academic Advising System [30 Points] Concordia University wishes to maintain a database for academic advising. Students can book advising sessions only with advisors from their own department. For simplicity, consider two departments, Engineering, and Art. An advisor has name and contact information. A student can schedule a session with an advisor on a specific date and time. 6pts (a) Draw the E.R. diagram for the academic advising system as described above. Make sure to indicate primary and partial keys, carnality constraints, weak entities, and participation constraints where applicable. Please state any assumptions you make. 12pts (b) 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. 12pts (c) When a student arrives for a session, the advisor must see the history of previous advising session of the student. Write a view to achieve this task. 4 Submission • The assignment is due at 11:59PM on January 30th, 2021. • The submission for this assignment consists of two files: – Your answers for the problems as a single PDF document with the following name pattern: assignment1___.pdf – A signed “Concordia Expectation of Originality” form found here: https://www. concordia.ca/content/dam/ginacody/docs/Expectations-of-Originality-Feb14-2012. pdf • We highly encourage the use of digital tools to create ER diagrams such as: https: //app.diagrams.net/. The rest of the problems must be digitally typed (photos of handwritten answers are not allowed). • If you have any problems with the submission, contact your respective TA: – Monday lab: Philippe (p_arrie@live.concordia.ca) – Monday lab: Ahmed (ahmed.aly.20211@mail.concordia.ca) – Tuesday lab: Yasaman (y_sabbag@live.concordia.ca) – Tuesday lab: Hussein Abdallah (hussein.abdallah@mail.concordia.ca) – Wednesday lab: Mossad Helali (mossad.helali@mail.concordia.ca) – Wednesday lab: Reham Osama (rehamosama1993@gmail.com) 5 Late Policy • If you hand in on time, there is no penalty. • 0-24 hours late = 25% penalty. Page 4 • 24-48 hours late = 50% penalty. • More than 48 hours late = you lose all the points for this project. Page 5
