Item 1: Database Design (25% Weighting)

Hand In: Friday March 15th, 2013

Learning Outcomes

Module Learning Outcomes / Map to Programme Learning Outcomes
2) SELECT APPROPRIATE PRACTICES AND TOOLS TO DESIGN AN EFFECTIVE INFORMATION SYSTEM. / Analysis
Enquiry

Description

This is an individual assignment. You are expected to spend between 20 and 30 hours completing the work.

You are to imagine that you are a database expert brought in by the management team to design an information system to store data and provide information for Hopeville School

Internal IT staff members at Hopeville School have produced a draft model which is known to be unsatisfactory. You are required to refine this model to accurately reflect the case study and to ensure that the required data can be retrieved. The current model contains some structural defects. The only attributes present are primary and foreign keys. Some of the keys may not be accurate and additional attributes will need to be added in order for the required information to be retrieved.

You should read through the case study carefully and perform a critical analysis of the situation, noting the information provided in the appendices and the information to be retrieved through the queries in assessment item 2 as these will have an impact on your modifications.

Item 1 work should be collated as a report (circa 1000 words):

Choose three different structural features of the design which could be improved. For each feature you are expected to

 Describe the structural feature

 Give one or more examples of where it is incorrectly modelled in the design

 Explain the effect that this would have on the ability to retrieve information

 Show with a suitable diagram how you have improved this feature

(6% - 2% for each different structural feature)

NOTE: Missing attributes and incorrect keys are NOT structural features.

Improve the Entity Relationship Model such that it accurately reflects all the requirements outlined in the case study and that all the information required in item 2 can be retrieved. This will involve making the improvements described above, correcting any other inaccuracies, amending Primary and Foreign keys where necessary and adding necessary attributes.

(10%)

Your model should include

 Relationships (giving name, order and optionality)

 Attributes (making sure that those necessary for information retrieval are present)

 Keys and referential integrity constraints

 Supporting documentation - Entity definitions. Your relations will be in 3rd normal form (3NF) and you should include any diagrams of the normalisation process you have used.

NOTE: A suitable modelling tool (QSEE, SELECT, Net Beans, etc.) must be used for the Entity Relationship Models.

Undertake the following:

Explain the following terms giving example(s) from the case study

Operational information
Tactical Information
Strategic information

(6% - 2% for each area)

Using an example of data which will be stored in your database state how you would ensure that the organisation complies with the Data Protection Act.

(3%)

NOTE: Your work MUST be submitted as a paper document. Before handing in your work, carefully check to ensure that it meets the assessment criteria. The work submitted MUST be as a result of your own individual efforts.

Assessment marking criteria for Item 1 (25%)

Grade Point / Percentage / General Comment / Detailed Criteria
13-15 / >70 / The work greatly exceeds the requirements of the assessment / Correct identification of three different structural problems with accurate suggestions for correction
A sophisticated ERD with all of
 Entities and relationships showing name, order and optionality
 A comprehensive set attributes
 Suitable primary keys, necessary Foreign Keys and integrity constraints
 Supporting documentation including entity descriptions and details of normalisation
An excellent explanation of the information types with relevant examples from the case study. An example relating to the DPA showing excellent understanding of the act and its application.
10-12 / 60-69.5% / The work meets and goes beyond the requirements of the assessment / Discussion of three different problems with some reasonable suggestions for correction
A fairly sophisticated ERD with all of
 Entities and relationships showing name, order and optionality
 A comprehensive set attributes
 Suitable primary keys, necessary Foreign Keys and integrity constraints
 Supporting documentation including entity descriptions and details of normalisation
A very good explanation of the information types with useful examples from the case study. An example relating to the DPA showing reasonable understanding of the act and its application.
7-9 / 50-59.5 / The work meets the requirements of the assessment / Discussion of three problems with some thought given to correction
An adequate ERD with most of
 Entities and relationships showing name, order and optionality
 A comprehensive set attributes
 Suitable primary keys, necessary Foreign Keys and integrity constraints
 Some evidence of supporting documentation
A good explanation of the information types with examples from the case study. An example relating to the DPA showing good understanding of the act and its application.
4-6 / 40-49.5 / The work barely meets the requirements of the assessment / Discussion of at least two problems
An ERD with some of
 Entities and relationships showing name, order and optionality
 A comprehensive set attributes
 Suitable primary keys, necessary Foreign Keys and integrity constraints
 Supporting text may be incomplete or missing
An explanation of the information types. An example relating to the DPA and its application within the scenario.
3 / 30-39.5 / The work fails to adequately meet some of the requirements of the assessment / Some of the required work will be missing or poorly attempted
1-2 / 10-29.5 / The work fails to meet most of the requirements of the assessment / Most of the required work will be missing or poorly attempted
0 / <20 / Non-submission or totally inadequate.