Submit your solution document in the form of a single MS Word file on or before the cut-off date shown above.
_________________________________________________________________________________

This TMA is based on the following four tables of a relational database model for dog grooming company:

Owner(ownerId(PK), name, address, contact(AK))
Service(serviceId (PK), serviceName, cost)
Dog (petId(PK), name, breed, dateOfBirth, gender, ownerId(FK))
GroomingHistory (petId(PK)(FK), date(PK), serviceId (PK)(FK))
where GroomingHistory.petId must exist in Dog.petId and
GroomingHistory. serviceId must exist in Service.serviceId
Payment(paymentNumber(PK), paymentMode, petId(FK), date(FK), serviceId(FK)
where Payment.petId, date, serviceId must exist in GroomingHistory. petId, date, serviceId

Assume the following:
 for the Owner table,
 all columns are required.
 ownerId is exactly 9 characters long. It starts with a letter and ends with a letter, and there are 7 digits between the letters.
 name is up to 40 characters long.
 address is up to 60 characters long.
 contact is 9 digit long, starts with a 9, 8 or 6 and is a candidate key.

 for the Service table,
 all columns are required.
 serviceId is exactly 4 characters long. It starts with 2 letters followed by 2 digits.
 serviceName is up to 40 characters long,
 cost is a number with 2 decimal places and is at least $15 and at most $250.

 for the Dog table,
 all columns except name, are required.
 petId is exactly 6 characters long. It starts with a letter followed with 5 digits.
 name and breed are each up to 40 characters long.
 dateOfBirth must be at least 8 weeks before today’s date, that is, a dog must be at  least 8 weeks old when it registers,
 gender is 1 character long and can take the values ‘M’ or ‘F’.
 for the GroomingHistory table,
 all columns are required.

 for the Payment table,
 all columns are required.
 paymentNumber is a surrogate key which starts at 1, incemented by 1.
 paymentMode is either cash, master, visa or cheque.

 Cardinalities and minimum cardinality enforcement actions
C1 Cardinality between Owner and Dog is 1:N, M:M with delete being disallowed and update is cascaded.
C2 Cardinality between Service and GroomingHistory is 1:N , M:O with delete being disallowed and update is cascaded.
C3 Cardinality between Dog and GroomingHistory is 1:N, M:O with delete and update cascaded.
C4 Cardinality between GroomingHistory and Payment is 1:N, M:O with delete being disallowed and update is cascaded.

Question 1 (45 marks)

This question assesses your knowledge and understanding on SQL Data Definition Language and Data Manipulation Language, views and stored procedure covered in Chapter 7.
(a) Write SQL statements to create the Dog, Service and the Payment tables.
(b) Consider the cardinalities and minimum cardinality enforcement actions: C1 Cardinality between Owner and Dog is 1:N, M:M with delete to ownerId being
disallowed and update is cascaded.
C2 Cardinality between Service and GroomingHistory is 1:N, M:O with delete to  serviceId being disallowed and update is cascaded.
C3 Cardinality between Dog and GroomingHistory is 1:N, M:O with delete and update to dogId cascaded.
C4 Cardinality between GroomingHistory and Payment is 1:1, M:O with delete and update being cascaded.

State with explanation, whether and which minimum cardinality action will be enforced for the following situations:

(i) A new dog has just registered but does not need a grooming service yet.

(ii) The only pet dog of an owner has just died. A staff wishes to delete its records in the Grooming History table first and then delete its record in the Dog table.
(iii) The owner of a dog wishes to give his one of his pet dogs to another customer of the grooming company.

(c) Use the data to test your code for this part of the question.
(i) Construct a view named ServicePopularityView to list all the services offered by the company and the number of times they have been provided.
(ii) Using the view in (i), write an SQL query to show dogs that have not used the service that is most popular.

Question 2 (35 marks)
This question assesses your knowledge and understanding of the database re‐design topic covered in Chapter 8.

Consider only the base tables in the database of the dog grooming company and the following view, trigger and stored procedure defined in the database:
 a view named DogGroomingHistory constructed from the Dog, Service and GroomingHistory tables
 a stored procedure named PromoteService that uses the DogGroomingHistory view and the Owner and Service tables.

(a) Draw the complete dependency graph for the database.

(b) The dog grooming company is facing stiff competition. In response, its management has planned several changes to its business policies.
 The company will expand its services to include services for other types of animals such as cats and rabbit. Therefore, when registering a pet animal, the type of animal
must be included to the data presently collected for registering a dog.

 The company will incorporate fairer pricing for each grooming service and takes into account the size of the animal. The current pricing is for large animals. Medium siz
animals pay 20% less while small animals pay 30% less. Thus, during registration and over time, the size of each dog is determined and recorded/updated if necessary.

 The cost of the same grooming services will be different for the different types of animals.

(i) Show how you will redesign the database by listing the complete set of tables and including all necessary columns specifying primary, foreign keys and constraints.

Indicate which tables are unchanged and if a table is changed, give a short statement to indicate what have been changed.

(ii) List specific steps, including the SQL codes where applicable, to redesign the database for these changes:

(c) Write SQL code to determine whether (petid, date) can determine serviceId for the GroomingHistory table. If so, explain whether it is good to replace the current key with (petid, date).

Question 3
This question assesses your knowledge and understanding of managing multi‐user databases covered in Chapter 9 and Chapter 10.For this question, use the structure of  the database that has not been redesigned.

(a) Write a trigger to enforce this constraint: A dog is at least 3 months old. Otherwise, the service must not be provided. Display appropriate messages whether or not the service can be extended.

Show that your trigger is working by submitting the result of screenshot(s) of the run(s) in your TMA word document.
(b) Both Mary and Jane wished to make an appointment for grooming services for their dogs on the same time slot with groomer Jones. They went online to make the booking. State whether a lost update can result due to the transactions being non-atomic. Explain your answer.

(c) Given transaction log below, explain what is done during the database recovery procedure for transactions T1, T2, andT3.