Please see attached SQL Assignment
assignment1-specification Question 1 8 Marks The IT Service of a university has designed the following database schema to manage rental contracts and transactions for its residents: • Resident(UniID, Name, Age, Email, PhoneNo, Balance) • Contract(Building, RoomNo, UniID, StartDate, ContractNo, LengthInMonth, Month- lyRent) • Transaction(TranID, TransactionTime, Description, ContractNo, PaymentMethod, Amount) Resident contains resident information and has the primary key {UniID}. Contract con- tains rental contracts with the primary key {Building, RoomNo, UniID, StartDate}. Con- tractNo contains contract numbers, which can uniquely identify each contract. Transaction contains credit/debit transactions on resident accounts (e.g., rental, miscellaneous payments, and returning deposit) and has the primary key {TranID}. The template file mydatabase.sql contains the SQL statements which were designed by the IT Service to create this database. Your task is to answer the following questions by adding SQL statements into mydatabase.sql. Note that, do not change or remove any existing SQL statements that were written in the template file and do not add your DROP TABLE statements into mydatabase.sql. 1.1 The IT Service wants to add an additional attribute, namely EndDate, into the table Contract. Can you help them by adding your SQL statement into mydatabase.sql? (1 Mark) 1.2 The University has informed the IT Service that, for security reasons, each resident must be an adult of age 18 years or older. Can you help them to ensure that the values of the attribute Age in Resident satisfy the requirement? If you can, add your SQL statements into mydatabase.sql. (1 Mark) 1.3 Due to COVID-19, each resident must have a valid phone number, i.e. 10 digit mobile number or 8 digit landline number (with no spaces or area code), so that they can receive emergency notifications on time. Add your SQL statements into mydatabase.sql to make sure that the attribute PhoneNo in the table Resident contains only values that meet the requirement. (1.5 Mark) 1.4 To ensure data integrity, each value of the attribute ContractNo in the table Trans- action must be a contract number existing in the table Contract. Add your SQL statements into mydatabase.sql. (1.5 Mark) 1.5 If a resident is moved out of accommodations of the University, the record of the resident in Resident can only be deleted if all contract records of this resident in Contract and all transaction records of this resident in Transaction are also deleted. For example, if the record of a resident named ”J Smith” is deleted from Resident, then this resident should have no records existing in Contract and Transaction. How can you achieve this? Add your SQL statements into mydatabase.sql. (2 Marks) 2 1.6 Suppose that the University wants to increase the MonthlyRent of the building “U Hall” by 10%. Can you achieve this using a SQL statement? Add your SQL statement into mydatabase.sql. (1 Mark) Question 2 12 Marks Consider the following relation schemas for a relational database moviedb: Movie(title, production year, country, run time, major genre) primary key: {title, production year} Person(id, first name, last name, year born) primary key: {id} Director(id, title, production year) primary key: {title, production year} foreign keys: [title, production year] ✓ Movie[title, production year] [id] ✓ Person[id] Writer(id, title, production year, credits) primary key: {id, title, production year} foreign keys: [title, production year] ✓ Movie[title, production year] [id] ✓ Person[id] Scene(title, production year, scene no, description) primary key: {title, production year, scene no} foreign keys: [title, production year] ✓ Movie[title, production year] Role(id, title, production year, description, credits) primary key: {title, production year, description} foreign keys: [title, production year] ✓ Movie[title, production year] [id] ✓ Person[id] Appearance(title, production year, description, scene no) primary key: {title, production year, description, scene no} foreign keys: [title, production year, scene no]✓Scene[title, production year, scene no] [title, production year, description]✓Role[title, production year, description] Award(award name, institution, country) primary key: {award name} Movie Award(title, production year, award name, year of award,category, result) primary key: {title, production year, award name, year of award, category} foreign keys: [title, production year] ✓ Movie[title, production year] [award name] ✓ Award[award name] Director Award(title, production year, award name, year of award, category, result) primary key: {title, production year, award name, year of award, category} foreign keys: [title, production year] ✓ Director[title, production year] [award name] ✓ Award[award name] 3 Writer Award(id, title, production year, award name, year of award, category, result) primary key: {id, title, production year, award name, year of award, category} foreign keys: [id, title, production year] ✓ Writer[id, title, production year] [award name] ✓ Award[award name] Actor Award(title, production year, description, award name, year of award, category, result) primary key: {title, production year, description, award name, year of award, category} foreign keys: [award name] ✓ Award[award name] [title,production year,description]✓Role[title,production year,description] Your task is to answer the following questions using SQL queries. For each question, your answer must be a single SQL query that may contain subqueries, and you must write your answers into the template file myqueries.sql. 2.1 Which movies have “Alien” in their titles? List the titles and production years of these movies. (1 Mark) 2.2 Who is/are the oldest person(s) in the database? List the id, first name, and last name of this person. (1 Mark) 2.3 How many Oscar awards have been given to writers who were aged 50 and over at the time of winning the awards? List the ids, first names, last names of these writers, as well as their ages when winning the awards. (1 Mark) 2.4 What is the percentage of directors among all the people in the database? List the percentage as a decimal (round to two decimal places). (1 Mark) 2.5 What are the roles appearing in the first five scenes of the movie “The Birds”? List the scene numbers and descriptions of these roles. (1 Mark) 2.6 Which movies have won an NYFCC Award (i.e., movie award) and who have directed these movies? List the titles and production years of these movies, together with the first names and last names of their directors. (1 Mark) 2.7 Which movies have won at least two actor awards in the same year? Show the titles and production years of these movies, as well as the year of awards and the number of awards. (1.5 Mark) 2.8 Who have been nominated for an actor award for at least twice but have never won any? List the ids of these persons, and the award names, years of awards and categories that they have been nominated for. (1.5 Mark) 2.9 Who have starred in the most movies that have won at least one movie award? Show the first names and last names of the persons, as well as the corresponding number of such movies. (1.5 Mark) 2.10 Which directors are one of the writers in every movie directed by themselves? Show their ids, first names and last names. (1.5 Mark) +++++ 4