ICT330 Database Management Systems Tutor-Marked Assignment Questions 2026, Singapore

ICT330  Tutor-Marked Assignment January 2026 Presentation

This assignment is worth 18% of the final mark for ICT330, Database Management Systems.

The cut- off date for this assignment is Monday, 02 March 2026, 2355 hours.

Note to Students:

You are to include the following particulars in your submission: Course Code, Title of the TMA, SUSS PI No., Your Name, and Submission Date.

Answer All Questions. (Total 100 marks)

Please note that Questions 1, 2 and 3 are unrelated although they have the same domain. Each question can be worked on independently of the other questions.

Question 1 (33 marks)

It is envisioned that all citizens can use a portal to make and/or cancel appointments for public health practitioner, categorised as general practitioners, specialists, physiotherapists etc.

The portal will allow citizens to describe medical conditions that they wish to see a health practitioner for. Citizens first choose from a standard list of medical conditions that best match their conditions. The standard list includes medical conditions, each medical condition has a unique code, a name and a description. After choosing the relevant medical conditions from the standard list, citizens enter a detailed description for each of those medical conditions selected. Each detailed description is timestamped.

Next, citizens make an appointment request. They select those medical conditions they have described earlier or for previous requests, that they wish to seek consultation for. A request id is generated with a request date and time. If the described conditions for the appointment request are newly entered, the system selects a general practitioner based on the earliest availability. It is a policy that appointment for newly described medical conditions are first seen by a general practitioner who will apply judgement to determine whether referrals for specialised consultations are required.

Note that the system records also a field of specialty, e.g., sports physiotherapy or internal medicine, for each medical condition in the standard list where applicable. Similarly, a field of specialty can be recorded for each health practitioner if applicable.

If the medical conditions selected for the current request are all those that have been previously seen by a health practitioner, then a health practitioner from the same specialty is selected. For example, if the citizen saw a sports physiotherapist previously for those conditions, then a sports physiotherapist (rather than a general practitioner) is selected for the new appointment request, again based on the earliest availability. It is necessary to record which slots for each health practitioner have been taken to allow the system to select earliest available suitable health practitioner. A field of specialty has a unique specialty code, a name and a description.

A consultation by any health practitioner may result in referral to other specialists or therapists or may result in a follow up consultation. For each referral or follow- up consultation, the system generates new appointment request. Hence, an appointment request may be initiated by a citizen or through a referral or for a follow- up consultation for the citizen. For appointment request for a follow up consultation, the same category of health practitioner will be assigned to the new request. Not all consultations initiate new appointment requests if it is assessed that is possible that a citizen’s medical condition is resolvable with the current consultation. Furthermore, not all appointment requests end with a consultation as some citizens may miss their appointment.

Currently, a consultation is either a medical consultation or a therapy session, e.g., physiotherapy. The health practitioner handling the consultation will record one or more consultation notes for each medical condition the consultation is for.

For a therapy session, the therapist records the duration of the session and the treatments provided e.g., manipulation, strengthening exercise. The therapist chooses from a standard list of possible therapy treatments. Each treatment has a unique code, a name and a description. The therapist then records details of the treatment performed e.g., neck rotation and extension for strengthening exercise.

For a medical consultation, the health practitioner may request for investigations to be carried out, e.g., blood test or ultrasound scan. The health practitioner chooses also from a standard list of possible investigations. Each investigation has a unique code, a name and a description. The results of the investigation should be recorded in the system once available.

A medical consultation can also result in a prescription for medication. The health practitioner chooses from a standard list of drugs. Each drug has a unique code, a name and one or more contraindications e.g., pregnancy. The health practitioner has to ensure that citizens are not allergic to prescribed drugs, as well as those drugs chosen do not interact negatively with each other. The health practitioner also indicates the dosage, duration and instruction for each prescribed drug in the system.

Without adding any attribute/identifier not described in the question, construct a conceptual entity relationship (ER) model from the statements of requirements to represent the data model, showing the data requirements in one diagram.

• entities, with entities name, relevant attributes and identifier, (16 marks)
• relationships with maximum and minimum cardinalities and relationship name (17 marks)

State your assumption(s) for only data requirements that are not specified. Show your model as ONE diagram.

Question 2 (32 marks)

You are given the table:

Prescription(prescriptionId, deliveryDatetime, patientId, name, address, drugCode, drugName, dosage, instruction, contraindication)

This table is used by a pharmacy to deliver prescribed drugs if a patient request for home delivery.

