[SOLVED] SIT772 Assignment 2-Database Design Report and Implementation

0. Learning Outcomes

   This assessment assesses the following Unit Learning Outcomes (ULO) and related Graduate Learning Outcomes (GLO):

Purpose

This task requires students to apply their understanding and ability to use Relational Database Management Systems (RDBMS) as well as use SQL in the modelling of the physical world. Students will be provided with a set of business scenarios and are required to design a database and provide related SQL queries.

Instructions and Submission Guide
This is an individual assessment task. Students are required to submit ONE written report and THREE SQL Files.

• Read these instructions and the following four questions.
• ONE written report

o It should be in PDF format, which includes all the questions/tasks and their answers. To do so, you can work on Word document and convert it into PDF file.

o Note: all SQL queries for Q1, Q3 and Q4 must be included in the PDF file as well. Otherwise, no assessment and penalty will be applied.

o Place your name, ID, Unit Information 2020 T1 in the ONE written report.

o Name the written report using student ID_givenname_A1.pdf, e.g., 123456_Kevin_A1.pdf • THREE SQL Files used to test by markers.

o The 1st SQL file includes the answers in Q1, Q3, and Q4. Name the SQL file using student ID_Sale.sql, e.g., 123456_Sale.sql.

o You must guarantee the SQL file can be executed successfully before your submission.
• All the four files must be submitted via CloudDeakin assessment portal. The wrong submission

venue or the wrong submitted files may lead to the penalty.

Question 1:

(a)  Transform ERD designed in Assessment Task-1-Q1 to Relational Schema and normalize the

1. relations to 3rd Normal form. Highlight the Functional and Transitive Dependencies.
2. (b)  Populate the relations with dummy data (at least 5 records) and perform the following SQL

   queries. Consider the following queries and insert data accordingly.

• ➔  Tom and Anna are looking to buy property jointly, Tom hold 2 property in Seven Hills, and earn 150K/year, while Aana ears 160K/year. How much Tom and Anna can barrow?
• ➔  Tom has already barrow 500K, update the above query accordingly. **Add dummy (2-3) to compute average sold price.

  Question 2:

  Transform ERD in Assessment Task-1-Q2 to Relational Schema and normalize the relations to 3rd Normal form. Highlight the Functional and Transitive Dependencies.

  Question 3: [8 Marks] HD task*

1. (a)  Transform the ERD in Assessment Task-1-Q3 to Schema and normalize the relations to 3rd Normal form.
2. (b)  List the Functional and Transitive dependencies in each relation
3. (c)  Insert the data (6 records in each table) into “database” and submit mydata.sql.
4. (d)  Complete the following queries and provide query and its output

• ➔  Present a report listing the Manager’s name and telephone number for each hall of residence
• ➔  Present a report listing the names and student id with the details of their lease agreements.
• ➔  List each student and his mentor who lives in either Victoria Hall or DeakinUnit.
• ➔  Present a report of the names and ID of students with their room number and place number in a particular hall of residence

Question 4:

We have provided you with an Oracle sample database which is based on a global fictitious company that sells computer hardware including storage devices, motherboard, RAM, video card, and CPU. The company maintains the product information such as name, description standard cost, list price, and product line. It also tracks the inventory information for all products including warehouses where products are available. Because the company operates globally, it has warehouses in various locations around the world.

The company records all customer information including name, address, and website. Each customer has at least one contact person with detailed information including name, email, and phone. The company also places a credit limit on each customer to limit the amount that customer can owe. Whenever a customer issues a purchase order, a sales order is created in the database with the pending status. When the company ships the order, the order status becomes shipped. In case the customer cancels an order, the order status becomes canceled. In addition to the sales information, the employee data is recorded with some basic information such as name, email, phone, job title, manager, and hire date.

The following illustrates the sample database diagram:

To do the following tasks, it needs to execute the schema.sql to build the database schema and run data.sql to insert the data into the created database if you are using Oracle database lab environment; If XAMPP with MariaDB lab environment used, it needs to execute the schemaXAMPP.sql and dataXAMPP.sql to build the database. Note: Oracle and MariaDB use different syntax.

3

SIT772 Database and Information Retrieval
Trimester: 2021 T3
Assessment Task 1-B: Database Design Report and
Implementation ____________________________________________________________________________________

Task 1.1:

Write the SQL query to list the Customer Name and total purchase (amount) in all orders.

Task 1.2:

Write the SQL query to find total sale by each employee.

Task 1.3:

Write the SQL query to list all employee who have the sequential letters ‘c’ or ‘a’ in their name and their manager name. List must include the employee ID, names and ordered by their names in ascending.

Task 1.4:

Write the SQL query to list all products’ ID, Name and price where the products haven’t been purchased by any customer in the database. The list must be ordered by the product price.

Task 1.5:

Write the SQL query to list all the warehouses and their total sales. Here, given a product, the total sale of the product is calculated by the sold quantity of the product and its unit price. The list must be ordered by the total sales in the descending.
Note: One product_ID may link to more than one warehouses in the provided data. You can ignore this and just count the sale of the product to all its linked to warehouse.

Task 1.6:
Write the SQL query to list the product and available stock in all warehouses. The list must be sorted by the quantity of available product in the descending order.

Assessment feedback

General feedback to the class will be provided via CloudDeakin-Discussion Forum. The formal assessment feedback will be released with the marks in CloudDeakin altogether.

Extension requests
Requests for extensions should be made to Unit/Campus Chairs 3 days early before the assessment due date.

Special consideration

You may be eligible for special consideration if circumstances beyond your control prevent you from undertaking or completing an assessment task at the scheduled time.

See the following link for advice on the application process:

http://www.deakin.edu.au/students/studying/assessment-and-results/special-consideration

Assessment feedback

Detailed written feedback and results will be provided within two weeks of submission.

6