ICT285: You are Working with a Database that Stores Information about Suppliers: Databases Assignment, MU

Question 1: Relational algebra 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 inbold). SUPPLIER …
Continue reading “ICT285: You are Working with a Database that Stores Information about Suppliers: Databases Assignment, MU”
The post ICT285: You are Working with a Database that Stores Information about Suppliers: Databases Assignment, MU appeared first on Assignment Help Singapore No 1 : Essay & Dissertation Writers, SG.

Question 1: Relational algebra

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 inbold).

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.

NOTE:

• You can use the symbolss, P, 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.

1. List the name and city of all suppliers.
2. List all details of Projects being run in Australia.
3. List the Project name and Part name of any Parts where more than 50 of the part has been supplied to a particular project
4. List the names of Suppliers who have supplied the Part with the name “Valve Stem04”.
5. List the names of Projectsthat have had Parts supplied by Zloof Co# or a Supplier located in Perth.
6. List the names of all Projects and the Parts supplied to them, if any.
7. List the name of any Supplier who supplied Parts to the Project called “Project 01” and to the Project called “Project 02”.
8. List the name of any Supplier who supplied Parts to the Project called “Project 01” but not to the Project called “Project 02”.
9. List the name of any Supplier who supplied Parts to the Project called “Project 01” or to the Project called “Project 02”.
10. List the names of any Supplier who has supplied Parts forall Projects run in Australia.

Question 2: SQL–SELECT queries

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.

1. Family name and suburb of patients who live in the State named ‘WA’
2. Name and suburb of patients who live in the State named ‘WA’ and have been treated by Dr Brian or Dr Barbara
3. Name and suburb of patients treated by Dr Brian but not Dr Barbara
4. Name and suburb of patients who have been treated by both Dr Brian and Dr Barbara
5. 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)
6. Name of patients who have been treated by Dr Brian and who have had an Extended Consultation
7. Total number of patients in each of the different States of Australia
8. Total number of treatments for each item number
9. Total amount of fees charged to any Patient named Jessie Stange
10. Name of any patient who, over the lifetime of the database, received all of the treatment items

Question 3: Further SQL

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. Give the SQL to record the fact that all lectures have had their capacity increased by 10.

Question 4: Normalisation

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:

1. What are the specific problems associated with the current design and why do they arise (about ½ page)?
2. How would you change the current design and how does your new design address the problems you have identified with the current design.

Question 5: Conceptual Design

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 accounts. 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.

The post ICT285: You are Working with a Database that Stores Information about Suppliers: Databases Assignment, MU appeared first on Assignment Help Singapore No 1 : Essay & Dissertation Writers, SG.