Topic 03 & 04 : Entity Relationship Modelling

50 %
50 %
Information about Topic 03 & 04 : Entity Relationship Modelling
Education

Published on December 11, 2013

Author: PradipKharbuja

Source: slideshare.net

Description

Entity Relationship Modelling

Topic 3 & 4 Entity Relationship Modelling Er. Pradip Kharbuja

Entity Relationship(ER) Diagram  We will learn to develop ER Diagram in this topic.

Entity  An entity can be defined as anything, which can be uniquely identified and about which data is stored in a database.  A place, person, picture, concept (e.g. course, position, job) are the example of an entity.  Some other examples can be student, workers, car, departments, etc.

Entity Type  An entity type is a collection of similar entities.  An entity-type is a category.  An entity type is a group of objects, people, concepts or other items that have the same set of properties or attributes.

Entity Relationship(ER) Modelling  In 1976, Peter Chen first proposed modeling databases using a graphical technique that humans can relate easily.  An Entity–Relationship (ER) model is an abstract or conceptual way of describing a database  An ER Model is based on a perception of a real world that consists of collection of basic objects called entities and relationships among these objects.

Entity Relationship(ER) Modelling  The ER model achieves a high degree of data independence which means that the database designer do not have to worry about the physical structure of the database.  The goal of ER Modelling is to produce a model of data that is nontechnical and free of ambiguities

Entity Relationship(ER) Diagram  ER diagram is a graphical modelling to standardize ER modelling.  The modeling can be carried out with the help of pictorial representation of entities, attributes, and relationships.

Attributes  An attribute is a property of an entity type.  The data that we want to keep about each entity within an entity type is contained in attributes.  Examples of Attributes : 1. Roll number, name and grade are the attributes of Student. 2. Brand, cost and weight are the attributes of Cellphone. 3. Book name, price, author, publisher, etc. are attributes of Book.

Types of Attributes 1. Single-valued attribute 2. Multi-valued attribute 3. Simple Attribute 4. Composite Attribute 5. Key Attribute 6. Derived Attribute 7. Attribute Domain

Types of Attributes 1. Single-valued attribute : Attribute that holds a single value for an occurrence of an entity type. E.g. age, first_name, last_name, full_name, etc. 2. Multi-valued attribute : Attribute where there might be more than one value for a given occurrence of an entity type, e.g. for the 'Telephone Number', where a person or company might have many of these.

Types of Attributes 3. Simple Attribute It is composed of a single component. e.g. age, first_name, last_name, etc. But full_name is not simple attribute. Because it is composed of first_name and last_name. 4. Composite Attribute It is composed of more than one component. For example, 'address' might have city, state, country, zip code, etc.

Types of Attributes 5. Key Attribute Primary Key is a key attribute. Not Null and Unique attribute of an entity At most one attribute in each entity 6. Derived Attribute The value of the derived attribute can be derived from the values of other attributes Age of a person can be derived from the date of birth and current date i.e. age = current date – date of birth.

Types of Attributes 7. Attribute Domain A domain is the set of allowable values for an attribute or number of attributes. A domain therefore limits the values that an attribute can have. For example, the domain of 'gender' would include the values 'Male' and 'Female'. Similarly, the domain of fruit would include the values 'Apple', 'Orange' etc.

Relationship  A relationship is an association of entities showing how the entities are related with each other. The examples of relationship are:  Teaching is the relationship between Lecturer and Student.  Buying is the relationship between Vendor and Customer.  Treatment is the relationship between Doctor and Patient.

Types of Relationship or Multiplicity  Number or range of possible occurrences of an entity type in relation to another entity type 1. One-to-One (1 : 1) 2. One-to-Many (1 : N) 3. Many-to-One (N : 1) 4. Many-to-Many (M : N)

Types of Relationship 1. One-to -One (1 : 1) Each department can have only one department head. Each person can have only one age. 2. One-to-Many (1 : N) One course can have many students registered. A lecturer may be teaching to many students.

Types of Relationship 3. Many-to-One (N : 1) Many cities come under one country. Many books can be written by one writer. Many authors can write one book. 4. Many-to-Many (M : N) An Author can write several Books, and a Book can be written by several Authors A teacher teaches many class and a class studies from many teachers.

