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.
• You can use the symbols , , etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.
• You do not need to try to make efficient queries – just correct ones.
• Where you use a join, always show the join condition.
a. List the name and city of all suppliers.
b. List all details of Projects being run in Australia.
c. List the Project name and Part name of any Parts where more than 50 of the part has been supplied to a particular project
d. List the names of Suppliers who have supplied the Part with the name “Valve Stem 04”.
e. List the names of Projects that have had Parts supplied by Zloof Co# or a Supplier located in Perth.
f. List the names of all Projects and the Parts supplied to them, if any.
g. List the name of any Supplier who supplied Parts to the Project called “Project 01” and to the Project called “Project 02”.
h. List the name of any Supplier who supplied Parts to the Project called “Project 01” but not to the Project called “Project 02”.
i. List the name of any Supplier who supplied Parts to the Project called “Project 01” or to the Project called “Project 02”.
j. List the names of any Supplier who has supplied Parts for all Projects run in Australia.
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.
a. Family name and suburb of patients who live in the State named ‘WA’
b. Name and suburb of patients who live in the State named ‘WA’ and have been treated by Dr Brian or Dr Barbara
c. Name and suburb of patients treated by Dr Brian but not Dr Barbara
d. Name and suburb of patients who have been treated by both Dr Brian and Dr Barbara
e. Item Description and the treatment date of all treatments for any patient named Jessie Stange (i.e., Given name is Jessie, family name is Stange)
f. Name of patients who have been treated by Dr Brian and who have had an Extended Consultation
g. Total number of patients in each of the different States of Australia
h. Total number of treatments for each item number
i. Total amount of fees charged to any Patient named Jessie Stange
j. Name of any patient who, over the lifetime of the database, received all of the treatment items
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.
a. Give the SQL to create the PRESENTER table. Choose appropriate data types. None of the attributes should be allowed to be null. Include the primary key constraint.
b. Give the SQL to create the LECTURE table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that if a presenter is deleted from the database, any lectures that he or she is running will also be deleted.
c. Give the SQL to add your own record to the PRESENTER table. Include your name, ‘Murdoch University’ as your institution, and make up a short (10-20 words) biography.
d. Give the SQL to add the attribute VenueName to LECTURE. There are three possible venues: Building A, Building B and Building C, which should be enforced by the SQL definition.
e. Give the SQL to record the fact that all lectures have had their capacity increased by 10.
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:
a. What are the specific problems associated with the current design and why do they arise (about ½ page)?
b. 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 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.
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:
c. Have you included all required entities in the design?
d. Have you included specialisation hierarchies where appropriate?
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 supports the enterprise requirements?
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.