Database Entity Relationship Diagram - TeX - LaTeX Stack Exchange
Description: ER diagrams symbolically shows the objects (aka entities) within a system. Here is an example. This is an ERD for a simple order system. It covers. Print out this exercise and answer the questions on the printout. Below we present several entity relationship diagrams for modeling information about authors. It's ER Diagram notation is the same as the general attribute you saw in the previous .. In our example, the answer is “No” because some employees are not.
The next section explains these databases, each with its ER diagram and an explanation of the motivation for its design. The Music Database The music database stores details of a personal music library, and could be used to manage your MP3, CD, or vinyl collection. It ignores the requirements of many music genres, making it most useful for storing popular music and less useful for storing jazz or classical music.
The collection consists of albums. An album is made by exactly one artist. An artist makes one or more albums.
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
An album contains one or more tracks Artists, albums, and tracks each have a name. Each track is on exactly one album. Each track has a time length, measured in seconds. When a track is played, the date and time the playback began to the nearest second should be recorded; this is used for reporting when a track was last played, as well as the number of times music by an artist, from an album, or a track has been played. Conversely, each play is associated with one track, a track is on one album, and an album is by one artist.
The attributes are straightforward: The track entity has a time attribute to store the duration, and the played entity has a timestamp to store when the track was played. If you wanted to use the music database in practice, then you might consider adding the following features: Support for compilations or various-artists albums, where each track may be by a different artist and may then have its own associated album-like details such as a recording date and time.DBMS - Question Solve on ER-Diagram - 1
Under this model, the album would be a strong entity, with many-to-many relationships between artists and albums. Playlists, a user-controlled collection of tracks. For example, you might create a playlist of your favorite tracks from an artist. Track ratings, to record your opinion on how good a track is.
Entity-Relationship Diagram (ERD)
Source details, such as when you bought an album, what media it came on, how much you paid, and so on. Album details, such as when and where it was recorded, the producer and label, the band members or sidemen who played on the album, and even its artwork. Smarter track management, such as modeling that allows the same track to appear on many albums.
The University Database The university database stores details about university students, courses, the semester a student took a particular course and his mark and grade if he completed itand what degree program each student is enrolled in.
We explain the requirements next and discuss their shortcomings at the end of this section. Consider the following requirements list: The university offers one or more programs.
A program is made up of one or more courses. A student must enroll in a program. A student takes the courses that are part of her program. A program has a name, a program identifier, the total credit points required to graduate, and the year it commenced. A course has a name, a course identifier, a credit point value, and the year it commenced. Students have one or more given names, a surname, a student identifier, a date of birth, and the year they first enrolled.
When he finishes the course, a grade such as A or B and a mark such as 60 percent are recorded. Each course in a program is sequenced into a year for example, year 1 and a semester for example, semester 1. Although it is compact, the diagram uses some advanced features, including relationships that have attributes and two many-to-many relationships. The ER diagram of the university database In our design: Each student must be enrolled in a program, so the Student entity participates totally in the many-to-one EnrollsIn relationship with Program.
A program can exist without having any enrolled students, so it participates partially in this relationship. As a weak entity, Course participates totally in the many-to-one identifying relationship with its owning Program.
Does a department have to be associated with a division? Does a department have to have a manager?
Entity Relationship Modeling Examples - Learning MySQL [Book]
These questions would have to be answered before we complete the ER model. And we will answer these questions later. For now we are going to stop this part of the analysis since the purpose of this example is to demonstrate what ER modelling is all about.
The ER modelling process is not something for which a set of steps can be given and then performed. The process contains almost as much art as science. Some steps are performed many times and many decisions are re-visited and revised. Given these conditions, a broad outline can be given: Determine what entity types are involved. Determine which entity types are related. Refine the definition of the relationships. Understand now that there are several methods for representing ER models graphically.
Notice what has happened with this situation. Initially we had a text description of the problem. After analysing it and making some necessary assumptions, we created an ER diagram that reflects the situation accurately and makes explicit the relationship among the entity types.
This is why we perform ER modelling. It is quite a straight-forward step to go from this ER model to an implemented database. Remember why we are doing all this: We are finding out all we need to know to create a database that will hold our data. And a well-defined database can be a very useful tool for solving business problemsand it is also in high demand by recruiters. You will learn how to perform the steps necessary to create such a database in later chapters.
In this section I present more detail on some of the basic concepts. In the example in an earlier section, we saw that divisions are directly associated with departments and departments are directly associated with employees.
No direct association between division and employee was given. This does not mean that there is no relationship between division and employee.
In fact, the ER diagram tells us that there is a relationship between the two: Given any one division, there can be many employees managing departments within that division. Certainly, this is not earth shattering news. But it is in the ER diagram. The above fact is not represented as a separate relationship between division and employee because it can be inferred from existing relationships.
An ER diagram should contain the minimum number of relationships necessary to reflect the situation. For relationships between two entity types, there are three basic cardinalities. Each of the following descriptions are given in terms of a relationship between entity type X and entity type Y.
One entity of type Y can be associated with, at most, one entity of type X. A car has only one steering wheel and a steering wheel can only be installed in one car. M one-to-many One entity of type X can be associated with many entities of type Y. A building can have many rooms but a room can be in, at most, one building. M many-to-many One entity of type X can be associated with many entities of type Y.
One entity of type Y can be associated with many entities of type X. A car can have many options and an option can be installed on many cars. Determine the cardinality of the relationships between the following four pairs of entity types.
For each relationship you have to answer two questions: Answering these two questions gives you the answer to the following questions. For example, if you answered M to the first question and 1 for the second question, then this relationship between entity types X and Y is of cardinality M: Patient under care of primary care physician Physician performs operation Doctors have speciality in disease Needle injected into patient It would seem that at any particular time a patient can only have one primary care physician and that any physician can have many patients M: One physician can perform many operations and one operation can be performed by many physicians M: One doctor can have specialities in many diseases and one disease can be the speciality of many doctors M: In this section we examine the minimum number of entities in a relationship.
Existence is given as optional, mandatory, or unknown. This is best clarified with an example. Consider again the example discussed in Section 2. Specifically, focus on the manage relationship between department and employee. We know the cardinality is 1: This tells us that at most one department is managed by an employee and an employee can manage, at most, one department. Be sure you understand the distinction between these two phrases. The existence of this relationship tells us the fewest number of departments that can be managed by an employee and the fewest number of employees that can manage a department.
Only one of the following can be true: Similarly, only one of the following may be true: For each set of three above, which ones would you choose? It is not entirely clear from the situation description which of the above are true. I make the relatively standard assumptions that a department must have at least one manager and that an employee need not be the manager of any department. Thus, the existence of this relationship is mandatory in one direction and optional in the other. Going back to the definition of existence, we can also look at this situation in this way: Given any randomly chosen department, there must be an employee on the other side of the manage relationship.
Thus, the relationship is mandatory in this direction. Given any randomly chosen employee, there need not be any department on the other side of the manage relationship. Thus, the relationship is optional in this direction. I assume that the contains relationship is mandatory in both directions. Given this information, the ER diagram is modified in the following manner: This diagram is beginning to look a little complicated but remember the following pieces of information and it gets a little easier: The marks on the lines tell you the minimum number in a relationship.
A dash on the line looks like a 1; it tells you the minimum number in the relationship is one so the existence is mandatory. A circle on the line looks line a 0; it tells you the minimum number in the relationship is zero so the existence is optional.
If there are not any marks on the line, then the existence is unknown. The marks below the line tell you the maximum number in a relationship. An M tells you the cardinality is many while a 1 tells you it is one. Look at the manages relationship again.
Cover up the line connecting the manages diamond with the department rectangle. Now all you see are the department entity type, the employee entity type, and the right side of the line with a dash on it and a one below it. This tells us that a department is managed by at least the dash one employee and at most the 1 one employee.
A department is managed by one and only one employee. Cover up the line connecting the manages diamond with the employee rectangle. Now all you see are the department entity type, the employee entity type, and the left side of the line with a circle on it and a 1 below it.
This tells us that an employee does not have to manage any departments the circle and may manage at most the 1 one department. An employee may manage no more than one department.
For each of the relationships listed in Problem 5: Define the existence in both directions. Draw the ER diagram for the relationship. Write out two sentences that represent what the ER diagram says. If it is not important, it should not be in the database. In an accounting database you would expect to find entity types for expenses, assets, liabilities, expenditures, deposits, etc.
You would not expect to find entity types for colour of check, quality of dollar bills received, etc. The database is supposed to reflect realitybut only the part of reality that is important to the company. This allows us to make generalisations about that type. This is a powerful capability; however, sometimes we want to make a generalisation only about a certain subset of those entities and another generalisation about the rest of the entities.
Consider a simple example. Suppose you have an accounting database which keeps track of accounts receivable and accounts payable. Of course the database keeps track of the companies to which you owe money and the companies that owe you money.
For all these companies, you keep track of their mailing address and a contact person. For the companies that owe you money you keep track of how much they owe you. For the companies that you owe money you keep track of how much you owe them.
Should we have three entity types: That would be a mess. That is why the concept of entity subtypes was created.
Database Entity Relationship Diagram - TeX - LaTeX Stack Exchange
In this company example, what is the entity type? What are the subtypes? In this database you should define a company entity type with two subtypes: The company entity type stores all facts that are common attributesin this case, the address and contact person. There are many situations in which subtypes can be created but should not be.
Only create subtypes if the subtype is involved in relationships that the other subtypes are not or if the subtype needs to have additional facts stored with it.
If one of these two requirements is not met, then do not create the subtype. What is the relationship among the following? List a few facts common to all items for each question. List a few facts about each subtype that is not common to the other subtype.
Also draw the ER diagram for each. Consider the following figure. Many students would first suggest the diagram on the rightdivide customers into investors and attendees and show that investors buy stocks and attendees register for seminars.
I suggest that the figure on the left is better. What is it that makes an investor an investor? And what is it that makes an attendee an attendee? She registers for seminars. Is there anything about an investor that keeps her from being an attendee? Do you want to prevent investors from being classified as attendees or vice versa?
So, define relationships buy and register for the customer entity.