Assignment # 2
Section 1) Preliminaries
Important Points
- Evidence of academic misconduct (e.g., plagiarism, collaboration/collusion among students) will be taken seriously and University regulations strictly followed. You are advised to be familiar with the University definitions of academic misconduct including plagiarism and collusion (Northumbria, 2015).
- You are expected to produce a word-processed answer to this assignment. Please use Arial font and a font size of 12. For SQL code and output, you can use courier new, which usually preserves SQL format and layout.
- You should use the Harvard Style of referencing and citation when needed. The “Cite them right” guide is recommended for referencing and citation (Pears and Shields, 2008) which should be followed throughout your assignment. A good alternative is Northumbria (2016).
- Late submissions will be given zero marks unless prior permission is gained from the school office/programme leader.
Module Learning Outcomes (MLOs) assessed:
Knowledge & Understanding:
- Key concepts of data warehousing.
Intellectual / Professional skills & abilities:
- Conceptual data modelling, relational database design and implementation in SQL & PL/SQL, and object-based databases.
- Design and Implementation of a data warehouse using Oracle database system.
Section 2) Tasks of the Assignment
Part 1 (60 marks)
Your task is to define, using Oracle 10g/11g/12c, a nested-relational / object-relational structure for online ordering system called MarketPlace, using SQL and PL/SQL to drive the input of data and searching. A sample MarketPlace invoice is provided in the appendix.
The requirements, for the MarketPlace system, are that the following information should be held in the database:
- Data on each order, including order number, name and contact details (email, addresses, telephones, etc) of customer and date the order was placed and order total;
- Data on each item within the order including product code, quantity ordered, its condition, unit price, and cost charged together with any postage and packing cost;
- Data on each seller who sells different items (e.g., name, profile, URL, rating, etc);
- Data on despatch and delivery of the items / sub-orders, and
- Data on each product in the product catalogue including product code, name, description, and category.
- Data required for uniqueness / identification of different entities in the system, which are required but may not be so obvious from the invoice.
You should then write two PL/SQL procedures:
- The first to allow the user to input information for an order and n number of order items / sub-orders (where 2 ≤ n ≤ 10) as parameters (You should make it general but if you use fixed parameters for just two order items, then you will get a maximum of half marks for this procedure).
- The second to allow the user to input a product code as a parameter, then retrieve and display name of the product and information (order and sub-order number, quantity ordered and cost charged, and the related customer) about the orders that include the given product (this will need a cursor to handle the multiple orders retrieved).
NOTE the following important points when answering Part 1:
- You must provide a conceptual/logical structure of your database (4 marks) and all the SQL code used for creating (30 marks), populating (8 marks) and manipulating your database using PL/SQL procedures (14 marks).
- You must populate your database with some sample data to test your procedures.
- You must provide the output of running your code (e.g., spool file) in electronic form (4 marks).
Part 2 (40 marks)
- The Sales History (SH) Data Warehouse
SH is a sample database schema provided by Oracle, which has been extensively used in the Oracle’s Data Warehousing Guide (Lane, 2013) The SH schema, as shown in Figure 3, consists of a big fact table, SALES, and five relatively small dimension tables: TIMES, PROMOTIONS, CHANNELS, PRODUCTS and CUSTOMERS. The additional COUNTRIES table linked to CUSTOMERS creates a simple snowflake. The model and the attributes are aimed at demonstrating data warehousing functionality like star transformation and query rewrite. They do not necessarily represent the optimal approach for this kind of data warehouse in real productive environments; and such a design would be driven more by business requirements than by the star itself. Table 1 shows the cardinalities of the tables in the SH schema.
| Table | Number of Rows |
| Customers | 55,500 |
| Countries | 23 |
| Products | 72 |
| Sales | 9,18,843 |
| Times | 1826 |
| Promotions | 503 |
| Channels | 5 |
Table 1: Cardinalities of SH tables.
Figure 3: SH Star Schema.
Usually, a star schema includes a single fact table and few dimensional tables, however, the SH schema designer identified another useful fact table called COSTS, which is linked to the TIMES and PRODUCTS dimensional tables. Figure 3 shows two fact tables: SALES and COSTS.
- Implementation of SH schema
Table 2 shows the files that constitute the implementation of the SH schema using Oracle 10g/11g/12c.
| File |
Description |
| sh_cre.sql | Script for creating the base tables of the SH data warehouse. |
| sh_cons.sql | Script for enabling constraints. |
| sh_drop.sql | Script for dropping the SH schema objects. |
| sh_idx.sql | Script for creating indexes. |
| sh_pop1.sql | Script for loading data into COUNTRIES and CHANNELS tables. |
| sh_pop2.sql | Script for populating the TIMES dimensional table. |
| sh_schema_objects.sql | Script for calling other scripts e.g. sh_cre.sql for creating base tables and populating the small dimensional tables. |
| sh_cremv.sql | Script for creating materialized views. |
| sh_ext_table.sql | Script for creating an external table. |
| sh_main.sql | Script that calls several other scripts including e.g. sh_cremv.sql, sh_idx.sql etc. |
| sh_costs.sql | Script for populating the COSTS fact table. |
| sh_hiera.sql | Script for creating dimensional hierarchies, which are called DIMENSION in Oracle. |
Table 2: Files used to implement the SH schema.
- Two versions of SH schema/database
- The default and optimized version of the SH database is created under the SH2 user. You have read-only access to query any of the tables of SH database by prefixing any table or view name by “SH2.”, e.g., to query the contents of CHANNELS table, use:
SELECT * FROM SH2.CHANNELS;
- An un-optimized version of the SH database has been created under your own username DWn. You will be given a special username and password for doing this part of the assignment (i.e. an Oracle account starting with DW and followed by a number e.g. DW1, DW2, etc). Throughout this part, you MUST use this special username. However, it is assumed that you will not create any indexes on any of the tables in your own personal version of the SH until asked to do specifically. Moreover, neither materialized views (see sh_cremv.sql) nor dimensional hierarchies (see sh_hiera.sql) should be created in your version of the SH database.
- Important Note
Whenever, you need to assess the performance of existing database structures (e.g., materialized view, index, dimensional hierarchies) you must refer to SH2 as above (using the “SH2.” prefix). Any new database structures that you need to create should be created under your username. Likewise, when you need to assess the performance of any new database structures that you create under your DWn username, you need to prefix all tables/views/materialised views accordingly, e.g., to query the contents of your CHANNELS table, use:
SELECT * FROM DWn.CHANNELS;
- Tasks of the Assignment
Note: You must submit all the SQL queries and any other code that you wrote in answering any of the parts / questions (e.g., the use of Explain Plan statements for the queries and their output).
- Study the index definitions in sql. Discuss in detail (using cost based analysis) why these indexes (at least two of them) are useful for answering queries over the SH2 and DWn versions of the database. You do not need to run the sh_idx.sql script at all.
(10 Marks)
- Identify two new indexes and justify why they could be useful. Write the SQL code for creating these indexes under your DWn user. Give example queries with cost-based analysis for both DWn (which will have the new indexes) and SH2 users (which will not have any of your new indexes). (10 Marks)
- Given the materialized views (MVs) defined in sql, discuss in detail why these MVs are useful for users of the SH database. You should provide detailed examples (cost based analysis, e.g., using Explain Plan for running sample queries on both SH2 and DWn) to illustrate your answer. You do not need to run the sh_idx.sql script at all. (10 Marks)
- Identify two other MVs based on the base tables in the SH schema under your DWn user and justify why they would be useful. Write the SQL code for creating these MVs.
(10 Marks)
Hand-in procedure
You should hand in your answer for this assignment as a single word processed document electronically on the ELP. A sample assignment template file will be uploaded on the ELP near the submission deadline.
References
Lane, P. (2013) Oracle Database Data Warehousing Guide, 11g Release 2 (11.2). Part Number E25554-02. Available at:
https://docs.oracle.com/cd/E11882_01/server.112/e25554/toc.htm (Accessed: 17 February 2017).
Northumbria (2015) Assessment Regulations for Northumbria Awards. Available at: https://www.northumbria.ac.uk/static/5007/arpdf/aq/ARNA1516.pdf (Accessed: 28 January 2018).
Northumbria (2016) Quick guide to Referencing and Plagiarism. Available at:
https://cragside.northumbria.ac.uk/Everyone/skillsplus/database_uploads/87.pdf
(Accessed: 30 October 2016).
Pears, R. and Shields, G. (2008) Cite them right: the essential referencing guide.
Newcastle upon Tyne: Pear Tree Books. Available at:
http://nuweb2.northumbria.ac.uk/library/skillsplus/loader.html?55388321
(Accessed: 5 October 2017).
Oracle (2005a) Application Developer’s Guide – Object-Relational Features 10g Release 2 (10.2). Part Number B14260-01. Available at: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/toc.htm (Accessed: 9 November 2016).
Oracle (2005b) Oracle Database PL/SQL User’s Guide and Reference 10g Release 2 (10.2). Available at: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/toc.htm (Accessed: 16 March 2017).
Appendix (MarketPlace Invoice)
| Ordering Information: | ||||||||||||||||||||||
| E-mail Address: ABC@gmail.com | ||||||||||||||||||||||
|
||||||||||||||||||||||
| Order Date: 09/Dec/2017 Order Grand Total: £219.95 | ||||||||||||||||||||||
| Order Summary: | ||||||||||||||||||||||
| Your order has been divided into 3 separate sub-orders. | ||||||||||||||||||||||
| Sub-Order #1: : Vodafone Limited | ||||||||||||||||||||||
|
||||||||||||||||||||||
| Sub-Order #2: : emartbuyuk | ||||||||||||||||||||||
|
||||||||||||||||||||||
| Sub-Order #3: : Prepaymania | ||||||||||||||||||||||
|
||||||||||||||||||||||