Data Dictionary

Paul Monk 12CR

Data Dictionary

Companies Table

Field Names / Field data type / Field size / Example data / Validation
Account number / Primary Key / 7 / MT00001 / None
Company Name / Text / 30 / Weaver Motors / Must start with capital letter
>L<LLLL…..L
Motor Trader / Text / 30 / Mr J Grant / Must start with capital letter
>L<LLLL…..L
Telephone Number / Text / 13 / (01514)712825 / Must be 11 numbers long and have a 5 number code using input mask
(“0”9999)999999
Mobile / Text / 10 / 7976775307 / Must be 10 numbers long using input mask
“0”9999999999
Street / Text / 30 / 28 Grange Street / None
Town / Text / 30 / Liverpool / Must start with capital letter
>L<LLLL…..L
Postcode / Text / 6 / L359WZ / Must be in correct postcode format using input mask and all letters must be capitals
>L<999>L>L<
Email / Text / 30 / / None

Validation:- I will validate company name with an input mask so that less mistakes will be made in data entry. All characters have to be letters and the first letter is always a capital as it is a name.

I will validate motor trader for the same reason to reduce the amount of mistakes in data entry and because it is a name.

I will validate telephone number with an input mask so that all numbers are in the correct format which reduces the chance of errors in the data. I have put a code into the input mask which must always start with a “0”. Then the rest of the numbers come after the code so the number is in the correct format. The input mask also sets all characters to be numbers which further reduces the risks of mistakes on data entry.

I will validate mobile number with an input mask as well. As there is no code I have just made sure that all the characters are numbers and the number is the correct length. I have also added a “0” at the start of the number so that it does not have to be added in manually.

I will validate town using an input mask. This mask just ensures that all the characters are letters and also that the first letter is a capital letter as it is a name.

I will validate the post code using an input mask. This mask ensures that the postcode is in the correct format so greatly reduces the chance of errors. It also ensures that all letters are capitals which will make the code easier to read and use.

All the validations in this table will reduce the amount of mistakes in data entry and will make it a lot easier for customers and staff to use.

Bidding Table

Field Names / Field data type / Field size / Example data / Validation
Account number / Text / 7 / MT00001 / None
Registration / Text / 7 / MF06 QDA / Must be in correct format and all letters must be capitals using input mask
>L>L<99 >L>L>L<
Selling price / Currency / Currency / 5815 / Can’t be more than 7 numbers long
Bidding Date / Date/Time / General Date / 04/03/2009 00:00 / Must be in correct format
Bidding form number / Primary key / 11 / BID00000001 / None

Validation:- I will validate registration using an input mask. This input mask will ensure that the registration is always in the right format with numbers and letters. It also ensures that all the letters are capitals so it will stand out more and will be easier to read and use.

I will validate selling price by adjusting the size of field to 7 characters. This will ensure that the selling price can’t be more than 7 numbers long.

I will validate bidding date by changing the data type of the field to Date/Time and then picking the long date format. This will ensure that all data within the field is in the correct format.

All the validation in this table will help to prevent data entry errors and ensure all data is in the correct format.

Sales Table

Field Names / Field data type / Field size / Example data / Validation
Account Number / Text / 7 / MT00001 / None
Registration / Text / 8 / MW07 FJX / Must be in correct format and all letters must be capitals using input mask
>L>L<99 >L>L>L<
Number of Lots / Number / Integer / 97 / Can’t be more than 4 numbers long
Reserve price / Currency / Currency / 9600 / Can’t be more than 7 numbers long
Selling Price / Currency / Currency / 10402 / Can’t be more than 7 numbers long
Sold / Number / Integer / 1 / Must be 1 number long
Blocked / Number / Integer / 0 / Must be 1 number long
Auction Date / Date/Time / General
Date / 04/03/2009 00:00 / Must be in correct format

Validation:- I will validate Registration using an input mask. This input mask will ensure that the registration is always in the right format with numbers and letters. It also ensures that all the letters are capitals so it will stand out more and will be easier to read and use.

I will validate number of lots so it can’t be more than 4 numbers long by setting the field size to 4 characters long, this ensures the data in this filed can’t be more than 4 characters long. Also to make sure only numbers are present in the field I will set the field to be a number field so no letters or other characters can be put into the field.

I will validate reserve price so that it can’t be more than 7 numbers long by again setting the field size to only 7 characters long. I will also ensure it is a currency by changing the field data type to currency.

I will validate selling price in the same way as reserve price.

I will validate the sold and blocked fields by setting the field size to only 1 character long and setting the field type to number, so anything in the field can only be a number and can only be 1 character long.

I will validate auction date by setting the field type to date/time and selecting the long date format. This will ensure all data in this field is in the correct format.

Cars Table

