I will need data about the customer such as Name, Age, Gender, Address, DOB, and E-Mail. This data will come from the customer, giving the details on booking. It will be entered by the receptionist.
I will need data about the activity such as what it is, where it is located, who is in charge, Cost, start and end times. This will be inputted by the receptionist or the manager of the sport hall, who will decide the programs and give the information also. If it needs to be changed or updated this will be done by the receptionist, who will get all of the information from the manager.
I will need data about the booking such as: Customer Name, the activity details and what day they want to make the booking. The details about the booking will come from the customer data and activity data and the booking itself will be keyed in by the receptionist.
Inputs
I will need the following inputs:
- Customer Details
- Activity Details
- Booking Details
- Cancellation Details
All inputs will be by keyed in by the receptionist. I will be able to use forms, which has many advantages, like making corrections and analysing data. I can also enter data into one more table and error trapping.
Outputs
There are three outputs that I require, these are: a reports to show the customer details and all of the bookings. Also it will need to store customer bookings, details and one that I will need to devise.
These will all be in reports that I will need to create.
Report 1
Customer Details:
This report will show all of the customer details, and will be used to identify the customer correctly. It will be printed as a letter from Smarts Leisure Park.
Report 2
Booking Conformation:
This will show the customer bookings, the activity, what time the activity was booked for, date of activity, address of the customer and how much they have to pay; it will be as a receipt and will have all of their details. It will need to look professional. It will need to identify the customer and the relevant bookings made by them.
Report 3
Cancellation Details for Manager. – My Own
This report will show all of the cancellations that will take place. It will show the customer details, activity and date of cancellation.
Processes
I will need to search and extract data, these are my processes.
Process 1 (Report 1)
Process 1 is used to produce a report on the customer details. It will need to have information about the specific customer. I will know of this from their membership number. I will use a query, asking for their surname and with this extract all of their details.
Process 2
I will find the information that I need for the report about the customer bookings. To do this I will use a query. The first thing I will need to do is gather their name. I will then need to identify the booking and the date. I will then need to add up the cost that they owe, I will do this when I make the report.
Process 3 – produce a report on all of the cancellations that have happened over a period of time or generally for internal use.
This is the process which will show all of the cancellations in the past month or week to the manager. To do this is will use a query and print the information gained as a form for internal use.
Data Flow Diagrams
DFD 1
In DFD 1, the Customer gives their data to the receptionist, who keyes them in to the database. The data is stored in the customer data table.
DFD 2
In DFD 2, the Manager gives their details to the receptionist, who keyes them in to the database. The data is stored in the activities data table.
DFD 3
In DFD 3 the customer requests to make a booking. Data is looked up in the Customer Data table and the Activity Table. The booking details are stored in the Bookings Table.
DFD 4
In DFD 4 if the customer wishes to see their data that is stored on the database, then the receptionist has to make a report of all of the bookings, activities and personal data that is on the database.
DFD 5
In DFD 5 the customer cancels the booking and then all of the data is changed in the three tables. The receptionist gives the receipt of money back or the changed booking receipt.
DFD 6
In DFD 6 the receptionist gathers information for the Cancellations query to see which bookings are live. The receptionist then produces a report and gives it to the manager for internal use.
Examining my DFD’s I know I will need four tables:
- Customer Details
- Activity Details
- Booking Details
- Instructor Information
Entity Relationship Diagram (ERD)
Introduction
For my database to work, my tables must be linked. The ERD below shows this relationship.
Security
I am required to make the database secure because all of the users want me to. I will use a password system to do this and I will also advise them to back up all the files on a regular basis in case the system fails.
Menu System
For the menu system I will use a form with links using Macromedia. This will link the forms with other pages or items.
User Guide
I must produce a user guide which is very simple to use. It will explain how to use my database.
Design
Data Requirement
From my analysis I need data about three things, these are:
- Customer Details
- Activity Details
- Booking Details
- Instructor Details
I must now produce
- An entity definition
- Data dictionary
Entity Definition – Activity Data
R = Required
ACTIVITY (ActivityCode, ActivityName, Venue, Instructor, Day, Time, Cost)
Data Dictionary
Entity Definition – Customer Data
CUSTOMER (MembNo, FName, SName, Gender, Age, DoB, City, StreetName, HouseNo, PostCode, ContactNo, NextKin, MedicalCond, TeleNo, Email, DoctorNo)
BOOKINGS (BN, MembNo, ActivityCode, Date, Live, PaidY/N)
Entity Definition
INSTRUCTOR(InstructorID, Instructor FName, Instructor SName, Activity (s), Venue)
Input Forms
I must now start to design my forms. I must decide how many forms that I need.
I am going to have four forms.
- Customer details
- Activity details
- Booking details (not included with customers)
- Cancellation details.
Processes
Process One
To extract a customer details query. I will extract all customer details from the Customer Details Table.
To locate the correct customer I will enter [Please Enter Surname] in the criteria box of SName Field.
Process Two
To extract all of the booking details query. I will extract all of the booking details from the customer and activity data tables.
To locate the right booking and the correct customer I will enter [Please Enter Surname] in the criteria box of SName Field. To find the right booking or the bookings that the customer has made I will enter [Please Enter Date] in the criteria box of Date
Field.
Process Three
To extract all of the cancellation details from a query. I will extract all of this information from the, Booking Details query. I will then provide a report showing this for the manager.
To locate the right booking and the correct customer I will enter [Please Enter Surname] in the criteria box of SName Field. To find the right booking or the bookings that the customer has made I will enter [Please Enter Date] in the criteria box of Date
Field.
Outputs
My next output is to create a report about the customer details. I will design it and send it to the customer for conformation.
Reports
I need three reports which are shown in the analysis.
User Guide
I will produce a very simple user guide in MS Word.
Security
To make my database secure for other unauthorised users, I will allocate a password
Menu
I want to make a simple menu that will have buttons for the user to press making the menu simple to use.
Creating the Database
Introduction
Having completed my design of the database system for SLP I am now going to create the database using MS Access. I will explain what I am doing and I will also provide screenshots for this.
I clicked on the MS Access Link:
I am going to save my database in My Documents.
Creating Tables
To create my tables I selected “Create table in Design view”. I then entered the field names, data types, field lengths and my validation. In some of my tables I have used lookup fields to get information from other tables. Below are screenshots showing my tables.
Table 1 – Customer Details
The first table that I am going to create is the Customer Details Table. I will create it in accordance with the entity definitions and the data dictionary.
This screenshot shows me inputting the data into the table. I will then do the same for all of the other fields in accordance with the entity definition and the data dictionary.
I have now entered all of the details. The screenshot below shows that I have followed all of the criteria showed by the Data Dictionary.
I am now going to save my table. This is the Customer Details Table. I have saved it as “tblCustomer”
Table 2 – Activity Details
Now I have created “tblCustomer” I must now create the table showing all of the Activity details. I will create this table in a similar way as “tblCustomer”
I will also save it as “tblActivity”
When I entered the cost for the Activity it came up with dollars “$”. I realised that I needed to change it to a pound sign “£”. To do this I had to enter a formula. In the rule I entered the following:
Now when I looked at the table normally, the cost was in pounds:
I am now going to link the Instructor table that I created with the Activity Table. I have shown the fields for the Instructor table in the Design in a data dictionary.
In the field for Instructor on the Activity table, I used a lookup wizard to link this to show different instructors.
I then selected the fields, and now when I enter an instructor I pick from a list
Table 3 – Booking Details
Now that I have created “tblCustomer” and “tblActivity” I must now create the booking table. I will call it “tblBookings”
Here is proof of the saved name. I have created the table as I did with “tblCustomer” and “tblActivity”.
Now that I have created all of my tables, the link allows me to access all of the bookings via the activity table:
Linking the Tables
Now that I have created all of the tables for my database, I must now link them. In the specification I produced this link diagram. It was called an ERD, it is shown below.
I must now link the tables in accordance to this.
The first thing that I did was to click on the relationships key.
I then linked all of the tables as it is shown in the ERD.
I have now linked all of the tables in accordance to the ERD. I now know that they will be linked for the whole database in the way that was shown.
Forms
As I said in the specification I must now design my forms. They must be accurate to the design that I stated with my drawings. They must include all of the things that I put on them.
The first one I must create is “frmCustomer”
Firstly, I clicked on “New object: Autoform”
When I clicked on New Object: Autoform, this allowed me to put all of the details that I had in my tables into a form layout. I must complete the forms to how I have specified.
After I clicked on this link, it cam up with the following screen:
This is the basic layout of the form and now I will need to customise it to make it something like my design.
Firstly I will need to get rid of all of the navigation bars, scroll bars and dividing lines because I will make my own on the form.
To do this I had to click on the square in the corner and click properties:
In then turned all dividing lines, scroll bars, and navigation bars off:
After I had turned all of these off the form view then looked like this:
After I had done this I must now design the layout of the form in general, in keeping with the designs that I have drawn.
I said that I wanted a light blue background to make the form readable for the user. I did this on the form and now it looks like this:
I now need to centre all of the information on the form. I did it by selecting it all and moving it to the desired position. This also makes the form more readable for the user.
I must now add a heading. I will also add the logo of SLP.
Now I have done all of the layout and colouring of the form, I must now add some buttons. These buttons must be navigation buttons, close form and search buttons.
I do this by selecting the toolbox key to open the toolbox.
I then clicked on the command button key and drew it out, it then came up with this:
After I selected the form options and “close form” it came up with this
I want to use text so I selected the text option and wrote in “Close Form”
I then repositioned it and increased the font size:
Now that I have made my close form button I must make my own navigation buttons. These are going to be used to go between records and are to be positioned at the bottom of the page.
To do this I again clicked on the toolbox key and when the menu came up I clicked on the record operations option.:
The button then was on the screen:
I then had to create all of the other navigation buttons and then align them to the bottom of the screen as shown on my drawings and design.
When I had done all of the buttons in the same process shown above and aligned them, the form then looked like this.
I must now create my Print, Search and Preview buttons. I will go on the same menu as before to create the navigating buttons but select “form operations” this is evidence of them on the screen after I have inserted them and aligned them.
I then saved my form as “frmCustomer”:
I must now create my two other forms: “frmActivity” and “frmBookings”. I will create the two forms in a similar way to how I did “frmCustomer”. This is evidence that I have created them:
Form Bookings
I must now create my bookings form to allow the customer to see what bookings they have made. This is also because the user needs to see all of the bookings in on place.
I created the form in a very similar way to the Customer Form, however I needed a subform with combo boxes ore lookup fields which provide a list a data.
I needed A MembNo combo box lookup field and also an ActivityName lookup field. I did this by adding a combo box.
To get the combo box I needed to press the combo box icon on the tools menu:
It then came up with this:
I know that I want to retreave all of the data from the customer details for the MembNo combo box. I clicked this and then entered the fields that I want to see in the list .
I then finished it and now it looks this up for MembNo
I then did the Activity Lookup field in a similar way and now it looks up a list for it.
I now have the two combo boxes that I wanted and this makes it easier for the user to pick the specific item off the list.
Security
I must now make everything on my database secure. As I said, I need to add a password that nobody else will know to prevent people hacking into the data.
This is how I made it secure.
Firstly I opened an exclusive copy of my database via MS Access
It then came up with the tables screen:
I the clicked on “tools” then “security” and then “Set database password”.
The password for the database is “dsr”. This will make it secure to allow nobody to get into the database. With this password, only the user will be permitted to enter the database because they will know the password.
Now when I open my database, it asks me for the password.
Creating the Queries
Now that I have created my tables, forms and made by database secure, I must create my queries. These, as I have said, will be used to extract correct details for customers to see any information that the user requires.
Query One – “qryCustomer”
This query is the customer query. I will extract all of the information for this from the table “tblCustomer”. This is how id did it:
I first clicked on “Create a Query using Wizard”:
I know that I want to extract all of the information for tblCustomer. It then came up with this screen:
I need to move over every one of the fields:
I have called it “qeyCustomer”:
I then wanted to modify the design of the query, and so it came up with this screen.
I know that I want to enter a search to find the correct customer and all of their correct details. To do this I had to add a search criteria in the box:
Now when I run the query it asks me for a surname:
When I search the surname it then comes up with all of the people on the database with that surname:
Query Two – “qryBookings”
Now I have created my first query I must now create my second. This query will show all of the bookings made by different people, what they booked, when they booked it and more. I will extract all of the information from “tblBookings”
I will produce this query in a similar way to the first one. I will extract all of my data though from the Bookings table:
First I clicked on the wizard for creating a Query.
I then transferred all of the data from the Bookings Table.
I also need to add information for the Customer table also for personal details:
This is what the query looks like in design view:
I now need to add a search criteria to search for the right customer and their bookings, so I added “please enter surname” in the search criteria for SName:
Now when I run the query it asks for the Surname and thus the user has to enter a surname:
It then comes up with all of the bookings that have been made under the name of Richardson
Query Three – Cancellations
My own process is to produce a report on all cancelled bookings from a Cancellation query. I will need to extract all of the information from the Bookings and Customer Tables, like I did query two.
I used the same process to extract the same information as the 2nd query but in design my search criteria changed. I had to add a search criteria in the “Live” field to show which bookings were not live:
I also entered a search criteria in SName also to see which person had cancelled the booking.
.
When I ran the query it then asked me for a Surname and whether any of the bookings were live or not:
When I ran the query and entered the name and status it then found the results:
I have now successfully created all of my queries and now I need to create my reports using data from these queries.
Creating My Reports
Now that I have created my queries I must create my reports. In my analysis I said that I needed two reports plus my own output. These were:
- Customer Report
- Bookings Report
- Cancellation Report – My Own
Report One – Customer Report
The first report that I am going to create is the Customer report. This report will allow me to pick out a customer and send a letter to confirm the details that we hold about them.
This is how I did the report.
I first clicked on “Create a report using wizard”
It then came up with this screen:
I know that I want to extract all of the customer details from “qryCustomer” and I need to move all of the files across:
I need to edit the report to be in a business letter format. This si what the basic report looks like without my editing:
I now need to edit my report. I must first get more detail about the customer and enter this into an address below the letter head.
I clicked on an icon which brought up the details from “qryCustomer” and all of the fields were in front of me:
The address consists of the customer’s title, surname, house number, street, city and post code. I will need to drop all of the fields corresponding to this onto my report.
I now need to get the logo of smarts leisure park from the website and include it in my letter head, which is the first thing on a business letter.
I also need to add the address of the Leisure Park and add contact details to the letter head. I will do this using a label. I also need to add a reference to the report for internal use.
Here is the finished SLP letterhead.
Now that I have the address as well I can now see the top of the letter for customer details.
I need to use a text box to add text to the report. The text will exist for every ingle report that the manager produces.
I did this by doing the following: To add the date I had to enter a formula to show it:
In a text box I entered:
I need to edit the date to show a simple and understandable date. I will do this by clicking on properties
And I then clicked on the data type and changed it to “Medium Date”
Now that I have changed the date to medium date it is shown in the report like this:
I must now add the “Dear” and paragraphing of the report. I did the “dear” by using a text box and a formula.
I entered this to get the report to gather the information when I searched the surname of a customer:
This allowed the report to get the correct customer when it asked me to search for one based on the criteria in my query:
First when I looked at the report it asked me for a surname which shows my correct criteria:
When the report opened and showed the address of the customer it also showed the Dear Mr Richardson, which shows that my formula was correct:
I then added the paragraphing to explain the report purpose by using a label.
I am now going to add all of the customer details to show the customer for them to check in the letter.
I did this in the same way as I did the address but extracted all of the details, due to the fact that they need checking by the customer.
I then put them in place and aligned them in the report. Now I need to test them to see if the details of the customer I search are presented in the boxes:
All of these formulas now work and I need to add a final paragraph which will explain all of what the report is about.
To do this I used a label.
I have now finished my customer report and I have saved it as “rptCustomer”
Now that I have created my first report I can now create the Bookings report. I will call this “rptCustomer”
I will have to do the labels in a similar way to the Customer report and extract he address data and dear paragraph the same and the date the same. I must change, however the way that I show the bookings in the report.
I did this report by:
Clicking on create a report using wizard:
I now know that I need to extract all of the details from the Booking query, for my report to work.
I clicked on this and selected the Booking Query:
It immediately asks me for a surname, which must show that the link between the query and report works:
When I entered a correct surname, the basic report showed.
I now need to edit the layout, to look similar to the customer report.
I entered the date as shown before and edited the letterhead and entered the address as before.
This is how the report looks after this.
Here is evidence of the date that I have put in:
I have now completed my Bookings report, and I now must complete my Cancellations Report.
I must now create my cancellations report. It is my own output, so I decided what to put in it.
I will extract data from the bookings query and report and see which bookings were made live or not. This will determine which bookings have been cancelled and by who.
Firstly I clicked on the create a report in design view. I then selected the cancellation query and moved all of the things into the other column.
I then, viewed the basic report. It asked me for the validation Y or N for active and the surname
It then came up with all of the bookings in the name of Richardson and cancelled bookings:
I now need to edit the layout. Because this report is for internal use only, I do not need to have the letter- head and requirements that I had for the others.
I will set it out with just the date and sign it and put a heading of Monthly Cancellation Report.
I did the medium date as I did before in the other ones.
I then added the heading and signed it. This is the finished report.
I have now completed all of my reports and I must now create my menu.
Creating the Menu
I must now create a menu that will show all of the reports and forms that I have just created.
I will create it as a form and I must add link buttons to all of the forms.
I did this by
Clicking on the Command button icon:
I must then select the button type and create the link.
The button is now inserted and links to one of my forms/reports.
I did this for all of the forms and reports and now I have a list of them on the menu:
That is now my finished menu.
Startup
When the user clicks the icon for the database, I want it to show the Menu maximised after I enter the password.
I will do this by entering a macros. I clicked on the new macros icon:
It then came up with this menu:
I clicked on Maximise and savced it at “maximise”
I clicked on tools>startup and then set the Switchboard to open after the password has been entered:
The macros will ensure that it will maximise, once it is shown.
User Guide
I must now create my user guide. I am going to create it using Macromedia Dreamweaver.
This is what my user guide will consist of:
Page 1 – Main Menu
Page 2 – Customer Form
Page 3 – Activity Form
Page 4 – Bookings Form
Page 5 – Customer Report
Page 6 – Bookings Report
Page 7 – Cancellation Report
It will also have to following links on the side of the page.
Link 1 – Main Menu
Link 2 – Customer Form Page
Link 3 – Activity Form Page
Link 4 – Activity Form Page
Link 5 – Customer Report Page
Link 6 – Bookings Report Page
Link 7 – Cancellations Report Page
Link 8 – SLP Homepage Website
Page One
The main Menu:
Page Two – Customer Form
Page Three – Activity Form
Page Four – Booking Form
Page Five – Customer Report
Page Six – Bookings Report
Page Seven – Cancelling Report
As you can see, these are all of the pages in my user guide, as I have said. I also have all of the links that I described also.
Cand. Name: David Richardson Page of