Conceptual Data Modeling Using E-R Diagrams
PM Jat
pm_jat@[Link]
Summary
Entity Type: Description of an Entity. For example:
Student(ID, Name, Batch, CPI)
Entity: for example a student
{ID:200701001, Name:Charu Chawla, Batch:2007, CPI: 6.12}
Entity Set: set of all entitities belonging to a type, for example all students
17-Aug-12
Data Modeling Using E-R Diagrams
Entity-Set
{ {ID:200701001,Name:Charu,Batch:2007, CPI:6.12}, {ID:200711002,Name:Amit Khanna,Batch:2007,CPI:7.12}, {ID:200711003,Name:Kamla Kiran,Batch:2007,CPI:7.50}, {ID:200711004,Name:Raj Kumar,Batch:2007,CPI:4.00}, {ID:200711005,Name:Raj Tiwari,Batch:2007,CPI:5.56}, {ID:200811001,Name:Rama Kant,Batch:2008,CPI:8.12}, {ID:200811002,Name:Akshya,Batch:2008,CPI:9.22}, {ID:200811003,Name:Unnati Gupta,Batch:2008,CPI:5.52} }
17-Aug-12
Data Modeling Using E-R Diagrams
Entity Set with entities having Multi-value attributes in
{ {ID:200701001,Name:Charu,Batch:2007, email:{charu@[Link], charu_x@[Link]},CPI:6.8}, {ID:200711002,Name:Amit Khanna,Batch:2007, email:{amil@[Link]}, CPI:7.12}, {ID:200711003,Name:Kamla Kiran,Batch:2007, email:{},CPI:7.50}, ... }
17-Aug-12 Data Modeling Using E-R Diagrams 4
Entity Set with entities having composite attributes
{ {ID:200701001,Name:{Fname:Charu,Minit:K, Lname:Chawla}, Batch:2007, CPI:6.8}, {ID:200711002, Name:{Fname:Amit,Minit:C, Lname:Patel},Batch:2007,CPI:7.12}, {ID:200711003, Name:{Fname:Kamla,Minit:S, Lname:Kiran},Batch:2007,CPI:7.50}, ... }
17-Aug-12
Data Modeling Using E-R Diagrams
Observe interpretation of following two representation of same entity
{ID:200701001, Name:{Fname:Charu,Minit:K, Lname:Chawla}, Batch:2007, CPI:6.8},
{ID:200701001, Fname:Charu,Minit:K, Lname:Chawla, Batch:2007, CPI:6.8},
17-Aug-12
Data Modeling Using E-R Diagrams
Relationship - Schema
Schema Diagram (ERD) tells (intention) that instance of one entity type is to be related with instances of other entity type
17-Aug-12
Data Modeling Using E-R Diagrams
Relationship - instance
When you say Aditi studies in BIT, it is one instance of studies relationship; and It is interpreted as a student entity identified by Aditi is related with a program entity identified by BIT through studies relationship.
17-Aug-12
Data Modeling Using E-R Diagrams
Relationship - instance
Example: entities related to another entities
When an entity is associated with other entity in the relationship type, then it is said to be participating in the relationship
17-Aug-12 Data Modeling Using E-R Diagrams 9
Relationship
When an entity is associated with other entity in the relationship type, the it is said to be participating in the relationship
17-Aug-12
Data Modeling Using E-R Diagrams
10
Exercise
Identify Entities and relationships in company schema
17-Aug-12
Data Modeling Using E-R Diagrams
11
17-Aug-12
Data Modeling Using E-R Diagrams
12
Cardinality Constraints in ERD
17-Aug-12
Data Modeling Using E-R Diagrams
13
Examples: Cardinality Constraints
A student can study in only one program, where as a program can have any number of students An Employee works for only one department, where as a department can have any number of employee. An Employee can work on any number of Project, and a Project can have any number of employees working on it. ==>These are examples of business rules that are specified as part of schema.
17-Aug-12 Data Modeling Using E-R Diagrams 14
Cardinality Constraints in ERD
Cardinality Constraints are specified to one of following -
One to One (1:1) One to Many (1:N) Many to Many (M:N)
Cardinality Constraints are also called Cardinality Ratio (Elmasri/Navathe), though not ratio in precise terms. Probably, because it written in ratio format (1:N)! We will use the term Cardinality Constraints
17-Aug-12 Data Modeling Using E-R Diagrams 15
Cardinality Constraints in ERD
One to one (1:1): When entities of both types can be related to atmost one from entity of other type For example, Manages relationship in company schema: One department can have atmost one employee as manager, and one employee can be manager of at-most one department 1:1 is not very common type of relationship.
17-Aug-12 Data Modeling Using E-R Diagrams 16
Cardinality Constraints in ERD
One to Many (1:N): When entities of one types can be related to atmost one from entity of other type, and other can be related to any number (N) of entities from first type. For example, Works_For relationship in company schema: One employee works for only one department, but one department can have any number of employees. Other examples are Student Studies Program, and Department Offers Programs in XIT schema.
17-Aug-12 Data Modeling Using E-R Diagrams 17
Cardinality Constraints in ERD
Many to Many (M:N): When entities of both types can be related to any number (N) of entities from other type. For example, Works_On relationship in company schema: An employee can work on any number of projects and a project can have any number of employees working on.
17-Aug-12
Data Modeling Using E-R Diagrams
18
One to One (1:1)
One Customer has one account and one account is owned by one customer In other words: a customer entity can atmost relate to one account entity, and an account entity can atmost relate one customer entity
17-Aug-12
Data Modeling Using E-R Diagrams
19
One to Many (1:N)
One Customer has many accounts, and an account is owned by one customer In other words: A customer entity can relate to N account entities, while an account entity can relate to atmost one customer entity
17-Aug-12
Data Modeling Using E-R Diagrams
20
One to Many (1:N)
Reverse of previous is also 1:N; i.e. One Customer can have only one accounts, but one account is owned by multiple customers In other terms: a customer entity can relate to atmost one account entity, while an account entity can be related to many customer entities Some people, though, call it many to one relationship (N:1), but this is just saying of same thing (1:N) other way round just flip positions of entities !
17-Aug-12 Data Modeling Using E-R Diagrams 21
Many to Many (M:N)
One Customer has many accounts, and an account can be owned by multiple customers jointly
17-Aug-12
Data Modeling Using E-R Diagrams
22
Identify cardinality constraint
Bill has Item(s) Student has one of Professor as Mentor Course uses Text Book(s), and one text book could be used in multiple courses A city is in a Country, and Each Country has one of its city as its capital
17-Aug-12 Data Modeling Using E-R Diagrams 23
Participation Constraints
17-Aug-12
Data Modeling Using E-R Diagrams
24
Participation Constraint
Participation Constraint can be Total (mandatory) or Partial (optional) If participating of an entity in a relationship is mandatory, then it is total participation otherwise, it is partial. For example, consider following Can a Student Entity exist without related with Program or without participating in studies relation. Can Employee exists without associated with a department as an employee Can an employee exist without becoming manager of some department If answer of any question above is yes, then participation of the entity in the relationship is Partial, otherwise it is Total
17-Aug-12 Data Modeling Using E-R Diagrams 25
Participation Constraints
In our Customer-Account example If customer record can exist in database without having associated with an account then participation of customer is partial otherwise total. Can a customer exist without having (associated with) an account? Can an account exist without having (associated with) a customer?
Partial Participation Total Participation
17-Aug-12
Data Modeling Using E-R Diagrams
26
Student-Program schema
Participation of Student in Studies relationship is mandatory student has to study in a program. Participation of a Program can be optional we may not take admission in a program.
Total Participation
17-Aug-12
Data Modeling Using E-R Diagrams
Partial Participation
27
Most of the time figuring out cardinality and participation constraints are easy but sometimes may become hard. Relationship Instance Diagram should helps in visualizing and figuring out these constraints
17-Aug-12
Data Modeling Using E-R Diagrams
28
Relationship diagram should help in better visualization of Cardinality and Participation constraints
17-Aug-12
Data Modeling Using E-R Diagrams
29
Exercise
Company Database (Elmasri/Navathe)
17-Aug-12
Data Modeling Using E-R Diagrams
30
Recursive Relationship
Entity having relationship with itself There can be many situations like this Employee supervises another employee One course has pre-requisite of another course One category is parent of another category One citizen is spouse of another citizen
17-Aug-12
Data Modeling Using E-R Diagrams
31
17-Aug-12
Data Modeling Using E-R Diagrams
32
Example - Recursive Relationship
Employee supervises another employee
Can you find out cardinality of this relationship?
17-Aug-12 Data Modeling Using E-R Diagrams 33
Instance diagram - Recursive Relationship
Employee
r1: e5 is supervisor of e1 r2: e1 is supervisor of e2 r3: e1 is supervisor of e2 r4: e5 is supervisor of e4 r5: e4 is supervisor of e6 r6: e4 is supervisor of e7
e1 e2 e3 e4 e5 e6 e7 r1 r2 r3 r4 r5 r6
Supervises
Roles: Supervisor (Green), Subordinate (Red)
17-Aug-12 Data Modeling Using E-R Diagrams 34
Cardinality constraints in Supervises relationship..
Employee supervises another employee
17-Aug-12
Data Modeling Using E-R Diagrams
35