SUNWAYMART ONLINE ORDERING AND DELIVERY SYSTEM
The SunwayMart in Kuala Lumpur has decided to build an online ordering and delivery system. As part of this, a database with the following facilities has to be designed for SunwayMart.
SunwayMart wants customers to be able to browse the product catalogue and place orders over the Internet. On the website, customers should first identify themselves by their unique customer identification number. Then they should be able to browse the catalogue and to place orders online.
The product catalogue includes all the products sold by the mart. For each product, a unique product code, the product name, manufacturer’s name and unit price are stored. An additional attribute of photo number will also be used to identify the photograph of the product in the catalogue.
SunwayMart also maintains a list of the internet customers’ records with their identification number, name and address, date of birth, gender, telephone number and email address.
Payment is made by credit card. After the order is received and put together, a sales person prepares the delivery note details containing the customer’s name and address, the total cost of the order and for each product ordered, the product name, quantity ordered and cost.
Orders are delivered by a dedicated team of full-time van drivers. Van drivers are identified to the system by a driver number, name, home address, and date of birth.
A van (identified by van number, make and year of manufacture) may be taken out of SunwayMart depot whenever available. It is possible for any van to be taken out more than once on a given day. Any van can be taken out by any driver any number of times each day.
Each time a driver takes out a van, he/she takes several delivery notes. Any number of deliveries can be made during a trip. An address of the delivery note is recorded for each stop, together with the delivery note number. A driver will stop at an address only once during one trip. However, it is possible for stops to be made at the same address on different trips.
- Produce a complete Entity Relationship (ER) diagram (include keys and participation constraints) for the above-mentioned scenario.