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
...
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 Murdoch Academic
Passport (MAP) unit https://moodleprod.murdoch.edu.au/course/view.php?id=2684
University policies on academic integrity can be accessed here:
http://our.murdoch.edu.au/Educational-technologies/What-you-need-to-know/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 SQL 15
Question 4: Normalisation 20
Question 5: Conceptual design 25
Total 100Question 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.
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.
Supplier Name, City (SUPPLIER) ON (S1)
b. List all details of Projects being run in Australia.
Country = ‘Australia’ (P1)
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
P2JobName, P1.PartName (S2.Quantity>50 (P, ∞ S2 ) ∞ P2 )
d. List the names of Suppliers who have supplied the Part with the name “Valve Stem 04”.
S1.SupplierName (p1.PartName=”ValueStem04” P, ∞ S2) ∞ S1
e. List the names of Projects that have had Parts supplied by Zloof Co# or a Supplier located in
Perth.
P2.JobName (S1,SupplierName = “Zloof Co#” V S1.City = “Perth”
(S1 ∞ S2) ∞ P2 )
f. List the names of all Projects and the Parts supplied to them, if any.
P2.JobName,P1.PartName ( P2 ∞ P1)
g. List the name of any Supplier who supplied Parts to the Project called “Project 01” and to the
Project called “Project 02”.
S1.SupplierName (P2.JobName = ‘Project01’ n P2.JobName =” Project02”)
[Show More]