A prescription is identified by a prescription id. Each prescription contains one or more drugs, each with its required dosage. A drug is identified by a drug code, has a drug name and comes with one or more instructions e.g., after food and no alcohol, and one or more contraindications e.g., pregnant women and diabetic persons. A patient is identified by patient id.

Each prescription is assigned a delivery date and time and a patient to deliver to.

Question 2a

Formulate and list the functional and multi- valued dependencies (MVD). Do not introduce any new columns not in the given table. State your assumption(s) for only data requirements that are not specified. (7 marks)

Question 2b

Draw dependency graph and propose candidate key(s) for the table. Show composite keys in brackets. (4 marks)

Question 2c

(i) Explain whether the MVDs in your answer to Q2(a) are subsumed or not subsumed.

(ii) Apply the BCNF approach to normalise the table to Boyce Codd Normal Form (BCNF) and to Fourth Normal Form (4NF).

Show how you apply the steps for all FDs and unsubsumed MVDs. For each pair of two tables resulting from an iteration, state whether each of them is in BCNF or in 4NF or otherwise and explain why. (10 marks)

Question 2d

Assemble your BCNF and 4NF tables and constraints clearly by giving each table and the referential integrity constraint(s), if any, in the forms described below:

Tables in this form:
TABLE- NAME(col- name1, col- name2, …, col- name n)
indicate (PK) against the primary key and indicate (FK) against the foreign keys, if any.

Referential integrity constraint(s), if any, together with the child table in this form:
TABLE- NAMEx.col- namex1 …col- namexn must exist in
TABLE- NAMEy.col- namey1…col- nameyn

Note that there is no need to show the derivation steps or explain them for this question part. Simply provide the final tables, the primary keys, the foreign keys and the foreign key constraints. (11 marks)

Question 3 (35 marks)

All SQL statements for this question (create, insert and select statements) MUST be in text format.

You MUST also paste the screenshots of the result of executing all your SQL statements.

Question 3a

(i) Transform the ERD in Figure Q3(a)(i) into a logical model.

Show the tables in the logical model in the form:

TABLE- NAME(col- name1, col- name2, …, col- name n)
indicate (PK) against the primary key, indicate (FK) against the foreign keys, if any, including integrity constraints in the form: TABLE- NAMEx.col- namex1 …col- namexn must exist in TABLE- NAMEy.col- namey1…col- nameyn

(ii) Refer to the description of the columns for the column definition, and then code using SQL statements to implement all tables derived from the ERD in Figure Q3(a)(i).

All columns are required except for the column comments.

List your SQL create statements in the order that they should be executed.

1CT330

Constraints for the relationships in Figure Q3(a)(i):

(iii) Populate the tables you created in Question 3(a)(ii). You must identify the relevant data rows from the view shown in Table Q3(a)(ii) to populate the tables created for Question 3(a)(ii).

List your SQL insert statements in the order that they should be executed.

Enlarged version of the data rows is shown in 3 vertical table cells, in Table

Q3(a)(ii) Enlarged Version.

Question 3b

This question is based on the tables implemented from the ERD in Figure Q3(a).

You must NOT create views using the Create View statement, but you can use inline views in your query formulation. You MUST copy your SQL statements into your solution document in TEXT format. You MUST provide comments on each of your SQL statements. You MUST provide a screenshot of your output you get when you execute your SQL statement.

Marks are given to solutions that adhere to the above instructions.

Formulate SQL queries to perform the following tasks:

(i) List the afternoon (2 pm and later) consultations for pain and where the cost is at least $100$

(ii) List the ages of the patients at the time of visits. You may take 1 year to be 8766 hours or \(3651 / 4\) days.

(iii) List specialists without a consultation in January 2026.

iv) List the number of specialists in each specialty, and the number with and without a consultation.

(v) For each patient, count the number of specialists consulted, the number of consultations and total cost paid per specialty. List according to patient id and specialty in alphabetical order.

(vi) List patients who see specialists from more than one specialty. List in descending order of number of specialties and ascending order of patient id.

(vii) List patients who see specialists from dermatology as well as those from rheumatology.

(viii) List patients who see specialists from cardiology but not from geriatric.

(ix) List specialists with the highest number of consultations.

(x) List specialists with the most number of patients.

xi) List specialists with the number of consultations they have, the number of reviews and their average rating (display with one digit after the decimal point). Specialists without reviews will have zero (display as a whole number) as their average rating.

(25 marks)

END OF ASSIGNMENT