We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy

VHD Tools Case Essay

Essay Topic:

Sorry, but copying text is forbidden on this website!

1. Purpose of VHD Tools

The VHD tools database was constructed to aid the staff in their daily duties. Before the database, all operations such as invoices and receipts were carried out manually. With the introduction of this database, receipts and invoices are printed out automatically with minimum user intervention. In addition to receipts and invoices, the system is able to carry out many tasks. These tasks include tracking customers. So that if a user wishes to find out what products a certain customer has bought or rent in the past, they simply need to enter the unique customer code and all the details would be provided.

Stock management is improved with the use of this system. By simply using the user friendly menu, all levels of stock can be shown. This way the user would know whether a certain product is in stock before attempting to sell it. It would also aid the user in deciding what new stock to purchase by reviewing all the items that are low in stock.

The system also allows for employee sales to be tracked. Whenever a customer buys or rents an item, the employee that has dealt with that transaction is recorded. This is beneficial so that if the company introduce targets, the system would provide adequate information to support them.

If the company wishes to view all the transactions for the past month, by simply executing a simple query, the transactions can be shown. This will aid the company into showing what products sell better than others.

Above is a copy of the employee details data table. From this table, transactions and their stock details can be accessed.

Count: 277 words

2. Assumptions

* Each item available for sale or rent will have one unique Stock ID. However, each item for sale and each item for rent will have unique Item IDs. This is needed to prevent confusion in the transaction table. By specifying the Item ID, we can see whether the item was sold or simply rented out.

* Each transaction has more than one product. It is assumed that when a customer comes to the business, they are able to buy more than one item at any one time.

* Each employee has a unique ID code. This code is used when recording the transactions as well as delivery schedules. If an employee updates their personal details, i.e. Miss Smith becomes Mrs Smith; only one change will need to be done, due to the unique ID code being referenced. By recording what each employee does in the business, the company can see how many sales each individual has achieved.

* The only payment methods accepted are credit cards, cheque and cash. The type of payment will be recorded in the database. However secure details such as credit card numbers will not be stored in the database, since there is no real use for them once they have been successfully charged.

* Every customer that wishes to rent an item is assumed to rent the item for the maximum rental time. This is assumed to maintain simplicity.

Count: 235 words

3. Revised list of tables

Key:

* = Primary Keys

+ = Foreign Keys

CUSTOMER

Field Name

Data Type

Field Size

Customer ID*

AutoNumber

Title

Text

30

First Name

Text

25

Surname

Text

25

Address 1

Text

40

Address 2

Text

40

Town

Text

25

County

Text

20

Post Code

Text

8

Telephone Number

Text

20

TypeOfCustomer

Text

6

STOCK

Field Name

Data Type

Field Size

StockID*

AutoNumber

Description

Text

40

BuyingPrice

Currency

RENTSTOCK

Field Name

Data Type

Field Size

ItemID*

Text

5

StockID+

Number

Bay Number

Text

2

Section Number

Text

2

Quantity

Number

4

CostPerDay

Currency

MaxRentTime

Text

2

SALESTOCK

Field Name

Data Type

Field Size

ItemID*

Text

10

StockID +

Number

Bay Number

Text

2

Section Number

Text

2

Quantity

Text

4

SellingPrice

Currency

TRANSACTION

Field Name

Data Type

Field Size

TransactionID*

AutoNumber

CustomerID+

Number

DeliveryID+

Number

EmployeeID+

Number

TypeOfTransaction

Text

15

TransactionDate

Date/Time

PaymentMethod

Text

15

TRANSACTION/STOCK

Field Name

Data Type

Field Size

TransactionID*

Number

StockID*

Number

QuantitySold

Text

3

DELIVERY

Field Name

Data Type

Field Size

DeliveryID*

AutoNumber

DeliveryCharge

Currency

DeliveryDate

Date/Time

EmployeeID +

Number

Mileage

Text

8

EMPLOYEE

Field Name

Data Type

Field Size

EmployeeID*

AutoNumber

Title

Text

5

First Name

Text

25

Surname

Text

25

Department

Text

25

Address 1

Text

40

Address 2

Text

40

Town

Text

30

County

Text

30

Post Code

Text

8

Telephone Number

Text

15

DELIVERY / VEHICLES

Field Name

Data Type

Field Size

VehicleID*

Number

DeliveryID*

Number

VEHICLE

Field Name

Data Type

Field Size

VehicleID*

AutoNumber

Vehicle Reg. Number

Text

7

VehicleType

Text

7

LastServicedDate

Date/Time

TotalMileage

Number

N/A

DateofPurchase

Number

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.

How to cite this page

Choose cite format:

VHD Tools Case. (2017, Jul 30). Retrieved from https://studymoose.com/vhd-tools-case-essay

We will write a custom sample essay onVHD Tools Casespecifically for you

for only $16.38 $13.90/page
Order now

Our customer support team is available Monday-Friday 9am-5pm EST. If you contact us after hours, we'll get back to you in 24 hours or less.

By clicking "Send Message", you agree to our terms of service and privacy policy. We'll occasionally send you account related and promo emails.
No results found for “ image
Try Our service
online

Hi, I am Sara from Studymoose

Hi there, would you like to get such a paper? How about receiving a customized one? Click to learn more https://goo.gl/CYf83b

online

Hi, I am Sara from Studymoose

Hi there, would you like to get such a paper? How about receiving a customized one? Click to learn more https://goo.gl/CYf83b

image

Your Answer is very helpful for Us
Thank you a lot!