Reference no: EM133127249

SIT772 Database and Information Retrieval - Deakin University

SQL Query Practice and Implementation

Learning Outcome 1: Design and implement relational database systems through the use of SQL

GLO 1: Discipline-specific knowledge and Capabilities
GLO 2: Communication
GLO 4: Critical thinking
GLO 5: Problem solving

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.

Question 1: Understanding an example database and write basic SQL queries

We provide you with an Oracle sample database which is based on a global fictitious company that sells computer hardware including storage, 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 schema.sql to build the database schema, i.e., create the tables, and then run data.sql to insert the data into the created tables. We have provided the training and practice at the workshop session of Week 2.

Task 1.1: Write one SQL query to list the region names and the number of countries for each region from the above database.

Task 1.2: Write one SQL query to find all customers who have made orders before 2016. List must include the customer ID, customer name, and ordered by their ID values in descending.

Task 1.3: Write one SQL query to list all customers who have the sequential letters ‘co' in the customer name where the condition ‘co' is case-insensitive, i.e., ‘CO', ‘Co', ... can also be retrieved. List must include the customers' ID, names and ordered by their names in ascending.

Task 1.4: Write one 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 one SQL query to list the employees and the number of orders that each employee processed in the database. The output list must include employee ID, name, and the number of orders. The list must be sorted by the number of orders in the descending order.

Task 1.6: Write one SQL query to list all the warehouses and the revenue of each warehouse. Here, given a product, the revenue of the product is calculated by the sold quantity of the product and its List_Price. The list must be ordered by the revenue value in the descending.

Question 2: Understanding and practice of normalization

Below provides a list of sample transactions in a database. It provides the full information about the transactions, products, the companies of the products, customers' names, the shop information of the sold items, and the sold prices of items for some shops and customers.

Task 2.1: Normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization and present the results step by step from 1NF to 3NF. You are required to specify the primary keys in your generated tables.
(Marking Rubric: 2 marks if all tables confirm from 1NF to 3NF with correct keys specified; 1 mark if all tables confirm to 1NF-3NF but keys are incorrect; otherwise, 0 mark will be given.)

Task 2.2: Write a set of SQL queries to create your normalized tables for building the database schema. Each table must declare the primary keys, foreign keys if applicable.
(Marking Rubric: 1 mark if there is no mistake to create the tables or only has one mistake; otherwise, 0 mark will be given.)

Task 2.3: Write a set of SQL queries to add data into the database implemented in Task 2.2. The database must include all the provided information. If need, you can add unique identifiers or ids for tables.

Attachment:- Database and Information Retrieval.rar