Notes On Erd Design1

Entity Relationship Diagrams An entity-relationship diagram is a graphical depiction of organizational system elements and the association among the elements. E-R diagrams can help define system boundaries. The elements that make up a system are referred 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 Kendall text uses the Crow's Foot notation. Using this notation entities are represented by rectangles and relationships are indicated by lines connecting the rectangles. Cardinality is shown by a series of "tick marks" and "crows 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 but may not teach at all if on sabbatical. Assume there is no team teaching, therefore each section must have a single professor.

Finally, a more complex 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 department 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.

Weak Entity : Occasionally, entities of an entity set need “help” to identify them uniquely. Entity set E is said to be weak if in order to identify entities of 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 characteristics of an 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.

Relationships Relationships illustrate how two entities share information in the database structure. Learn how to draw relationships: First, connect the two entities, then drop the relationship notation on the line.

Cardinality Cardinality specifies how many instances of an entity relate to one instance of another entity. Ordinality is also closely linked to cardinality. While cardinality specifies the occurences 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 (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

Figure 2: One-to-many from customer to account

Figure 3: Many-to-one from customer to account

Figure 4: One-to-one from customer to account 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 instances. E.g. the employee John Jones, Mary Smith's payment, etc. 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. Synonyms include property, 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 METHODOLOGY: (One way of doing it)

Identify the roles, events, locations, tangible things or concepts about which the en 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 entities Determine the number of occurrences of one entity for a single occurrence of the r 4. Fill in Cardinality entity. Identify the data attribute(s) that uniquely identify one and only one occurrence of 5. Define Primary Keys entity. 6. Draw Key-Based ERD Eliminate Many-to-Many relationships and include primary and foreign keys in ea 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 for entities or relationships discovered in st 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 an entity, but it is a false entity 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. The primary key for Employee-Project is the concatenated key Employee 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, tangible things, or concepts. The best way 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 is enrolled in one or more courses subject verb objects Step 3. Draw Rough ERD Using rectangles for entities and lines for relationships, we can draw an Entity 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 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. 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 an indefinite number of fields to maintain the many-to-many relationship. Fortunately, by introducing an extra entity, called an associative entity for each many-tomany relationship, we can solve this problem. The new associative entity's name will be

the hyphenation of the names 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 before we introduced the associative entity. The original relationship between the parents will be deleted 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 name in its rectangle. Step 7. Identify Attributes A data attribute is a characteristic common to all or most instances of a particular 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 do 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 copy. Cross out those which will not be 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 Name, etc.) or determine which entity an attribute "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 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. Even if you have no new entities to add to the Key-Based ERD, you still need to add the attributes to the Non-Key Data section of each rectangle. Adding these attributes 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 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 (entities). In this case 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 two entities (e.g. Company and Employee) 2. Pick one that you’re interested in (e.g. how a single Company relates to employees) 3. Read the notation on the second entity (e.g. the crow’s feet with the O 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: •

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 is a crow’s feet with an O next to it, then the first entity can have zero, one, or many of the second 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 of the second entity. More specifically it may not contain zero of the second 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 can’t (and for various reasons probably shouldn’t if it could) be enforced by a database. Thus, you will only see these in logical, but not a physical, 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.” 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 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 through many notation on one side of a relationship and a one and only one or 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 data models as follows:

One-to-One Probably the least common and most misunderstood relationship is the one-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 to mistake for traditional one-to-many relationships, I have taken to drawing a red dashed

line around them. The red dashed 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. 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 patient is required to take a variety of drugs a certain number of times per day and for varying lengths of time. The system must record details concerning patient treatment and staff payment. Some staff 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 what treatments are required for which patients and when and it should be capable of calculating the cost 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 of 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. There is also an example of a simplified view of an airline on that page. 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, each division is managed by only one manager and each manager manages only one division

2. Among the automobile manufacturing companies, a company 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 may be books which are not borrowed by any member

5. A teacher teaches many students and a student is taught by 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 manager. 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 There is no standard for representing data objects in ER diagrams. Each modeling 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. Today, there are a number of notations used, among the more common are Bachman, crow's foot, and IDEFIX. All notational styles represent entities as rectangular boxes and relationships as 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.

•

existence is represented by placing a circle or a perpendicular bar on the 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 table sharing all of the columns of both. Alternatively, and 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 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 information on the arrival times of particular seismic phases might have a 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. 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 more tense than this, so that symbols can occur on each end in various 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 "crow's feet" on both ends. The problem is that such relationships cannot 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. belongs has M to model uses three separate entities and two INVOICE CUSTOMER LINE 1The E-R relationships to represent an invoice transaction. Because customers can buy more than one item at a time, each invoice references one or more lines, one item per line. And, because invoices are generated by customers, the data modeling requirements include a customer entity and a relationship between the customer and the invoice.

Example 1 :Take a simple purchase order application. customers, purchase orders, line items, and stock items.

Here the basic entities 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 is optional because zero 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 relationship with a Stock Item because a purchase order can refer to many 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 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 a simple manufacturing system. Here, we can have many entity sets. Let us limit the list to only six entity-sets, namely, Worker, Department, Project, Buyorder, Product, and Supplier. Below given is a sample E-R diagram drawn using Microsoft Visio software.

Below given is a completed E-R Diagram of a College Application:

advises member teaches has employs operates offers generates isused contains is Deanfor in found awritten Employee Student College Enroll Building Course Class Professor Room of of in Student_Num College_Code Building_Code Course_Code Class_Code Emp_ID Room_Code

Room_Type Stu_LName College_Name Bldg_Location Crs_Title Class_time Emp_LName (FK) Stu_FName Student_Num Crs_Descrip Emp_ID Emp_FName Building_Code (FK) Stu_Initial Crs_Credits Emp_Initial (FK) (FK) Stu_Email Enroll_Date Dept_Code Course_Code Emp_Email Stu_Mobile Enroll_Grade Emp_Mobile (FK) (FK) Emp_ID (FK) Dept_Code(FK)

Department Dept_Code Dept_Name College_Code (FK) Emp_ID (FK)

Student 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 works with on editor, but may submit another work for publication 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 Hospital consists of a number of specialized wards (such as Maternity, 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 a description of cars, subcontractors (i.e. garages), company expenditures, 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, addressees, range of services and the like. Some garages require payments 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 and examination results).

Example 5 A relational database is to be designed for a medium sized Company dealing with industrial applications of computers. The Company delivers various products 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 short-term or temporary staff. Although the Company is somehow structured 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 independently of the Company's hierarchy. The following is a brief statement of some facts and policies adopted by the Company.