Part 1 Read the scenario below, provide responses to the related questions and submit them through Web. Place your Student Identity number and your Name in the document header, left aligned. Scenario...

Part 1
Read the scenario below, provide responses to the related questions and submit them through Web. Place your Student Identity number and your Name in the document header, left aligned.
Scenario
A medium sized company called PCWORLD Computing is in the business of supplying desktop computer systems, PC components and accessories. They have a shop in the main street for retail sales. Upstairs from the shop the company office has several sales people who sell to new and existing business clients. Because of the greater volumes of products purchased by business clients they receive a discounted sales price on all products. The sales people are paid a base salary plus a commission of 3% of their total invoiced sales to customers. The retail sales staff in the shop below receive a salary but no commission on sales.


Customers usually deal with the same sales person, and they can place any number of sales orders at any time. A separate invoice is created for each sales order. Orders are delivered the same day or next day depending on how big and complex is the order. Also, depending on whether or not stock is available. Part of an order will be delivered if full quantities cannot be found in the warehouse. Both retail sales staff and sales people draw stock from the same warehouse. Any single payment by a business customer may be for part of an invoice, all of the invoice or for multiple invoices. Business customer payments are by cheque, by credit card or by bank transfer. Retail customers in the shop must pay with cash or by a credit card.


At the rear of the shop is the single warehouse from which all sales stock is drawn. Each product stocked has an identity number. Some products, such as motherboards, have a unique serial number for each unit in stock which must be recorded for warranty purposes and for identifying version differences. Every product sold has a single unit of measure. That means that cables are only sold packaged in standard lengths and each different length of the same type of cable has a different identity number.


From the above details you can easily imagine that PCWORLD Computing needs to keep information about its customers and their orders for products as well as information about the retail sales. That means that the company also needs to keep track of its stock holding in the warehouse. The company needs to know how much money it has taken and how much money it can expect to take in the next accounting period.


For the purposes of this tutorial we will not consider the payments that PCWORLD Computing must make to its suppliers.
Part B
Instructions
Based on your result from Tutorial 1, produce an Entity Relationship Diagram (ERD) for the entities that you initially identified. After you have normalised the data in those entities you will have produced a set of Third Normal Form (3NF) relations.
Tutorial Criteria
The following criteria must be met for full marks.
a.    All identified entities are present in the ERD.
b.    Only use the Entity and Relation names in the diagram. Do not list the attributes within the Entities or Relations.
c.    The relationship arrow in diagram show the appropriate cardinality and optionality.
d.    All instructions in this tutorial are followed.


Submission
Submit your tutorial as a Visio document, a PowerPoint document or a Word document at the upload link for tutorial 2 in Web. Do not use any software tools other than these 3. Put your Name and Student number, left justified, in the document header.


Part C
This tutorial requires you to construct a database designed from the Third Normal form tables that you produced in Tutorial 2. The computer supply company scenario was provided as the basis for the data model.


1.    You have already created a data model from the information in the scenario, identifying all required attributes, primary and foreign keys. Now also identify any necessary constraints.


2.    Create the necessary Oracle 11g database tables to implement the normalised data model of the computer shop. Include all necessary constraints and sequences.


3.    Use INSERT INTO tablename commands to populate the tables with appropriate data for 10 customers and 8 sales of a varying number of products that are subsequently invoiced, delivered and paid for. Provide a minimum of 5 product records to be available for sale.


4.    All of the SQL statements that you use to create and populate the database must be saved in a word processor file named with your student number (9999999_ICT118.doc) that can be executed by the examiner.


5.    Create reports of data held in the database using SQL SELECT queries.


Tutorial Criteria


Your tutorial will be assessed against the following criteria:


1.    The correctness of the database structure of tables and columns with respect to the normalised data model.


2.    The appropriateness of your chosen constraints and keys.


3.    The correctness of the SQL statements used to create, populate and report on your database.


Test the correctness of your script file by running it in SQL Developer or a similar development platform.


Submit your tutorial answers through the Web upload for Tutorial 3.
1.    Submit the SQL statements to create and populate the database as a text file (9999999_ICT118.doc).
2.    Submit the query reports as part of the above .doc file. The queries you are to produce are listed below the tutorial criteria. Note that in order to produce the reports you may
have to add attributes to your tables that you had not thought of. The tutorial criteria and their relative weightings are listed here: Provide correct SQL statements to create the tables (10)


Correctly insert data into all tables (5) Ensure that the data values loaded by you are such as will enable you to complete the SQL queries that follow:


Execute statements in SQL to search for an order between a range of dates Execute statements in SQL to search for a particular type of product


Execute statements in SQL to produce a list of orders and their line items within a date range sorted by sales staff member in ascending order by staff id.


Correctly report the queries above (5)