The above model shows the most important entities and the relationships between them.
- For the customer, many customers have many order details and in relation to this many order details are placed by many customers.
- For the order details entity, many order details are placed for many different products as not everyone wants the same product and there are many products for all of the order details.
- For every order that’s made there is a payment therefore it is a one to one relationship. So one order is met with one payment.
Phase 2
Customers Order Order Design Product
Details Details
Payments
The above model is the same as phase 1 but I have added two intersecting tables. I have added an order entity in between the customer’s entity and the order details entity. A customer will make many orders and for every order there are many order details. I have also added a design details entity in between order details and the product entities. This is because there is many order details for every design detail and for all of the different designs there is one product.
Phase 3
Customers Order Order Design Product
Details Details
CustomerID OrderID OrderID* DesignID ProductID
CustomerID* DesignID* ProductID*
In this phase we have resolved all of the relationships to one to many, which must be done in order to have an efficient relational database and to help you get quick retrieval of any information that may be required. We have also merged the payment entity into the order details entity. In the next phase the attributes will be entered that are relevant to the Tee Shirt Manufacturing Company and we will review the entities to make sure they are normalised to third normal form. This is to make sure that we don’t have any attributes that are not reliant on the primary keys.
Customers Order Order Design Product
Details Details
CustomerID OrderID OrderDetID* DesignID ProductID
Name CustomerID* DesignID* ProductID* Price
Address Order Date Quantity Size Colour
Phone No Delivery Date Description
E-mail Payment Method
Deposit Paid
In this phase of the ERM, which is the last phase, we have put all of the remaining attributes in for each of the 5 entities. The primary keys for each of the entities are in bold and have also been underlined. The foreign keys have a * besides them. There are composite primary keys in the order details entity and the design details entity, both are marked as * and underlined in the respective entities. You cannot have two of the same attributes in an ERM unless one is a primary key and the other is a foreign key. Putting this ERM into an Access table and fields should be fairly easy and as soon as the tables have been completed then the relationships can be established. You don’t need to follow every step of this exactly but I recommend that you do if you are inexperienced at this. It is fairly easy unless you need to do a very complex ERM.
If Cathy decides to use the database that I have included in her ERM, she can use it to help her keep on top of all her paperwork and also to check on her customers with a lot less hassle than she would have had previously. By using this new computerised system Cathy can look up in the database to see if customers pay on time, how long they have been a customer for how many orders they make and so on, so there is no end to the benefits that this system can bring to her business. This method of checking on customers wouldn’t take very long compared to the length of time it would take the previous way. Having the new system would also be good for storage, all of the information that is needed is on computer where as before it would have been stored in filling cabinets or away in drawers. Also with this system all of the information in the database is easily updated or changed and if there is any errors they can be deleted.
The most obvious improvement for this database would be upgrading the database system by making it more complex therefore it would give more information at the touch of a button.
