Assessment 2 Brief Database Modelling Kc7013 Programmes MSc
Assessment 2 Briefdatabase Modelling Kc7013programmesmsc Computer
Assessment Brief: This assignment involves designing and implementing a database for a university’s academic information system (AiS) and a data warehouse based on sales history data. It requires creating conceptual, logical, and physical models, implementing object-relational features in Oracle, populating and querying databases, and analyzing indexing and materialized views to optimize data warehousing performance.
Paper For Above instruction
Assessment 2 Briefdatabase Modelling Kc7013programmesmsc Computer
This comprehensive assignment requires the development of a sophisticated database system for a fictitious university named University of Gharnata, alongside a data warehouse schema based on Oracle's Sales History (SH) database schema. The core tasks involve conceptual and logical modeling, implementation using Oracle object-relational features, data population, and performance analysis through indexes and materialized views. The work must demonstrate a thorough understanding of data modeling, SQL/PL/SQL coding, and data warehousing concepts, along with analytical skills for optimization and performance enhancement of large-scale data systems.
Part 1: Implementing object-relational database for AiS (50 Marks)
This section focuses on developing an object-relational database to support the university's academic activities based on the AiS scenario. The tasks include:
(A) Conceptual and Logical Data Modeling
Create a comprehensive conceptual and logical data model for AiS, emphasizing object-oriented or extended entity relationship modeling. Incorporate advanced concepts such as aggregation, composition, inheritance, or generalization/specialization to accurately represent university entities, relationships, and constraints. This model should be built either from scratch or by enhancing your previous assignment’s model.
(B) Object-Relational Database Construction
Implement an object-relational database using Oracle's features based on the conceptual and logical models. All relationships—including one-to-one, one-to-many, and many-to-many—must be bidirectional. Use nested tables for to-many relationships and REF types for shareable objects. Ensure your design adheres to Oracle documentation for object-relational capabilities.
(C) Data Population
Populate the object-relational database by querying data from the relational version you developed in Assignment 1. Use PL/SQL and SQL to insert objects and establish relationships, including many-to-many associations, by inserting into nested tables and referencing objects via REF.
(D) Querying the O-R Database
Write PL/SQL procedures to query the object-relational database. The queries must retrieve: 1) names of postgraduate students with course, module, and marks; 2) details of all people in AiS, including students and staff, with their addresses and departmental affiliations. Provide code, explain the procedures, and submit both code and execution outputs.
(E) Relational vs. Object-Relational Comparison
Compare and contrast the relational and object-relational versions of the AiS database. Highlight advantages and disadvantages, discussing aspects like data integrity, flexibility, performance, and ease of maintenance. Conclude which version best suits the AiS system based on your analysis.
Part 2: Data Warehousing Tasks (50 Marks)
Using the Oracle SH schema, this section addresses advanced data warehousing concepts, optimization strategies, and query performance analysis:
(A) Index Analysis for SH
Analyze at least two existing indexes (from sh_idx.sql) using cost-based reasoning to explain their utility in query performance over the SH2 and DWh schemas. Do not execute the scripts; base your analysis purely on schema understanding and index purpose.
(B) Index Creation and Justification
Propose two new indexes to improve query performance. Justify their utility by providing SQL creation statements and cost-based analysis using hypothetical or estimated execution plans, comparing performance in DWn and SH2 environments.
(C) Materialized View Utility
Discuss the existing materialized views (defined in sh_cremv.sql). Explain their roles in enhancing query performance and reducing workload, supported by sample query rationales and explain plan analysis for both SH2 and DWn schemas.
(D) New Materialized Views
Identify two additional materialized views based on the SH base tables that could optimize common queries. Provide SQL code for creating these views and justify their usefulness by explaining their expected performance benefits and use cases.
(E) Aggregation Optimization with ROLLUP
Demonstrate the advantages of using Oracle’s ROLLUP for multi-level aggregation. Write a query over a fact table with at least two dimensions and explain why this benefits users. Then, replicate the same logic using UNION ALL instead of ROLLUP, and analyze the cost differences via EXPLAIN PLAN outputs, discussing efficiency and system workload impacts.
Assignment Submission
All SQL and PL/SQL code developed for creating, populating, and querying the databases must be submitted as part of the assignment. Include the output (e.g., spool files) to demonstrate correct execution. The entire document should follow a clear, professional format, adhering to Harvard referencing style, with appropriate citations for Oracle documentation and scholarly sources supporting data modeling and warehousing principles.
References
- Lane, P. (2013). Oracle Database Data Warehousing Guide, 11g Release 2 (11.2). Oracle.
- Northumbria University. (2018). Quick guide to Referencing and Plagiarism. Northumbria Digital Learning Services.
- Pears, R., & Shields, G. (2008). Cite them right: the essential referencing guide. Pear Tree Books.
- Oracle. (2005a). Application Developer's Guide - Object-Relational Features. Oracle Documentation.
- Oracle. (2005b). Oracle Database PL/SQL User's Guide and Reference. Oracle Documentation.
- Additional scholarly articles on data modeling, data warehousing, indexing, and Oracle performance tuning.