Write about the database that you are going to design e.g. a database of cars that are for sale in a car dealership or second hand garage.
The aim of this exercise is to identify the data items that you will need to create in your database and to make you think about why you want to include these fields.
Imagine someone you know wants to buy a car, new or second-hand. What information would they need to help them make a decision? The information would be presented as outputs from a database program. The database would store facts about cars such as Make, Model, Year and Price.
The first thing you need to decide and document is the general type of car that you will store in your database. Write this up as ‘I am creating a database of cars. The cars will be second hand and of good quality so they will be makes like Mercedes and Jaguar, fairly new and with fairly low mileage’.
Each car is a single record in the database. Outputs will include one or more fields. A field is a data item that records a property of something e.g. colour. A record is a group of data items that are related e.g. Make, Model, Price.
Task
List ten questions that produce outputs that will help the car buyer make a decision. The outputs that you specify must include: the form of the output, list or chart; a list of the fields for each record; the field on which the data are sorted; display medium – screen or paper;, paper size, if printed.
You will need to consider the amount of information that you can practically display in your chosen output medium and format. You are unlikely to display more than six fields in any output and most will be in the range of three to five.
The outputs will have constraints, in particular the amount of space on screen or paper. Fields should not be too wide or they will not display in the space available. Outputs will usually include more than one field so the total width of the fields chosen should fit into the space available. Screen displays can scroll; printed output can be split over more than one sheet of paper; paper orientation can be changed from portrait to landscape to accommodate wider output. In general you will want to limit output to a screen without horizontal scrolling and to a single sheet of paper (by width, not length): this will make it easier for the user.
Outputs come in a number of forms:
Task
Specify the output that each question will require i.e. list, table, chart, report.
Simple: list all cars made by Ford.
The fields displayed will be Model, colour, year and price, sorted in descending order by price. This will in the form of a list of the Ford cars available with the most expensive at the top. The list will be displayed first on screen with the option to print if required. Printing will be to A4 in portrait. Font chosen is Arial, 12 point for easy reading.
Complex: List all Ford Fiestas that are red AND registered after 2000.
Outputs: The fields displayed will be Model, Year of Registration, Price and Mileage, sorted in ascending order by Year of Registration. The output will be a list with the oldest cars at the top. The list will be displayed first on screen and printed if required. The font will be Arial 12 points so the list should fit onto a single page in portrait orientation.
Complex: list all cars that are made by Ford AND cost less than £3,000.
Complex: list all cars made by Ford that are NOT Mondeos.
Complex: list all cars made by Ford OR Nissan that are red.
Complex: List all cars that are two seater AND with top speed greater than 150 mph.
Complex: List all cars that are made in the UK AND have a fuel consumption >=50mpg
Complex: List all cars with top speed > 150 AND fuel consumption > 25 mpg
Complex: List all Nissan cars registered before 2000, NOT Patrol
Complex: List all cars that are in tax band A or B AND registered after 2005.
Complex: List all cars in insurance groups 1-5 AND with fuel consumption > 50 mpg
Complex: List all estate cars registered after 2000 excluding (NOT) Volvo
You can include a couple of simple questions: these are best used for creating charts. The rest of the questions should be complex, involving the use of AND, OR and NOT. You should have at least one AND, one OR and one NOT question in your list of questions. Choose from the examples above and add some of your own, introducing new fields.
The outputs should include at least two fields that are numeric – you will need these later when you define validation routines, which are much easier for numeric items.
Task
Choose eight of the above or modify them or write your own. You must have a statement of outputs after each one.
Questions like this, which are addressed to a database, are known as 'queries'
You need to appreciate chart output in more detail before you can define questions and outputs.
Task
Copy the carstest database from K:\ict\gcse\project1B into your N drive. Add an extra record to the list so you are familiar with the data stored.
To make a chart choose the Create tab and PivotChart. To make a chart click in the bottom grey box to display the list of fields. Drag the fields onto the chart to create it (this is quite easy!)
Our first charts will be based on the entire database.
Charts are useful to show trends in groups of data e.g. group by Make, Colour, Model.
Simple: make a column chart showing the mileage of all Ford cars. Specify format details.
This shows that you can make charts that show individual records. You can only display individual items (cars) on a chart if you include the primary key (ID – the unique identifier for each record).
Be careful not to include too many records, otherwise the chart will have too much information and will not be very useful. If your database includes 100 or 1000 records you should use a query or filter to select a group of records before displaying individual items in a chart.
Example: make a column chart showing the mileage of all Ford cars. Specify format details. Type of chart (column, pie, line, X-Y); variables on chart axes; chart colours for screen and printer; headings and labels.
Task
Now define one more chart output.
Each step in this project builds on the previous one. You must get everything right in this stage before you start the next one. Hard work here will be repaid with easier work later.
Task
Write down a list of the fields that you will need to collect in order to answer the questions defined in section 1. Write a brief explanation of why each field is needed. This is best done in a table with two columns. Reasons for including the field must link to the outputs required. Simple reasons are fine e.g. need the Model so the user can see exactly what type of car it is; need the engine size to judge running costs; need fuel consumption and insurance group to judge total cost of ownership.
You will need to choose items that you know will be available from standard sources: it’s no use choosing fields that you cannot hope to find or which are too obscure e.g. make of air filter.
You may need some other fields that are not part of the queries but are so basic that they cannot be omitted. You will need a primary key, which is a unique identifier for each record. Access provides an autonumber data type for this but you can probably think of a unique identifier found on cars, at least on second hand ones.
Field |
Reason for Including |
Make |
Make is an important piece of information about cars and users would often state the make of car they want to buy. Therefore it is an output in queries 1, 2, 3, 5, 8 and 10. |
Model |
Model refines the make; users will want to see outputs for particular models, not just make. Queries 1,2 and 3 include the model. |
Colour |
|
Mileage |
This field is used to sort the data in queries 3 and 7 |
Price |
|
ID |
Access adds this automatically. It will be useful as the primary key or unique identifier for each car. |
Task
Design a data capture form (dcf) with the list of fields that you defined. You will use this to write down your data by hand, copying from your sources. Insert a table in Word with columns to match your fields and 30 rows. Copy the field names into the top row of the form. Adjust the column widths to match the likely width you will need for items like Make and Top Speed. Adjust the height of the rows so that there are 12-15 rows on each page. Print this form. You should be able to write (by hand) the details of each field in the spaces on the form. Make adjustments and print again if necessary.
Task
In your documentation comment on features of the data capture form such as the width of the columns. Explain why you chose the particular format of the data capture form, e.g. a table or a whole page per record or a questionnaire. Refer back to the outputs e.g. “In order to produce the outputs I will need to collect data. To make sure that I get reliable and accurate data for the outputs I will...”
You have decided on the fields that you need and you have developed a form on which to collect them. Now you need to identify suitable sources for the data you need. You must use at least two different sources, typically the internet (a web page) and print (a book, magazine, calendar).
Task
Write down the sources you have used: titles of books, magazines, addresses of web sites.
You will need to include evidence of these sources in your documentation. Print or screen shot the web page for every car that you collect data for. Paste the web pages into your documentation: start a new page, add a title “Data Sources” and a sub-heading for each car e.g. Audi 80 2.3E then add the screen shot. Crop the image if there bits you don’t need and resize the image so the data is legible. Make copies of the magazine sources.
Task
Enter your car data on the data capture form.
You may find it necessary to use more than one source to complete the data for each car. For example, you may get 5 fields from a newspaper and the final three from another source such as the internet or a magazine. You should print the form and hand-write data into it. You will transfer it to the electronic form later.
If you cannot find data for a field don’t worry. You have to reject some records later and missing information is a good reason for removing either a field or a record. You must document at least two cars that you left out.
The cars you select should meet one or more of the queries you defined earlier. You don’t want to make the queries at the end of the project and find that they all return empty results because none of the cars meet them.
Copies of a car magazine can be found in the library. We shall go to the library during a lesson and copy data from the magazine to the data capture form. Use one or two pages from the magazine as a source of data. Take copies of the pages and highlight the data you select. Copy the data about your cars onto the data capture form by hand.
Concentrate on particular types of car e.g. a particular make or a particular type. If you choose a type of car such as estate, 4x4, sports, etc. then you will need to copy a number of pages as the data will be spread over many pages.
Task
Write a paragraph about the method you used to collect the data. Link the data collected to the outputs required.
Task
Say why you rejected some records e.g. because you were collecting only Ford cars or only sports cars of different makes.
Task
Use the data capture form to define a data dictionary for the database.
This will be a table with columns for: field name, data type, field length, validation. These are the items you will need to create in Access.
Field Name |
Field Type |
Field Length |
Validation rule |
ID |
Autonumber |
N/A |
Primary key |
Make |
Text |
15 |
|
Model |
Text |
10 |
|
Price |
Currency |
|
Between 0 and 1000000 |
Field type includes text, number, Yes/No, date/time, currency
Task
Work out the maximum length required for text fields from the data on your dcf (round up to the nearest 5 or 10). Remember that the length should be kept to a minimum so the field will fit onto a screen or sheet of paper; fields will usually be displayed in combination with others so size matters.
Task
Specify the type of number required, again based on the dcf. Types include integer, real/fractions and currency. Specify the number of decimal places required. Zero dp for currency is most likely on big prices like cars.
Yes/No can be used for data which is either one thing or another.
Some fields can be put into drop-down lists e.g. Make, colour, year.
Some fields may appear to be numbers but are actually text: phone numbers, years.
You are unlikely to include complete dates (often used for a date of birth) or times.
Validation rules define the range of values that a field can take. They are easy to define for numeric variables e.g. price: between £1 and £100,000. Add your own message in the properties section: this is very important. You must have at least two validation rules in your database.
Task
Define validation rules for at least two fields and add a validation message such as ‘Invalid data. The engine size must be in the range 500 to 5000.’
For text fields you can use a drop-down list to validate items as the user can only choose items from the list. If you use a drop-down list for validation you must set the Limit to List property to True.
Task
Choose a primary key. You can use an existing field or, if there is no obvious candidate, add an autonumber and call it ‘ID’ or ‘carID’, etc.
Task
Create a table in your documentation with columns for field name, field type and length. Justify the choice of type and length for each field.
The reasons should link back to the outputs required by the user and the data you have collected on the dcf.
Simple reasons again: type for Make is text because the output includes words like ‘Ford’ and ‘Nissan’; length is 15 because the longest in my dcf is 12 and I have provided a little extra in case new makes are longer. Price is currency because…; range of price is … because…
Field Name |
Reasons for Field Type |
Reasons for Field Length |
Make |
This is text because ‘Ford’ is a word, made up of letters and the user needs to see the make of a car in the outputs |
15 characters is just long enough for all my car makes. I need to keep the length of outputs to a minimum so that they will fit into my output displays |
Model |
|
|
Price |
|
|
The quality of your notes in this section will be a deciding factor in your final mark. Marks are in a range 23-25 and 26-28; to get the highest mark in the range you need to produce good work.
To get into the top mark band you must write about why the software you chose was most appropriate for the outputs you wanted to produce. The main types of output are tables and charts. You will need to do some practical work with Excel and Access before you can write this up properly. You will need to describe how each application handles tables and charts and then justify your choice. The deciding factor is probably QBE in Access as this makes it very easy to produce the tables you require and the Pivot Chart tool is fairly easy to use.
Database Operations in Excel
Extension:
Task
Write a summary of how suitable Excel would be as the software for hsoting your database of cars.
Points to note. Write up these points and illustrate them with screen shots.
Task
Start Access and transfer the information in the data dictionary into the table definition sheet in Access.
Task
When you have finished take a screen shot of the definition table and paste it into your documentation after the data dictionary. Take separate screen shots of the Properties panel for each field – this provides the information on field length and validation rules. Save your table with a suitable name such as ‘cars’.
Task
Copy the data from your dcf to the table in Access. As you do this you should test the validation routines you added.
Validation rules will generally apply to numeric data such as price, speed, mpg. Make some errors such as ‘1600’ instead of ‘160’ and check that the validation works.
Task
Create a new section in your documentation called ‘Validation’ and show the validation routines in action. State what values you entered to activate the routines. Take screen shots to show that the routines work and that your custom messages are displayed.
You need at least two fields with validation rules and you should show the results of entering data on both sides of the valid range. This should make a minimum of four documented validation checks.
Task
When you have finished entering the data, print a copy of your data and check it for errors (make a couple of deliberate ones!) Highlight the errors and add a label (‘Unchecked Data’) to the sheet.
Task
Correct the errors in Access and print the table again showing the changes. Add a label ‘Checked Data’.
Task
Comment on why accuracy is important for the outputs.
Task
Illustrate these points about Access with suitable screen shots.
Task
Task
Write a conclusion about your choice of software.
Task
Use the QBE editor to set up the queries you defined at the start of the project. Specify fields, search criteria and sort order.
Task
Write an account of each query. Use the same numbers for the queries as in section 1. Add a heading based on the query. Define the query in terms of what it will find and the outputs it will produce. Take a screen shot of each query and paste it under the query definition. Print the output from the query and add it immediately after the definitions.
Use one query as the basis of a report where you format the output in particular ways e.g. font, font size, paper orientation. Use the report wizard to create the report and then use edit mode to adjust the output settings.
Use two queries to create chart output. You will have specified at least two of your questions in section 1 as chart outputs: this is where you create the charts.
This is similar to section 9 and builds on what you wrote there.
Task
Comment on how easy it was to create the tables and charts that you made. What features of the software made this easy? Use screen shots to support your comments (you can use some of the ones you made earlier).
Examples of features are the data definition sheet (screen shot), QBE (screen shots made in section 10 so use any of those) and wizards (for queries, charts and reports).