Reference no: EM133709716

DATA MODELLING QUESTIONS

Question 1

The ER model, provided below, contains errors and is incomplete. You need to identify the errors/omissions, rectify them, and then redraw the diagram, so that the redrawn ER model illustrates the entities and relationships for a Strata unit model with the following requirements:

CQH required a conceptual model for the following scenario:
A Surgeon performs a surgery on a patient in one of the theatre rooms of the hospital. Support staff help the surgeon during the surgery.
Each surgery is done on a certain date for a patient by a surgeon in a theatre with the help of support staff.
A surgery will have at least 1 support staff or many support staff involvement.
A surgeon can do many surgeries on many patients over several days.
A patient can undergo many surgeries over several days by the same surgeon or a different surgeon. For example, a patient who is admitted with a fracture and bruises might need surgery performed for the bones and then plastic surgery to be performed
Many surgeries can be performed in a theatre over several days. But a theatre will surely have at least one surgery allocated per day.
You can assume that on a single day
A surgeon may not perform any surgery but could perform a max of 2 surgeries.
only one surgery is done in a theatre at a time.

Question 2 and Question 3 relate to the ER model given below. The ER model illustrates the entities and relationships for a large motel. The motel has a number of buildings, each with a number of rooms. Customers make bookings for rooms.

Question 2

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

Can a room have more than one facility specified in its description?
Does CorporateDiscount apply to every customer?
Can a customer be both a corporate customer and a private customer?
Is it possible for a building to have no rooms?

Question 3

Converting ER models
Convert your redrawn ER model for Question 1 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/write the relations. You can 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

An incorrect transformation of the Booking entity type (from the ER model given at the start of Part A) to a Booking relation is represented below. The primary key of this relation is BookingID. Examine the relation and answer the questions that follow.

On the above table, if we want to add a record related to a customer, what kind of anomaly will happen? Justify your answer
What is the highest normal form that this relation satisfies and why?
Normalise the relation given above 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

Formulate SQL queries to answer the following information requests. Use the relations CUSTOMER, BOOKING, FLIGHT and AIRLINE provided below. These relations describe customers booking flights that they want to take. These flights are operated by airlines as shown in the tables.

Relations
CUSTOMER(CustomerID, FirstName, LastName, Address, Phone) BOOKING(ReservationID, FlightNo, CustomerID, TravelDate)
Foreign key (FlightNo) references FLIGHT
Foreign key (CustomerID) references CUSTOMER
FLIGHT(FlightNo, DepartureTime, ArrivalTime, DepartureCity, ArrivalCity, Price,
AirlineCode)
Foreign key (AirlineCode) references AIRLINE AIRLINE(AirlineCode, AirlineName, CustomerServicePhone)

Note: In the table of Flight, the DepartureCity and ArrivalCity are the city names which are abbreviated as three letters such as
SYD referring to Sydney.
BNE refers to Brisbane
MEL refers to Melbourne
ROK refers to Rockhampton

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 assumptions that you make to clarify your understanding of the information request.

Question 1  Show customers who have not booked any flights.

Question 2  List all flights and customers who depart from Rockhampton.

Question 3 Show details of all flights between Brisbane and Sydney.

Question 4 Find out the most expensive ticket prices of flights. Hint: Use the max() function.

Question 5 Show the number of booking for all flights. Note: Group by the Airline code.

PART C

Question 1
What is a deletion anomaly? Give an example using the table in Part A, Question 4.

Question 2
Briefly explain what is the partial functional dependency. Give an example using the ERD in Question 3 of Part A.

Question 3
Complete the following to provide your understanding of data warehouses. Identify the words that should be used in place of U, V, W, X
Data in the warehouse are defined using consistent naming conventions, formats, encoding structures making them U while focusing on a V allowing us to study trends and changes over W. However, the data in data warehouses is refreshed from operational systems but cannot be updated by X

Question 4
Today, most companies are focusing on utilising cloud-based services. Cloud computing has the following features
Remotely hosted
Ubiquitous
Commodified
Consider any two of these features and explain what they mean in the context of cloud computing.

Question 5
Jonathan created a table called ‘Payroll', however he forgot to add a constraint that can check if the salary is more than $2000 and lesser than $150000. Answer the following given this situation:
Can Jonathan add the constraint to the existing table? YES/NO?
What is the type of constraint that Jonathan should add?
Help Jonathan by providing the query that he would need to run.

Question 6
Use the following key words and insert it into the correct blank (represented by A,B,C,D) within the following details for databases.

Users are A aware of the physical spread/location of the B in distributed databases, since they appear as a C database. This feature called D is one of the important advantages of distributed databases.