Description

Please make sure that you always use notations consistent with lecture notes.
Fri 15 Mar, 5:00 pm
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:

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)
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’.
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.
Late Submission Penalty
Zero mark