Assignment 1 Question 1 (6 marks)
A music organization hires you to design a small database to store information about the creation and publication of songs. You’re 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 (6 marks)
Convert your ER-diagram from Question 1 into a relational model.
Question 3 (8 marks)
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:
1) Find the titles of ‘comedy’ movies that are shown in the ‘Event’ cinema at ‘George St’.
Please make sure that you always use notations consistent with lecture notes. Different notations will not be accepted. The deadline for assignment 1 is:
Fri 15 Mar, 5:00 pm
2) Find the titles and release dates of movies that are shown in both the ‘Event’ cinema at ‘Chatswood’ and the ‘Hoyts’ cinema at ‘Chatswood’.
3) 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.
4) Find the names of directors who have been to a cinema to watch a ‘fantasy’ and ‘violence’ movie directed by himself.
5) 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 person’s name is unique. So, if a customer’s name is the same as a director’s name, you can regard them as the same person.
Assignment Submission
We accept electronic submissions only. Please submit your assignments as follows:
• The file name should be ass1.pdf.
• Ensure that you are in the directory containing the file to be submitted. (note: we only
accept files with .pdf extension)
• Type “give cs9311 ass1 ass1.pdf” to submit.
• You can also use the web give system to submit.
• Please keep a screen capture (including timestamp and the size of the submitted file)
for your submissions as proof in case that the system is not working properly. If you
are not sure how, please have a look at the FAQ. Note:
1. If the size of your pdf file is larger than 2MB, the system will not accept the submission. If you face this problem, try converting to compress pdf.
2. If you have any problems in submissions, please email to comp9311unsw@gmail.com.
3. We do not accept e-mail submissions, and the submission system will be immediately
closed after the deadline.
Zero mark
Late Submission Penalty