Critical understanding of the entire data life cycle

Assignment Help Database Management System

Reference no: EM132866862

KL7011 Advanced Databases - Northumbria University

Learning outcome 1: Demonstrate critical understanding of the entire data life cycle and classical database engineering processes and approaches, and non-traditional database systems

Learning outcome 2: Critically analyse, select, apply and evaluate advanced data modelling, database design, implementation and manipulation methods, techniques and tools to a complex data management problem

Learning outcome 3: Develop critical awareness of the responsibilities of database developer with respect to professional, legal, security and ethical issues individually or as part of a team

Assessment Background and Scenario

This assessment is based on the scenario "Library Management System (LMS)"of a fictitious organization called Newbigging Council Library (NCL). The council wants to develop an information system to support its library activities.The details of the scenario are provided in Appendix 1.

Assignment Questions

Part 1
This part is based on the LMSscenario as described in the Appendix.

(A) Using entity-relationship (ER) and enhance entity-relationship (EER) modelling, produce a conceptual design for the database to support the LMS required by NCL.

(15 marks)(B) Convert the ER / EER diagram from Part 1(A) to produce a logical relational schema using ER / EER to relational mapping.

Notes for Part 1 (A & B)
• The ER/EER diagram should show all relevant entity types, relationship types, attributes, primary keys, and structural constraints. Note that not all keys are identified/mentioned in the scenario, so you are required to identify/devise appropriate primary keys for all the entity types. Your ER/EER diagram must not show/include any foreign keys or any such attributes that represent foreign keys as these are logical and not conceptual concepts.
• As part of the mapping process, for each relation, you should identify appropriate primary keys as well as foreign keys (if applicable). Furthermore, you need to make sure your relations obtained from mapping your ER/EER diagram are in the 3rd normal form.

(C) Based on your logical design from Part 1 (B) and the information available in the scenario, produce an SQL script file using Oracle 11g/12c/higher.

Notes for Part 1 (C): Your submission must include:
• An SQL script file containing appropriate SQL DDL (e.g., CREATE TABLE, ALTER TABLE, etc.) statements for creating all the relations from

Part 1 (B).
• The output from running the script file in a live Oracle 11g/12c/higher session (e.g., using SPOOL, copy and paste, screenshots, etc.). If output is missing, 4 marks will be deducted from the above 15 marks. If output is incomplete or inadequate or misleading, then adequate marks up to a max of 4 will be deducted.
• You should use relational features from the SQL92 standard in Oracle 11g/12c/igher for constructing your data structures / tables, including appropriate primary and foreign keys.
• You should aim for a high degree of reliability in the data with use of as many constraints as possible, e.g., check constraints on various columns (e.g., particular format of primary key values, positive physical values as described in week 4 lecture slides on integrity constraints).

Part 2

This part is based on your answer / solution to Part 1, i.e., design and implementation of the database for the LMS scenario.

(A) Populate the database with some sample data (e.g., data similar to the data kept about books and other bibliographic items within Northumbria University Library).
(10 marks)

(B) Answer the following queries (retrievals) using SQL.

q1) Display details of the top 10most frequent users (members) of the library with the number of books/titles/items they have borrowed in the past 18 months.

q2) Display details of the top 20 most frequently borrowed titles/books/items of the LMS with the total number of borrowings in the past two financial years.

Notes for Part 2 (A & B): Your submission must include:
• An SQL script file containing appropriate SQL DML (e.g., INSERT) statements for populating the tables you have created in Part 1 (C).
• An SQL script file containing SQL retrieval (e.g., SELECT) statements for Part 2 (B).
• An output file for running each of above the TWO script files in a live Oracle 11g/12c/higher session (e.g., using SPOOL, etc.). If any of the output is missing, 2 marks will be deducted from the above. If outputs are incomplete or inadequate or misleading, then adequate marks up to a max of 4 will be deducted.

Part 3

This part is based on your answer / solution to Part 1 (A), i.e., conceptual design of the database for the LMS scenario.

(A) Choose and justify what aspects of LMS conceptual design would be better off if implemented using object-relational database; then provide logical design and implementation of the subset of the LMS using ER/EER to object-relational mapping and object-relational features of Oracle Database System; populate the object-tables with sample data and demonstrate your choice of design and implementation by running two complex queries on your object-tables.

(B) Analyse the conceptual database design from Part 1 (A) and the LMS scenario in the Appendix and propose what aspects of the LMS database would benefit from incorporating NoSQL Database concepts. Illustrate your answer with code from a representative code from NoSQL Database implementation.

Part 4

Consider the LMS scenario in the Appendix. Produce a report for the Mayor of the Newbigging Councilelaborating on professional, legal, ethical and security issues that need to be considered and make recommendations that you think are appropriate for LMS.

The report should be concise and comprehensive and in the region of 800-900 words. You should use Harvard style of citation and referencing by following the guidelines in Pears and Shields (2008).

Attachment:- Advanced Databases.rar

Reference no: EM132866862

Questions Cloud

Identify by number in chief complaint : Choose one skin condition graphic (identify by number in your Chief Complaint) to document your assignment in the SOAP

What political ideology is best equipped to fight a pandemic : At the constitutional convention, why did the Northern states accept the three-fifths compromise? What political ideology is best equipped to fight a pandemic

Which entrepreneurial and small businesses : Discuss how Web 2.0 and social media have altered the way in which entrepreneurial and small businesses

Describe the specific theories that the characters in film : Describe the specific theories, assumptions, or "schools of thought" that the characters in the film have. How do their schools of thought differ?

Critical understanding of the entire data life cycle : Critical understanding of the entire data life cycle and classical database engineering processes and approaches, and non-traditional database systems

Discuss three potential obstacles : Discuss three potential obstacles that may occur during implementation of your designated improvement initiative;

What is relevance in the current discourse of ipe : What is its relevance in the current discourse of IPE and its respective issue-area? What is the author's argument? What have other authors said

Franklin equipment : Evaluate the criteria FEL uses to assign managers to project teams. What efficiencies do these criteria create? What are the resulting problems?

Explain the meaning of gni-ppp-the big mac index : Explain the meaning of GNI, PPP, the Big Mac Index, the underground economy, unit labor costs and unit labor productivity.

User Account

All Pages