patternModerate
Need help on my first ER diagram
Viewed 0 times
diagramfirsthelpneed
Problem
I just started my first Database course online, and I have a homework assignment to create an entity-relationship diagram (ERD) from a list of specifications.
The specs are as follows:
-
The company is organized into departments. Each department has a name, an unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the dept. A dept may have several locations.
-
A dept controls a number of projects, each of which has a name, unique number, and single location.
-
We store each employee’s name, SSN, address, salary, sex, and DOB. An employee is assigned to one dept but may work on several projects, which are not necessary controlled by the same dept. We keep track of the number of hours per week that an employee works for each project. We also keep track of the direct supervisor of each employee.
-
We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent’s first name sex, DOB, and relationship to the employee.
The specs are as follows:
-
The company is organized into departments. Each department has a name, an unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the dept. A dept may have several locations.
-
A dept controls a number of projects, each of which has a name, unique number, and single location.
-
We store each employee’s name, SSN, address, salary, sex, and DOB. An employee is assigned to one dept but may work on several projects, which are not necessary controlled by the same dept. We keep track of the number of hours per week that an employee works for each project. We also keep track of the direct supervisor of each employee.
-
We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent’s first name sex, DOB, and relationship to the employee.
Solution
For your first database class and first attempt at an ER Diagram (ERD) I think you have done a great job! I'd like to give you some feedback in the context of the process I use to break down a set of requirements like you were given and create a draft diagram. Hopefully by taking this approach I'll help you in your development of the skill of ER modeling and database design and not just give you an answer to a homework assignment.
Find the Entities and Relationships
The idea behind an ERD is to identify the entities - things of fundamental significance to the business - and how they are related. Thus the name Entity-Relationship Diagram. The goal at this point is not to design or implement a database but instead to develop a more structured understanding of the domain of interest on which a database will ultimately be based. A good way to get a start on finding the entities is to look for the nouns in the requirements and then pick out those which are persons, places, things, concepts, or events. Then, you can find most of the relationships by looking for the verbs that connect those nouns. Here is my first pass at finding entities and relationships in the requirements provided:
I highlighted the nouns in a bright green, and the verbs in a bright pink. Now notice there is one noun - supervisor - that is highlighted in a darker green. We'll ignore that for now. Comparing what I found to what you placed on your ERD we are on the same page! The only difference is that I also highlighted location. I can see why this was left off of your ERD as its not clearly an entity. You could just as easily think of it as an attribute - which is a common property or characteristic that all entities in an entity type share - of the department and the project and that is how you modeled it. The reason I elevated it to an entity type is this:
A dept may have several locations
This indicates that each department will be associated to one or more locations, and thus necessitates breaking this out to its own entity type. A second reason to elevate it is that we can think of a location as a place and as such would have its own attributes such as name, address, city, state, zip, and so on. A third reason to elevate it is that the location is referenced by more than one entity type - the department and the project. If you find the element in question is referenced by more than one already identified entity, you should consider it an entity in its own right that is related to the others, and not simply an attribute of the others. There is no science to this however - its purely a judgment call. This is why ER diagramming is at the conceptual level as its subjective to individual perspectives. One person's entity is another person's attribute depending on their perspective into the domain of interest.
Regarding the relationships, here is a list of what I highlighted in pink:
Of these, you identified all but the first one. This, along with the supervisor I mentioned earlier, gets into a discussion of roles which we'll postpone to later. Right now we are just trying to identify the fundamentals and I'd say you were right on.
Associative Entities
Looking at the relationships we have a few that are many to many. The requirements state that an employee can work on many projects, which are not necessarily controlled by the same department. Given that most projects have more than one member, we can safely assume this is a many to many relationship. When you have such a relationship on an ERD, it is perfectly acceptable to show a many to many relationship line. I typically only use the many to many relationship though if I'm not showing attributes at all. Since we are showing them here, I prefer to resolve the many to many relationship with an associative entity. Even if there are no attributes for the entity now, we may uncover some as analysis proceeds. However, in the case of employees and projects, we have attributes - the hours worked - that must be added for that relationship and so it must be resolved by creating the associative entity to show them. Thus we will create an associate entity called Project Assignment. But we are still not done. The requirement calls for us to know the hours worked per week. When you think about it, you realize there would be many weeks in the life of the project, and we need to record the hours worked by that employee on that project each week. Thus another entity type is required which I called Hours Worked of which there will be many occurrences per project assignment. This entity type will hold the week ending date and the hours worked.
Next, by creating a location entity type, the requirement that states a department may have several locations means we now have a relationship where one department can have many
Find the Entities and Relationships
The idea behind an ERD is to identify the entities - things of fundamental significance to the business - and how they are related. Thus the name Entity-Relationship Diagram. The goal at this point is not to design or implement a database but instead to develop a more structured understanding of the domain of interest on which a database will ultimately be based. A good way to get a start on finding the entities is to look for the nouns in the requirements and then pick out those which are persons, places, things, concepts, or events. Then, you can find most of the relationships by looking for the verbs that connect those nouns. Here is my first pass at finding entities and relationships in the requirements provided:
I highlighted the nouns in a bright green, and the verbs in a bright pink. Now notice there is one noun - supervisor - that is highlighted in a darker green. We'll ignore that for now. Comparing what I found to what you placed on your ERD we are on the same page! The only difference is that I also highlighted location. I can see why this was left off of your ERD as its not clearly an entity. You could just as easily think of it as an attribute - which is a common property or characteristic that all entities in an entity type share - of the department and the project and that is how you modeled it. The reason I elevated it to an entity type is this:
A dept may have several locations
This indicates that each department will be associated to one or more locations, and thus necessitates breaking this out to its own entity type. A second reason to elevate it is that we can think of a location as a place and as such would have its own attributes such as name, address, city, state, zip, and so on. A third reason to elevate it is that the location is referenced by more than one entity type - the department and the project. If you find the element in question is referenced by more than one already identified entity, you should consider it an entity in its own right that is related to the others, and not simply an attribute of the others. There is no science to this however - its purely a judgment call. This is why ER diagramming is at the conceptual level as its subjective to individual perspectives. One person's entity is another person's attribute depending on their perspective into the domain of interest.
Regarding the relationships, here is a list of what I highlighted in pink:
- Employee manages Department
- Department controls Project
- Employee assigned to Department
- Employee works on Project
- Employee has Dependent
Of these, you identified all but the first one. This, along with the supervisor I mentioned earlier, gets into a discussion of roles which we'll postpone to later. Right now we are just trying to identify the fundamentals and I'd say you were right on.
Associative Entities
Looking at the relationships we have a few that are many to many. The requirements state that an employee can work on many projects, which are not necessarily controlled by the same department. Given that most projects have more than one member, we can safely assume this is a many to many relationship. When you have such a relationship on an ERD, it is perfectly acceptable to show a many to many relationship line. I typically only use the many to many relationship though if I'm not showing attributes at all. Since we are showing them here, I prefer to resolve the many to many relationship with an associative entity. Even if there are no attributes for the entity now, we may uncover some as analysis proceeds. However, in the case of employees and projects, we have attributes - the hours worked - that must be added for that relationship and so it must be resolved by creating the associative entity to show them. Thus we will create an associate entity called Project Assignment. But we are still not done. The requirement calls for us to know the hours worked per week. When you think about it, you realize there would be many weeks in the life of the project, and we need to record the hours worked by that employee on that project each week. Thus another entity type is required which I called Hours Worked of which there will be many occurrences per project assignment. This entity type will hold the week ending date and the hours worked.
Next, by creating a location entity type, the requirement that states a department may have several locations means we now have a relationship where one department can have many
Context
StackExchange Database Administrators Q#112850, answer score: 10
Revisions (0)
No revisions yet.