Database Design – Assignment 1
Downloads
Validation Rules and Input Masks [P4]
Field |
Validation Rules and Input Masks |
The driver's rates vary from £40 to £80 per session and their wages are paid by the firm each month. |
>=40 And <=80 |
The first time customers make a booking they are given a client ID which consists of the first three letters of their surname plus a four figure number. Customers quote their ID when they make further bookings. Regular customers can negotiate discounts. |
>LLL0000! |
Date of session |
>=Date() And <Date()+90 |
Number of passengers |
>=1 And <=20 |
Date booking made |
Date() |
Payment made |
>=0 |
Postcode |
>LL09\ 0LL! |
Customer and driver titles |
"Mr." Or "Mrs." Or "Ms." Or "Miss" Or "Dr." |
Driver NIN |
>LL000000?! |
Sessions |
"M" Or "A" Or "E" |
Importing Data Into Access [M2]
The following images show the process I took to import data from a text file into a Microsoft Access database.
Firstly, the importing dialogue box is opened, and the user prompted to choose the imported data's destination. This is most often a new database table.
The user must give Access a reference to the file they wish to import before proceeding.
Next the user is prompted to tell Access whether the data is delimited by fixed-width columns or a delimiting character (generally a comma).
One must then select how exactly the data is divided into columns. I chose
Comma from the list of delimiters, and ticked the
First Row Contains Field Names box. This takes the first row of the file out of the table, and uses the values as column headings instead.
Next, one is able to refine the naming and data types of the columns, potentially correcting any auto-detection mistakes made by Access. I chose to change the indexing rules for the primary key column.
The next step is to select a column or create a new column to hold primary key values. These are used to uniquely identify different rows of a database, meaning that no repetition is allowed. I used the
Stock ID column for this purpose.
Lastly, one must name the new table that will hold the imported data. I would normally add a
tbl_
prefix, but I did not as the table would be deleted once the contents had been analysed. Were I not going to tick the analysis wizard box at the bottom of the window, I would have added the prefix.
If one ticks the analysis box in the Import Text Wizard, Access will show the Table Analyser Wizard. Here I changed the name of the main table to a more suitable one, including a
tbl_
prefix.
I then selected the fields of the table that needed to be moved out. This included all of the information about item suppliers.
I dragged the fields into their own table, and named it appropriately.
I then set the correct field to be the primary key for the new table.
Once the data had been analysed and split into the correct tables, I closed the wizard. I did not choose to save the import steps I took as the importing process was a one-off.
The Table Analyser Wizard also gives the option to create a query which will show the data as it as originally imported. This would server no use in my situation, so I did not ask Access to create it.
As the data had been successfully split into the correct tables, the original imported table was no longer needed, and I deleted it.
Above is the final relationships diagram for the Denim Daze database.
For the S-Cars database I told Access to open the data analysis wizard again, allowing me to split the data into multiple tables.
I did not use the analysis wizard for the
tbl_drivers
table import. This is because there is little to no repetition in the data, and it is therefore efficient enough to begin with.
Database Design [P2] [P3]
I started working through each table changing settings for the different fields. I changed field names while removing redundant information, added input masks to the fields, added validations rules and error text, and set most fields as required fields. The input mask shown for the
BookingID
allows for four upper-case letters followed by four numeric digits. All characters are mandatory.
Although not shown in the screenshot, the ID column in the customers table would have an input mask of
>LLL0000!
, which would require three letters and four numerical digits to be entered.
The National Insurance number field in the
tbl_drivers
table also uses an input mask. This required two letters to be entered, followed by eight numerical digits. A further letter is permitted, although not mandatory, as the last digit in an NIN is a checksum and is sometimes omitted.
Auto-incrementation was also configured for some fields, such as the
ID
column in
tbl_sessions
. This means that every time a new record is added to the table, the primary key column is automatically generated.
As car registration numbers do not take a consistent form, an input mask cannot be used for this field. The field settings were still changed however, making the field a required one, for example.
The final
Relationships view of the database, showing the tables and contained fields.
Input Forms [P6] [M4]
When the Form Wizard is invoked, one must choose which fields from which table(s) and/or query(ies) to use in the form.
In the case of the S-Cars database, I decided to use all of the fields available in the
tbl_bookings
table.
I chose to use a basic columnar layout for the form, as it was of minimal complexity. More detailed and capable forms can utilise more suitable and user-friendly layouts.
The next and final stage of the wizard allows you to give the new form a name. I chose
frm_bookings
, following the previously-used naming convention.
Access SubForms
Following the initial creation of the bookings data entry form, I decided to add a sub-form. The first stage of Access' SubForm Wizard asks one to select a field or fields from tables or queries, which will become the fields shown in the sub-form.
I chose to use all of the fields in the
tbl_sessions
table.
When prompted by the wizard whether I wanted to create a new form or add the sub-form to an existing one, I chose the latter option, using the
tbl_bookings
table I created before.
The wizard also allows one to name the new sub-form. I used
frm_sessions
, following convention as before.
I also enabled conditional formatting for the
NumberOfPassengers
field in the sub-form. This makes it instantly clear which bookings have a greater or smaller number of passengers.
Five value brackets were used, grouping the bookings into either the red, orange, yellow, lime or green group. A gradient colour scale would have been more suited, but this feature is only available in Excel, and not Access.
Buttons, useful for a host of tasks, can be added to an Access form from the ribbon.
Buttons were added to the database form allowing the user to navigate backwards and forwards through the records. Buttons were also added or going to the first and last records.
Either text or an image can be used on buttons. For the navigation buttons, I chose to use icons as I feel the symbols are widely recognised enough.
The button wizard also requires for buttons to be named, and I named the buttons I created with a prefix of the form name. This would make them clearly distinguishable in a list of buttons in a bigger database.
Macro Code
The VBA code for the macros can be downloaded in the 18.1-macro.vba
file available above, and can also be viewed below.
Option Compare Database
' Click event handler for PaymentMade on tbl_bookings
' This function generates a random ID for a new booking when the user clicks
' in the "Payment Made" field.
Private Sub PaymentMade_Click()
' only create an id if it's needed
If IsNull([BookingID].Value) Then
' variable declarations
Dim Chars As String
Dim Alpha As String
Alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim Ref As Integer
Dim Digits As Integer
Dim DigitsStr As String
' add a random letter on, four times
For n = 1 To 4
Randomize Timer
Ref = Int(26 * Rnd) + 1
Chars = Chars & Mid(Alpha, Ref, 1)
Next
' generate a random number
Randomize Timer
Digits = Int(10000 * Rnd) ' 0-9999
' convert number to string
DigitsStr = Trim(Str(Digits))
' zero-pad id string
For j = 1 To 4 - Len(DigitsStr)
DigitsStr = "0" & DigitsStr
Next j
' concatenate the four letters and four digits
[BookingID] = Chars & DigitsStr
End If
End Sub
' AfterUpdate event handler for NameLast on tbl_customers
' This function will fire after the content of the NameLast field has been
' changed. The function takes the surname of the customer and a random number,
' and generates a (likely) unique customer ID.
Private Sub NameLast_AfterUpdate()
' variable definitions
Dim CustNameClip As String
Dim CustId As Integer
Dim CustIdStr As String
' cut the first four characters, and convert to upper-case
CustNameClip = UCase([NameLast].Value)
CustNameClip = Replace(CustNameClip, " ", "")
CustNameClip = Replace(CustNameClip, "'", "")
CustNameClip = Left(CustNameClip, 3)
' pad name string
For i = 1 To 3 - Len(CustNameClip)
CustNameClip = CustNameClip & "X"
Next i
' generate a random number
Randomize Timer
CustId = Int(10000 * Rnd) ' 0-9999
' convert number to string
CustIdStr = Trim(Str(CustId))
' zero-pad id string
For j = 1 To 4 - Len(CustIdStr)
CustIdStr = "0" & CustIdStr
Next j
' concatenate the two halves of the id, then set id field to output value
[ID] = Trim(CustNameClip) & Trim(CustIdStr)
End Sub