Reference no: EM132674848 , Length: word count:1600

COIT20247 Database Design and Development - Central Queensland University

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 researches, 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 this section 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) A manager must trade at least one stock?
(d) Is it required for a stock that must be recommended by analysts?

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 justify that 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 identifies the information that the funding managers trade the stocks. But this table has been wrongly designed. It has been assumed that the combination of ManagerID, StockCode and TradeDate uniquely identifies each tuple in this relation.

StockTrade
ManagerID ManagerName StockCode CompanyName Action Quantity Price TradeDate
M101 James Cook ORG Origin Energy Buy 10,000 10.10 12-1-2019
M101 James Cook RIO Rio Tinto Sell 20,000 88.50 15-1-2019
M102 Shirley Lin ORG Origin Energy Sell 10,000 11.50 15-1-2019
M102 Shirley Lin BHP BHP Billiton Buy 50,000 31.20 18-1-2019
M103 David Johnson AGL AGL Energy Sell 30,000 17.90 19-2-2019
M104 Linda Coles RIO Rio Tinto Buy 70,000 75.90 19-2-2019

(a) On the above table, if we want to add a record related to the stock - TLS (Telstra), what kind of anomaly will happen? Justify your answer.

(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
Students are required to answer ALL questions from this part.
Each question is worth two marks

Formulate SQL queries to answer the following information requests. Use the relations CUSTOMER, RENTAL, CAR, and VEHICLE_CATEGORY provided below. These relations describe the activities in which customers hire cars from a car hire service firm. The relevant data on customers, cars, and rental activities are stored in a database.

Relations

CUSTOMER(CustomerID, FirstName, LastName, Address, Phone, LicenseNo)
CAR(RegoNo, MakeAndModel, ManufacturedYear, NumOfSeats, CategoryID)
Foreign Key (CategoryID) References Vehicle_Category
VEHICLE_CATEGORY(CategoryID, CategoryName, DailyHireRate)
RENTAL(RentalID, CustomerID, RegoNo, StartDate, EndDate, Cost)
Foreign Key (CustomerID) References Customer
Foreign Key (RegoNo) References Car

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

Tables

CUSTOMER

CustomerID FirstName LastName Address Phone LicenseNo
1 David Hacker 101 Yammba road, Rockhampton 0749008877 089 777 123
2 Tony Morrison 98 South street, Melbourne 0490787772 088777555
3 Issac Newton 90 Heaven road, Sydney 0299001122 099 776 123
4 James Farrell 101 St Lucia Garden, Brisbane 0733099000 090 566 777
5 David Land 345 Illinois road, Brisbane 0739000554 456 000 999
6 Peter Garry 201 South port road, Gold Coast 0745676766 234 090 767

RENTAL

RentalID CustomerID RegoNo StartDate EndDate Cost
1 4 NAK455 1/07/2019 3/07/2019 $105.00
2 4 QWA321 5/07/2019 7/07/2019 $180.00
3 1 QWA321 2/06/2019 5/06/2019 $240.00
4 2 LLP677 15/06/2019 21/06/2019 $280.00
5 3 SUN909 15/07/2019 18/07/2019 $140.00
6 3 NAK455 2/08/2019 5/08/2019 $140.00

CAR

RegoNo MakeAndModel NumOfSeats ManufacturedYear CategoryID
ABC455 Toyota Camry 5 1
LLP677 Toyota Hilux 4 2
NAK455 Toyota Corolla 4 2017 1
PAK561 Nissan Navara 5 2017 3
QWA321 VW Caravelle 9 2017 3
SOU320 GM Cardillac 7 2011 4
SUN909 VW Passat 5 2000 1
QLD101 Volvo XC60 5 2017 5

VEHICLE_CATEGORY

CategoryID CategoryName DailyHireRate
1 sedan $35.00
2 ute $40.00
3 minivan $60.00
4 limos $450.00
5 suv $55.00

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 cars that are Toyota sedan. The details include registration number, make and model, the number of seats, manufactured year, category ID, and category name.

RegoNo MakeAndModel NumOfSeats ManufacturedYear CategoryID CategoryName
ABC455 Toyota Camry 5 1 sedan
NAK455 Toyota Corolla 4 2017 1 sedan

Question 2 ()
Which cars have never been rented out? List the details that include the car's registration number, make and model, number of seats, manufactured year, category as well as the daily hire rate.

RegoNo MakeAndModel NumOfSeats ManufacturedYear Category DailyHireRate
ABC455 Toyota Camry 5 sedan $35.00
PAK561 Nissan Navara 8 2017 minivan $60.00
QLD101 Volvo XC60 5 2018 suv $55.00
SOU320 GM Cardillac 7 2011 limos $450.00

Question 3 ()
Which cars have been rented out more than once? Show the car's
registration number, make and model, manufactured year, and the number of rentals.

RegoNo MakeAndModel ManufacturedYear NumOfRental
NAK455 Toyota Corolla 2017 2
QWA321 VW Caravelle 2017 2

Question 4 ()
Find out the rental activities that have the most expensive cost. This includes the customer names, car registration numbers, make and models, the start hire dates, end hire dates and the costs. Note: You will need to use an aggregate function.

FirstName LastName RegoNo MakeAndModel StartDate EndDate Cost
Tony Morrison LLP677 Toyota Hilux 15/06/2019 21/06/2019 $280.00

Question 5 ()
How many times of rental activity were from each customer? Show the customer name, phone, and the number of rentals. Order the list so that the customers who rented the most appear first.

FirstName LastName Phone NumbOfRental
James Farrell 0733099000 2
Issac Newton 0299001122 2
Tony Morrison 0490787772 1
David Hacker 0749008877 1

PART C
SHORT-ANSWER THEORY QUESTIONS
Students are required to answer ALL questions from this part.

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, Buidling, AccommodationFee) as shown in the figure below. Explain why this relation is in 2NF but not in 3NF.
StudentID Building AccommadtionFee
101 Alpha $320
102 Betta $250
103 Alpha $320
104 Betta $250
105 Gemma $400

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