Reference no: EM133085710

COIT20247 Database Design and Development - Central Queensland University

PART A - Data Modelling Questions

Questions 1, 2 and 3 in this part relate to the ER model given below. The ER model illustrates the entities and relationships for modelling a financial service firm that has been engaged in stock research and trade. Assume the firm has two types of staff - trade managers and research analysts. Analysts make recommendations based on their research, whereas the manager's duty is to trade the stocks with consideration of the recommendations by analysts. Examine the ER model below and answer all the questions in thissection relate to the ER model given below.

ER model

Question 1

Data Modelling
According to the ER model given above, answer either yes or no to the following questions:

(a) Can each manager have more than one email address?
(b) Is it possible for an analyst to recommend more than one stock?
(c) Is a manager required to trade at least one stock?
(d) Are there any other possible types of staff?

Question 2
(a) Explain the total or partial specialisations that have been used in the ER model.
(b)List the reasons for using disjoint classification in the ER model.

Question 3

Converting ER models
Convert the ER model given in Part A into a set of relations that satisfy Third Normal Form (3NF). You do not need to show your workings. You do not need to justifythat they are in 3NF at this stage. You do not need to show sample data. Just show your relations. You should write your relations in either format shown below:

Student (StudentID, StudentName, DateOfBirth)

Enrolment (EnrolmentID, StudentID, DateOfEnrolment)

or:

Student (StudentID, StudentName, DateOfBirth)

Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
Foreign key (StudentID) references Student

Question 4

Relational model and Normalisation

The following table is designed to include customer, seller, and vehicle information for test drives in a car sale firm. But this relation has been wrongly designed. Ithas been assumed that the combination of CustomerID, SellerID,VehicleID and the date of test drive uniquely identifies each tuple in this relation.

VehicleTest

(a) On the above table, if deleting the test drive of Prius on 19/02/2019, justify what anomaly will happen.

(b) What is the highest normal form that this relation satisfies and why?

(c) Normalise the relation into a set of relations that satisfy 3NF using the format as follows:
Customer (CustomerID, CustomerName)
Order (OrderID, Amount, Date, CustomerID)
Foreign key (CustomerID) references Customer

Part B - Structured Query Language Questions

FormulateSQL queries to answer the following information requests. Use the relations CUSTOMER, BOOKING, ROOM, ROOMTYPE and BRANCH provided below. These relations describe the business activities in which customers book a room from a chain-motel in central Queensland area. The relevant data on customer, branch, room, roomtype and booking activities are stored in a database.

Relations

CUSTOMER(CustomerID, FirstName, LastName, Address,MobilePhone,
DateOfBirth,DriverLicenseNo)
ROOM(RoomID, MaxNumOfAccom, NumOfBed, BranchID, RoomTypeID)
Foreign Key (RoomTypeID) References ROOMTYPE
Foreign Key (BranchID) References BRANCH
ROOMTYPE(TypeID, TypeDescription,PricePerNight)
BRANCH(BranchID, BranchName, ManagerFirstName, ManagerLastName,
Address, Telephone)
BOOKING(BookingID, StartDate, EndDate, NumOfPeople, TotalCharge,
CustomerID, RoomID)
Foreign Key (CustomerID) References Customer
Foreign Key (RoomID) References Room

Note: The following E-R diagram may assist you to understand the relations as described as above.

Tables

CUSTOMER

BOOKING

ROOM

ROOMTYPE

BRANCH

Note that:
• You are asked to provide a general solution to each request. If the database contents change, each of your queries should continue to answer the information requested correctly.
• Simple queries are preferred; if your queries are unnecessarily complex you may lose marks.
• For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each request.
• You are not required to sort the results in any order unless requested.
• State any assumption that you make to clarify your understanding of the information request.

Question 1 List the details of rooms that have never been booked. The details include roomID, room type and the price per night.

Question 2 How many rooms does each branch have? Show the manager's name, the branch name and the number of rooms. Order the list so that the manager who has managed the most rooms appears first.

Question 3 Identify the rooms of hotels in Gladstone with the daily rent no higher than $150 per night.

Question 4 Which customers have made more than one booking in last sixmonths? Show the customer names, mobile phones and the number of bookings.

Question 5 Find the details of the bookings that have the most total charges. The details include the customer name, the room ID, the start date, the end date, and the total charge.

PART C
Question 1 Briefly explain the referential integrity rule. Provide a suitable situation when the referential integrity constraint is violated.

Question 2 This question refers to the Part table as used in this unit lecture slides about the customers to order homeware parts. Using SQL DCL command to write a statement to permit a user with the log in ID of JOHNSON to access the Part table and update its UnitPrice value.

Question 3 Consider a relation named STUDENT_ ACCOMMODATION (StudentID, Building, AccommodationFee) as shown in the figure below, where StudentID is the primary key. Assume the value of Building is unique. Explain why this relation is in 2NF but not in 3NF.

Question 4 Explain the purpose of transaction logs and checkpoints.

Question 5 Compare and contrast Data Warehousing against Database.

Attachment:- Database Design and Development.rar