SIT772-Assignment 2 Solved

Question 1: 
(a)   Transform ERD designed in Assessment Task-1-Q1 to Relational Schema and normalize the relations to 3rd Normal form.  Highlight the Functional and Transitive Dependencies.  

(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* 
 

(a)   Transform the ERD in Assessment Task-1-Q3 to Schema and normalize the relations to 3rd Normal form.   

(b)   List the Functional and Transitive dependencies in each relation  

(c)   Insert the data (6 records in each table) into “database” and submit mydata.sql.

(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  



Assessment Task 1-B: Database Design Report and Implementation  
____________________________________________________________________________________   

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.  

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 Task 1-B: Database Design Report and Implementation  
____________________________________________________________________________________   

 

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. 

 

Referencing

You must correctly use Harvard referencing in this assessment. See the Deakin referencing guide.