KL7011 Advanced Databases

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 font, which preserves SQL format and layout.

You are required to use the Harvard Style of referencing and citation. The “Cite them right” guide is recommended for referencing and citation (Pears and Shields, 2008) which should be followed throughout your answer especially Part 3. Please do not include references to lecture notes.

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:

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

5. 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 Regulations

You are advised to read the guidance for students regarding assessment policies (Northumbria, 2019). They are available online here.

Late submission of work

Where coursework is submitted late without approval, after the published hand-in deadline, the following penalties will apply.

For coursework submitted up to 1 working day  after the published hand-in deadline without approval, 10% of the total marks available for the assessment (i.e.100%) shall be deducted from the assessment mark.

Coursework submitted more than 1 working dayafter the published hand-in deadline without approval will be regarded as not having been completed. A mark of zero will be awarded for the assessment and the module will be failed, irrespective of the overall module mark.

These provisions apply to all assessments, including those assessed on a Pass/Fail basis.

The full policy can be found here.

Students must retain an electronic copy of this assignment (including ALL appendices) and it must be made available within 24hours of them requesting it be re-submitted.

Academic Misconduct

The Assessment Regulations for Taught Awards (ARTA) contain the Regulations and procedures applying to cheating, plagiarism and other forms of academic misconduct.

The full policy is available at here

You are reminded that plagiarism, collusion and other forms of academic misconduct as referred to in the Academic Misconduct procedure of the assessment regulations, which are taken very seriously. Assignments in which evidence of plagiarism or other forms of academic misconduct is found may receive a mark of zero.

For ERD, SQL and other database technical components:

The students will produce exceptional models (conceptual/logical/physical), and will demonstrate the use of notation/language, which have outstanding syntactic accuracy (e.g., adhering to ER modelling standards, error free SQL code), with exceptional semantic relevance (e.g., are relevant to the requirements of the particular scenario).

The students will produce fully complete models (conceptual/ logical/ physical), and will demonstrate the use of notation/language, which have high syntactic accuracy, with high semantic relevance.

The students will produce almost complete models (conceptual/ logical/ physical), and will demonstrate the use of notation/language, which have appropriate syntactic accuracy with reasonably well semantic relevance.

The students will produce fairly complete models (conceptual/ logical/ physical), and will demonstrate the use of notation/language, which have adequate syntactic accuracy with reasonable semantic relevance.

4The students will produce models, and will demonstrate the use of notation/language, which have some syntactic accuracy and semantic relevance but on balance inadequate as a whole.

The students will not produce sufficient models, and/or will be unable to demonstrate the use of notation/language with significant syntactic accuracy and/or significant semantic relevance.

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

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

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

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

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

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

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