Entity relationship diagram question and solution

Simple Order System Entity Relationship Diagram Example

entity relationship diagram question and solution

Translation of a (Normal Form) ER Diagram to a RDB .. Another example of Identifier-dependency relationship type country One solution is use aggregation. attributes. Answer: 7:Extend the E-R diagram of the previous question to track the same information for all teams in a league. Answer. Entity Relationship Modeling Examples Earlier in this chapter, we showed you how to design a database and understand an Entity Relationship (ER) diagram.

Each isntance becomes a record or a row in a table. For example, the student John Smith is a record in a table called students. Relationships Relationships are the associations between the entities.

entity relationship diagram question and solution

Verbs often describe relationships between entities. We will use Crow's Foot Symbols to represent the relationships. Three types of relationships are discussed in this lab. If you read or hear cardinality ratios, it also refers to types of relationships. One to One Relationship 1: Each student fills one seat and one seat is assigned to only one student.

Each professor has one office space. One to Many Relationship 1: M A single entity instance in one entity class parent is related to multiple entity instances in another entity class child For example: One instructor can teach many courses, but one course can only be taught by one instructor. One instructor may teach many students in one class, but all the students have one instructor for that class.

Many to Many Relationship M: M Each entity instance in one entity class is related to multiple entity instances in another entity class; and vice versa. Each student can take many classes, and each class can be taken by many students.

Entity-Relationship Diagram (ERD)

Each consumer can buy many products, and each product can be bought by many consumers. The detailed Crow's Foot Relationship symbols can be found here.

Crow's Foot Relationship Symbols Many to many relationships are difficult to represent. We need to decompose a many to many M: M relationship into two one-to-many 1: Attributes Attributes are facts or description of entities.

entity relationship diagram question and solution

They are also often nouns and become the columns of the table. For example, for entity student, the attributes can be first name, last name, email, address and phone numbers.

  • Entity-relationship modelling
  • Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
  • Entity Relationship Diagram Example: Simple Order System

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.

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.

entity relationship diagram question and solution

Often a single field or property is a primary key e. Other times the identifier is a set of fields or attributes e.

Introduction to Entity-relationship modelling

When the entity is written with all its attributes, the primary key is underlined. 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-to-many 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 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.

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.

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.

entity relationship diagram question and solution

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.