Further SQL

Question 1: Further SQL (15 marks)

a.       Provide ALL of the SQL statements required to insert the details of the following: A customer, John Smith, of 47 Moodle Street, Highwater, WA, 6709, Australia (email: jsmith@someisp.com) has sold a work of art called “Gorillas in the Mist” by the renowned French artist, Gallic Symbol (b. 1972) to the Gallery (i.e., the Gallery purchased it from him). It is a unique Watercolour on Paper and is 45 * 35cm signed by the Artist. The purchase price was $4600 and the transaction took place on 27th July 2020. (7 marks)

b.       You have been given the following specifications of a simple database for a netball association that keeps record of players, teams and matches.

Give the SQL to create the Match table. You may assume that the Player and Team tables have already been created, and that the PlayerID and TeamID colums are of the data type VARCHAR2(5). The result of the match refers to the result for the HomeTeam and can be only W, L or D (win, lose or draw). (5 marks)

c.       Your match table must also include a column that records the scores of both teams in the match. Provide the SQL to amend the original table design to allow for this change in requirement. It is most unlikely (impossible) that a team would ever score more than 999 goals in a match. (3 marks)

Question 2: Normalisation (20 marks)

The following question is based upon the Patient-Treatment relation which records the details of transactions occurring in a medical surgery. You may assume the data are representative.

PATIENT ID

PATIENT NAME

PATIENT DOB

CONSULT DATE

ITEM NUMBER

ITEM DESCRIPTION

FEE

PROVIDER NUMBER

DOCTOR NAME

437

REARDON

4/08/1989

19/7/2020

A012

Standard

56

S55768

Leahy

437

REARDON

4/08/1989

20/7/2020

A012

Standard

56

D42433

Meuller

562

BILSTEIN

12/05/2001

20/7/2020

A013

Short

35

S55768

Leahy

361

WILSON

13/08/1997

20/7/2020

A014

Long

75

S55768

Leahy

895

LEMOS

13/10/1952

20/7/2020

A013

Short

35

D42433

Meuller

895

LEMOS

13/10/1952

20/7/2020

A014

Long

75

D42433

Meuller

678

MORTAL

27/02/1969

20/7/2020

A014

Long

75

S55768

Leahy

432

SOAPIER

1/08/1976

21/7/2020

A012

Standard

56

S55768

Leahy

You have been asked to design a relational database for this system. You know that there are problems with the current design and that it will need to be modified in order to work effectively.

You need to write a 1-2 page report that addresses the following:

1.       What are the specific problems associated with the current design and why do they arise?

2.       How would you change the current design and how does your new design address the problems you have identified with the current design.

In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3, how they apply to this problem, and justify the changes you are making to the system. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.

 

Question 4: Conceptual Design (25 marks)

Terrific Airlines is a newly formed airline aimed at the burgeoning market of clandestine travellers (fugitives, spies, confidence tricksters, scoundrels, deadbeats, cheating spouses, politicians, etc.). Terrific Airlines needs a database to track flights, customers, fares, airplane performance, and personnel assignment. Since Terrific Airlines is promoted as a “…fast way out of town,” individual seats are not assigned, and flights of other carriers are not tracked. More specific notes about Terrific Airlines are listed below:

·                Information about a route includes its unique number, its origin, its destination, and estimated departure and arrival times. To reduce costs, Terrific Airlines only has non-stop flights with a single origin and destination.

·                Flights are scheduled for a route on one or more dates with an airplane and a crew assigned to each flight, and the remaining capacity (seats not taken) noted. In a crew assignment, the employee number and the role are noted. It is a government requirement that the number of hours that flight crew (i.e., pilots, co-pilots, engineers) are in flight must be recorded. There is no such requirement for Non-flight crew (e.g., attendants).

·                Airplanes have a unique serial number, a model, a capacity, and a next-scheduled-maintenance date.

·                The maintenance record of an airplane includes a unique maintenance number, a date, a description, the serial number of the plane, and the employee responsible for the repairs.

·                Employees have a unique employee number, a name, a phone, and a job title.

·                Customers have a unique customer number, a phone number, and a name (it may or may not be their own).

·                A record is maintained of flight reservations including a unique reservation number, a flight number, a customer number, a date, a fare, and the payment method (usually cash but occasionally some else’s cheque or credit card). If the payment is by credit card, a credit card number and an expiration date are part of the reservation record.

What you have to do:

1.       Create an entity-relationship diagram showing the data requirements of the system. Your ERD should be able to be implemented in a relational DBMS. You should use the ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD. The use of a drawing tool such as Visio will make this task easier. However, whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used. Please note that hand-drawn ERDs are not acceptable.

2.      List and explain any assumptions you have made in creating the data model.

Some important things to note:

1.       Part of understanding a system at sufficient enough detail to model well, involves asking appropriate questions. If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS.

2.      The University email server strips out any Visio (.vsd) files that are sent; even if they are included in a zip archive. So, if you want to send a draft of your design to your lecturer by email, you will need to change the extension to something other than .vsd (.blah works well) or paste the diagram into a word document.

3.      Marks will be allocated to each of the following functional areas:

a.      Legend

b.      Entities

c.       Have you included all required entities in the design?

d.      Have you included specialisation hierarchies where appropriate?

e.      Relationships

                                                              i.            Have you designed relationships between the entities that will support the functional requirements?

                                                             ii.            Are your relationships correctly annotated?

f.        Will the overall design support the enterprise requirements?

As Assignment 2 will require you to implement the Terrific Airlines database, you will need to take into account the feedback you receive on your conceptual design when commencing your logical and physical designs.

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?