database and information retrieval

1

Unit Name: SIT772 Database and Information Retrieval Trimester: 2020 T1 Assessment 1: Database Design Report and Implementation This document supplies the detailed information on assessment tasks for this unit. Key information

• Due: Week 8-Sunday, 10 May 2020, 23:59 (AEST) • Weighting: 30% • Submit: Through CloudDeakin

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

Unit Learning Outcome (ULO) Graduate Learning Outcome (GLO)

ULO 2: Explain the concept of data modelling and use Entity-Relationship (ER) models to represent data.

GLO 1: Discipline-specific knowledge and capabilities

ULO 3: Design and implement relational database systems through the use of SQL

GLO 1: Discipline-specific knowledge and capabilities GLO 4: Critical thinking

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. Note: all SQL queries for Task 1.1 – Task 1.6, Task 2.2 – Task 2.5, Task 3.2 – Task 3.3 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 Task 1.1 – Task 1.6. Name the SQL file using student

ID_Sale.sql, e.g., 123456_Sale.sql. o The 2nd SQL file includes the answers in Task 2.2 – Task 2.5. Name the SQL file using student

ID_Gallary.sql, e.g., 123456_ Gallary.sql. o The 3rd SQL file includes the answers in Task 3.2 – Task 3.3. Name the SQL file using student

ID_Bank.sql, e.g., 123456_ Bank.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.

2

Question 1: [12 Marks] 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 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. There are several commands that may be useful. Select table_name from user_tables; It can be used

3

