Entity Relationship Diagrams An entity-relationship diagram is a graphical depiction of organizational system elements and the the asso associ ciat atio ion n among among the the elem elemen ents ts.. E-R E-R diagr diagram amss can help help defin definee syst system em boun boundar darie ies. s. The The elem element entss that that make make up a syst system em are are refe referr rred ed to as entities. A relationship is the association that describes the interaction between entities. An E-R diagram may also indicate the cardinality of a relationship. Cardinality is the number of instances of one entity that can, or must, be associated with each instance of another entity. In general we may speak of one-to-one, one-to-many, or many-to-many relationships. There are several different styles used to draw Entity-Relationship diagrams. The Kendall and Kend Kendal alll text text uses uses the the Crow Crow's 's Foot Foot nota notati tion on.. Usin Using g this this notat notatio ion n enti entiti ties es are are repres represente ented d by rectangl rectangles es and relati relations onship hipss are indica indicated ted by lines lines connect connecting ing the rect rectang angle les. s. Card Cardin inal alit ity y is show shown n by a seri series es of "tic "tick k mark marks" s" and and "crows "crows feet feet"" superimposed on the relationship lines.
In the following example each student fills one seat in a class. Each seat is filled by one student. (In this usage a "seat" implies not only a physical place to sit but also a specific day and time.) This is a one-to-one relationship.
In the next example a single instructor may teach several courses. Each course has only one instructor. This is a one-to-many relationship.
As shown below, a single student may register for several courses. A single course can have many students enrolled in it. This is the many-to-many relationship.
The next example shows a relationship in which it is possible that no instances exist. Each professor may teach several course sections bu t may not teach at all if on sabbatical. Assume there is no team teaching, therefore each section must have a single professor. professor.
Finally, Finally, a more complex co mplex example which shows more than one relationship. All of the examples above depict single relationships. An actual E-R diagram would show the many entities and relationships that exist within a system. Here each dep artment offers at least one course; there is no cross-listing of courses with other departments. Each course must have at least one section but often has several sections.
What are ERDs?
Entity Relationship Diagrams (ERDs) illustrate the logical structure of databases.
An ER Diagram Entity Relationship Diagram Notations
Peter Chen developed ERDs in 1976. Since then Charles Bachman and James Martin have added some sligh refinements to the basic ERD principles.
Entity An entity is an object or concept about which you want to store information. Learn how to edit text on an entity.
Occasi asional onally ly,, ent entiti ities es of an ent entity ity set need “help” “help” to ide identi ntify fy the them m Weak Ent Entity ity : Occ uniquely. Entity set E set E is said to be weak if in order to identify entities of E E uniquely, we need to follow one or more many-one relationships from E and include the key of the related entities from the connected entity sets. Example : Nname is almost a key for football players, but there might be two with the same name. Number is certainly not a key, since players on two teams could have the same number. But number, together with the team name related to the player by Plays-on should be unique. Attributes are the properties or o r characteristics of an entity entity.. Learn how to edit text on this object.
Key attribute A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute.
Multivalued attribute A multivalued attribute can have more than one value. For example, an employee entity can have multiple skill values.
Derived attribute A derived attribute is based on another attribute. For example, an employee's monthly salary is based on the employee's annual salary salary..
Relationships Relationships illustrate how two entities share information in the database structure.
Learn how to draw relationships: First, connect the two entities, entities, then drop the relationship notation on the line. line.
Cardinality Cardinality specifies how many instances of an entity relate to one instance of another entity.
Ordinality is also closely linked to cardinality. cardinality. While cardinality specifies the occurences occ urences of a relationship, ordinality describes the relationship as either mandatory or optional. In
other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships.
Recursive relationship In some cases, entities can be self-linked. For example, employees can supervise other employees.
We can express the overall logical structure of a database graphically with an E-R diagram. Its components are: •
rectangles representing entity sets.
•
ellipses representing attributes.
•
diamonds representing relationship sets.
•
lines linking attributes to entity sets and entity sets to relationship sets.
In the text, lines may be directed d irected (have an arrow on the end) to signify mapping cardinalities for relationship sets. Figures 2.8 to 2.10 show some examples.
Figure 1: An E-R diagram
customer to account Figure 2: One-to-many from customer to
customer to account Figure 3: Many-to-one from customer to
customer to account Figure 4: One-to-one from customer to Go back and review mapping cardinalities. They express the number of entities to which an entity can be associated via a relationship. The arrow positioning is simple once you get it straight in your mind, so do some examples. Think of the arrow head as pointing to the entity that ``one'' refers to.
Developing Entity Relationship Diagrams (ERDs) WHY: Entity Relationship Diagrams are a major data modelling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner. INFORMATION: Entity : A data entity is anything real or abstract about which we want to store data. Entity types fall into five classes: roles, events, locations, tangible things or concepts. E.g. employee, payment, campus, book. Specific examples of an entity are called Mar y Smith's payment, etc. instances. E.g. the employee John Jones, Mary Relationship : A data relationship is a natural association that exists between one or more entities. E.g. Employees process payments. Cardinality defines the number of
occurrences of one entity for a single occurrence of the related entity. E.g. an employee may process many payments but might not process any payments depending on the nature of her job. Attribute : A data attribute is a characteristic common to all or most instances of a particular entity. entity. Synonyms include property p roperty,, data element, field. E.g. Name, address, Employee Number, pay rate are all attributes of the entity employee. An attribute or combination of attributes that uniquely identifies one and only one instance of an entity is called a primary key or identifier . E.g. Employee Number is a primary key for Employee. AN ENTITY RELATIONSHIP DIAGRAM DIAGRAM METHODOLOGY: METHODOLOGY: (One way of doing it)
Identify the roles, events, locations, tangible things or concep ts about which the e want to store data. 2. Find Relationships Find the natural associations between pairs of entities using a relationship matrix. 3. Draw Rough ERD Put entities in rectangles and relationships on line segments connecting the entitie Determine the number of occurrences of one entity for a single occurrence of the 4. Fill in Cardinality entity. Identify the data attribute(s) that uniquely identify one and only one occurrence o 5. Define Primary Keys entity. 6. Draw Key-Based ERD Eliminate Many-to-Many relationships relationships and include primary and foreign keys in e 7. Identify Attributes Name the information details (fields) which are essential to the system under deve 8. Map Attributes For each attribute, match it with exactly one entity that it describes. 9. Draw fully attributed Adjust the ERD from step 6 to account a ccount for entities or relationships discovered in s ERD 10. Check Results Does the final Entity Relationship Diagram accurately depict the system data? 1. Identify Entities
A SIMPLE EXAMPLE
A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number. 1. Identify Entities
The entities in this system are Department, Employee, Supervisor and Project. One is tempted to make Company Compan y an entity, entity, but it is a false entity en tity because it has only one instance in this problem. True entities must have more than one instance. 2. Find Relationships
We construct the following Entity Relationship Matrix: Department
Department Employee Supervisor
belongs to runs
Employee is assigned
Supervisor run by
Project
works on
Project
uses
3. Draw Rough ERD
We connect the entities whenever a relationship is shown in the entity Relationship Matrix.
4. Fill in Cardinality
From the description of the problem we see that: •
Each department has exactly one supervisor.
•
A supervisor is in charge of one and only one department.
•
Each department is assigned at least one employee.
•
Each employee works for at least one department.
•
Each project has at least one employee working on it.
•
An employee is assigned to 0 or more projects.
5. Define Primary Keys : The primary keys are Department Name, Supervisor Number, Employee Number, Project Number. 6. Draw Key-Based ERD : There are two many-to-many relationships in the rough ERD above, between Department and Employee and between Employee and Project. Thus we need the associative entities Department-Employee and Employee-Project. The primary key for Department-Employee is the concatenated key Department Name and Employee Number Number.. The primar primary y key for Employ Employee-P ee-Proj roject ect is the concat concatenat enated ed key Employe Employeee Number and Project Number.
7. Identify Attributes : The only attributes indicated are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee NUMBER and a unique project number. 8. Map Attributes Attribute Department Name Employee Number Employee Name
Entity Department
Employee Employee
Attribute Supervisor Number Supervisor Name Project Name Project Number
Entity Supervisor
Supervisor Project Project
9. Draw Fully Attributed ERD
10. Check Results : The final ERD appears to model the data in this system well. FURTHER DISCUSSION: Step 1. Identify Entities
A data entity is anything real or abstract about which we want to store data. Entity types fall into five classes: roles, events, locations, tang ible things, or concepts. The best wa y to identify entities is to ask the system owners and users to identify things about which they would like to capture, store and produce information. Another source for identifying entities is to study the forms, files, and reports generated by the current system. E.g. a student registration form would refer to Student (a role), but also Course (an event), Instructor (a role), Advisor (a role), Room (a location), etc. Step 2. Find Relationships
There are natural associations between pairs of entities. Listing the entities down the left column and across the top of a table, we can form a relationship matrix by filling in an active verb at the intersection of two entities which are related. Each row and column should have at least one relationship listed or else the entity associated with that row or column does not interact with the rest of the system. In this case, you should question whether it makes sense to include that entity in the system. . A student student is enrolled in one or more courses subject verb objects Step 3. Draw Rough ERD
Usin Using g rect rectang angle less for for enti entiti ties es and and lines lines for for rela relati tion onsh ship ips, s, we can can draw draw an Entit Entity y Relationship Diagram (ERD). Step 4. Fill in Cardinality
At each end of each connector joining rectangles, we need to place a symbol indicating the minimum and maximum number of instances of the adjacent rectangle there are for one instance of the rectangle at the other end of the relationship line. The placement of these numbers is often confusing. The first symbol is either 0 to indicate that it is possible for no instances of the entity joining the connector to be related to a given instance of the entity on the other side of the relationship, 1 if at least one instance is necessary or it is omitted if more than one instance is required. For example, more than one student must be enrolled in a course for it to run, but it is possible for no students to have a particular instructor (if they are on leave). The second symbol gives the maximum number of instances of the entity joining the connector for each instance of the entity on the other side of the relationship. If there is only one such instance, this symbol is 1. If more than 1, the symbol is a crows foot opening towards the rectangle. If you read it like a sentence, the first entity is the subject, the relationship is the verb, the cardinality after the relationship tells how many direct objects (second entity) there are. i.e. A student student is enrolled in one or more courses subject verb objects Step 5. Define Primary Keys
For each entity we must find a unique primary key so that instances of that entity can be distinguished from one another. Often a single field or property is a primary key (e.g. a Student ID). Other times the identifier is a set of fields or attributes (e.g. a course needs a department identifier, a course number, and often a section number; a Room needs a Building Name and a Room Number). When the entity is written with all its attributes, the primary key is underlined. Step 6. Draw Key-Based ERD
Looking at the Rough Draft ERD, we may see some relationships which are non-specific or many-to-many. many-to-many. I.e., there are crows feet on both ends of the relationship line. Such relationships spell trouble later when we try to implement the related entities as data stores or data files, since each record will need n eed an indefinite number of fields to maintain the many-to-many relationship. Fortunately, Fortunately, by introducing an extra entity, called an associative entity en tity for each many-tomany relationship, we can solve this problem. The new associative entity's name will be
the hyphenation of the names na mes of the two originating entities. It will have a concatenated key consisting of the keys of these two entities. It will have a 1-1 relationship with each of its parent entities and each parent will have the same relationship with the associative entity that they had with each other o ther before we introduced the associative entity. The original relationship between the parents will be de leted from the diagram. The key-based ERD has no many-to-many relationships and each entity has its primary and foreign keys listed below the entity en tity name in its rectangle. Step 7. Identify Attributes
A data attribute is a characteristic common to all or most instances of a particular entity. entity. In this step we try to identify and name all the attributes essential to the system we are studying without trying to match them to particular entities. The best way to d o this is to study the forms, files and reports currently kept by the users of the system and circle each data item on the paper pap er copy. copy. Cross out those which will not be b e transferred to the new system, extraneous items such as signatures, and constant information which is the same for all instances of the form (e.g. your company name and address). The remaining circled items should represent the attributes you need. You should always verify these with your system users. (Sometimes forms or reports are out of date.) Step 8. Map Attributes
For each attribute we need to match it with exactly one entity. Often it seems like an attribute should go with more than one entity (e.g. Name). In this case you need to add a modifier to the attribute name to make it unique (e.g. Customer Name, Employee Na me, etc.) or determine which entity an attribute a ttribute "best' describes. If you have attributes left over without corresponding entities, you may have missed an entity and its corresponding relationships. Identify these missed entities and add them to the relationship matrix now.
Step 9. Draw Fully-Attributed ERD
If you introduced new entities and attributes a ttributes in step 8, you need to redraw the entity relationship diagram. When you do so, try to rearrange it so no lines cross by putting the entities with the most relationships in the middle. If you use a tool like Systems Architect, redrawing the diagram is relatively easy. easy. Even if you have no new entities to add to the Key-Based ERD, you still need to add the attrib attribute utess to the Non-Key Non-Key Data sectio section n of each rectan rectangle gle.. Adding Adding these these attrib attribute utess automatically puts them in the repository, so when we use the entity to design the new system, all its attributes will be available. Step 10. Check Results
Look at your diagram from the point of view of a system owner or user. Is everything clear? Check through the Cardinality pairs. Also, look over the list of attributes associated with each entity to see if anything an ything has been omitted.
Another Example: A Resource Management Application
Consider that we’re writing a resource management application. The first step to creating an ERD is always to identify the nouns no uns (entities). In this case let’s let’s start with: •
Company
•
Employee
•
Project; and
•
Technology Project (which are a specific type of Project that perhaps require special fields like “number of entities”)
Here’s the Example Entity Relationship Diagram I’ll decipher piece by piece in this article (click to enlarge):
(note that I’m now using singular names since my somewhat controversial decision to switch to naming entities in the singular ) To read the notations of an Entity Relationship Diagram:
An Entity Relationship Diagram conveys a lot of information with a very concise notation. The important part to keep in mind is to limit what you’re reading using the following technique: 1. Choose Choose two two entiti entities es (e.g. (e.g. Compa Company ny and Employ Employee) ee) 2. Pick Pick one that that you’ you’re re inter interest ested ed in (e.g. (e.g. how how a single Company relates to employees) 3. Read the notatio notation n on the second second entity (e.g. the the crow’s crow’s feet with with the O above above it next to the Employee entity).
The set of symbols consist of Crow’s feet (which Wikipedia describes as looking like the forward digits of a bird’s claw), O, and dash, but they can be combined in four distinct combinations. Here are the four combinations: co mbinations: •
Zero through Many (crow's feet, O)
•
One through Many (crow's feet, dash)
•
One and Only One (dash, dash)
•
Zero or One (dash, O)
Zero through Many
If, as in the diagram above, the notation closest to the second entity en tity is a crow’s crow’s feet with an O next to it, then the first entity can have zero, one, or many of the second entity. entity. Consequently the diagram above would read: “A company can have zero, one, or many employees”. This is the most common relationship type, and consequently many people ignore the O. While you can consider the O optional, I consider it a best practice to be explicit to differentiate it from the less common one through many relationship. One through Many
If, as the next diagram shows, the notation closest to the second entity is a crow’s feet with a dash, then the first entity can have one through many man y of the second entity. More specifically it may not contain zero of the second entity. entity. The example above would thus read (read bottom to top): “A Project can have one through many Employees working on it.”
This is an interesting combination because it ca n’t (and for various reasons probably shouldn’t if it could) be enforced by a database. d atabase. Thus, you will only see these in logical, but not a physical, ph ysical, data models. It is still useful to distinguish, but your application will need to enforce the relationship in business rules. One and Only One (onne)
If the notation closest to the second entity contains two dashes it indicates that the first entity can have one and only one of the second. More specifically it cannot have zero, and it cannot have more than one. The example would thus read: “An Employee can have one and only one Company.” Company.” This combination is the most common after zero through many, and so frequently people consider the second dash optional. In fact, some ignore both dashes, but I would highly recommend at least using one for clarity so as not to confuse the notation with “I’ll fill in the relationship details later”. Zero or One
A zero or one relationship is indicated by a dash and an O. It indicates that the first entity can have zero or one of the second, but not more than one. The relationship in the example above would thus read: “A Project Project can have zero or one Technology Project.” The zero or one relationship is quite common and is frequently abbreviated with just an O (however it is most commonly seen in a many-to-many relationship rather than the oneto-one above, more on this later).
Relationship Types
Having examined the four types of notation, the discussion wouldn’t be complete without a quick overview of the three relationship types. These are: •
One to Many
•
Many to Many
•
One to One
One-to-Many
A one-to-many (1N) is by far the most common relationship type. It consists of either a one through many or a zero a zero through many notation on one side of a relationship and a one and only one or zero zero or one notation on the other. The relationship between Company and Employee in the example is a one-to-many relationship. Many-to-Many
The next most common relationship is a many-to-many (NM). It consists of a zero through many or one through many on both sides of a relationship. This construct only exists in logical data models because databases can’t implement the relationship directly. Physical data models implement a many-to-many relationship by using an associative (or link or resolving) table via two one-to-many relationships. The relationship between Employee and Project in the example is a many to many relationship. It would exist in logical and physical p hysical data models as follows:
One-to-One
Probably the least common and most misunderstood relationship is the o ne-to-one. It consists of a one and only one notation on one side of a relationship and a zero or one on the other. It warrants a discussion unto itself, but for now the Project to Technology Project relationship in the example is a one to one. Because these relationships are easy eas y to mistake for traditional one-to-many relationships, I have taken to drawing a red dashed
line around them. The red dashed da shed line is not standard at all (although a colleague, Steve Dempsey uses a similar notation), but in my experience it can help eliminate confusion.
Data models are tools used in analysis to describe the data requirements and assumptions in the system from a top-down perspective. They also set the stage for the design of databases later on in the SDLC.
There are three basic elements in ER models: Entities are the "things" about which we seek information. • Attributes are the data we collect about the entities. • Relationships provide the structure needed to draw information from multiple • entities. Developing an ERD : Developing an ERD requires an understanding of the system and its components. Before discussing the procedure, let's look at a narrative created by Professor Harman. Harman.
Consider a hospital: Patients are treated in a single ward by the doctors assigned to them. Usually each patient will be assigned a single doctor, but in rare cases they will have two. Heathcare assistants also attend to the patients, a number of these are associated with each ward. Initially the system will be concerned solely with drug treatment. Each pa tient is required to take a variety of drugs of drugs a certain number of times per day da y and for varying lengths of time. The system must record details concerning patient treatment and staff payment. payment. Some staff are paid are paid part time and doctors and care assistants work varying amounts of overtime at varying rates (subject to grade). The system will also need to track track what what treatments are required for which patients and when and it should be capable of calculating the cost of treatment of treatment per week for each patient (though it is currently unclear to what use this information will be put). How do we start an ERD?
1. Define Entities: these are usually nouns used in descriptions of the system, in the discussion of business rules, or in documentation; identified in the narrative (see highlighted items above). 2. Define Relationships: these are usually verbs used in descriptions of the system or in discussion of the business rules (entity ______ entity); identified in the narrative (see highlighted items above). 3. Add attributes to the relations; these are determined by the queries,and may also suggest new entities, e.g. grade; or they may suggest the need for keys or identifiers. What questions can we ask? a. Which doctors work in which wards? b. How much will be spent in a ward in a given week? c. How much will a patient cost to treat? d. How much does a doctor cost per week? e. Which assistants can a patient expect to see? f. Which drugs are being used? 4. Add cardinality to the relations Many-to-Many must be resolved to two one-to-manys with an additional entity Usually automatically happens Sometimes involves introduction of a link entity (which will be all foreign key) Examples: Patient-Drug 5. This flexibility allows us to consider a variety of questions such as: a. Which beds are free? b. Which assistants work for Dr. X? c. What is the least expensive prescription? d. How many doctors are there in the hospital? e. Which patients are family related? 6. Represent that information with symbols. Generally E-R Diagrams require the use o f the following symbols: Reading an ERD
It takes some practice reading an ERD, but they can be used with clients to discuss business rules. These allow us to represent the information from above such as the E-R Diagram below:
ERD brings out issues: Many-to-Manys Ambiguities Entities and their relationships What data needs to be stored The Degree of a relationship Now, think about a university in terms of an ERD. What entities, relationships and attributes might you consider? Look at this simplified view. view. There is also an example of a simplified view of an airline on that page. pag e. Example for Peter Chen notation
Given below are a few examples of ER diagrams using Bachman notation. First the textual statement is given followed by the diagram 1. In a company compan y, each division is managed by only one manager and each manager manages only one division
2. Among the automobile manufacturing companies, a compan y manufactures many cars, but a given car is manufactured in only one company
3. In a college, every student takes many courses and every course is taken by many students
4. In a library, a member may borrow many books and there ma y be books which are not borrowed by any member
5. A teacher teaches many students and a student is taught by b y many teachers. A teacher conducts examination for many students and a student is examined by many teachers.
6. An extension of example-3 above is that student-grades depend upon both student and the course. Hence it is an associative entity
7. An employee can play the role of a man ager. ager. In that sense, an employee reports to another employee.
8. A tender is floated either for materials or services but not both.
9. A car consists of an engine and a chasis
ER Notation Ther There e is no stan standa dard rd for for repr repres esen enti ting ng data data obje object cts s in ER diag diagrrams. ams. Each Each mode modeli ling ng methodology uses its own notation. The original notation used by Chen is widely used in academics texts and journals but rarely seen in either CASE tools or publications by nonacademics. academics. Today Today,, there are a number number of notations notations used, among among the more common common are Bachman, crow's foot, and IDEFIX. All notatio notational nal styles styles repres represent ent entitie entities s as rectan rectangul gular ar boxe boxes s and relati relations onship hips s as lines lines connecting boxes. Each style uses a special set of symbols to represent the cardinality of a connection. The notation used in this document is from Martin. The symbols used for the basic ER constructs are: •
entities are represented by labeled rectangles. The label is the name of the entity. Entity names should be singular nouns.
•
relationships are represented by a solid line connecting two entities. The name of the relationship is written above the line. Relationship names should be verbs.
•
attributes, when included, are listed inside the entity rectangle. Attributes which are identifiers are underlined. Attribute names should be singular nouns.
•
cardinality of many is represented by a line ending in a crow's foot. If the crow's foot is omitted, the cardinality is one.
•
existe existence nce is repres represent ented ed by placin placing g a circle circle or a perpen perpendic dicular ular bar on the line. line. Mandatory existence is shown by the bar (looks like a 1) next to the entity for an instance is required. Optional existence is shown by placing a circle next to the entity that is optional.
Examples of these symbols are shown in Figure 1 below:
Relationships The lines with symbolized ends connecting database entities represent the relationship between two tables. Each line describe both directions of the relationship. There are four basic relationships that can be used to describe the relationship of one table to another.
KEY: Key relationships.
to
ERD
The diagram on the right shows these four possibilities by discussing the relationship of some entity B with another entity A. The first relationship shown is that of a one to one relationship between the two tables. Specifically this means that for every row in table A there is one and only one corresponding row in table B. This requires that the number of rows in A must be the same as the number of rows in B. Such relationships represent a kind of a choice, in that it would be possible to merge two tables related in this manner into a single single table sharing all of the columns of both. Alternati Alternatively vely,, and particularly particularly for seismic applications, it might be better to keep two tables distinct, with B containing information of a specialized and possibly site specific nature, while A might contain information of a much more general nature. Generally one to one relationships are uncommon in database design. The second relationship shown is much more common, and will help to understand the bidirectional nature of this notation. Simply put, the symbol is to be interpreted that every row of B is uniquely related to specific row of A, while every row of A is related to at most one row of B. As a pneumonic, I sometimes think of the symbol near the B entity as reading "0 or 1", since that is what it looks like. One thing that is difficult to keep sorted out, speaking for myself, is the directionality of the relationship. One can think of the terminal symbol as an arrow head, defining how one entity, A in the example, is related to another, B. Apparently Apparently some of the rows in A do not relate to any rows in B, and the corresponding "foreign key" is said to be null. On the other hand, every row of B is related to exactly one row of A. One can see, that table A must contain at least as many rows as table B, and possibly considerably more. With the third relationship shown in diagam KEY, things get quite a bit more interesting. This notation reads that a given row of A is related to one or more rows in B. To accomplish this, particular values of some foreign key in B, which is also the primary key of A, might be duplicated one or more times. For example, a column in a table holding inform informati ation on on the arriva arrivall times times of partic particula ularr seismi seismicc phases phases might might have a column column (foreign key) containing an ID, which is the primary key of a table of hypocenters. In this example, the phase table is B, and the hypocenter list is A. In this way multiple phase arrivals can be "assocated" with a given hypocenter h ypocenter.. The simple example below, however, shows that the situation is generally a bit more tricky than this for real applications. Note that every row of A must be associated with at least one row of B. The situation where a row of A corresponds to no row in B is shown in the fourth symbol. This reads that rows of A are associated with zero or more rows of B. For the phase arrival example, this would mean that there could be a location for which no phase data was available. The location, perhaps, was simply typed in without supporting data. So far the discussion has been limitted to the symbol on the right side of the connector. In each example, the left hand symbol is shown as a unique connection. In fact the situation is a bit bit more more tens tensee than than this this,, so that that symb symbols ols can occur occur on each each end end in variou variouss combinations. For the most part this is not a concern, as the relationships in alternating directions can be considered independant. The exception to this is that of having any kind of "cro "crow' w'ss feet feet"" on both both ends. ends. The The prob problem lem is that that such such rela relati tion onsh ship ipss canno cannott be represented in a normalized database, and thus cannot occur without introducing an additional linking table. This leaves 12 permittable permutations, or only 7 if reflexive pairs are taken as one. How this works out in a realistic example is shown in the following section.
Rules for Drawing an E-R Diagram
Below given is a simple example which explains how three entities in an activity can be related together. together. belongs bel has M ongsE-R to model uses three separate entities and two CUSTOMER INVOICE LINE 1 The relationships to represent an invoic invoicee transactio transaction. n. Because customers can buy more than one item at a time, each invoice references one or more lines, one item per line. And, bec becau ause se invoic invoices es are generated by custo mers, the data modeling requirements include a customer entity and a relationship between between the c ustomer and and the invoic invoice. e.
Example 1 :Take :Take a simple simple purchas purchasee order order applicat application ion.. customers, purchase orders, line items, and stock items.
Here Here the basic entitie entitiess are
Above figure gives the E-R Diagram for Purchase Order Application. A Customer has a one-to-many relationship with a Purchase Order because a customer can place many orders, but a given purchase order can be placed by only one customer. The relationship relationship is optional optional because zero customers customers might place a given order (it might be placed by someone not previously defined as a customer). A Purchase Order has a many-to many-to-ma -many ny relati relations onship hip with with a Stock Item because a purchase order can refer to man y stock items, and a stock item can be referred to by many purchase orders. However, you do not know which purchase orders refer to which stock items. Therefore, you introduce the notion of a Line Item. A Purchase Order has a one-tomany relationship relationship with a Line Item because a purchase order can list many line items, but a given line item can be listed by only one purchase order. A LineItem has a many-to-one relationship with a StockItem because a line item can refer to only one stock item, but a given stock item can be referred to by many line items. The relationship is optional because zero line items might refer to a given stock item.
Example 2 :Take :Take a simple simple manufacturing manufacturing system. system. Here, we can have many entity entity sets. Let us limit the list to only six entity-sets, namely, Worker, Department, Project, Buyorder order,, Product, Product, and Suppl Supplier ier.. Below Below given given is a sample sample E-R E-R diagram diagram drawn drawn using using Microsoft Visio software.
Below given is a completed E-R Diagram of a College Application:
is isused generates contains employs operates advises member teaches offers hasDean awritten found for in Building Employee Professor Student College Enroll Class Room Course of of in
Building_Code Student_Num College_Code Course_Code Room_Code Class_Code Emp_ID Bldg_Location College_Name Stu_LName Emp_LName Room_Type Class_time Crs_Title (FK) Building_Code Student_Num Crs_Descrip Stu_FName Emp_FName Emp_ID (FK) Stu_Initial Emp_Initial Crs_Credits (FK) (FK) Enroll_Date Course_Code Stu_Email Emp_Email Dept_Code Enroll_Grade Emp_Mobile Stu_Mobile (FK) (FK) Emp_ID (FK) Dept_Code(FK) Dept_Code(FK)
Department Dept_Code Dept_Name College_Code (FK) Emp_ID (FK)
Student Exercises Exercises: Go through the following examples and draw the E-R diagrams.
Example 1 A publishing company produces scientific books on various subjects. The books are written by authors who specialize in one particular subject. The company employs editors who, not necessarily being specialists in a particular area, each take sole responsibility for editing one or more publications. A publication covers essentially one of the specialist subjects and is normally written by a single author. When writing a particular book, each author author work workss with with on edito editorr, but but may submi submitt anot another her work work for for publ public icat atio ion n to be supervised by other editors. To improve their competitiveness, the company tries to employ a variety of authors, more than one author being a specialist in a particular subject.
Example 2 A General General Hospit Hospital al consis consists ts of a number number of specia specializ lized ed wards wards (such (such as Matern Maternity ity,, Paediatry, Oncology, etc). Each ward hosts a number of patients, who were admitted on the recommendation of their own GP and confirmed by a consultant employed by the Hospital. On admission, the personal details of every patient are recorded. A separate register is to be held to store the information of the tests undertaken and the results of a prescribed treatment. A number of tests may be conducted for each patient. Each patient is assigned to one leading consultant but may be examined by another doctor, if required. Doctors are specialists in some branch of medicine and may be leading consultants for a number of patients, not necessarily from the same ward.
Example 3 A database is to be designed for a Car Rental Co. (CRC). The information required includes includes a descri descripti ption on of cars, cars, subcont subcontrac ractor torss (i.e. (i.e. garages garages), ), company company expendi expenditur tures, es, company revenues and customers. Cars are to be described by such data as: make, model, year of production, engine size, fuel type, number of passengers, registration number, purchase price, purchase date, rent price and insurance details. It is the company policy not to keep any car for a period exceeding one year. All major repairs and maintenance are done by subcontractors (i.e. franchised garages), with whom CRC has long-term agreements. Therefore the data about garages to be kept in the database includes garage names, names, addres addressee sees, s, range range of service servicess and the like. like. Some Some garage garagess requir requiree payment paymentss immediately after a repair has been made; with others CRC has made arrangements for credit facilities. Company expenditures are to be registered for all outgoings connected with purchases, repairs, maintenance, insurance etc. Similarly the cash inflow coming from all sources - car hire, car sales, insurance claims - must be kept of file.CRC maintains a reasonably stable client base. For this privileged category of customers special credit card facilities are provided. These customers may also book in advance a particular car. These reservations can be made for any period of time up to one month. Casual customers must pay a deposit for an estimated time of rental, unless they wish to pay by credit card. All major credit cards care accepted. Personal details (such as name, address, telephone number, driving licence, number) about each customer are kept in the database.
Example 4
A database is to be designed for a college to monitor students' progress throughout their course of study. The students are reading for a degree (such as BA, BA(Hons) MSc, etc) within the framework of the modular system. The college provides a number of module, each being characterised by its code , title, credit value, module leader, teaching staff and the department they come from. A module is co-ordinated by a module leader who shares teaching duties with one or more lecturers. A lecturer may teach (and be a module leader for) more than one module. Students are free to choose any module they wish but the following rules must be observed: some modules require pre-requisites modules and some degree programmes have compulsory modules. The database is also to contain some information about students including their numbers, names, addresses, degrees they read for, and their past performance (i.e. modules taken a nd examination results).
Example 5 A relational database is to be designed for a medium sized Company dealing with indust industria riall applica applicatio tions ns of compute computers. rs. The Company Company delive delivers rs variou variouss product productss to its customers ranging from a single application program through to complete installation of hardware with customized software. The Company employs various experts, consultants and supporting staff. All personnel are employed on long-term basis, i.e. there are no shortshort-ter term m or tempor temporary ary staff staff.. Althoug Although h the Company Company is somehow somehow struct structure ured d for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter-disciplinary way. For each project a project team is selected, grouping employees from different departments, and a Project Manager (also an employee of the Company) is appointed who is entirely and exclusively responsible for the control of the project, quite q uite independently of the Company's hierarchy. The following is a brief statement of some facts an d policies adopted by the Company Compan y.
