University : RMIT University UniLearnO is not sponsored or endorsed by this college or university.
Subject Code : BN204
Assignment Task:

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

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

b. Assume you need to find the details of all the bookings payment received is greater than $100.          

c. Display number of customers registered in the system.  

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 screenshots 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)      

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

c. Find the total of Payments received for all the bookings scheduled for 27th September 2020.            (5 Marks) 

d. Assume you want to list all total of Payments received from each customer. Modify SQL query in 1(c) to obtain this information.       

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

2. Determine the Functional Dependencies

a. Determine the Functional Dependencies that exist in the following Orders table. Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo,  ItemNum, Description, NumOrdered, QuotedPrice)  

b. 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 250 words on the topic with proper references.  (6 Marks) b. Explore the job listings advertised online and find out the skills you need to become a database developer.

 

This BN204: IT Assignment has been solved by our IT Experts at UniLearnO. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.

Eureka! You've stumped our genius minds (for now)! This exciting new question has our experts buzzing with curiosity. We can't wait to craft a fresh solution just for you!

  • Uploaded By : Alex Cerry
  • Posted on : September 15th, 2018

Whatsapp Tap to ChatGet instant assistance