You have been asked to create a database to handle various account combinations of customers in a bank of your choice Essays

You have been asked to create a database to handle various account combinations of customers in a bank of your choice Essays You have been asked to create a database to handle various account combinations of customers in a bank of your choice Essay You have been asked to create a database to handle various account combinations of customers in a bank of your choice Essay 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. This includes: * physical models showing the system as it is currently exists, with implementation details who does what and where * logical models showing the essence of the system what the system actually does, without any implementation details * models used during the systems analysis stage of the project * models used during the design stages of a project * models giving a general overview of a system, putting all the relevant parts of the problem into perspective * highly detailed models for one small part of a system * models that show different perspectives of a system, for example dynamic, functional or static. Capture the essentials Many models consist of documentation for businesses, such as invoices, orders, receipts. If it only consisted of documents, what would happen if the business changes? Most businesses today are dynamic meaning that they have to respond to increasing competition and to he changes in the environment around them. It is therefore necessary to capture the essence of the business and model around those core concepts to be able to handle changes properly. Be consistent with other models in the system Different models of the same system can be integrated or cross checked against each other for consistency. This is to say that if a set of models represent the same thing then it should be possible to put them altogether without introducing inconsistencies. Reduce complexity To deal with systems not too complex to understand directly by breaking them up into more manageable parts and separating out a small number of important things to deal with at a time. Be able to be drawn on ordinary paper Models are a communication aid. If we present users with a model that fills the back of a roll of wallpaper then no matter how accurately it may represent the system under investigation it is unlikely that it will communicate the main point to users. Models produced on a standard A4 sheet of paper are more likely o represent manageable chunks or a system that users can comprehend, comment on file, post to other users and so on. Be capable of manipulation by computer Drawing models is essentially an iterative process a model is produced, shown to the users then redrawn as many times as is necessary until the users are happy with it. Using a model that can be manipulated by computer cuts out the drudgery of redrafting, and ensures that models can be produced and amended speedily and without introducing errors. USER SPECIFICATION AND USER SKILLS In order to identify the type of working model that the user would want I designed a questionnaire. This was distributed to 10 employees of who all worked for Barclays and were used to an already existing system put into the banks. The purpose of the questionnaire was to identify what the user wants. As part of my GNVQ Advanced IT project I have to design a database for a bank. I would be very grateful if you could spare a few minutes to fill it in. Please tick as applicable. 1) When filling in information would you like the font to be a large or small size? -Large -Small 2) Would it be easier if drop down lists were made available to reduce time taking operations? -Yes -No 3) On average how long do you spend with a customer? -5 to 10 minutes -11 to 20 minutes -21 to 30 minutes -1 hour -longer 4) Would it not be easier if the customer could fill out a form first and then you could input the information into the computer? -Yes -No 5) How many ranges of accounts do you offer from the following? -Current account -ISA -Insurance -Savings and investments -Mortgages and home -Students and graduates 6) Would it be easier if information was copied from table to table or was identified in different sections using the customer ID? -Yes -No 7) Does adding colour make the database more interesting? -Yes -No 8) Would it be a good idea if the database was made more realistic using 3D effects and interesting graphics and writing? -Yes -No 9) Is it a good or bad idea if certain boxes were eliminated with a certain validation rules. E.g. Tel no. can only be a number up to 11 digits. -Good -Bad 9) Would it be suitable for certain formulas to imply be themselves such as date? -Yes -No Thank you. USER SPECIFICATION AND USER SKILLS Below you are able to view the results of this questionnaire based upon 10 people: In order to identify the type of working model that the user would want I designed a questionnaire. This was distributed to 10 employees of who all worked for Barclays and were used to an already existing system put into the banks. The purpose of the questionnaire was to identify what the user wants. As part of my GNVQ Advanced IT project I have to design a database for a bank. I would be very grateful if you could spare a few minutes to fill it in. Please tick as applicable. 1) When filling in information would you like the font to be a large or small size? -Large 8 -Small 2 2) Would it be easier if drop down lists were made available to reduce time taking operations? -Yes 9 -No 1 3) On average how long do you spend with a customer? -5 to 10 minutes 2 -11 to 20 minutes 1 -21 to 30 minutes 7 -1 hour 0 -longer 0 4) Would it not be easier if the customer could fill out a form first and then you could input the information into the computer? -Yes 2 -No 8 5) How many ranges of accounts do you offer from the following? -Current account 10 -ISA 10 -Insurance 10 -Savings and investments 10 -Mortgages and home 10 -Students and graduates 6) Would it be easier if information was copied from table to table or was identified in different sections using the customer ID? -Yes 10 -No 0 7) Does adding colour make the database more interesting? -Yes 10 -No 0 8) Would it be a good idea if the database was made more realistic using 3D effects and interesting graphics and writing? -Yes 5 -No 5 9) Is it a good or bad idea if certain boxes were eliminated with a certain validation rules? E.g. Tel no. can only be a number up to 11 digits. -Good 10 -Bad 0 9) Would it be suitable for certain formulas to imply be themselves such as date? -Yes 10 -No 0 Thank you. Final Specification Carrying out the questionnaire has enabled me to come up with the following specification according to the results that were achieved. 1) The model should be simple and user friendly and be able to communicate well. It should be able to: -exchange ideas -discuss proposals -understand requirements 2) It should capture the essentials so if the business does face changes the way it operates is not affected due to the pressures of ever growing competition. 3) Reduce complexity of the model and break it down into manageable chunks. 4) To be able to fit the basic concept or model onto one A4 sheet. 5) Ensure models can be produced and amended speedily and without introducing errors according to the user. 6) Ensure the font is of a reasonable size to ensure less constraint on the eyes of the user. A font size of 12 should be a good idea. 7) Drop down lists should be made available to ensure less time is taken. 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. Remember that determinacy exists when the value of the determined attribute is totally dependent on the value of the determining attributes. Test for TNF Given a value for the key, is there only one possible value for each attribute in the relation? Examine every non-key attribute and question its relationship with every other non-key attribute. Ask the question, If I know the value for attribute A, is there only one value for attribute B? If the answer is Yes, then attribute B is determined by attribute A (that is, is functionally dependent on A). Since A is a non-key attribute, attributes A and B must be removed to a separate relation, leaving A behind as a foreign key and using it as the primary key of the new relation. When this process is finished you should be able to examine each relation and answer Yes to the following question: Is each attribute dependent on the key, the whole key and nothing but the key? NORMALISATION TABLE UNF 1NF 2NF 3NF Customer ID Customer ID Customer ID Customer ID Title *Sort code *Sort code *Sort code Customer name Title Title Title Customer address Customer name Customer name Customer name Telephone number Customer address Customer address Customer address Sex Telephone number Telephone number Telephone number Date of birth Sex Sex Sex Marital status Date of birth Date of birth Date of birth Sort code Marital status Marital status Marital status Branch name Branch address Sort code Sort code Sort code Current account number Branch name Branch name Branch name Date of CA transaction Branch address Branch address Branch address CA Start balance CA Money out Customer ID Customer ID Customer ID CA Money in Current account number Current account number Current account number CA Overdraft limit Date of CA transaction Date of CA transaction Date of CA transaction CA Description CA Start balance CA Start balance CA Start balance Savings account number CA Money out CA Money out CA Money out Date of SA transaction CA Money in CA Money in CA Money in SA Start balance CA Overdraft limit CA Overdraft limit CA Overdraft limit Date of SA transaction CA Description CA Description CA Description SA Money out Savings account number SA Money in Date of SA transaction Customer ID Customer ID SA Overdraft limit SA Start balance Savings account number Savings account number SA Description Date of SA transaction Date of SA transaction Date of SA transaction Interest earned tax year ending 2002 (before tax) SA Money out SA Start balance SA Start balance Lower rate tax deducted SA Money in Date of SA transaction Date of SA transaction Mortgage account number SA Overdraft limit SA Money out SA Money out Date of MA transaction SA Description SA Money in SA Money in MA Balance bought forward Interest earned tax year ending 2002 (before tax) SA Overdraft limit SA Overdraft limit MA Description Lower rate tax deducted SA Description SA Description Debit Mortgage account number Interest earned tax year ending 2002 (before tax) Interest earned tax year ending 2002 (before tax) Credit Date of MA transaction Lower rate tax deducted Lower rate tax deducted MA Balance bought forward Mortgage account number MA Description Date of MA transaction Customer ID Debit MA Balance bought forward Mortgage account number Credit MA Description Date of MA transaction Debit MA Balance bought forward Credit MA Description Debit Credit EXPLAINING THE DATA DICTIONARY Different databases vendors supply different dictionary products. The following is a general review of the desirable components and the tasks it ought to be capable of. A data dictionary is a composite of: Data dictionary contains definitions of records; data; items; relations, etc. The user can find the characteristics of the data stored in the database. Data directory or catalogue Contains information about where data is stored. Thus, it will contain the internal schemes and indexes maintained by the DBMS (such as CustNo_Index and CustomerName_Index). Also, in a distributed database, the data directory keeps track of which data is stored at which nod. The data directory is used by the data management software rather than by users. Data stored by the data dictionary is known as meta data which is data about data. A data dictionary may hold only information about the database implementation or may also hold details of conceptual model ad process or functional model. The four partition model of a data dictionary can be used to describe the features that a fully implemented data dictionary may contain. The line between the boxes represents the relationship between data structures and the programs which use them, etc. This information is useful when amending data structures, for example so that the programs affected can be traced and the data model updated as necessary to correspond to the new data structures. Using the data dictionary in systems development As an example of how the data dictionary may be used, the following points illustrate how it may be used in systems development: * Information provided by the data dictionary helps to identify redundant data and help developers and users to understand and agree what data means. * Information can be used to help with conceptual data modelling and also to generate documentation. * Prototyping may be used to develop a database system it is a quick, experimental approach which requires a readily available store of data and a readily available description of that data together with some computer based tools. Other data dictionary users There are several things that management needs that the data directory provides, including: * Information about existing corporate data * Information about how and where data is used * A means of controlling data * A means of controlling new users or modifications of existing data or processes using the data * A tool for standardising data definitions * Security of data resources The data administrator: * Helps to ensure all users are provided with the communications network and data and processing structures they need * Is responsible for implementing and maintaining DBMS and DDS * Is responsible for controlling the consistency of entries. For the Data Administrator the dictionary provides: * Description of the DB * Subschema generation * Data description for application programs * Extensive and timely on-line documentation of all data structures * An effective and uniform means of recording detail about DBMS, non-DBMS and manual systems * A valuable tool for monitoring changes * A device for estimating major cost factors in implementing planned modifications * A facility that optimises the use of corporate data * A medium through which non-data processing staff can be acquainted with corporate data. For the system analyst/designer the dictionary provides: * Verification of the structure of company data * Identification of redundancy * Implementation standards * System documentation * Description of schemas and subschemas * Description of files records and fields * Automatic COPY facilities * Cross reference to users of data * Security facilities * Responsibilities for updating * Project progress facilities. For programmers the dictionary provides: * COPY facilities * Information about interdependencies * Description of the usage of files and programs within systems * Usage of data entities * Generation of reporting facilities The auditor needs: * Control of information * Accuracy of information about flow of data * To evaluate and audit new and revised systems for accuracy, effectiveness, reliability and timeliness * Quick and effective mechanism to trace and audit all activities. THE DATA DICTIONARY ENTITY KEY ATTRIBUTE DATA TYPE SIZE VALIDATION CUSTOMER ID PRIMARY Customer ID Auto number LI SORT CODE FOREIGN *Sort code Lookup wizard / / Title Text 4 chars Mr/Mrs/Miss/Ms Customer name Text 15 chars Customer address Text 50 chars Telephone number Text 11 digit Sex Text 6 chars Male/Female Date of birth Text 8 digits Format:00/00/00 Marital status Text 7 chars 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.