SIT772 Database And Information Retrieval: Oracle Sample Database

SIT772 Database And Information Retrieval: Oracle Sample Database

$20 Bonus + 25% OFF

Securing Higher Grades Costing Your Pocket?
Book Your Assignment at The Lowest Price
Now!

Students Who Viewed This Also Studied

SIT772 Database And Information Retrieval

Questions

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.

•  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 Q1, Q3 and Q4 must be included in the PDF file as well. Otherwise, no assessment and penalty will be applied.

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

•  Name the written report using student ID_givenname_A1.pdf, e.g., 123456_Kevin_A1.pdf

• Three Sql Files used to test by markers.

•  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.

•  You must guarantee the SQL file can be executed successfully before your submission.

• All the four files must be submitted via Cloud Deakin 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 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 barrowed 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

(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 Deakin Unit.

• 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 data XAMPP.sql to build the database. Note: Oracle and MariaDB use different syntax.

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.

SIT772 Database And Information Retrieval

Answer in Detail


Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

33 More Pages to Come in This Document. Get access to the complete answer.

Students from UK, US and Australia receive top-class service along with high-end online assignment help. Online web chat, direct email and telephonic conversation are three flexible communication modes to connect with MyAssignmenthelp.com. Whether you are fresh in college or about to finish the doctorate degree, we have solution for all subjects. We give our clients the liberty to chat and instruct guidelines to our writers directly. We cater college homework help and university coursework help to students worldwide. We never win our customers with false hope; rather we strive to offer you the best assignment help services ever. Getting services from MyAssignmenthelp.com is easy and safe! Visit our page, place your order, make the payment, and you are all set to receive the complete assignment.

More SIT772 SIT772 Database And Information Retrieval: Questions & Answers

31269 Business Requirements Modelling

Objectives: (2, 3, 4 and 5)2. Apply modelling and systems analysis techniques that help understand the working of a business system.3. Document and specify various requirements via Software Requirements Specification (SRS).4. Develop various models using a range of systems analysis techniques to ana …

View Answer

Good Health Clinic Introduction

Case Study Part 1 Good Health Clinic Introduction In 2010 cardiologists Tim Jones and Daniella Smith decided to combine their practices to form a new clinic. They wanted to concentrate on preventative medicine by helping patients maintain their health and fitness along with providing traditional med …

View Answer

System Analysis and Design

Case Study: Free Feet Socks Limited.Background: Free Feet Socks Limited (FFSL) is a Melbourne based business which has traditionally sold imported socks to retail outlets and through representatives who sell the socks at parties, people have within their homes (party plan).   The business …

View Answer

CSE5DWD – Data Warehouse Concepts and Design

Qantas central managements wish to analyze their business performance from many different business units to maximize their revenue. They need to perform detailed analysis of their business to see the efficient operations and effective achievement. It has observed that a simple reporting feature buil …

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the
content removal request.

Choose Our Best Expert to Help You





Asa Prince

I was a professor of engineering associated with the University of Warwick

460 – Completed Orders

Hire Me





Colin Forsyth

M.Phil in Pharmacy with Specialization in Health Services and Patient Safety

628 – Completed Orders

Hire Me

Still in Two Minds? The Proof is in Numbers!

33845 Genuine Reviews With a Rating of 4.9/5.

Statistics

Course Work: 4 Pages, Deadline:
2 days

The solution I received is a great one and beyond my expectation. Well done team!

User ID: 8***50 Somalia

Economics

Home Work: 1 Page, Deadline:
1 day

Expert did Well in terms of reviewing the attached materials on the assignment , and he got 17/20.

User ID: 8***42 Bahrain

Healthcare

Assignment: 5 Pages, Deadline:
5 days

Excellent work without mistakes or errors. I liked the input about the subject and the number of pages to do a good work

User ID: 3***25 United States

Marketing

Assignment: 4 Pages, Deadline:
30 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Marketing

Assignment: 4 Pages, Deadline:
4 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Management

Thesis: 1 Page, Deadline:
6 days

Thanks to your efforts. it’s well researched and well written your idea. Really appreciate it.

User ID: 6***55 Kuala Lumpur, Canada

Property Law

Assignment: 1.4 Pages, Deadline:
12 hours

It is a good job but there are no references given that I can refer back to it. I hope if the expert read this, please send me the references.

User ID: 8***44 Kuala Lumpur, Malaysia

Healthcare

Programming: 2 Pages, Deadline:
13 days

The writers are very kind and they revise the work for you if you’re not completely satisfied. I would recommend them

User ID: 7***23 Kuala Lumpur, Canada

Finance

Assignment: 1 Page, Deadline:
12 days

Thank you very much for preparing the PowerPoint presentation. I appreciate you going back and doing what I requested. I used a different PowerPoint …

User ID: 8***35 Kuala Lumpur, United States

Database

Assignment: 16 Pages, Deadline:
1 day

Excellent job, I am very surprised! Well done! I,d like to work with this expert in the future!

User ID: 5***90 United Kingdom, Great Britain

English

Essay: 4 Pages, Deadline:
2 days

Excellent and wonderful work. Thank you for the effort and thank you for your cooperation, expert. It is indeed a complete and excellent file, and it …

User ID: 9***3 United Kingdom, Kuwait

Management

Home Work: 1 Page, Deadline:
1 day

Great job. Thanks for the help on this assignment. I got an A. Will use expert again.

User ID: 5***56 San Francisco, United States

Statistics

Assignment: 3 Pages, Deadline:
4 days

Well done, sent detailed explanation of each part from your side, though you missed output of first task got 74 out of 100. Thank you

User ID: 8***63 United Kingdom, Great Britain

Marketing

Assignment: 3 Pages, Deadline:
2 days

The solution is legendary. Thank you, thank you, thank you, and thank you for your cooperation and patience with me, and I am sorry for any actions. …

User ID: 9***3 United Kingdom, Kuwait

Marketing

Programming: 6 Pages, Deadline:
2 days

Thank you so much Professor Thomas Lee. i got 21/30 for my assignment, definitely will ask for you to do the part two

User ID: 2***37 Sydney, Australia

Statistics

Home Work: 1 Page, Deadline:
6 days

Second writer did a great job to complete the task… Thank must go to the second writer and my assignment help group.

User ID: 8***32 Sydney, Sri Lanka

Maths

Home Work: 1 Page, Deadline:
3 days

The assignment was done with complete details showing all the essential steps. I am very happy with the work.

User ID: 4***77 Sydney, United States

Management

Assignment: 0 Pages, Deadline:
5 days

Great work on this assignment and I will definitely be returning for additional help

User ID: 6***02 Sumter, United States

Psychology

Essay: 1 Page, Deadline:
15 hours

Perfect job and a quick job done by the writer. Thanks for the help. Grammar and sentences was great

User ID: 7***54 Sumter, Spain

Assignment

Essay: 8 Pages, Deadline:
13 days

Good work but cloud be better, hope next work is better sadasdasdasdasdasasdsadsadsadas

User ID: 5***28 United Kingdom, Great Britain

Statistics

Course Work: 4 Pages, Deadline:
2 days

The solution I received is a great one and beyond my expectation. Well done team!

User ID: 8***50 Somalia

Economics

Home Work: 1 Page, Deadline:
1 day

Expert did Well in terms of reviewing the attached materials on the assignment , and he got 17/20.

User ID: 8***42 Bahrain

Healthcare

Assignment: 5 Pages, Deadline:
5 days

Excellent work without mistakes or errors. I liked the input about the subject and the number of pages to do a good work

User ID: 3***25 United States

Marketing

Assignment: 4 Pages, Deadline:
30 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Marketing

Assignment: 4 Pages, Deadline:
4 days

Good job, I am happy with the amazing services and completed work given. Thank you very much for helping me receive a good mark. Will definitely work …

User ID: 2***91 Kuala Lumpur, Malaysia

Management

Thesis: 1 Page, Deadline:
6 days

Thanks to your efforts. it’s well researched and well written your idea. Really appreciate it.

User ID: 6***55 Kuala Lumpur, Canada

Property Law

Assignment: 1.4 Pages, Deadline:
12 hours

It is a good job but there are no references given that I can refer back to it. I hope if the expert read this, please send me the references.

User ID: 8***44 Kuala Lumpur, Malaysia

Healthcare

Programming: 2 Pages, Deadline:
13 days

The writers are very kind and they revise the work for you if you’re not completely satisfied. I would recommend them

User ID: 7***23 Kuala Lumpur, Canada

Finance

Assignment: 1 Page, Deadline:
12 days

Thank you very much for preparing the PowerPoint presentation. I appreciate you going back and doing what I requested. I used a different PowerPoint …

User ID: 8***35 Kuala Lumpur, United States

Database

Assignment: 16 Pages, Deadline:
1 day

Excellent job, I am very surprised! Well done! I,d like to work with this expert in the future!

User ID: 5***90 United Kingdom, Great Britain

English

Essay: 4 Pages, Deadline:
2 days

Excellent and wonderful work. Thank you for the effort and thank you for your cooperation, expert. It is indeed a complete and excellent file, and it …

User ID: 9***3 United Kingdom, Kuwait

Management

Home Work: 1 Page, Deadline:
1 day

Great job. Thanks for the help on this assignment. I got an A. Will use expert again.

User ID: 5***56 San Francisco, United States

Statistics

Assignment: 3 Pages, Deadline:
4 days

Well done, sent detailed explanation of each part from your side, though you missed output of first task got 74 out of 100. Thank you

User ID: 8***63 United Kingdom, Great Britain

Marketing

Assignment: 3 Pages, Deadline:
2 days

The solution is legendary. Thank you, thank you, thank you, and thank you for your cooperation and patience with me, and I am sorry for any actions. …

User ID: 9***3 United Kingdom, Kuwait

Marketing

Programming: 6 Pages, Deadline:
2 days

Thank you so much Professor Thomas Lee. i got 21/30 for my assignment, definitely will ask for you to do the part two

User ID: 2***37 Sydney, Australia

Statistics

Home Work: 1 Page, Deadline:
6 days

Second writer did a great job to complete the task… Thank must go to the second writer and my assignment help group.

User ID: 8***32 Sydney, Sri Lanka

Maths

Home Work: 1 Page, Deadline:
3 days

The assignment was done with complete details showing all the essential steps. I am very happy with the work.

User ID: 4***77 Sydney, United States

Management

Assignment: 0 Pages, Deadline:
5 days

Great work on this assignment and I will definitely be returning for additional help

User ID: 6***02 Sumter, United States

Psychology

Essay: 1 Page, Deadline:
15 hours

Perfect job and a quick job done by the writer. Thanks for the help. Grammar and sentences was great

User ID: 7***54 Sumter, Spain

Assignment

Essay: 8 Pages, Deadline:
13 days

Good work but cloud be better, hope next work is better sadasdasdasdasdasasdsadsadsadas

User ID: 5***28 United Kingdom, Great Britain

Have any Query?

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER