Question 1: Relational algebra (20 marks)
You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.
The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold).
SUPPLIER (SNo, SupplierName, City)
PART (PNo, PartName, Weight)
PROJECT (JobNo, JobName, Country)
SUPPLY (SNo, PNo, JobNo, Quantity)
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.
Question 2: SQL – SELECT queries (20 marks)
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.
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 2 marks. These tables exist in arion and are owned by the user dtoohey.
Question 3: Further SQL (15 marks)
You have been given the following specifications of a simple database for keeping track of lectures and those who presented the lecture at a national Biological Sciences conference (note that primary keys are shown underlined, foreign keys in bold).
You should run your SQL to demonstrate that it works correctly, and paste in the statements used plus the output from Oracle.
PRESENTER (PresenterNo, PresenterName, Biography, InstitutionName)
LECTURE (LectureNo, LectureName, Description, Theme, Capacity, DateAndTime, PresenterNo)
Based on the table specifications provided, answer the following questions. Each question is worth 3 marks.
Question 4: Normalisation (20 marks)
The following question is based upon the APPOINTMENT relation below that lists details of appointments in a Podiatry Clinic.
StaffNo: Unique number used to identify each Podiatrist
Podiatrist: The name of the Podiatrist
PatientName: Name of the patient for whom the appointment has been booked
PatNum: Unique number allocated to individual patients
PatTelNo: Telephone number of the patient
ApptDateTime: The time and date when the appointment has been booked
RoomNo: The room in which the appointment will take place
RoomExt: The telephone extension number of the treatment room.
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:
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 questio
Q5. Conceptual design 25 mark
Your task is to develop a data model for the Dripping Wet Water Company (DWWC). DWWC is the sole supplier of water to the citizens and businesses of the town of RandomTown.
Each of DWWC’s customers is classified as being either residential or non-residential. Each customer has at least one water meter that measures the water consumed by the customer at a particular address. Water meters must be replaced when they have been in use for 5 years.
All customers are billed monthly. Meter readers, who are employed by DWWC, are paid per meter they read. They are each allocated up to 1000 meters to read each month.
Once the reader has read the meter, the reading is recorded and the bill for that customer is calculated based on the consumption since the last reading. For example, if the last meter reading was 123580 kilolitres (kl) and the next reading was 123683kl, the consumption for that period would be 103kl.
Residential customers’ bills are calculated as follows: for each residence, the customer is billed a flat rate of $25 supply charge. They are then billed a further 24c for each kl consumed since the last reading. There are no GST or other taxes imposed.
Non-residential customers’ bills are calculated as follows: there is no supply charge. They are charged only for the consumption since the last reading. They are charged 24c for the first 100 kl, 30c for the second 100 kl and 50c for each kl in excess of 200kl.
All customers are given 30 days from the billing date to pay their account. They can pay by cash, paypal, or EFT. Customers who do not pay within the 30 days will have an accounting charge of $5 added to the bill. After 45 days a further $10 accounting charge is added. At 60 days, the customer’s water supply is cut off. Once a customer has had their supply cut off, the person or business responsible for the bill will not be able to create a new account at a different address until their arrears have been cleared.
Each customer has the right to request a report that details the historical consumption of water at the address of their account, whether they were the customer at the time or not.
As Assignment 2 will require you to implement the DWWC database, you will need to take into account the feedback you receive on your conceptual design when commencing your logical and physical designs.
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.