Introduction:
In this section I will be showing my database I have made for the Winter Sports Sales. This will include how I designed the database, implemented correct verification through the tables, implemented relationships between the tables, tested the tables, importing data into them and then finally checking the imported data for errors.
Database description:
A database is a structured system that contains pieces of data about different types of information such as customer information, address, stock, products etc. All of this information gets stored within the system and can be accessed at any point. With this type of system allows us to store an unlimited amount of information and organise our work so that everything is perfect. It allows work to be backed up so if anything goes wrong. Also it is very easy to amend wrong information which is an advantage to paper work.
Database Design:
Microsoft Access system consists of a simple layout and design which is made of cells in which we can input different types of data. We can change the size and order of the cells in order to match our needs. We can change the colour and design of the database in order to make it look more professional. This type of a system allows errors and mistakes to me amended and erased with ease, which is an advantage as it saves times and isn’t messy.
Entity:
An entity is a relationship found between an object, place or person. An entity is a person, place or object which data can be collected. It is basically a piece of data an object or concept about which data is actually stored. Examples include employees, houses, and cars.
An entity relationship shows entity tables in a relational database which are related to each other. The way that these connect is based on the information they share. Each entity needs another entity on order to properly function and each table needs each other to complete the transaction process.
Example of Entity Relationship:
Entity or the Winter Sports Database:
Possible Validations Rules:
I will be adding different validation rules which will act on decreasing any possible errors which I may come across beforehand if the validations are not inserted. Validation rules are perfect due to making the user save time and effort.
Input mask:
• Postcode
• Order Number
• CD Number
Dropdown box:
• Card Type
• County
• Town
Range Check:
• Order Date
• Card Expiry Date
Primary Key:
A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code). For my database a good entity entitled to have the primary key may be the ‘Order Code’. This is simply because every Order is different and unique. As you can see in the screen shots above my primary key was added onto the table.
Foreign key:
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
Validation:
In each entity you are able to insert validation rules in each of the fields then allowing the input to have certain rules for what can be put into the field. Validation rules make sure there are very little errors within a database as possible. These validation rules allow the user to change the data which will be inserted whether it is e.g text, number, date etc. Within the database the main methods of validation have been put in drop down lists, input masks and range checks.
Drop down lists:
Using drop down lists in areas of my database in the areas chosen has been the customer data table. This allows there to be an option Mr, Mrs, Ms and Miss therefore customers cannot be inserted wrong.
Database implementation:
Creating a new table:
I decided to make my database on the software Microsoft access. When you would like to create a page the first screen you will see shows different options which can be chosen by preference from the user, from all the sections I chose to start from scratch with a ‘blank database’. When selecting this, a blank document in the database will open which I will then by inserting information which in my case was winter sports. The document will ask for you to, I chose to be ‘Winter Sports Database’. Then you must click ‘create’ made my blank database and saving it making sure I have my database in my files. When creating an entity with a blank table, you can upload the valid information you need. When creating a database the user is always available to edit. You could even import data into the database by using other methods which will be shown later on in the document. Once an entity is created you are presented with a blank table and it’s up to you what you would like to insert and where you would like to insert it. However you must remember that no matter what you are always able to edit letters and numbers in a database so you can always go back and rename entities and data information.
Setting data types:
Data type Description
Binary Varying Variable-length binary data with a maximum length of 8,000 bytes
Char Varying Variable-length non-Unicode data with a maximum of 8,000 characters
Character Fixed-length non-Unicode character data with a maximum length of 8,000 characters
Dec Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1
Double Precision Floating precision number data with the following valid values: -1.79E + 308 through -2.23E – 308, 0 and 2.23E + 308 through 1.79E + 308
Integer Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647)
National Character Fixed-length Unicode data with a maximum length of 4,000 characters.
National Character Varying Variable-length Unicode data with a maximum length of 4,000 characters
Row Version or Timestamp A database-wide unique number that gets updated every time a row gets updated
Date Time Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds
When choosing the right data type you are given a choice, there are many variety of options depending on what field it is. When using a database there is different factors e.g. short text which is used for things such as First Name and Last Name due to them not using a lot of characters whereas longer text is used for things such as house or business addresses?
The number dataset can be used for things such as date/time or card number which is in my database. When ending these options there is a feature called ‘Lookup Wizard’. This function allows the user to use a drop down list to choose the suitable information. An example of this would be the title of the person e.g. ‘Mr, Mrs, Miss or Ms’; therefore in this case the drop down list would appear. When selecting a data type this would change but with ‘Lookup wizard’ the feature would be set up using this.
Then this box below will appear which means you enter your data needed and make sure all spellings are correct when entering data so the customer doesn’t mess up their details. Clicking ‘finish’ allows the process system to go through making sure all data is accessible. The person entering their details only these will be able to be accepted.
Field Sizes: (Primary Keys)
Field sizes are a feature database. Field size puts a limit on the amount of characters which can be put when a customer is entering their details. An example used for primary key would be ‘Customer Names’. Inserting a primary key means you would need to click on the field and looking at the top showing the toolbar then clicking primary key when in design view. Knowing the primary key is selected you click on the key and see a shade, this states the process has been completed.
Creating the Primary Key:
Open the database that you want to modify. In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut menu, click Design View. Tip If you don’t see the Navigation Pane, press F11 to display it. Select the field or fields that you want to use as the primary key. A primary key tends to be used on the data that is most unique and stands out for each customer. In my opinion just like others ‘Order Number’ deserves the primary key as all numbers are different. It couldn’t be first name as there are more than 1 ‘Max’ or ‘Sophia’.
Validation Rule:
Validation checks ensure that data entered into the computer is sensible. Data is checked in accordance with a set of rules. The computer’s software can validate data while it is being entered into the computer. The main purpose of data validation is to spot an error.
Validation type How it works Example usage
Check digit the last one or two digits in a code are used to check the other digits are correct bar code readers in supermarkets use check digits
Format check checks the data is in the right format a National Insurance number is in the form LL 99 99 99 L where L is any letter and 9 is any number
Length check checks the data isn’t too short or too long a password which needs to be six letters long
Lookup table looks up acceptable values in a table there are only seven possible days of the week
Presence check checks that data has been entered into a field in most databases a key field cannot be left blank
Range check checks that a value falls within the specified range number of hours worked must be less than 50 and more than 0
Spell check looks up words in a dictionary when word processing
Validation text allows me enter a message for the user when invalid information is put into a field, a pop up box will appear showing the user where to find the right information. An example of this would be for the Customer Data entity and the message would say ‘Error: Enter Card Type from Drop down List’ which then the user will go back and choose the relevant data from the drop down list.
Input Masks:
You can easily create an input mask in an Access table by using the Microsoft Access Input Mask Wizard. Firstly you must get to the Navigation Pane right click on the table where you want to create an input mask. Select Design View from the pop-up menu. Then you must click on the field where you wish to add an input mask then you will have to click on the Input Mask box on the General tab of the Field Properties pane at the bottom of the window.
An input mask could be used in many different fields but most importantly card numbers e.g. 12 digit numbers on the user’s credit or debit card. Creating an input mask is a simple process which consists of clicking ‘Customer Card No.’
Expiry Date:
The field shows that a date is required which will be in this format for the database to accept the data which is entered ‘DD/MM/YYYY’. Only numbers are specified for this section due to the ‘0’ shown instead of it showing something such as a letter meaning text would be entered in this section. The field shows how the database has set out the date to display clearly for there are dashes also to separate the numbers.
Range checks:
A range check is commonly used when you are working with data which consists of numbers, currency or dates/times.
A range check allows you to set suitable boundaries:
Boundary Description: Validation:
Upper limit The maximum price of any item in a shop is £100 <=100
Lower limit In a shop, you cannot sell a negative number of items, however you can sell no items >=0
A range To achieve a B grade you must score between 75% – 84% >=75 AND <=84
Importing data into Database Software:
To start the user needs to make have both software’s which are ‘Access’ and ‘Excel’. When opened the software’s you can begin by opening access and going onto the data in the tool bar then clicking text as shown.
When browsing for data sets these must be saved somewhere where you can save and import them such as the user’s personal files. For the database I have created I used ‘Winter Sports’ then I can select and import.
Once the data has been imported you will be given two options. The options are known as delimited or fixed width. Delimited means having the limits or boundaries established which are disputed through the region. On the other hand, fixed layouts are layouts that start with a specific size and fixed width layouts allow a creator of the data sheet to have more direct control over how the page will look in most situations.
Evidence Fields:
TblCustomer table
Attribute name: Database type: Field size/format: Other validation: Comments: Screenshot:
Card Number: Number 12 Primary key
Input mask:
000000000000 Number type of data as it only contains numbers
NewCust_Title: Text 10 Validation rule: “Mrs” Or “Miss” Or “Mr” Or “Ms” Text data type as values are text.
Input mask can be used here as there are only 4 main titles.
First Name: Text 20 Required field Text data type as values are text
Last Name: Text 50 Required field Text data type as values are text
Address Line: Text 35 Required field Text data type as values are text and numbers
Town: Text 30 Required field Text data type as values are text
First Name: Text 20 Required field Text data type as values are text
County: Text 25 Required field Text data type as values are text
Post Code: Text 10 Input mask: >LL09\\ 0LL;;_ Text data type as values are text, numbers and characters.
Input mask can ensure that the postcodes entered are valid
Card Type: Text 25 Validation rule:
“SOCKET” Or “VISTA” Or “FRANCARD” Or “ARMENIAN EXPRESS” Or “MISTER CARD” Text data type as values are text and numbers.
Validation as only 5 credit cards are accepted
Expiry Date: Date/Time Short date Validation rule:
>=Now() Date data type as values are dates. Validation so that data followers the pattern
TblOrder Table:
Attribute name: Database type: Field size/format: Other validation: Comments: Screenshots:
Order number: Text 9 Primary key
Input mask:
LLL000000;; Text data type as text and numbers
Input mask will make sure the order numbers follow the same pattern of 3 letters and 6 numbers
Order date: Date/time Short date Validation rule:
>#31/12/2004#
Validation text:
“Please enter a valid order date” Date/time data type as the values are dates
The validation rule and text is to ensure that the correct date is entered correctly
Card number: Text 12 Input mask
000000000000 Text data type as text and numbers.
Input mask will make sure the card numbers are following the same pattern of 12
Testing before Importing Data:
Testing: TblCustomer
Test No. Test Details: Expected outcome: Test 1 Test 2
(Erroneous test) Test 1 results
1 Card number
(Primary key) The field should always contain a unique value in respect to the value of other Card Numbers. The card number should not be able to be put in twice. Entered a different card number Entered the same card number The field accepted the data
2 NewCust_Title
(Validation rule: “Mrs” Or “Miss” Or “Mr” Or “Ms”) The field should only accept Mrs, Miss, Mr, or Ms Entered Miss Entered a different title e.g. professor The field accepted the data
3 NewCust_Title
(Field size 10) The field should accept First Names that are 10 characters or less. Entered Miss Entered Anne-Louise The field accepted the data
4 First name
(Field size 20) The field should accept First Names that are 20 characters or less Entered Mary Entered Alessandra-Lucy-Susan The field accepted the data
5 Last name
(Field size 50) The field should accept Last names that are 50 characters or less Entered Smith Entered Sokratis Papastathopoulos
Avraam Salpingidis Liberopoulos The field accepted the data
6 Address Line 1
(Field size 25) The field should accept Address lines that are 25 characters or less 37 Flat H Newham 7564 Flat 12th Floor Sunset Avenue The field accepted the data
7 Town
(Field size 30) The field should accept Town names that are 30 characters of less Southampton Elizabeth House of Amazing Countries The field accepted the data
8 County
(Field size 25) The field should accept County names that are 25 characters of less Suffolk Tower Hampton Royal Embankment Way The field accepted the data
9 Post code
(Input mask: >LL09\\ 0LL;;) The field should only accept postcodes that are in the format of the input mask HB8 2GQ HKAJ9 89SH The field accepted the data
10 Card type
(Validation rule: “SOCKET” Or “VISTA” Or “FRANCARD” Or “ARMENIAN EXPRESS” Or “MISTER CARD” The field should only accept Socket, Vista, Francard, Armenian Express or Mister Card Vista No Card The field accepted the data
11 Expiry date
(Validation rule: >=Now() ) The field should only accept
dates that aren’t expired 7/03/2014 6/03/2014 The field accepted the data
Testing Database:
To make sure the database is ready for use when I hand it over to the man with his sportswear shop, we need to remove any errors that occur. In order to do so each individual aspect of the database needs to be tested. Including the Field Size, Input Masks, Validation Rules and Drop-Down Lists. Below is the testing for each of these aspects.
Field Size:
To test each of the set field sizes, a simple task was carried out where I entered a certain number of characters equal to the set field size and then tried to go one character further. If the character was not entered the test passed, if it was entered the test failed.
Appending Data:
In the event of any data in the data base being deleted, it can be appended back into the spread sheet to recover any data lost. An advantage of this is that it will save time of having to make an entirely new database. Below I deleted all the data in the database and then appending it back in. To start this process I deleted the data from each table.
The next step is creating a query for each table.
The next step is to then select each of the field names in the original tables.
Appending Data:
The final Step is to then run the query and the data will be appended.