COMP7240 Introduction To Database Concepts: SQL Assessment 1 Answer

COMP7240 - Introduction to Database Concepts

Assignment 1: SQL

This assignment will be marked out of 20. It will count for 20% of the final grade. Below you will find 2 questions to reach this score.

Instructions:

• This assignment should be done individually (no group work).

You must submit two files: mydatabase.sql  for Question 1 and  myqueries.sql  for Question 2 on Wattle before the due date. The template files are available in the folder “Assignment 1 ” on Wattle. You need to enter your answers into the template files, and ensure that the answers are executable as described below:

• For Question 1, it should be executable in your own database, e.g., “u1234567=>i  >mydatabse.sql ”.
• For Question 2, it should be executable in the database moviedb, i.e., “moviedb=>

\i myqueries.sql ”.

Late submission is not granted under any circumstance. You will be marked on whatever you have submitted at the time of the deadline. Please take careful note of deadlines and adhere to them. Of course, if you find yourself in a situation beyond your control that you believe significantly affects an assessment, you should follow the ANU’s special consideration process.

Plagiarism will attract academic penalties in accordance with the ANU guidelines. A stu- dent in this course is expected to be able to explain and defend any submitted assessment item. The course convener can conduct or initiate an additional interview about any sub- mitted assessment item for any student. If there is a significant discrepancy between the two forms of assessment, it will be automatically treated as a case of suspected academic misconduct.

Good luck and enjoy the time you will spend on this assignment.

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. The IT Service wants to add an additional attribute, namely EndDate, into the table
2. Contract.  Can you help them by adding your SQL statement into  mydatabase.sql ?
3. 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)
4. 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.
5. 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)
6. 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)
7. 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 212 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