Assignment 01: ER Diagram 

IT5100C: Database Modelling and Programming
Assignment 01: ER Diagram 
Objective
The objective of this team project is for you to apply what you have learned in class to design and develop
a database application using PostgreSQL. The project is to be done in teams of four students. The project
consists of the following four tasks:
(A01) Design an ER data model for the application. Your ER model should capture as many of the
application’s requirements as possible.
(A02) Translate your ER data model into a relational database schema. Your relational schema should
capture as many of the application’s constraints as possible.
(P01) Implement an SQL Query for each of the functionalities listed in Application Functionalities.
Deliverables
Upload a pdf file named “Assignment01.pdf” Submit your pdf file on Canvas assignment named “ER Diagram” (https://canvas.nus.edu.sg/courses/54297/assignments/99984). Only the latest submission
will be marked, so make sure your submission is before the deadline.
As a general guideline, your ER diagram should:
• not capture constraints not specified in the application’s constraints.
– Any constraints that can be captured but did not may be penalized.
• capture as many of the application’s constraints as possible as per the specification.
– Any constraint specified in the specification but is not captured or incorrectly added may
be penalized.
The submitted pdf file must be at most 6 pages with font size of at least 12 points and consists of the
following:
• The constraints required from the specification.
– The constraints should be categorized by the elements in the ER diagram (e.g., entity or
relationship sets).
– Indicates the constraints that are not captured by the ER diagram clearly.
• ER data model of your application.
– You may optionally use the ER diagram tools from https://thisisadi.yoga/cs2102/
ERD/.
– If your ER diagram is too large (e.g., spanning more than one page), you may want to
include a single-page simplified ER diagram (i.e., with non-key attributes omitted) before
presenting the detailed ER diagram.
– Your ER diagram cannot be too small and it must be readable when printed.
∗ The font of the ER diagram must be readable.
∗ Unreadable texts on ER diagram may be omitted from grading even if they contain
correct elements.
∗ You may rearrange your ER diagram manually in the ER diagram tools to produce a
more vertically oriented ER diagram.
• Justification for any non-trivial design decisions made.
– This includes but not limited to the use of ISA hierarchies, aggreagates, and any ternary
relationship set.
Deadlines
The deadline for the submission is 12:00 of Saturday of Week 03 (i.e., 3 February 2024). Only
submissions through Canvas will be accepted. Submissions through other means (e.g., emails) will not be
accepted.
Late Submissions
5 marks (out of 20 ) will be deducted for submissions up to 1 day late. Submissions late for more than
1 day will receive 0 mark and will not be graded.
Template
You may refer to the given template that contains partial constraints named Assignment01.docx. You may
use the template as a starting point of your answer.
2
Grading
The following grading scheme will be used. The details of the grading scheme is hidden.
Validity of ER Diagram (2 Marks)
• The ER diagram should not contain invalid constructs.
• The ER diagram should be readable when printed on an A4 paper.
• Note that the use of invalid constructs and/or unreadable ER diagram may cause deductions
in other areas as the meaning is unknown.
– We will look only at the ER diagram when determining data and constraint requirements.
Data Requirements (6 Marks)
• The ER diagram should capture all the data (e.g., attributes) needed by the application’s
requirement.
Constraint Requirements (8 Marks)
• The ER diagram should capture as many of the application’s requirements as possible.
• The ER diagram should not add constraints not specified in the application’s requirements
whenever possible.
Analysis (4 Marks)
• The report should contain a thorough assessment of the constraints requirements.
• The report should correctly mention the constraints not captured.
3