Extracts from this document...

Introduction

GNVQ ADVANCED INFORMATION AND COMMUNICATION TECHNOLOGY ASSIGNMENT - UNIT 6 DATABASE DESIGN SCENARIO You have been asked to create a database to handle various account combinations of customers in a bank of your choice. Your database must consist of at leas three tables (maximum of 7) to store information about customers, accounts, investments, mortgages, etc. The bank offers a range of accounts, such a current, student high rate deposit, etc and a range of investment services such as Individual Savings Account (ISA) or a portfolio holding shares. A customer may have more than one account and at the same time may also hold an investment or a fixed rate mortgage (or both). Your working database will allow users to: * set up new accounts * enter, view, and edit data in entry forms * initiate queries to search for specific information * and generate a monthly overall financial statement for each customer USER SPECIFICATION PURPOSE A model is a representation of something in the real world. We use models in all walks of life because they represent a simplified view of the world, highlighting the part which interests us. A good model should: Aid communication A model that is difficult to read or understand is of no use. Projects of various sizes all depend upon communication - exchanging ideas, discussing proposals, understanding requirements. A model should aid all ideas. There is no use communicating a complex model as it will be difficult to understand, to verify and to maintain. A test which can be applied to any model is to give someone unconnected with the system and ask them what the model represents. If they describe the system accurately then a good model has been produced. Fits its purpose A good model should have an explicit purpose that everyone uses it recognises. Project models are produced at many different stages of a project life cycle for various purposes. ...read more.

Middle

8) The whole database should be able to be filled in between 21 to 30 minutes. 9) A minimum of 5 tables should be implemented which will include information on customers, accounts, investments and insurances. 10) Information should be made available in different tables, which will be identified by the customer ID. 11) Colour should be implemented to make the database more interesting and easier to identify different sectors. 12) Certain boxes/cells should contain validation rules as it will mean that errors are minimised. 13) Certain formulas should also be implied as it will mean that it is for less time consuming. NORMALISTATION THE STEPS INVOLVED IN ORMALISTAION The steps involved are the creation of an un-normalised form (UNF) followed by first (FNF or 1NF), second (SNF or 2NF) and third normal forms (TNF or 3NF). UNF Collect all attributes and select a key for the un-normalised relation. The key could be any item but select a 'reasonable' key - that is, unique for the particular data source, involving the smallest combination of items possible and not textual. 1NF Remove all repeating groups of attributes (those with several values determined by a single value of the key) to form a new relation. The key to this new relation will be the key of the UNF (which should be taken with the removed group of attributes) plus any further attributes belonging to the removed group which are necessary to uniquely each row. 2NF Remove attributes determined by part of a compound or composite key to form a new relation. The determining attributes become the key of the new relation. A relation is in 2NF if it is 1NF and every non-primary key attribute is fully functionally dependent on the primary key. 3NF Remove attributes determined by non-key attributes to form a new relation. The determining attributes become the key of the new relation. Mark foreign keys. ...read more.

Conclusion

Married/Single SORT CODE PRIMARY Sort code Number LI Branch name Text 20 chars Branch address Text 50 chars CUSTOMER ID PRIMARY Customer ID Lookup wizard / / CURRENT ACCOUNT NUMBER PRIMARY Current account number Number 8 digits Date of CA transaction Text 8 digits Format:00/00/00 CA start balance Currency / / CA Money out Currency / / CA Money in Currency / / CA Overdraft limit Currency / / CA Description Text 50 chars CUSTOMER ID PRIMARY Customer ID Lookup wizard / / SAVINGS ACCOUNT NUMBER PRIMARY Savings account number Number 8 digits Date of SA transaction Text 8 digits Format:00/00/00 SA start balance Currency / / SA Money out Currency / / SA Money in Currency / / SA Overdraft limit Currency / / SA Description Text 50 chars Interest earned tax year ending 2002 (before tax) Currency / / Lower rate tax deducted Currency / / CUSTOMER ID PRIMARY Customer ID Lookup wizard / / MORTGAGE ACCOUNT NUMBER PRIMARY Mortgage account number Number 8 digits Date of MA transaction Text 8 digits Format:00/00/00 Balance bought forward Currency / / MA Description Text 50 chars Debit Currency / / Credit Currency / / ENTITY RELATIONSHIP DIAGRAM An entity relationship diagram is a graphic used to represent entities and their relationships. Entities are normally shown in rectangles or squares, and relationships are shown in diamonds. The cardinality of the relationship is shown inside the diagram. The simplest form of binary relationship is a one-to-one (1:1) relationship, in which an entity of one type is related to no more than one entity of another type. The second type of binary relationship is one to many (1:N), in which an entity of one type can be related to many entities of another type. The third and final type of binary relationship is many to many (M:N), in which an entity of one type corresponds too many entities of the second type, and an entity of the second type corresponds too many entities of the first type. ...read more.

This student written piece of work is one of many that can be found in our AS and A Level Computer Science section.