Reference no: EM133991732

Assignment 1: Database Design

The following learning outcomes will be assessed:

Have a critical awareness of current problems and insights associated with modern secure database information systems development.

Show comprehensive understanding of methodologies, tools and technologies for managing and developing secure database systems.

Important Information
You are required to submit your work within the bounds of the University Infringement of Assessment Regulations (see your Programme Guide). Plagiarism, paraphrasing and downloading large amounts of information from external sources, will not be tolerated and will be dealt with severely. Although you should make full use of any source material, which would normally be an occasional sentence and/or paragraph (referenced) followed by your own critical analysis/evaluation. You will receive no marks for work that is not your own. Your work may be subject to checks for originality which can include use of an electronic plagiarism detection service.

Where you are asked to submit an individual piece of work, the work must be entirely your own. The safety of your assessments is your responsibility. You must not permit another student access to your work.

Where referencing is required, unless otherwise stated, the Harvard referencing system must be used (see your Programme Guide).

Please ensure that you retain a duplicate of your assignment. We are required to send samples of student work to the external examiners for moderation purposes. It will also safeguard in the unlikely event of your work going astray.

Task 1: Smith and Co Second-Hand Bookshop

Case Scenario:
The Smith and Co second-hand bookshop wishes to maintain data on their customers, authors and books. They may have many books by each author in the bookshop at one time. Books may be bought and sold several times. In other words, as the bookshop is a second-hand store they may sell a book, then buy it back off the customer at a later date to sell on to another customer.
A sample customer history form can be seen below:

Requirements
Using normalisation, produce a set of entities thinking carefully about appropriate use of entity and attribute names.

Normalisation Table: produce a normalisation table up to Third Normal Form (3NF) of the proposed system. The normalisation table should include the entities, attributes, primary and foreign keys.
Use the example in Appendix 2 as a template for your normalisation table.
Short Report: Write a report (of approximately 500 words) on any two potential database attacks which could occur on the Smith and Co Second-Hand bookshop database. For each attack:
Include information relating to why the database might be a target for an attack,
the type of attacks which may occur; and,
the type of data that might be extracted from the system in each attack.
Remember to cite any resources using Harvard referencing.

Task 2: St. John's Hospital
Produce an E-R diagram and data dictionary for the following scenario. Ensure you think carefully about entity names and attribute names and data types. No AI shortcuts - Just Genuine Assignment Help from Real Tutors.
Scenario:

Introduction

St John's hospital are updating their filing systems and want to move their medical records within their hospital pharmacy to a computerised system to enable ease of use for staff and to modernise their old paper-based filing system. You have been tasked with developing a database application to meet their needs.

Current Position
Currently, St John's pharmacy record details of all patients (including their name, address, date of birth, ID number, telephone number). They also store the prescription details for each particular patient (including the prescription number, the date prescribed, the name of the doctor who prescribed it, the name of the pharmacist who dispensed it and the issue date).

The pharmacy also need to keep a record of their stock levels of drugs so that they can order more in when stock becomes too low, this includes keeping a record of each drug, its name and item cost as well as the specific details of when this drug is prescribed and dispensed including the quantity prescribed and issued.

Entity-Relationship Diagram: Using an Entity-Relationship (E-R) diagram, produce a design of the proposed system, correctly showing labelled relationships with cardinality constraints clearly indicated, using the notation taught in the module. Ensure that you state clearly any assumptions that you have made in creating your Entity-Relationship Diagram.
Data Dictionary: Using a data dictionary, specify a set of tables and appropriate attributes for your design from the Entity-Relationship diagram above. For each table, your data dictionary must specify:
Table name;
For each attribute, its name, description and data type (using PostgreSQL data types used in the SQL booklet for this module);
Primary key and any foreign keys (ensure you specify which table each foreign key relates to);
any further constraints on the data (e.g. business constraints on data values and dates; required format; and whether the attribute is null/not null).
Use the example in Appendix 1 as a template for your data dictionary.

Assignment 2

The following learning outcomes will be assessed:

Display a critical understanding of data models, e.g. relational, NoSQL, and where they should be used.

Use Database Management Systems (e.g. Oracle, Postgres) and associated technologies in secure information and database systems development

Important Information
You are required to submit your work within the bounds of the University Infringement of Assessment Regulations (see your Programme Guide). Plagiarism, paraphrasing and downloading large amounts of information from external sources, will not be tolerated and will be dealt with severely. Although you should make full use of any source material, which would normally be an occasional sentence and/or paragraph (referenced) followed by your own critical analysis/evaluation. You will receive no marks for work that is not your own. Your work may be subject to checks for originality which can include use of an electronic plagiarism detection service.

Where you are asked to submit an individual piece of work, the work must be entirely your own. The safety of your assessments is your responsibility. You must not permit another student access to your work.

Where referencing is required, unless otherwise stated, the Harvard referencing system must be used (see your Programme Guide).

Please ensure that you retain a duplicate of your assignment. We are required to send samples of student work to the external examiners for moderation purposes. It will also safeguard in the unlikely event of your work going astray.

Task 1

Case Scenario

Introduction
Milllie's Musical Emporium (MME) Ltd has grown from a small company based in a small market town, to one of the country's leading suppliers of musical instruments and associated media (e.g. printed music, books, CDs and DVDs). However, they have never quite managed to move away from using a paper-based filing system for storing customer, sales and stock information. To cope with their growth and allow for more efficient stock recording, the store has decided to computerise their customer management and stock recording system. You have been tasked with developing a database application to meet their needs.

Current Position
Currently, MME Ltd record details of all customers (including their name, address, telephone number, date of birth and bank details, i.e. bank name, address, sort code and account number) who either purchase a musical instrument or media. Records are also kept of every transaction that takes place in any of the stores.

A stock warehouse is also kept. This makes it possible to see where any given product (including its identifier, type, name, description, cost) is currently stored. The stock warehouse also contains details of all purchases and allows stores to move stock from one store to another. This is particularly useful for those customers who wish to purchase a product that is not available in their local store.

The organisation would also like to be able to create management reports, which may, for example, show all sales at a particular store or a group of stores, between certain dates.

Proposed System

Using PostgreSQL, you are required to design and develop a prototype system that not only satisfies the requirements of the current system, but also has features that you consider to be worthwhile enhancements to the current system.
To achieve this, base your system on the following entity-relationship diagram and partial data dictionary

Entity-Relationship Diagram

Partial Data Dictionary

Note: The data dictionary is only partially complete. Task 1:
Subsequently, produce a single SQL script file which can be run within PostgreSQL without error and which drops and creates your tables (correctly ensuring that any referential integrity issues can be resolved), and inserts sample data into each table.

The SQL script file must also contain the code for the PL/pgSQL code that you implement in
(b) below.
Using PostgreSQL develop:
A PL/pgSQL stored procedure (and any associated code) which allows for registration of new customers.
A PL/pgSQL stored procedure (and any associated code) which allows an existing customer to purchase a product. This transaction must

allow the client to specify a specific product to purchase, a delivery date and time, ensuring that the delivery can only be booked if both that product and delivery slot are available.
Each PL/pgSQL stored procedure may require you to develop other PL/pgSQL stored functions, triggers and cursors that you think necessary to fully implement the required functionality.
When developing the system you should take into account the important development issues identified below:
Data types used should match those used in the tutorial booklet.
Dates and other relevant data should be validated accordingly.
Exception Handling must be in place to deal with all errors, e.g. invalid dates, duplicate customers, incorrect products specified, insufficient stock etc.
Any fields that require mandatory input, i.e. NOT NULL must be validated on input.

Task 2
Millie's Music Emporium has two users: admin and customer.
For each table specify what privileges you would give each user and briefly explain why they would have this privilege.
As a reminder here is a link to privileges in PostgreSQL:

You do not have to test these privileges in PostgreSQL, just list the commands, i.e. write the relevant GRANT commands for each table and provide a brief explanation.

Task 3:
You are required to prepare and submit a 5 to 10-minute voice-over screencast in which you present a set of Powerpoint slides and critically discuss the potential benefits of using a NoSQL document store database for the organisation from Task 1.

It is recommended that you use a software tool such as ‘Screencast-o-matic' (which can be downloaded for free or OBS to record your screencast. If your screencast exceeds ten minutes then only the first ten minutes will be viewed and assessed.

You must use up-to-date academic research literature in your review, and provide correctly cited references (using the Harvard referencing system) within the Powerpoint slides. Note there is no need to submit a copy of your Powerpoint slides.

It is recommended that you include the following content in your Powerpoint slides:

A title slide (one slide);
Slides which provide an overview of NoSQL document stores (two slides maximum);
Slides which provide a critical discussion of the benefits of NoSQL document stores (two slides maximum);
A slide (one slide maximum) which gives a summary;
Two slides maximum which contains your reference list in Harvard format.