to check the existence of the tables. Suppose you upload your sql file at your network drive H:. Then you can run by the command, @schema.sql. [Update: To execute the sql file remotely, you need to launch your Deakin VPN - https://support.deakin.edu.au/kb_view_customer.do?sys_kb_id=69baaed84f671bc429ed30b01310c72f and set up “Map Network Drive” - https://deakin.service-now.com/kb_view_customer.do?sysparm_article=KB0010355. You can name Drive: H:, Folder: \\your-username.homes.deakin.edu.au\my-home, e.g., \\jianxinli.homes.deakin.edu.au\my- home for unit chair, and tick Reconnect at sign-in and Connect using different credentials.] Note: Oracle and MariaDB use different syntax. If XAMPP with MariaDB lab environment used, you launch your XAMPP app and open the shell, and type in mysql –h localhost –u root. [Note: empty space is mandatory in the command line] The following is to execute the schemaXAMPP.sql and dataXAMPP.sql to build the database. [Reminder: You need to create the database using Create database xamppdb;] If the sql files located in a directory, e.g., C:\Users\jianxinli\XAMPPBuyingDB, you need run by typing MariaDB[xamppdb]> Source :\Users\jianxinli\XAMPPBuyingDB\schemaXAMPP.sql [Reminder: no end semicolon, otherwise, it pops ERROR: Unknow command]. Since MariaDB cannot support disable constraint, you need to use the command window to alter the foreign key constraints by running MariaDB[xamppdb]> Alter table employees drop CONSTRAINT fk_employees_manager; [Reminder: need semicolon]. Now you can load the data into your created tables by running MariaDB[xamppdb]>Source C:\Users\jianxinli\XAMPPBuyingDB\dataXAMPP.sql [Reminder: no end semicolon, otherwise, it pops ERROR: Unknow command]. Task 1.1: [2 Marks] Write the SQL query to list the region names and the number of countries within the regions in the above database. Task 1.2: [2 Marks] Write the SQL query to find all customers who have made orders before 2017. List must include the customer ID, customer name, and ordered by their ID values in descending. Task 1.3: [2 Marks] Write the SQL query to list all customers who have the sequential letters ‘co’ in the customer name. List must include the customers’ ID, names and ordered by their names in ascending. Task 1.4: [2 Marks] 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: [2 Marks] 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. [Reminder: 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. ]

4

Task 1.6: [2 Marks] Write the SQL query to list the employees and the quantity of orders that they proceeded in the database. The output list must include employee ID, name, and the quantity of orders. The list must be sorted by the quantity of orders in the descending order. Question 2: [10 Marks] The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings can be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer. Here is an example record for a customer with his history purchases. Gallery Customer History Form

Customer Name Jackson, Elizabeth Phone (206) 284-6783

123 – 4th Avenue Fonthill, ON L3J 4S4

Purchases Made Artist Title Purchase Date Sales Price 03 - Carol Channing Laugh with Teeth 09/17/2000 7000.00 15 - Dennis Frings South toward Emerald Sea 05/11/2000 1800.00 03 - Carol Channing At the Movies 02/14/2002 5550.00 15 - Dennis Frings South toward Emerald Sea 07/15/2003 2200.00 Task 2.1: [2 Marks] Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization. You have to present the results step by step from 1NF to 3NF. Task 2.2: [2 Marks] Write a set of SQL queries to implement the database schema. You can follow the template in Question 1 – schema.sql or schemaXAMPP.sql. To create each table, it must declare the primary keys, foreign keys, constrains and ON Delete Cascade or ON Update Cascade [update cascade is not supported by sqlplus]. Task 2.3: [2 Marks] Write a set of SQL queries to add data into the database implemented in Task 2.2. You can follow the

5

template in Question 1 – data.sql or dataXAMPP.sql. The database must include at least five customers, six artists, 10 paintings, and 15 transactions on purchasing. Task 2.4: [2 Marks] Write a SQL query to list every customer and their purchased paintings. The list must be sorted by customer name first and painting title second. Task 2.5: [2 Marks] Write a SQL query to list the TOP-Three customers whose expenditure are the top-3 most in the database. Question 3: [8 Marks] Application Description: Suppose there is one bank company – Commonwealth that requires to develop a relational database. The daily duty is to serve their customers managing their saving bank accounts, e.g., withdraw, deposit, etc. Additionally, the company Commonwealth also makes business for the home loan marketing, e.g., a customer may have a home loan account. To set up a home loan account for a customer, there are several information to be collected and recorded: (1) the customer needs to specify a property address; (2) a bank staff needs to be assigned to process the home loan application; (3) the property value should be assessed by using the average of the sold price of the properties located in the same suburb. Note: suppose there are sold properties in every suburbs; (4) each customer has their own home loan limit that is calculated by his/her annual salary amount multiplied by 7. Note: In general, the bank company used 7 years’ salary to estimate, e.g., if the customer Kevin has annual income $60,000, then his home loan limit cannot exceed $420,000. For the customers having multiple home loans, their total home load amount cannot exceed the customers’ home loan limit; (5) a customer may have more than one home loan because multiple properties can be bought by one customer. Last but not least, your design should reflect the practical requirement as much as possible, i.e., meeting the maximum business rules. For example, some customers may have home loan accounts, but some ones may only have saving accounts. For a customer who need to set up a home loan account, it must create a saving account first. The average sold price for a suburb should be calculated on demand, not saved directly in the database. Task 3.1: [3 Marks] Draw the Entity Relationship Diagram (ERD) of the database designed for the above application scenario. In the ERD, you need to specify the main components including Entities, Attributes, Relationships, Primary Keys, and Constraints. Task 3.2: [3 Marks] Write a set of SQL queries to implement the database schema. You can follow the template in Question 1 – schema.sql or schemaXAMPP.sql. To create each table, it must declare the primary keys, foreign keys, constrains and ON Delete Cascade or ON Update Cascade. Task 3.3: [2 Marks] Write a set of SQL queries to add data into the database implemented in Task 3.2. You can follow the template in Question 1 – data.sql or dataXAMPP.sql. The database must include at least six customers, five suburbs, 10 sold properties, and home loan records.

6

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. Unit Chair: Jianxin Li, [email protected] 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. Academic integrity, plagiarism and collusion Plagiarism and collusion constitute extremely serious breaches of academic integrity. They are forms of cheating, and severe penalties are associated with them, including cancellation of marks for a specific assignment, for a specific unit or even exclusion from the course. If you are ever in doubt about how to properly use and cite a source of information refer to the referencing site above. Plagiarism occurs when a student passes off as the student’s own work, or copies without acknowledgement as to its authorship, the work of any other person or resubmits their own work from a previous assessment task. Collusion occurs when a student obtains the agreement of another person for a fraudulent purpose, with the intent of obtaining an advantage in submitting an assignment or other work. Work submitted may be reproduced and/or communicated by the university for the purpose of assuring academic integrity of submissions: https://www.deakin.edu.au/students/study- support/referencing/academic-integrity