University of Canberra Faculty of Business Government and Law Semester 2, 2016 Database Design 5915 Assignment 1 This assignment is worth 30 marks constituting 15% of the total marks for this unit. Due date: Friday Week 7 of Semester 2 2016 at 11:55PM 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 Canberra Accommodation Services (CAS) was established recently. CAS requires you to design a database system to enable more smooth operation of the rental accommodation services provided by CAS. The requirement collection and analysis phase of the database design process has provided the following data requirements specifications for CAS database. CAS has three branches in Canberra. The details of CAS branches are given below: Branch name Address Telephone Number Branch Number CAS Dickson 150 Camilla way, Dickson2662, ACT 61502211 610 CAS Brue 12 Page St, Bruce 2617, ACT 62171122 611 CAS Franklin 12 Trent St, Franklin 2612, ACT 62128888 612 CAS database will record and store the data about each of their clients including client's first and last name, postal address and home address (street number, street name, suburb, postcode, city, state), gender and telephone number. The name (first and last name), contact telephone number and address (street number, street name, suburb, post-code, city, state) of client's next-of-kin are also stored in CAS database. For each client the system records and stores all activities of each client including the client's accommodation bookings and payments that each client has made. The client information stored relates to those that made a booking or currently renting an accommodations supplied by CAS. Clients may rent a room from any of the three CAS hotels or any of the CAS service apartments. CAS has twenty two service apartments.CAS has several staff members. For each staff member the following data is stored in CAS database: staff first and last name, staff number, position, gender, date of birth, details of the branch and section that the staff member works in, internal telephone number, office number and branch number. Each CAS branch consists of several sections. These are: agent accommodation booking section, administration section, accounts and payment section and maintenance section. The information about each section is stored in the CAS database. The information about each section includes: section name, location. Each section has one telephone and one fax number. CAS works with several travel agents. Travel agents make accommodation bookings for clients. Travel agent details are recorded in the CAS database and each travel agent is given a travel agent number. The agent accommodation booking section of CAS is responsible for the monitoring of the accommodation bookings by travel agent for clients or by client directly via CAS website. The data stored about each travel agent include: full name of the travel agency, location, travel agent first and last name, travel agent number, position, gender, name of the director of the travel agency, internal telephone number, e-mail address of the travel agent and the website of the travel agency. CAS provides three hundred single and double bedrooms in their three hotels. CAS has also eleven one bedroom and eleven two bedrooms fully furnished service apartments. Each hotel has a hotel manager and all service apartments have one service apartment manager. CAS stores full details of its accommodations. For hotel accommodation CAS database stores the following details: hotel number, room number, room type daily rental price and hotel address. For service apartment CAS database stores apartment number, apartment type (single or double bedroom apartment), daily rental price and its address. A client may rent a room in a hotel or a service apartment. Rental agreements are issued at the start of each rental period with minimum rental period of one day. Each rental agreement between CAS and its clients is uniquely identified using a rental number. The data stored for each rental includes: rental number, duration (start and end date), client's name and accommodation details. At the start of each rental a client is given an invoice for the rental period. Each invoice has a unique invoice number. The data stored on each invoice includes the invoice number, rental number, duration (start date and end date), payment due date, amount to be paid, client's full name and accommodation details and name of staff who issued the invoice. Each client is required to make a payment for each of their accommodation bookings. The method of payment can be cash, check or credit cards. The payment can be performed via CAS branches, CAS website or travel agencies that booked the client's accommodation. CAS database stores the details of all payments made for booking of hotel rooms and service apartments. The details stored about a hotel room payment are: payment number, invoice number, rental number, room number, hotel number, payment method (cash, money transfer or credit card), amount paid and details of the client who made the payment. The detailsstored about a service apartment payment are: payment number, rental number, invoice number, apartment number, payment method (cash, money transfer or credit card), amount paid and details of the client who made the payment. Apartments and hotel rooms are inspected regularly by staff to ensure that each accommodation is well maintained. The details of all staff inspection as well as the condition (satisfactory or not satisfactory) of each apartment and hotel room is recorded in CAS database. The details recorded about each inspection include, accommodation details, date of inspection and the staff number of staff who made the inspection as well as the condition (satisfactory or not satisfactory) of each apartment and hotel room. The details recorded about maintenance services include, accommodation details, date and type of maintenance (electrical, plumbing, painting repairs) and the repairer number who made the repairs and maintenance. Repair requests for each accommodation are performed by each accommodation manager. This is achieved by using a requisition request form. The information on a requisition form includes a unique requisition number, the name of the staff placing the requisition and room number for rooms in hotels and apartment number for service apartments. The description of the repair required is also included. CAS database also records and store the data about each of their repair staff members including staff first and last name, office number, position, gender, date of birth, specialisation (i.e. electrician, plumber, painter) and telephone number. CAS has two seminar rooms. The seminar rooms are rented to any customer that wants to organise a seminar. The cost of hire of a seminar room is $800 per day. Seminar room details are stored in CAS database. CAS seminar room details are: room number, room size, room location and rental price per day. Detail of customers that hire a seminar room is stored in CAS database. These details are: seminar booking number, customer details, dates for which a seminar room is hired (i.e. start date and end date), seminar room number. At the start of each seminar rental an invoice is issued to the customer who rented a CAS seminar room. Each invoice has a unique invoice number. The data stored on each invoice include: invoice number, booking number, duration (start date and end date), payment due date, amount to be paid, customer details and staff number of the staff who issued the invoice. Each customer is required to make a payment for his/her seminar booking. The payment can be performed via CAS branches, CAS website. CAS database stores the details of all payments made for booking of seminar rooms by its customers. The details stored about seminar room payments are: payment number, seminar room number, invoice number, booking number, payment method (cash, money transfer or credit card), amount paid and details of the customer who made the payment. Requirement: For the scenario in the problem above: (a) Identify entity types and their attributes, including the primary keys. (20 Marks) (b) Compile the E-R of the system in third normal form. (state all assumptions that you have made) (10 Marks)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 website of Database Design (6672) on the due date specified above. The first page of your assignment should include the following information: Student Name: Student ID: Assignment Name: Assignment 1