Count: 235 words
3. Revised list of tables
Key:
* = Primary Keys
+ = Foreign Keys
CUSTOMER
STOCK
RENTSTOCK
SALESTOCK
TRANSACTION
TRANSACTION/STOCK
DELIVERY
EMPLOYEE
DELIVERY / VEHICLES
VEHICLE
3.Revised List of tables showing attributes of each table with primary keys in bold
5. Changes made to Part 1 design
Initially all primary keys were named ‘xx Number’, they have now been renamed to ‘xx’ ID. This is easier to understand.
In the customer table, transaction number was removed. A new table called transaction was introduced. Here there was a foreign key Customer Id and the primary key of Transaction ID. The reason that this table was introduced and the transaction number removed was to ensure that normalisation was present in the database. The database needs to be in 3NF, however, to ensure that this occurs; changes had to be made to existing tables. Both ‘Transaction: Sales’ and ‘Transaction: Rentals’ were removed and replaced with one transaction table. Stock was split into sale and rent to distinguish between stocks. In the rental stock table, a new field of price per day needed to be introduced.
Company name was removed from the customer table because not all customers are going to be trade. There will be both public and trade sales, thus resulting in an inappropriate field name.
The stock table only includes three fields; the remaining missing fields were moved to the relevant sale stock or rent stock tables. This was also done to ensure that 3NF normalisation was ensured.
In the delivery table, the customer ID was removed, this was because the delivery being made did not relate to a customer. A customer could have many deliveries, so this would be incorrect. To solve this problem, the delivery table was linked to the transaction table. Therefore each transaction will only have one delivery. And each customer can have many transactions, this is more feasible. Similarly vehicle registration number was moved to the vehicle table. Initially this was the primary key, but after re arranging the tables, it was more sensible to have a dedicated vehicle ID primary key.
More information about the employee is now stored in the database, personal contact details have been added.
Availability has been removed from the vehicles table. This was initially a value that determined whether the vehicle was available, but it soon became apparent, that availability is not an attribute, and it is a query.
Count: 352 words
6. Difficulties encountered
As explained earlier in changes made to part 1, most of the difficulties I encountered were when it came to creating the tables in Access. I noticed that the existing tables were not fully in 3NF form and would therefore lead to an inconsistency in the database. There would be a repetition of data and this is not good for an efficient database since data could be entered twice wrongly.
The other main difficulty I had in this project was creating the queries. Unfortunately once I had changed the tables to how I saw fit, they still did not seem right. This only became apparent when I was creating queries. As a result, tables had to be created, fields had to be moved. Because of this, the creation of queries took a long time. This had a knock-on effect on the reports that were created, because of the confusion of the tables.
Count: 152 words
7. Queries and Reports
This system has more queries than reports. There are a total of eight queries and four reports.
The check rent stock query displays all the stock that is available at the present time. All that is required is the StockID. This query is similar to checking the sales stock. Once a StockID is entered, an up to date amount of current stock held is shown.
When a CustomerID is entered in the customer details query, all the details of the customer are shown. A report has been created that uses this query. Customer details such as address, full name and telephone number are shown.
Delivery details query shows all the details associated with the delivery. Details such as the delivery charge, delivery date, the employee who carried out the delivery and the mileage are all shown.
By entering the StockID the totals of all rent stock and stock to sell are shown.
If a date is entered, all the products rented on that particular date will be shown by a specific customer.
To find out all the deliveries that are to be taken place on a certain day, the user would need to enter a date, then as a result, all the deliveries for that day would be shown. This query is also created as a report.
By entering a transaction ID number, the details of the customer, the items they bought and the cost of those items are shown. This can effectively be called an Invoice.
Finally there is a report whereby all the employees are shown. Here we can see what transactions they have carried out along with their individual details.
Below is an example of one of the queries:
Count: 281 words
9. Future enhancements to the system
Since there is no check made for vehicles that are serviced, it would be wise to create one.
When a vehicle is approaching travelling for 10,000 miles, an alert should be created. This alert would inform the user to get the vehicle serviced. Once it is serviced, the user would be able to confirm to the system that it is serviced so the alerts would no longer appear until the next 10,000 mile. This would be beneficial because it would keep a constant reminder that a vehicle needs to be serviced. If the vehicle has not been serviced and it reaches it 10,000 mile mark, the system should refuse to allow that vehicle to do deliveries.
Regular customers and bulk buying customers usually are eligible for discounts. The current system does not currently support this feature. However to effectively use this feature, a fixed discount must only be allowed, and a user with a secure password should be able to implement this, else staff members may begin giving discounts to all customers
A check could be made to see how far the delivery address is. By using simply the postcode, the system could roughly calculate the distance and if over 50 miles, reject the order.
A useful addition would be the implementation of an internal credit card authorisation scheme. Here the user simply enters the customers’ credit card; the system would then authorise and charge the card accordingly with a print out receipt for the customers’ records. This would improve the ease of use as well as tighten security.
To improve the system further, the introduction of a query which records number of days the customer requires could be produced. This would eliminate the assumption that each customer rents for the max total rent time, which is not completely realistic.
From an accountant’s point of view, there are no queries that relate to the profit of the business. If the selling price and buying price are known, it would be wise to attempt to create a gross profit report.
Count 339 words
10. What I learned during the course of this project
- Database design and construction
Even though I have previously made databases, I found this project difficult and realised that each database is unique and customised in its own individual way. No two databases are the same, and even though they may share similar functions, due to customisation they can be very different. This creates problems in itself, and the planning stages to database design are crucial, and it is usually necessary to spend more time on the planning rather than the actual construction. However, many problems arose during construction which I did not forecast during the initial planning stages. These problems, as discussed earlier, affected the functionality of the database and thus the design of the database too had to be reviewed and changed.
I also realised the absolute importance of a rigid and tightly formed E-R diagram. There were many problems in the E-R diagram which was produced in part of part one of the project, due to lack of normalisation in some tables and the over-use of unnecessary junction tables. This meant that the reviewed E-R diagram took a lot of time to produce, and a clearer, fresher mindset was needed when approaching this.
What I also had problems with was data normalisation. Even though there are stages of normalisation, I still find it difficult to come to 3NF. However, with practice I found that I was picking this up a lot quicker, and that as I became more educated at doing it, it became easier to imagine in my head how the tables would fit together to maximise efficiency and minimise duplication of data.
- Working as part of a team
Working as part of a team in part 1 was very enjoyable and extremely insightful. The group itself functioned very well, there were people who had different levels of experience using access within the group, from those who had done Access for A-level to those with no experience at all. The group accommodated this, and in our first two meetings there was an emphasis to educate those who did not understand various parts of Access. At the same time those with less proficiency with Access provided some really useful insights as they were able to stand back and take a look at the system as a whole.
Within the group there were 3 people, including my self, who had already used Access before. This in itself created debate, and also I think that this created the problems of the first E-R diagram. However, having this level of experience within the group was educational, it challenged my way of doing things and at the same time I was able to learn new things about database construction too.
I think I played a key part within the group itself. Although it created problems at times, it was good to have 3 people who had used Access before, and because communication was open within the group it was easy to exchange ideas and thoughts. I was able to generate many ideas and insights, especially because I have seen many company databases, so I knew what type of tables and entities were needed for the project.
Count: 515 words
