Assignment 1

Task

The Fermoy House database

The owners of Fermoy House, a Bed and Breakfast guest house in the Blue Mountains of NSW, have approached you to build them a database to help them run their business.

Fermoy House provides overnight accommodation and breakfast to couples and has four guest bedrooms and a detached private cottage in a private part of the garden. Each bedroom, and the cottage, will accommodate a maximum of 2 guests only.

Task 1. Create an ERD that shows the entities, attributes, relationships, cardinality and optionality that describe the booking of a room by a guest. This ERD is to be labelled ERD 1.

In order to create a point of differentiation from other local bed and breakfast houses, Fermoy House will allow guests to have up to 2 of their dogs stay in the attached kennels. The dog names are normally recorded so that Fermoy House can provide guests with a personalised dog collar for each dog.

Task 2. Add the entities, attributes, relationships, cardinality and optionality that describe the addition of a dog(s) by a guest to their booking. Add these to ERD 1. This new ERD is to be labelled as ERD 2.

Breakfast is provided for all guests as part of their accommodation, and guests can choose from either a Continental or a cooked Irish breakfast. This information is normally recorded at the time of booking the accommodation.

Task 3. Add the entities, attributes, relationships, cardinality and optionality that describe the addition of breakfast by a guest to their booking. Add these to ERD 2. This new ERD is to be labelled as ERD 3.

Bookings are normally taken for up to 12 months in advance and guests who have stayed more than 5 times at Fermoy House receive a gift of wine and chocolates on arrival.

Task 4. Add the entities, attributes, relationships, cardinality and optionality that describe how many times a guest has booked a stay at Fermoy House. Add these to ERD 3. This ERD is to be labelled as ERD 4.

Rationale

This assessment item is designed to test your understanding of the following learning outcomes:

Basic database design and modelling concepts, Identification and interpretation of business rules, Creation of an Entity Relationship Diagram from a set of business rules, Application of correct relationships, cardinalities, optionality using the Crows Foot modelling notation.

Assignment 2

Home Library

Tasks:

Using the Home library relation above:

1. Draw a dependency diagram to show the functional dependencies that exist in this relation.
2. Decompose the Home Library relation into a set of 3NF relations and draw a dependency diagram for each of the 3NF relations.
3. Develop the Relational Schema for each of these 3NF relations and show the referential integrity constraints that apply.

Rationale

This assessment item is designed to test your ability to Gather, analyse and model business requirements using Enhanced Entity Relationship Diagrams (EERD), Critically analyse a database design and apply Normalisation Theory and techniques.

Assignment 3

Tasks Part 1

Open the database prime_minister database (prime_minister.sql ) from the ITC556 Interact Resources Databases folder. Answer the following queries using this database.

1. Find certain Governors General of Australia.

a. Find all Governors General of Australia who were Barons at the time of their appointment. List them by Title and name and date that they were appointed to the position. Order the list by ascending date of appointment.

b. Now, format the date of appointment as day of the week, day of the month, month and year; eg. Monday, 01 January, 1901. Order the list by ascending date of appointment.

2. Find certain Leaders of the Opposition.

a. Find all Leaders of the Opposition and their date of appointment, who assumed their position after 01/01/1980.

b. For each Leader of the Opposition listed, add their wife's name and their date of marriage.

3. Find certain Governors General of Australia.

a. List the title, name, date of appointment for Governors General of Australia who were appointed between 01 January 1930 and 01 January 1960. Order by ascending date of appointment.

b. Now add to the results of q3a, the list of Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment as Governor General of Australia. Order by ascending date of appointment.

4. Who are the Opposition Leaders who subsequently became Prime Minister after 1930?

a. List their name, the date they were elected Opposition Leader and the date they were elected Prime Minister. The dates must be formatted as day of the week, day of the month, month in digits and year in four digits; eg. Monday, 01/01/1901. Order the list in ascending date of appointment as Prime Minister.

b. Now add their Deputy Prime Minister's name and the party that they led. Order the list by ascending date of appointment as Opposition Leader.

Part 2

The design of the Prime_Ministers database is now very old. You have been asked to review this design, as shown in the ERD below and advise how it could be updated. You are to complete the following tasks:

5. Advise how you would improve the ability to query information in this database. For simplicity, use only the tables prime_minister, governor_general, ministry and opposition in your answer.

a. What new integrity constraints would you use in each of these tables?
b. Why would you use these integrity constraints? Explain how your constraints would improve queries on the tables.
c. Write the DDL code that would implement your new integrity constraints for the following tables:

i. Prime_minister
ii. Governor General
iii. Ministry
iv. Opposition

(Note: You must keep all of the data attributes currently in these tables. Your implementation should include all existing data attributes and any new integrity constraints)

Attachment:- Database-Assignment.pdf