Field Names / Field data type / Field size / Example data / Validation
Registration / Primary Key / 8 / MW07 FJX / Must be in correct format and all letters must be capitals using input mask
>L>L<99 >L>L>L<
Sold / Number / Integer / 1 / Must be 1 number long
Blocked / Number / Integer / 0 / Must be 1 number long
Manufacture / Text / 30 / Volkswagen / Must start with capital letter
>L<LLL….L
Model / Text / 50 / Golf 2.0 TDi / None
Mileage / Number / Long Integer / 20631 / Can’t be more than 7 numbers long

I will validate the sold and blocked fields in the same way as the sold and blocked fields in the sales table.

I will validate manufacture by using an input mask. It will make the first letter a capital as it is a name and make sure that all the remaining characters in the field are letters and not any other sort of character.

I will validate mileage by setting the field length to 7 characters and changing the field format to number. So that data in the field can be a maximum of 7 characters long and all have to be numbers.

Outline of all relationships

Company Table
Account number
Company Name
Motor Trader
Telephone Number
Mobile
Street
Town
Postcode
Email
Bidding Table
Account number
Registration
Selling price
Bidding Date
Bidding form number
Sales Table
Account Number
Registration
Number of Lots
Reserve price
Selling Price
Sold
Blocked
Cars Table
Registration
Sold
Blocked
Manufacture
Model
Mileage

Companies Table

Field Names / Relationship
Account number / This will be linked with account number so that they will not have to be entered more than once, which will make the database more efficient and user friendly.
Company Name / There will be no relationships from this field
Motor Trader / There will be no relationships from this field
Telephone Number / There will be no relationships from this field
Mobile / There will be no relationships from this field
Street / There will be no relationships from this field
Town / There will be no relationships from this field
Postcode / There will be no relationships from this field
Email / There will be no relationships from this field

Bidding Table

Field Names / Relationship
Account number / This will be linked with account number so that they will not have to be entered more than once, which will make the database more efficient and user friendly.
Registration / This will be linked with Registration so they will not have to be entered more than once, which will make the database more efficient and user friendly.
Selling price / There will be no relationships from this field
Bidding Date / There will be no relationships from this field
Bidding form number / There will be no relationships from this field

Sales Table

Field Names / Relationship
Account Number / This will be linked with Account Number so that they will not have to be entered more than once, which will make the database more efficient and user friendly.
Registration / This will be linked with Registration so they will not have to be entered more than once, which will make the database more efficient and user friendly.
Number of Lots / There will be no relationships from this field
Reserve price / There will be no relationships from this field
Selling Price / There will be no relationships from this field
Sold / There will be no relationships from this field
Blocked / There will be no relationships from this field

Cars Table

Field Names / Relationship
Registration / This will be linked with Registration so they will not have to be entered more than once, which will make the database more efficient and user friendly.
Sold / There will be no relationships from this field
Blocked / There will be no relationships from this field
Manufacture / There will be no relationships from this field
Model / There will be no relationships from this field
Mileage / There will be no relationships from this field

Query Design

Company Invoices Query

This query will find particular customers successful bids and automatically open it in a report which the customer can print off as a hard copy of their outstanding payments.

Fields this will include: From the Sales Table: Account Number

Registration

Selling Price

Sold

Auction Date

From the Companies Table: Company Name

Motor Trader

It will be a parameter search. The criteria will be in the Account Number field it will be [Enter Account Number]. Auction date will be sorted into ascending order but all other fields will be unsorted. All fields apart from account number will be set to show.

My query will take the form of:

Field / Account Number / Registration / Selling Price / Sold / Auction Date / Company Name / Motor Trader
Table / Sales Table / Sales Table / Sales Table / Sales Table / Sales Table / Companies Table / Companies Table
Sort / Ascending
Show / No / Yes / Yes / Yes / Yes / Yes / Yes
Criteria / [Enter Account Number]
Or

Bidding History Query

This query will find all the bids for a certain customer. It will find all the bids if they were successful or not. It will also record sales and blocks of cars.

Fields this will include: From the Sales Table: Account Number

Registration

Reserve Price

Selling Price

Sold

Blocked

Auction Date

Bidding Date

It will be a parameter search. There will be criteria in the Account Number field it will be [Enter Account Number]. The auction date filed will be sorted into ascending order all other fields will be unsorted and all fields will be set to show.

My query will take the form of:

Field / Account Number / Registration / Reserve Price / Selling Price / Sold / Blocked / Auction Date / Bidding Date
Table / Bidding Table / Sales Table / Sales Table / Sales Table / Sales Table / Sales Table / Sales Table / Bidding Table
Sort / Ascending
Show / Yes / Yes / Yes / Yes / Yes / Yes / Yes / Yes
Criteria / [Enter Account Number]
Or

Type of Car Query

This query will find all the different types of cars. This can be done by the customer to find a car they wish to bid on or by the staff to check car details. Cars will be found by Manufacture.