Assignment: ERD
The following business information should be used to develop your ERD:
- Each Employee is assigned to a Department. There are four departments: Administration, Sales, Inventory, and Customer Support.
- The Sales staff employees are responsible for Sales to Customers. For each Sale, the company wishes to record the salesperson and the customer. A Sale can have many Products and a Product can be sold on many Sales.
- The Inventory staff employees are responsible for Orders to Vendors. This restocking Order is placed to a Vendor for additional Products when the number of products available falls below a specified reorder amount for each product. A restocking Order can have many products and a Product can appear on many restocking Orders over time.
- The Customer Support staff employees are responsible for answering Calls from Customers. Each Call is in reference to one particular Product. The company wishes to maintain information on Calls, including date and time, customer who made the Call, employee who handled the Call, description of the question or problem, and resolution of the Call.
- Employees have a manager who is also an Employee.
- Products are stored in a warehouse at the company. For each product, the company wants to record the location of the product in the warehouse (aisle number).
- Some employees telecommute from home. These employees are assigned Equipment such as laptops and printer/fax machines for their use at home. The company maintains detailed information on this Equipment including the person using it and date assigned so that they can track their equipment.
Additional information
- Departments have a number, a name, a fax number, and telephone number.
- Employee information includes employee ID, name, address, date of hire, date of birth, job title, salary, office number, and office phone.
- Employee equipment has inventory number, name, date purchased, manufacturer.
- Vendor has name, address, phone number, and contact person.
- Customer information includes customer ID, name, address, credit limit, and current balance. - Products have product number, name, description, quantity on hand, reorder quantity, cost, suggested sale price (MSRP).
Tips on Creating Your ERD:
Instead of thinking of it as one big problem, think of it as several very small problems! (When we ask our minds to try to consider too many different concepts at the same time, most of us experience at least some overload!)
One technique to try involves modeling "views" of the data. (Not the same as a database view.) To use this technique:
1. Consider each paragraph or line in the problem one at a time. Carefully analyze just the one line/paragraph for any information that should be used in the ERD.
2. Then draw the ERD for that one piece of the problem. It may be very small - that is OK.
3. Move to the next paragraph/line and analyze it. Forget about the previous line-just concentrate on this one. Draw the ERD for that paragraph/line.
4. Continue through the problem-analyzing each piece for information and drawing the ERD for that information. When you are finished, you should have a number of small ERD's-some may only be an entity with some attributes or maybe two entities that are related, but have no attributes specified.
5. Combine all the little ERD's into one bigger one. If you have several ERDs that refer to Employee, for example, combine them all using one entity for Employee. Be sure to transfer all information from the little ERDs to the large one (attributes, identifiers, cardinality, etc.)
By focusing on only one small portion of the problem at a time, you will find the overall problem much easier to solve and you should find that you have not overlooked or forgotten any detail.