Problem Set 1- Database for Concordia Foundation

Database Systems for Software Engineers
SOEN 363 - 
Problem Set 1

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_<FIRST NAME>_<LAST NAME>_<STUDENT ID>.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)