Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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

1 answer below »
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
Answered Same Day Jul 31, 2021

Solution

Neha answered on Aug 02 2021
111 Votes
62679/CarServicingDB.accd
        date_of_service        payment_received        customer_Id        Mechanic_Id        booking_id
        8/2/20        500        mit192384        101        B01
        7/29/20        90        mit192385        103        B02
        8/4/20        450        mit192384        102        B03
        cu
ent_mileage        engine_size        License_Number        customer_Id
        356        large        EM345        101
        435        small        EM567        102
        23        large        CG45        103
        First_Name        Last_Name        Title        Gender        email_address        phone_number        address_line1        city        State        Customer_Id
        sharad        dhakal        Mr.        Male        [email protected]        546756767        ABC        NSW        NSW        mit192384
        Joy        Smith        Mr.        Male        [email protected]        56576766        BCD        Queensland        Queensland        mit192385
        Sally        Joseph        Miss.        Female        [email protected]        56767768        DEF        NSW        NSW        mit192386
        manufacturer_name        manufacturer_Code
        Suzuki        101
        ABC        102
        CDE        103
        mechani_Name        other_mechanic_details        mechanic_Id
        David        3 yrs experience        101
        Roy        3 yrs experience        102
        Sarah Joseph        3 yrs experience        103
        manufacturer_Code        model_name        Model_Code
        101        XYZ        123
        102        HUJ        234
        103        YZA        908
SELECT Car.[License_Number], Car.[cu
ent_mileage], Car.[engine_size], Car.[customer_Id]
FROM Ca
ORDER BY Car.[cu
ent_mileage];
SELECT Booking.[booking_id], Booking.[date_of_service], Booking.[payment_received], Booking.[customer_Id], Booking.[Mechanic_Id]
FROM Booking
WHERE Booking.[payment_received] > 100;
SELECT count(Customer.[Customer_Id]) AS NumberOfCustome
FROM Customer;
62679/Part B/car servicing report.docx
SELECT Booking.booking_id, Booking.customer_Id, Customer.First_Name, Customer.Last_Name
FROM Customer INNER JOIN Booking ON Customer.[Customer_Id] = Booking.[customer_Id]
WHERE (((Booking.payment_received)100));
SELECT Customer.First_Name, Customer.Last_Name, Booking.booking_id, Booking.payment_received, Booking.customer_Id, Customer.email_address
FROM Customer INNER JOIN Booking ON Customer.[Customer_Id] = Booking.[customer_Id]
WHERE (((Customer.First_Name)="L*") AND ((Customer.city)="City of Knox"));
SELECT DISTINCTROW Format$([Booking].[date_of_service],'Long Date') AS [date_of_service By Day], Sum(Booking.payment_received) AS [Sum Of payment_received]
FROM Booking
WHERE Format$([Booking].[date_of_service],'Long Date') = '27 September 2020'
GROUP BY Format$([Booking].[date_of_service],'Long Date');
SELECT DISTINCTROW Sum(Booking.payment_received) AS [Sum Of payment_received]
FROM Booking
GROUP BY Booking.customer_Id;
SELECT Booking.booking_id, Customer.Customer_Id AS Customer_Customer_Id, Customer.First_Name, Customer.Last_Name, Car.License_Numbe
FROM (Customer INNER JOIN Booking ON Customer.[Customer_Id] = Booking.[customer_Id]) INNER JOIN Car ON Customer.[Customer_Id] = Car.[customer_Id];
Functional Dependencies
Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo, ItemNum, Description, NumOrdered, QuotedPrice)
OrderNum -> OrderDate
Customer_No -> Customer_Name, Customer_ContactNo
OrderNum, ItemNum -> NumOrdered, QuotedPrice
ItemNum -> Description
To make the table in third normal form, it is important to remove all the functional dependency from...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here