Database project

---

Data control

---

Input, processing and output needs

Inputs

This is the data that I will be processing:

tbl Customers

tbl Planes

tbl rentals

Processing

To satisfy user needs I will need to create:

1. 3 related tables; customers, planes, rentals.
2. 3 forms for entering customers, planes and rentals record
3. 3 queries, to search for:

1. Current rentals (planes on loan to customers)

The search criteria will be:

Returned = No

2. Overdue planes (planes taken out but not returned before the due back   date)

The search criteria will be:

        Datedueback<date()

        AND

        Returned = No

c) The day’s rental (this will find rentals on any day. This will request the user to input a parameter value – the date to be searched for)

The search criteria will be:

        Date Rented = [please enter a search criteria}

4.   4 reports, one based simply on tbl Customer, the other 3 based on 3 queries.

5.    A front-end menu (switch board) to display BOTH the forms and the reports at the click of a button.

---

Outputs

These will be my four outputs:

1. Current Customers list (based on tbl Customers data)
2. Withdrawn planes list (based on the query for current rentals)
3. Overdrawn planes list (based on the query for overdrawn books)
4. A day’s rental list (based on the query to find a particular day’s rentals)

---

Implementation and Testing

Switchboard

I created a switchboard to make access to inputting and outputting systems easier and more user friendly. I did this so that the user is prevented from using programs they do not need. A switchboard is easier to use, as the user just has to click his chosen option.

                                                                             

I tested my switchboard by checking all seven buttons. Here is one of the buttons I tested the ‘Open Product’s Form’.

As expected the product’s form came up.

Startup and Hiding Access

I set my system so that the Front End Menu will load automatically when the user opens the file.

I also completely hid Access and the object menu so that the user will only see the Front End Menu I have created for him. The advantages of this is that the user will not have direct access to the storage and processing programs, which if messed up will make the whole database useless.

Now when the user opens the file, all they will see is the Front End Menu:

To do this I went to Tools: Startup and set the options as follows:

 

---

Combo box testing:

---

Data Type testing

Validation testing:

---

Relationships

I have related my three tables to form a relational database

        

When I was setting up the relationship I chose to enforce referential integrity for both relationships.

 

I did this so that non-existing customers (that are not in tblCustomers ) can’t rent out any products or non-existing products (that are not in tblProducts) can be rented out to customers

---

Data Entry Forms

Data entry forms are made to meet the user’s needs when entering a specific record as this is much faster and more accurate.

---

These are improved data entry forms that more user friendly to use as they clearly explain what is done.

        

 

---

Multiple Field Sort

I have decided to sort the Customers table, first by Surname (ascending) and then by First name (ascending). Here is the sorted data:

Here is how I did it:

---

Filtering

Here I have decided to filter so only the Albas appear.

This is how I filtered the data:

---

Queries

Query 1 (Qry Current Rentals)

My first query was to find what planes were currently rented, and by whom. Here is how I asked the query:

Here are the 2 results I got:

I tested my query by adding a new record. I rented out Plane 2 (Boeing 787) to Customer 8 (Eider Gudjohnson). I then ran the query again and here are the results:

As expected the new record now appears in the query results

---

Query 2 (Qry Overdue rentals)

My second query was to find what planes are currently overdue, and by whom. Here is how we asked the query:

 

Here are the tree results I got:

---

Query 3 (Qry Anyday)

With my third query I could search all plane rentals on any one particular day. Here is how I did this.

When I ran the query this is what first appeared.

-

 

Evaluation

I believe that I have met the user needs to a great extent here is what the user needed:

Input

• He wants to be able to enter new customers to the database and edit them.
• He wants to be able to enter new planes to the database and be able to edit them
• He wants to be able to enter new rentals to the database and edit them.

Output

He would like, at any time, to be able to quickly and easily view and print out

• A list of all the customers and their contact details.
• A list of all planes that are currently rented out and who by.
• A list of all the planes that are overdue and who by.
• A list of all rentals for a particular day.        

I have met my user needs by firstly creating a switchboard from which the user can open the inputting and outputting sections at the click of a button. This is faster and more accurate and improves efficiency for my very busy user. As my user is not a database expert I don’t want him to change key areas of the database, which will make it unusable. These areas are the storage and processing systems and so these don’t get changed I have limited the switchboard to be able to only enter the inputting and outputting records.

My user’s first problem was to be able to input the customer, product and rental tables at any time. I met this by creating forms that access and change the customer, product and rental table without having to enter the actual table.  This form is labelled correctly to exclude any confusion to what it is, and then each field is labelled well and easy to read. All fields are resized to fit the records that that are inputted so as to be able to see them well.  Also the records are all edited to be more presentable, all fields are resized and all fields have validation so that for example in the date of birth field you cannot put letters. Also I have deleted fields like the customer ID field as the user does not change this so there is no need for it.

        After all the tables are set up the key to a database is there relationship, which can be created. The rentals table can be linked through referential integrity by bringing in the primary keys of the products table and customer table. By doing this all rentals are recorded and systems can be set up where you can’t double book a product and overdue products are recorded.

        

To get any output there needs to be a process to get this information out and that is the quires. I set up three quires, which are necessary for the user; they are the search of the rentals for any day, the current rentals and the overdue rentals.  This aspect of the database does not need to be accessed by the user who is not an expert and may accidentally delete essential parts of the system. To prevent this I set up a switchboard where the user can

        

        The output is found through the reports, which have had a lot of alterations to make it more presentable as this will be the final part of the database. I have resized all fields to be able to read clearly the information also all useless fields have been removed. In some cases there were fields which needed to be ticked, of course they had to be removed.

Output

He would like, at any time, to be able to quickly and easily view and print out

5. A list of all the customers and their contact details.
6. A list of all planes that are currently rented out and who by.
7. A list of all the planes that are overdue and who by.
8. A list of all rentals for a particular day.        

---

System Improvements

        I have made many system improvements and the foremost is adding more range check validation, which means that in the male or female field you have only male or female to choose from. Also I have made sure that in number fields you cannot write in currency. To improve systems further I have sincerely tested all aspects of the database twice just to be sure that there was no problem anywhere.

        For the database to be more useful business wise I have added a mailing label system to it, this way all customers can be made aware of the company’s promotional offers. Also I have added calculated fields to the forms so the user can see exactly how much he will make by renting that product out. This is done by calculating the daily price of every plane then multiplying it by the amount of days rented out for.

        Also for legal reasons the user cannot rent out the same product twice so I have set up against double renting. This is done by finding an unmatched query then going into the planes’ table, current rentals query, plane ID and then to query In. then you must go into the rentals form, design view then change data to query in. this way the rental system can only rent available stock.

Possible Alternate Method

        

        I could have used a spreadsheet instead of a database but this system would not be much better then the paper based one. This is because the spreadsheet cannot perform the essential task of building a relationship. Thus leaving just many tables of records instead of a fast, easy to use and professional working system of the database.

        

---

Saving and Backup

I saved my work on the user area in the school network.

I also baked up my work by saving it on the USB drive.