Database Design and Implementation for Library Management System

Task

This assignment covers the following learning outcomes for the module:

Knowledge & Understanding: 

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

Intellectual / Professional skills & abilities:

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 

Personal Values Attributes (Global / Cultural awareness,  Ethics, Curiosity) (PVA):

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.

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.

This part is based on the LMS scenario 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. (10 marks)

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. (15 marks)

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

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. (5 marks)

q1) Display details of the top 10 most 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.

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. (20 marks)

(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. (15 marks)

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

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

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.