IT Database / ERD Task
| Create requirement definition document for your project (see attached). Then use ER Assistant, Visio, SQL Developer Data Modeler or other suitable diagram tool to create Entity Relationship Diagram (ERD) for your project. First analyze the business problem you want to solve, and put the results into a requirement definition document. The document describes business rules by identifying what business data you want to keep track of and how those data are related to each other. Now that you have clarified your business requirements and business rules, you now start to design your database by creating the ERD. The ERD should consist of a minimum of 5, but no more than 6, entities with a minimum of 5 attributes for each entity. Draw appropriate relationships to connect related entities. (This is complete, but needs some changes. Please see below) ER Assistant, Visio, or SQL Developer Data Modeler is the standard Diagramming tool for this course. Use of another tool is acceptable as long as the final product is submitted in a format (such as PDF or JPG) that can be read, evaluated and graded. The crow's feet notation is required. |
ERD Improvements:
The ERD has more relationships than are absolutely needed, you can easily drop the relationship between customer and service, since you can get the customer information to the machine entity and services are performed on machines, which customers happen to own. This also saves you from having to create an associative or branch entity to resolve the many to many relationship between customer and services.
You can also drop the relationship between customer and sales, since the relationship can also be traced through the machine entity.
Looking at the business rules you provided and restructuring them:
The relationship between customer and machine is fully describe but could be described more succinctly: a customer must purchase one to many machines, and a machine is owned by zero or one customer.
The business rule for the relationship between machine and service entities indicates that it is mandatory in both directions; however the ERD has omitted the indicator that tells whether a machine must have a service or if the relationship is optional. I would recommend changing the business rule so that a machine may have zero to many services, while a services performed on one and only one machine.
The relationship between machine and sales needs to be a little clearer in the direction of sales to machine: a machine will have 0 to 1 sales, and a sale must be associated with one and only one machine
The business rules for the relationship between customer and salesperson is vague on the participation rule for salesperson and customer, does the salesperson have to have at least one customer? The participation indicator is also missing in the ERD. The rule could read: a customer is assigned to one and only one salesperson, and a salesperson can have zero to many customers assigned.
In general when I read a set of business rules the word may means optional participation. However some people treat cannas mandatory participation while others treated is optional, so it is better to be specific as to participation.
Introduce the use of foreign keys. The ERD should show the foreign keys, which go in the entities on the many side of a one to many relationship. For one-to-one relationships you can put them in either entity.
