Access 2000


Task –1


Purposes served by the VHD Tools Database





VHD TOOLS LIMITED is dealing in equipment used in the building trade. Most of the trade information is stored on papers such as customer details, stock details, invoices etc. Considering time utilization and convenience, current system has been converted into computerized system.


VHD TOOLS database is capable of handling customer details, orders, stock, staff and daily transactions (sales or rental). With the help of current database, we can see which transaction was made on a particular date, to which customer, whether the transaction was made on sale or on rental basis, what were the delivery charges etc. We can also check the stock availability. From the vehicle table we can judge vehicle’s performance. It is used for keeping records of the vehicles e.g. on which date service is due for different vehicles.


Transaction Table


Transaction


No


Transaction


Type


Transaction


Date


Return


Date


Customer ID


Staff ID


Delivery


Applicable


Rental


Charges


1


Sale


12-Apr-2003


HELSH


2


Yes


2


Sale


12-Apr-2003


DIRJO


2


Yes


3


Hire


14-Apr-2003


16-Apr-2003


FERLY


1


Yes


200


4


Hire


10-May-2003


15-May-2003


KIJLA


2


Yes


500


5


Sale


14-May-2003


HELSH


4


No


The above table lists some of the transactions, which are on delivery basis; we can see that the first sales transaction was made on 12th April, 2004. Sale of the product was made to Helima Sherly whose customer ID is HELSH in our database. This sales transaction was handled by Michal Clark whose Staff ID is 2 and the product was delivered by the company to the customer residence.


[260 words]
Task-2


ASSUMPTIONS REGARDING BUSINESS OPERATIONS OR USE OF THE DATABASE


Customer ID has been assumed to be of 5 characters. First three characters will be chosen from first name and the next two from the last name. Each customer ID will be unique so it is considered as a primary key. A single staff member is assumed to be responsible for dealing in a particular transaction.


Regarding the vehicle servicing it is assumed that particular operator will fill the servicing date of all the vehicles in advance in service table and vehicle service is done on that particular date.


In delivery table, delivery number is assumed to have the same number as that of transaction number as it is helpful in locating a particular delivery transaction. Further ‘Lowrence’ and ‘Ontario’ are assumed to be the neighboring counties of ‘Worcester’. It is also assumed that the company (VHD TOOLS LIMITED) is dealing in products that are meant both for sales and rental purposes. Rental charges for any product are assumed to be £100 per day. Only five staff members out of twenty have been taken in database assuming they all are handling the company transactions. All the data related to customers like postcode, address, and contact number is taken fictitious.


Assumption is made that delivery address of customer can be different from customer address in customer table as our customer may have purchased or rented the product for some other person in his own name.


[244 words]
Task - 3


List of tables

Customer
Field Name

Data TypeField Size
Customer ID


Text


5


Customer Name


Text


25


Customer Address


Text


30


Post Code


Text


7


Customer County


Text


10


Contact No


Text


15


Customer Type


Text


7



Staff
Field Name

Data TypeField Size
Staff ID


AutoNumber


4


First Name


Text


15


Last Name


Text


15


Date Of Birth


Date/Time


8


Hire Date


Date/Time


8


Job Type


Text


30


Address


Text


35


City


Text


20


Country


Text


20


Snap


OLE Object






































Product


Field Name

Data TypeField Size
Product Name


Text


25


Product Price


Currency


8


Warehouse Address


10


Unit In Stock


Number


4

Transactions
Field Name

Data TypeField Size
Transaction No


AutoNumber


4


Transaction Type


Text


5


Transaction Date


Date/Time


8


Return Date


Date/Time


8


Customer ID


Text


5


Staff ID


Number


4


Delivery Applicable


Yes/No


1 bit


Rental Charges


Number


4

Order
Field Name

Data TypeField Size
Order No


AutoNumber


4


Product Name


Text


5


Quantity


Date/Time


8


Vehicle
Field Name

Data TypeField Size
Vehicle No


Text


10


Vehicle Type


Text


15


Mileage


Number


1


Service
Field Name

Data TypeField Size
Service Date


Date/Time


8


Servicing Done


Yes/No


1


Vehicle No


Text


10

Delivery
Field Name

Data TypeField Size
Delivery No


Number


4


Vehicle No


Text


10


Delivery Address


Text


30


Delivery County


Text


30


Delivery Distance


Number


1


Delivery Charges


Currency


8



E-R Diagram
Transaction No.
Transaction Type


Transaction Date


Return Date


Customer ID


Staff ID


Delivery Applicable


Rental Charges

Transactions
Product Name
Product price


Warehouse Address


Units in Stock

Product



Order No


Product Name


Quantity

Order
Staff ID
First Name


Last Name


Date Of Birth


Hire Date


Job Type


Address


City


County


Snap

Staff
Customer ID
Customer Name


Customer Address


Post Code


Customer County


Contact No


Customer Type

Customer
Delivery No


Vehicle No


Delivery County


Delivery Distance


Delivery Charges

Deliveries
Vehicle No


Service Date

Service
Vehicle No
Vehicle Type


Mileage

Vehicle
8


8


8


8


8


8


1


1


1


1


1


Process:



o A customer places an order.
o The products for that order are recorded
o The staff involved in the transaction is recorded.
o The delivery details, if applicable are not recorded.
o The vehicle used in delivery , if applicable, is then recorded.
0329960


Access version - 2000












Task –5


Changes made in Part –1 design


Various changes have been made for designing the database. Customer No. whose field size was of 10 bytes has been changed to just