[Solved] COMP9311 Assignment1

A music organization hires you to design a small database to store information about the creation and publication of songs. Youre given the following requirements:

• A song is identified by song ID. For each song, we want to record its title and genres. A song may have multiple genres.
• An artist is identified by his/her artist ID and we also record his/her name, birth date, and contact number. A song must be created by one or more artists and an artist must have created at least one song. We also want to record the role (e.g. writer, producer) when an artist participates in the creation of a song.
• A company is identified by its company ID, and we also need to know its name and address. The address is composed of street and suburb. An artist may join multiple companies and a company must have at least one artist. Some artists may not join any companies. The number of artists in a company is needed.
• An album is uniquely identified by its album ID, and we also need to know its name. An album must have one or more songs. A song must belong to exactly one album. We also want to know the number of songs in an album. For each album, we record the company which publishes it. A company may publish zero or more albums and an album must be published by exactly one company. We also want to record the publish date of an album. Draw an ER diagram to represent this scenario, and clearly state the assumptions you make if any.

Question 2

Convert your ER-diagram from Question 1 into a relational model.

Question 3

Consider the following relational schemas:

Movie (mID, title, runningTime, releaseDate)

Cinema (cID, cName, location)

MovieShowing (mID, cID, sDate, eDate)

Customer (cusID, name, age, gender)

WatchMoive (cusID, mID, cID, date)

Director (dID, name, age, gender)

Filming (dID, mID)

GenreOfFilm (mID, genre)

Write relational algebra expression to answer the following questions:

• Find the titles of comedy movies that are shown in the Event cinema at George St.
• Find the titles and release dates of movies that are shown in both the Event cinema at Chatswood and the Hoyts cinema at Chatswood.
• Find the names of male customers who have watched the movie Aquaman directed by James Wan and have not watched any other movies directed by him.
• Find the names of directors who have been to a cinema to watch a fantasy and violence movie directed by himself.
• Find the names of the customers aged between 30 and 50 who have watched all the films whose running time is longer than 120 minutes and have never been to any Hoyts cinema.

Note that, only the following operators can be used in your answer: Select, Project, Union, Intersection, Difference, Cartesian Product, Join, and Divide. The running time of a movie is counted in minutes. To simplify the questions, each persons name is unique. So, if a customers name is the same as a directors name, you can regard them as the same person.