Data Base: Postgree SQL (Compulsory Use)
Subject: Secure Database Systems
Please read the scenario below carefully and then attempt the database design tasks set out below that.
Task 1: The Gill Art Gallery
Scenario:
The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it 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 Gill Art Gallery 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: Millie’s Musical Emporium (MME)
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
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.
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. 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 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.
Appendix 1 – Sample Data Dictionary
The following is a sample data dictionary for the LEASE table from the property for rent database that you have been using during the module. It shows how the data dictionary must be laid out for each table.
Length is the maximum size permitted.
Required states ‘Y’ if the column is NOT NULL.
Validation states if there are any business constraints, e.g. the payment method can only be the character C or D.
Format specifies if there is any restriction on the format of the data, e.g. the rent_pm.
PK identifies attribute(s) which make up the primary key.
FK identifies whether an attribute is a primary key and indicates which table/attribute it relates to.
Comments are for any other relevant comments, e.g. to explain an attribute or validation constraints.
Guidelines
You need to submit your normalisation table and short report for Task 1 and E-R diagram and data dictionary for Task 2.
The submission should be either a Word or PDF file.
Struggling with Data Base: Postgree SQL (Compulsory Use) Assignment help? Take the best online service. Order Now!
No Fields Found.