Hi, need help with all of the questions as per attached. Please let me know if you need any further. 1 an

Question 1

The database used for this question is based upon the following relational schema that represents a very simplified medical billing database (Primary Key, Foreign Key):

Patient (PatientID, FamilyName, GivenName, Address, Suburb, State, PostCode)

Doctor (ProviderNo, Name)

Item (ItemNo, Description, Fee)

Account (AccountNo, ProviderNo, PatientID, Date)

AccountLine (AccountNo, ItemNo)

The Patient relation contains data about patients treated at a typical medical surgery. The details of the Doctors are contained in the Doctor relation. The ProviderNo attribute is a unique code allocated to each registered medical practitioner in Australia. The Item relation contains the details of treatment items, including the ItemNo which is a unique code allocated to each treatment item.

When a patient visits the Doctor, an Account is created. A Patient can only be treated by one Doctor on a particular visit. An Account can have several lines, each of which list the item number of the treatment provided. A Patient can have more than one account for a day.

A. Provide Relational Algebra (NOT SQL) queries for the following (20 marks):

NB: You should present your relational algebra queries as per those in the lecture examples. You may use intermediate relations if you wish. Each query is worth 2 marks.

1. Family name of all patients
2. Family name of all patients who live in the State called ‘Western Australia’
3. Address of any patients whose Family name is Smith and who live in the State called Western Australia
4. Family name of all patients who live in the States Western Australia or South Australia
5. Family name of all patients treated by Dr Brian
6. Family name of all patients treated by Dr Brian but not Dr Barbara
7. Family name of all patients treated by Dr Brian andDr Barbara
8. The dates on which Dr Brian has treated Aliza Wiseman
9. Item Description and the treatment date of all treatments for any patient named Aliza Wiseman
10. Family name of any patient who has at some stage received ALL treatment items

S

B. Provide SQL queries and the result tables for the following (30 marks):

Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 3 marks. These tables exist in sphinx and are owned by the user dtoohey.

The database being used for this question is structured as follows with Primary Keys bolded, and Foreign Keys underlined.

Countries (Name, Country_ID, area_sqkm, population)

Teams (team_id, name, country_id, description, manager)

Stages (stage_id, took_place, start_loc, end_loc, distance, description)

Riders (rider_id, name, team_id, year_born, height_cms, weight_kgs, country_id, bmi)

This database includes the results of the 2012 Tour de France. Riders are from countries and ride stages for teams. Teams are from countries.

a. Write a query to report the information for all Riders whose "first" name starts with Jerome

b. Write a single query to list all the riders from country 'Belarus'. Do not hard code the country_id for Belarus.

c. Write a single query to report the total time (in seconds) taken by 'EVANS Cadel' to complete the complete race (i.e., all of the stages). The query should not hard code the rider_id for 'EVANS Cadel' (i.e., must do at least one join). Name the output column 'total_time'.

d. Write a single query to find the names of the countries with no riders in the race.

e. Find the longest stage.

f. Create a list (year, numridersborn) where we count the number of riders born in different years. Output columns: year, numridersborn. Order by: year

g. Find the list of riders for whom we don't know the height.

h. Write a query to find the teams who have at least one senior rider, defined to be a rider born in 1973 or earlier.

i. Write a query to find the teams that contain at least 6 riders from the same country as the country that the team is from.

j. Bradley Wiggins won the tour. Write a query to find the riders who beat him in at least 4 stages, i.e., riders who had a better time than Wiggins in at least 4 of the 21 stages.

Question 2

The following question is based upon the SALES relation above that lists details of sales made in a hardware shop.

Commission Percentage: the percentage of the total sales made by a salesperson that is paid as commission to that salesperson.

Year of Hire: the year the salesperson was first hired

Department Number: the number of the department where the salesperson works

Manager Name: name of the manager of the department

You have been asked to design a relational database based on this design. 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 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 and how they apply to this problem. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.

Question 3

The Fly by Night Operation (FBNO) is a newly formed airline aimed at the burgeoning market of clandestine travellers (fugitives, spies, confidence tricksters, scoundrels, deadbeats, cheating spouses, politicians, etc.). FBNO needs a database to track flights, customers, fares, airplane performance, and personnel assignment. Since FBNO 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 FBNO are listed below:

· Information about a route includes its unique number, its origin, its destination, and estimated departure and arrival times. To reduce costs, FBNO 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.