ICT285 Databases

ICT285 Databases September Trimester, 2020 Assignment 1 Individual Assignment (20% of the final mark for the unit)

Submission Instructions:

Please submit a single word document using the link in Moodle. The due date is on LMS. You do not need to include a separate cover sheet but you should include your name and student number as part of your document filename. Your name and student number should also be included within in the assignment document.

From the Unit Information Guide:

-It is your responsibility to keep a copy of any assignment handed in for assessment. Make sure you keep a copy of the final (submitted) version as well as the backups you make as you create the assignment. It is recommended that you also keep a hard copy of any written assignments.-It is especially important to maintain regular backups of all your practical work. You should ensure you keep a separate backup on a thumb drive, cloud or similar. It is recommended that you keep both a hard copy and an electronic copy.-Late submission will be penalised at a rate of five percent of the total marks available for the assignment per day (or part thereof). The deadline for submission will be specified in LMS and on the assignment. Each assignment will have a final date by which the assignment will be accepted for marking. Assignments not received by the final date WILL NOT BE MARKED and will be recorded as a 0.-Under exceptional circumstances an extension can be provided. Extensions for assignment submissions can only be granted if requested in advance of the due date for submission, and with a good reason. Applications for extensions should be made to the Unit Coordinator, not to the student's lecturer/tutor who is unable to grant extensions. If an extension is granted, the Unit Coordinator will tell you at the time of granting it whether any penalty in marks will apply to the submitted work.-This unit uses software called Urkund when viewing work that you submit. Urkund is a pattern-matching system designed to compare work submitted by students with other sources from the internet, journals/periodicals, and previous submissions. Its primary purpose is to detect any submitted work that is not original and provide a thorough comparison between the submitted document and the original sources.-More information about how to avoid plagiarism is contained within the

This assignment requires you to answer a number of questions on relational database principles and SQL, and to design a database based on a case study.

The assignment addresses the following learning outcomes for the unit:

LO 2.Demonstrate an understanding of relational database principles and theory LO 3.Demonstrate practical skills in using SQL LO 4.Demonstrate practical skills in data modelling using entity-relationship modelling LO 5.Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF (partly)

Marks are distributed as follows:

Question 1: Relational algebra 20

Question 2: SQL Select queries 20

Question 3: Further SQL15

Question 4: Normalisation20

Question 5: Conceptual design 25

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.

NOTE:

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