ER Diagram Notation (CHEN) Represents an entity set Represents an attribute Represent linking of attributes to entity sets and of entity sets to relationship sets. Represents a relationship

ER Diagram

ER Diagram  Draw an ER Diagram for ClassRoom which has two attributes : room_no and building_name.

ER Diagram

Task : Draw ER Diagram in MS-Visio  Email the visio file within one week.

Multiplicity  A teacher teaches only one subject.  A teacher teaches many subjects.

Classification of Entity Sets (CHEN)  Entity sets can be broadly classified into: Strong entity Strong entity is one whose existence does not depend on other entity. Weak entity Weak entity is an entity that depends on another entity. Weak entity doesn't have key attribute of their own. Associative or composite entity Associative entity is used to create many-to-many relationship between different entities

Classification of Entity Sets (CHEN)  Consider the example, student takes course. Here student is a strong entity.  Course is considered as weak entity because, if there are no students to take a particular course, then that course cannot be offered. The Course entity depends on the student entity.

Associative or Composite entity  The database relational model does not offer direct support to manyto-many relationships. So, we require associative or composite entity.  Consider the example, customer purchases book.  A customer can purchase many books. And a book can be purchased by many customers. So, here purchases is an associative entity. The entity purchases is mediator for customer and book.

Attribute Notation (CHEN) Single Valued Attribute Multi-valued Attribute Key Attribute Derived Attribute Composite Attribute

Multi-valued Attribute  An employee has full name, age and can have more than one qualifications.

Key Attribute  Primary Key  A department has Primary Key dept_no, department_name and location

Derived Attribute  The value of the derived attribute can be derived from the values of other related attributes.  Age of a person can be derived from the date of birth and current date i.e. age = current date – date of birth. In this example, age is the derived attribute.

Composite Attribute  Composite attribute is one which can be further subdivided into simple attributes.

Task : ER Diagram 1. Draw an ER diagram for student. 2. Draw an ER diagram for customers and loan relation. 3. Draw an ER diagram for hospital with a set of patients and medical doctors. 4. Draw an ER diagram for banking system. 5. Draw an ER diagram for car insurance company.

Relationship Degree  Relationship degree refers to the number of associated entities in a relationship.  The relationship degree can be broadly classified into 1. Unary(Recursive) relationship 2. Binary relationship 3. Ternary relationship

Unary(Recursive) relationship  The number of associated entity is one.  An entity related to itself is known as recursive relationship.

Binary relationship  In a binary relationship, two entities are involved.  Consider the example: each department will have many workers.

Ternary relationship  In a ternary relationship, three entities are simultaneously involved.  Example : Consider a project is assigned to many employee. And the project is assigned to one location.

