Database Design

Assignment 1

1. General Information

The purpose of this assignment is to provide you with experience in analysing and designing a database for a given problem. It will help you to understand the nature and purpose of database analysis and design.

This assignment is an individual assignment. There are no restrictions on the use of word processors or similar tools for the production of submissions for this assignment.

Be sure to maintain regular back-ups for any models or material prepared with the aid of software. Loss of files will not be accepted as an excuse for non-completion of this assignment.

Submit your assignment to the Moodle site of this subject. Marked assignments will be available from Moodle website of this subject.

2. Problem Description - The Canberra bed and breakfast company Case Study

Canberra Bed and breakfast requires you to design and implement a database to assist with the administration of the company. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the Canberra Bed and breakfast company database followed by examples of query transactions that should be supported by the database.
Canberra Bed and breakfast has many rooms and apartments for rent in Canberra. It stores the details of each customer. These details include: the customer number, name (first and last name), home address (street, city, postcode), mobile phone number, email, date of birth, gender, category of customer (i.e. local or international), special needs, any additional comments, current status (i.e. currently stay at the Canberra Bed and breakfast accommodation or booked or reserved for Canberra Bed and breakfast accommodation).

The customer information stored relates to those currently renting a room and those that booked or reserved a room. Customers may rent a room or reserve an apartment.

Canberra Bed and breakfast has many staff members. The data held on a staff includes: full name (first and last name), position, branch and section name, internal telephone-number, email, and office number, position.

Canberra Bed and breakfast has a manager, who supervises the operation of the Canberra bed and breakfast. The Canberra Bed and breakfast provides single rooms, which have a room number, building number, and monthly rent rate. The building number and room number together uniquely identifies each room in Canberra bed and breakfast.

Canberra bed and breakfast also offers rental apartments. These are fully furnished apartments for groups of three, four, or five customers. The information held on apartments includes: apartment number, address, and the number of bedrooms in each apartment. The apartment number uniquely identifies each apartment. Each apartment has a daily rental price.

A customer may rent a room in a Canberra bed and breakfast or he/she may rent an apartment for a period of time. Each lease agreement between a customer and Canberra bed and breakfast is uniquely identified using a lease number. The data stored on each lease includes: the lease number, duration of the lease (i.e. lease start date, lease end date), customer's name and customer number, room number or apartment number.

At the start of each lease, each customer is given an invoice for the rental period. Each invoice has a unique invoice number. The data stored on each invoice includes: invoice number, lease number, payment due date, customer full name and customer number, room number or apartment number. When customer makes a payment for an invoice then payment details are recorded. The payment details include: invoice number, payment mount, date of payment and the method of payment (cash or credit card).

Staff on a regular basis inspects each room and apartments. This is to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional comments.

The Canberra bed and breakfast has two wedding rooms. These wedding rooms are rented to any customer that wants to organise a wedding. The cost of hire of a wedding room is $1000 per night. Wedding room details are stored in Canberra bed and breakfast database. The wedding room details are: room number, room size, room location, room size and rental price per night. Detail of customers that hires a wedding room is also stored in the Canberra bed and breakfast database. These details are: customer number, customer first and last name, dates for which a wedding room is hired. Canberra bed and breakfast provides food if required for any wedding booked with Canberra bed and breakfast. The details stored about all wedding rooms hired and food provided is recorded in Canberra bed and breakfast. For each wedding the number guest are recorded in the data based and the type and price of food ordered for each wedding is also recorded.

3. Requirements

For the scenario above:

(a) Identify entity types and their attributes, including the primary keys. Make sure that all your entities and your designed database is in third normal form.

(b) Compile an E-R of the system. (State all assumptions that you have made).

Note: If you make any assumptions, they should be explained clearly.

Submit your list of entities and their attributes, including the primary keys, your E-R diagram and all assumptions you have made to Moodle site of Database Design(5915)/Database Design G (6672) on the due date specified above.

The first page of your assignment should include the following information:

Student Name:

Student ID: