Reference no: EM133342566

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

Learning outcome 2: Show a comprehensive understanding of methodologies, tools, and technologies formanaging 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 Program 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 the use of an electronic plagiarismdetection 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 Program Guide).

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

Task 1: Smith and Co Second-Hand Bookshop

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 normalization, produce a set of entities thinking carefully about the appropriate use ofentity and attribute names.

Normalization Table: produce a normalization table up to the Third Normal Form (3NF) of theproposed system. The normalization table should include the entities, attributes, and primary and foreign keys.

Use the example in Appendix 2 as a template for your normalization 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 that 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.

Scenario:
Introduction

St John's hospital is updating its filing systems and wants to move its medical records within its hospital pharmacy to a computerized system to enable ease of use for staff and to modernize its 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, and telephone number) they also store the prescription details for each 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 needs to keep a record of their stock levels of drugs so that they can order more 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 labeled relationships with cardinality constraints clearly indicated, using the notation taught in the module. You do not need to show attributes on the diagram, only entity names. 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 dictionarymust specify:
- Table name.
- For each attribute, its name, description, and data type (using PostgreSQL data typesused in the SQL booklet for this module);
- Primary key and any foreign keys (ensure you specify which table each foreign keyrelates 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.

Attachment:- Database design.rar