ER Diagram Notation (CROW'S FEET) Entity Relationship Entity Name Illustrates an association between two entities. It has a name which is a verb. It also has cardinality. Entity Name PK attribute name attribute name attribute name is assigned to

ER Diagram Notation (CROW'S FEET) Cardinality Cardinality refers to the maximum number of times an instance in one entity can be associated with instances in the related entity. It can be zero or one or more. zero or more one or more one and only one zero or 1

Some examples (CROW'S FEET) course teacher teaches course student takes

ER Diagram Notation (UML) A Plane Ticket System  A passenger may book many tickets.  Each ticket is for one flight and one passenger.  A flight has many passenger. 1 Passenger 0..N 0..N Ticket 1 Flight

Many-to-Many Relationship  Module having many Students and Students taking many Modules 0..N Module Attributes ModuleID(PK) ModuleName 0..N Taken By Student Attributes StudentID(PK) StudentName

Many-to-Many Relationship Module Attributes ModuleID(PK) ModuleName 1 0..N 0..N ModuleStudent Attributes ModuleID(FK) StudentID(FK) 1 Student Attributes StudentID(PK) StudentName

ER Diagram Notation (UML)  A Book is written by one or more authors. An author writes one or more Books. A Category may have zero or many books.  [Note : Many to Many Relationship cannot be shown in relational model] Category 1 0..N 1 Book 1..N 1..N Book Author 1 Author

A Gardening Company  The company hires out workers as they need to.  Workers in the town stay in lodgings. A lodging may have many workers in it. We are only concerned with the workers’ current lodging. Workers possess certain skills such as ‘Tree Surgery’ and ‘Garden Design’. They may have more than one skill.  [Note : Try to identify nouns that could possibly be entities.]

A Gardening Company - Nouns  The company hires out workers as they need to.  Workers in the town stay in lodgings. A lodging may have many workers in it. We are only concerned with the workers’ current lodging. Workers possess certain skills such as ‘Tree Surgery’ and ‘Garden Design’. They may have more than one skill.  [Note : We are only concerned with one town. So, town is not entity here. But 'Town' is an attribute of the Lodging.]

Possible Solution 1 Worker 0..* Worker Skill 0..* 1 Lodging 0..* 1 Skill

Primary Key  The PRIMARY KEY constraint uniquely identifies each record in a database table.  Primary keys must contain unique values.  A primary key column cannot contain NULL values.  Each table should have a primary key, and each table can have only ONE primary key. departments -----------------------dept_no (PK) department_name location

Foreign Key  A FOREIGN KEY in one table points to a PRIMARY KEY in another table  A FOREIGN KEY is a column or a combination of columns that is used to establish and enforce a link between two tables or entities. departments -----------------------dept_no (PK) department_name location workers -----------------emp_no (PK) first_name last_name age job_title dept_no (FK)

Creating Foreign Key

A Problem  A campus may have one or many staffs. Also a campus may have one or many department. Staff 1..* 1 Campus 1 1..* Department

A Problem Campus Staff Campus ID Campus Name Staff ID Name Campus ID (FK) C1 Softwarica S1 Eric Hudson C2 C2 Islington S2 Sandra Smith C1 C3 The British College S3 Malia Shah C1 Department Department ID Name Campus ID (FK) D1 Humanities C1 D2 Computing C2 D3 Maths C2 Now the question is : In which Department does Eric work?

The Solution The solution is to adopt a different structure where we have a Campus having one or more Departments which in turn have one or more Staff. Staff 1..* 1 1..* Department 1 Campus

The Solution Campus Staff Campus ID Campus Name Staff ID Name Department ID (FK) C1 Softwarica S1 Eric Hudson D3 C2 Islington S2 Sandra Smith D1 C3 The British College S3 Malia Shah D1 Department Department ID Name Campus ID (FK) D1 Humanities C1 D2 Computing C2 D3 Maths C2 Now do you know in which Department does Eric work?

Presentation  Fan Traps  Chasm Traps  Pronunciation /ˈkaz(ə)m/

Problems with ER Models  There are several problems that may arise when designing a ER model. These are known as connection traps.  There are two main types of connection traps: 1. Fan traps 2. Chasm traps

Fan Traps  Fan traps occur in a situation when a model represents relationship between entity types however a path between certain entity occurrences is ambiguous. Staff 1..* 1 Campus 1 1..* Department  Example: (Staff)-1:N-works in-1-(Campus)-1-has-1:N-(Department).  In this model, it may be impossible to determine the department a staff belongs to.

Fan Traps – The Solution  Restructuring the model resolves trap (Campus)-1-has-1:N(Department)-1-has-1:N-(Staff) Staff 1..* 1 1..* Department 1 Campus

Chasm Traps  Chasm Traps occur where there are relationships between entities, but one of the relationships is non-mandatory.  A model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences. This connection traps is known as Chasm Traps.  http://db.grussell.org/section005.html

Chasm Traps – The Problem  A branch has many staff members who manage properties, but not all properties are managed by a member of staff, and not all staff manage properties. Branch 1 1...* Staff 0...1 0...* Property

Chasm Traps – The Problem Staff Branch Branch ID 1 2 Staff ID S1 Name Davinda Branch ID (FK) 1 S2 S3 Branch Name Ilford Redbridge Roberta Eddie 1 2 Property Property ID P1 Property Name Yap Mansions Staff ID (FK) S1 P2 P2 Hill House Usher House S2 Which Branch manages ‘Hill House’? Because Hill House is not managed by a member of Staff, we do not know from which Branch that Property is managed.

Chasm Traps – The Solution  The solution is to change the structure and represent both relationships. 1 1...* Staff 0...1 0...* Property Branch 1 0...*

Chasm Traps – The Solution Branch Branch ID 1 2 Staff Branch Name Ilford Redbridge Staff ID S1 S2 S3 Property Name Branch ID (FK) Davinda 1 Roberta 1 Eddie 2 Property ID Property Name Staff ID (FK) Branch ID (FK) P1 P2 Yap Mansions Hill House S1 1 2 P2 Usher House S2 2 We now know which Branch manages ‘Hill House’

Draw the ER : Car Hire Company  Company has a number of models of car available for hire.  Customers hire cars and this is recorded against a particular car rather than a model, as the company might have several cars of the same model.  When a customer wants to reserve a particular model they can do so. This is recorded against a model rather than the car.  When a car of that model becomes available, it will be held for the customer.  When they come in for it, a record is made on the reservation to say which car satisfied the reservation.

Draw the ER : Car Hire Company  Company has a number of models of car available for hire.  Customers hire cars and this is recorded against a particular car rather than a model, as the company might have several cars of the same model.  When a customer wants to reserve a particular model they can do so. This is recorded against a model rather than the car.  When a car of that model becomes available, it will be held for the customer.  When they come in for it, a record is made on the reservation to say which car satisfied the reservation.

Car Hire Company 1 0..N Car 0..N 1 Hire 0..N 1 Customer 0..N 1 1 Model 0..N Reservation 0..N 1

Task 1. A customer records systems for a mail order beauty products company. A customer is assigned to one and only one geographical region. A customer may be interested in a number of different product lines. Any particular product line belongs to one product category that may contain many product lines. 2. Aboat is rented toa customer for a set periodof time. Any damage to the boat is recorded for that particular rental. 3. The shop needs to keep track of rentals. A member can rent films. A film can be rented by many members. A film can be rented by the same member more than once.

References  http://www.studytonight.com/dbms/er-diagram.php  http://www.blackwasp.co.uk/SQLDerivedTables.aspx  http://www2.cs.uregina.ca/~bernatja/crowsfoot.html  http://revj.sourceforge.net/traps.html  http://db.grussell.org/section005.html

END OF TOPIC 3 & 4 Any Questions

Add a comment

Related presentations

Related pages

Talk:Entity–relationship model - Wikipedia, the free ...

... (talk • contribs) 04:04, 15 July 2011 (UTC) ... Entity Relationship Modelling Principles ... It is based on Peter Chen's view of this topic.
Read more

Entity Relationship (ER) Modeling - Learn With a Complete ...

Entity Relationship (ER) Modeling ... previous topic if you have not done so before proceeding further. Here we are going to design an Entity Relationship ...
Read more

Entity Relationship Diagram - Common ERD Symbols and Notations

An entity relationship diagram is a means of visualizing how the information a system produces is ... Describe the relationship. How are the entities related?
Read more

Conceptual Modeling CSC2507 IV. The (Extended) Entity ...

2004 John Mylopoulos The Extended Entity-Relationship Model -- 1 Conceptual Modeling CSC2507 IV. The (Extended) Entity-Relationship Model
Read more

Topic 1 Discussion Topic: Database Design Topic 2 ...

Topic 2 Discussion Topic: Enhanced Entity Relationship Concepts Provide an example of the use of supertype and ... Enhanced Entity Relationship Modeling
Read more

Topic 2 Business Entity - Education

Topic 03 & 04 : Entity Relationship Modelling. VIVO 2013 Topic Modeling Entity Extraction. 2 Entity Relationship Model.
Read more

CHAPTER 3: DATA MODELING USING THE ENTITY-RELATIONSHIP MODEL

CHAPTER 3: DATA MODELING USING THE ENTITY-RELATIONSHIP MODEL Answers to Selected Exercises
Read more

Data modeling - Wikipedia, the free encyclopedia

Data modeling (or modelling) in ... Entity relationship diagrams. Example of an IDEF1X Entity relationship diagrams used to model IDEF1X itself. The name ...
Read more

Domain modeling with Entity Framework scorecard | Jimmy ...

Domain modeling with Entity Framework ... But what about Entity ... internal virtual” AND explicitly create the HasMany relationship for the ...
Read more