ICT330 Database Management Systems Tutor-Marked Assignment July 2025 Presentation, Singapore

Question 1 (33 marks)

A video streaming company wishes to improve its current business model by offering different subscription types to its customers. Each subscription type will make available videos from one or more production houses e.g., subscription type named A1, introduced in the year 2020 allows customers to view videos from Universal Pictures and Paramount, and subscription type named A2 introduced in the year 2021 allows customers to view videos from Paramount, Frontier Pictures and National Geographic Studios. Each production house has a name and country that it is incorporated in.

A customer starts by opening an account. Account details include an account number, the customer name, address and a balance which can be topped up. After account creation, the customer can select subscription types and durations he wishes to subscribe e.g., A1 for 30 days and A2 for 180 days. Each subscription is paid using the account balance. The subscription rate depends on the subscription type as well as the subscription duration.

There is no limit to the number of videos a customer can stream during the subscription period. Videos details include a video code, a title, description, a video length (in minutes) and language used. A movie video includes a genre whereas a documentary includes the name of the producer. The application allows customers to add comments and to rate videos they have watched. Customer can also make friend requests. If a friend request is accepted, the requester can view his friend’s ratings and comments that are marked public. Private comments and ratings cannot be viewed by friends even if friends requests are accepted.

The application tracks each video streaming such as where a customer stops so that the customer can resume watching from that point. The viewing patterns of customers are also tracked e.g., the start and end points of a video stream, the playing speed and the language of the subtitle if used. The application provides APIs for production houses to extract data for their titles to study the viewing patterns.

Construct a conceptual (ER) model from the statements of requirements to represent the data model, showing

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

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

Question 2 (34 marks)

You are given the following table:

MoviePayment (movieTitle, movieDescription, yearProduced, directorName, directorNationality, directorDOB, actorName, actorNationality, actorDOB, contractFee, roleplayed)

An actor can perform in one or more movies, and a movie may require one or more actors. An actor can play multiple roles in a movie e.g. Robin Williams played the roles of Daniel Hillard and Mrs. Euphegenia Doubtfire in the movie Mrs. Doubtfire. In some cases, a role within a movie can also be played by multiple actors e.g., John Travolta and Nicolas Cage played the role of Sean Archer in Face/Off. Each actor in a movie has one contract fee for the movie, irrespective of whether he has a single role or multiple roles.

An actor can play the same role in multiple movies e.g., Tom Holland plays Peter Parker in movies such as Far From Home, No Way Home and Brand New Day. Of course, different actors can play the same role in different movie e.g., Andrew Garfield played Peter Parker in movie The Amazing Spider-Man.

You may assume that there is one director per movie, a movie is identified by a movie title, a director is identified by the director name, and an actor is identified by the actor name.

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. (8 marks)

Question 2b

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

Question 2c

(i) For each MVD in your answer to Q2(a), explain why it is subsumed or not subsumed.

(ii) Normalise the table to Boyce Codd Normal Form (BCNF) and Fourth Normal Form (4NF) following the steps covered in the module. (according to student answer whether MVD is not subsumed, apply procedure)

(iii) Show how you apply the steps to arrive at the tables, explain whether the structure of each derived table at each iteration is already in BCNF and 4NF (if applicable). (12 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-namen)
• 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

(10 marks)

Question 3 (33 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, 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-namen)
• 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

(ii) Refer to the description of the columns for the column definition, and then write the appropriate SQL statements to implement ALL tables, derived from the transformation of the ERD in Figure Q3(a)(i) in part (i). List your SQL create statements in the order that they should be executed.

Description for columns:

• productionId is a whole number starting from 1 with step increment 1 and is auto-generated.
• title is at most 120 character long.
• genre is exactly 10 characters, either science, crime, historical, nature or society.
• startDate and completionDate of production, both are dates and where completionDate is later than startDate.
• budget is less than 100 million dollars and is at least 1000 dollars. Budget may include cents.
• runningNumber is a whole number.
• expeditureDate is datetime and has the default value which is the current date.
• amount may include cents, is non-zero, no-negative and not more than 50000.
• description is at most 128 character long.
• category code is exactly 4 characters, starting with 2 letters followed by 2 digits.
• categoryName is at most 30 characters long.
• mainCategory is at most 30 characters long.

Constraints:
Refer to Figure Q3(a)(i) for the minimum and maximum cardinalities.
• incurs relationship – delete and update disallowed.
• isFrom relationship – delete disallowed and update cascade.

(iii) Use the data in Figure Q3(a)(iii) to populate the tables implemented in part (ii). List your SQL insert statements in the order that they should be executed.

Question 3b

For part (b), use the tables you have created based on Figure Q3(a)(i). You are not allowed to create view, but you can define inline views where applicable. Formulate SQL queries to display the given result sets:

1. Details of productions about nature and with title that includes island.
2. Details of completed production and the number of days to complete the production.
3. Details of productions and whether budget is exceeded or indicate that project is still ongoing. List in ascending order of the difference between the budget and total spent. Refer to https://www.w3schools.com/sql/sql_case.asp on how to apply the case expression.
4. The number of productions completed and ongoing for genres with productions.
5. The number of completed productions each genre has, the total budget and the total spent for the genre, in descending order of the number of productions and the total budget.
6. Details of productions whose expenditure includes the categories PP03 and PP04.
7. Details of production with the highest number of LO03 expenditure. Include also the total amount spend on LO03. Do NOT assume that there is one row in the resultset.

(23 marks)

—– END OF ASSIGNMENT —–