Prepared by: Dr. Deepani Guruge XXXXXXXXXXModerated by: Dr. Nandini Sidnal July, 2020
Assessment Details and Submission Guidelines
Unit Code BN204
Unit Title Database Technologies
Term, Year T2, 2020
Assessment
Type
Assignment 1 (Individual Assessment)
Assessment Title Developing and Managing Databases
Purpose of the
assessment
(with ULO
Mapping)
The purpose of this assignment is to develop skills in managing data in databases and
to gain understanding of data model development and implementation using a
commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Describe issues related to the integrity and security of database (DB) systems.
. Make an informed and critical assessment of database management systems
(DBMS).
c. Develop data models and implement DB systems.
Weight 75 =( Part A 25 marks) + (for Part B 50 marks)
Total Marks 15% of total assessment for the unit
Word limit Not Applicable
Due Date This assignment consists of two parts. Due dates are given below:
Part A: -Database creation and SQL queries due in WEEK 3, 9th August 2020, before
11.50 PM.
Part B: Advance SQL queries, issues related to the integrity and research question
due in WEEK 8, 13th September 2020, before 11.50PM
Submission
Guidelines
• Part A: -Submit WEEK 3 on Moodle.
• Part B: Submit WEEK 8 on Moodle
Part A and B should save separately as below.
“BN204_T2_2020_Assigment1A_your_name.doc”.
• The assignment must be in MS Word format, 1.5 spacing, 11-pt Cali
i (Body) font
and 2.5 cm margins on all four sides of your page with appropriate section headings.
• Reference sources must be cited in the text of the report, and listed appropriately
at the end in a reference list using IEEE referencing style.
Extension • If an extension of time to submit work is required, a Special Consideration
Application must be submitted directly to the School's Administration Officer, in
Melbourne on Level 6 or in Sydney on Level 7. You must submit this application
three working days prior to the due date of the assignment. Further information is
available at:
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-
and-guidelines/specialconsiderationdeferment
http:
www.mit.edu.au/about
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment
BN204 Database Technologies Page | 2
XXXXXXXXXXPrepared by: Dr Deepani Guruge Moderated by: Dr. Nandini Sidnal July,2020
Academic
Misconduct
• Academic Misconduct is a serious offence. Depending on the seriousness of the
case, penalties can vary from a written warning or zero marks to exclusion from the
course or rescinding the degree. Students should make themselves familiar with
the full policy and procedure available at: http:
www.mit.edu.au/about-
mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-
Academic-Misconduct-Policy-Procedure. For further information, please refer to
the Academic Integrity Section in your Unit Description.
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure
BN204 Database Technologies Page | 3
XXXXXXXXXXPrepared by: Dr Deepani Guruge Moderated by: Dr. Nandini Sidnal July,2020
Assignment Description
Answer All Questions (Part A and Part B)
This assignment consists of two parts.
• Part A: -Submit 2 files - due in WEEK 3, 9th August 2020, before 11.50 PM on Moodle.
o Database created
o MS Word document with SQL queries and screen shots.
• Part B: Submit MS Word document with the following on Moodle -due in WEEK 8, 13th September
2020, before 11.50PM.
o Advance SQL queries,
o issues related to the integrity and
o a research question
BN204 Database Technologies Page | 4
XXXXXXXXXXPrepared by: Dr Deepani Guruge Moderated by: Dr. Nandini Sidnal July,2020
Part A: (25 Marks)
1. The snapshot of online database structure is given below. CarServiceDB is a database that keeps track of
information about the Customers, their on-line bookings for car servicing.
Assume that you are working as an IT specialist in this organisation and required to extract information from
this database by executing SQL queries according to the instructions given below. The primary keys and
foreign keys are marked in the following snapshot of database, shown in Figure 1.
Figure 1: Snapshot of database for online line bookings for car servicing © Database Answers Ltd. 2016
1. CREATE DATABASE –YOU CAN FINISH THIS ACTIVITY IN WEEK 2
First, you need to create the above database structure on MS Access and populate all the tables with
suitable data (at least 3 records per table).
BN204 Database Technologies Page | 5
XXXXXXXXXXPrepared by: Dr Deepani Guruge Moderated by: Dr. Nandini Sidnal July,2020
a. You only need to create 5 tables including 4 tables Car, Customers, Mechanic (mechanics that will
take care of the service) and Bookings (their on-line transactions). You do not have to create other
tables in the ER.
. Populate all the tables with suitable data (at least 3 records per table). For Customer_ID and
Cust_name you need to use your MIT ID and your name.
You can use datasheet view in MS Access
OR
INSERT INTO SQL statement (as given below) to enter suitable data records.
INSERT into TableName
VALUES (“..”,”..”,.....)
You are required to upload your database file on the submission link and 10 marks will be deducted as
per assignment ‘Marking Criteria’ for not submitting the database file.
This is an individual assignment; it should be your own individual work (You should not copy Ms Access
Database). If not, this is considered as cheating and you will get zero marks for the whole assignment.
XXXXXXXXXXMarks)
2. WRITE SQL QUERIES
Write SQL queries (do not use QBE) for the following questions and execute the queries on the above
“CarServicingDB ” database (created on MS Access).
NB: Include screen shots of the outputs and write all SQL statements you used to answer following
questions.
(3 marks for each screen shot & remaining marks for the SQL query)
a. Display details of all registered Cars in the CarServicingDB database. Your result set should be
sorted on ascending order of the cu
ent_mileage XXXXXXXXXXMarks)
. Assume you need to find the details of all the bookings payment_received is greater than $100.
XXXXXXXXXXMarks)
c. Display number of customers registered in the system XXXXXXXXXXMarks)
BN204 Database Technologies Page | 6
XXXXXXXXXXPrepared by: Dr Deepani Guruge Moderated by: Dr. Nandini Sidnal July,2020
Part B: (50 Marks XXXXXXXXXXQuestions
1. SQL Queries -execute on “CarServicingDB ”
Write SQL queries (do not use QBE) for the following questions and execute the queries on the “
“CarServicingDB ” database created on MS Access for Part A. Include screen shots of the outputs and
write all SQL statements you used to answer following questions.
(3 marks for each screen shot & remaining marks for the SQL query)
a. Assume you need to find out Booking_Id, Customer_Id, Customer first name and last name of all
customers who paid more than $100 for their booking (i.e Payment_received in Booking table)
(Hint: Here you need to join two tables XXXXXXXXXXMarks)
. This is an extension to query in question 1. How do you filter the output receive in question1 to
obtain details of Booking_Id, Customer_Id, Customer first name, last name and email address of all
customers who live in “City of Knox” whose first name starts with letter “L”.
(Hint: Here you may need to join 2 tables XXXXXXXXXXMarks)
c. Find the total of Payments received for all the bookings scheduled for 27th September 2020.
XXXXXXXXXXMarks)
d. Assume you want to list all total of Payments received from each customer. Modify SQL query in
1(c) to obtain this information XXXXXXXXXXMarks)
a. Write a SQL Query to find out the following information:
Booking_Id, Customer first name, last name, email address and their car licence number
(Hint: You may need to Join 3 tables XXXXXXXXXXMarks)
2. Determine the Functional Dependencies
a. Determine the Functional Dependencies that exist in the following Orders table.
XXXXXXXXXXMarks)
Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo, ItemNum, Description,
NumOrdered, QuotedPrice)
. Normalize the above relation to 3rd normal form, ensuring that the resulting relations are
dependency-preserving and specify the primary keys in the normalized relations by underlining
them. (5 Marks)
3. Research Question
a. A data warehouse (DW) is a collection of corporate information and data derived from operational
systems and external data sources. Research more about this topic and find out benefits of using
a Data Warehouse for business. Write at least