3. e r model - weebly

3.

E – R Model

2. E-R Model

Entity-Relationship Model

Graphical Representation of Database

Equivalent to Flow-Chart in Programming

It makes easy to understand Database

Prior Step to implement Actual Database

Designer’s First Choice

It consist…

Entity Sets

Relationship Sets

Attributes

Entity

Entity is a “thing” or “object” which is unique in the world

e.g. each student in the college is an entity

Entity has a set of properties called attributes or values for

some set of properties may uniquely identify an entity.e.g. each student having rollno, name , sem, branch, ph, address, age

Entity Set is a set of entities of the same type that

share the same attributes or propertiese.g. the set of all the students having the

same attributes is an entity set

Attributes

Nature of the things or objects

An entity is basically represented by a set of Attributes

An each entity set has the entities of having the same

Attributes

Each entity has the value for each of its Attribute

For each attribute, there is a set of permitted values called

Domain or Value Set

Each entity in an entity set described by a set of

(attribute, data value) pairs

Attribute Types

Simple and Composite

Simple(Atomic) attributes are not divided into subparts e.g. custid

Composite attributes are divided into subparts

e.g. cust-name (firstname, middlename, lastname)

Single-valued and Multi-valued

Single-valued attribute has only one value from specific domain

Multi-valued attribute has zero, one or more values from specific

domain.( 2 phone no. per person)

Stored & Derived

Value of Derived attribute can be derived from values of other

attributes (stored).e.g. DOB & age

E-R Diagram With Composite, Multivalued,

and Derived Attributes

id

E-R Diagram Symbols

customer loanborrower

namestreet

city

number amount

m:n

1:n

n:1

1:1

entity set attribute relationship set

primary key

multivalued

derived

weakTotal participation

Relationship Set

Logical connectivity or Association among several entities

“Relationship Set” (Relation) is a

set of relationships of the same type

If E1, E2, …,En are entity sets,

Then a relationship set R is a subset of

{(e1, e2, …,en) | e1 E1, e2 E2, …, en En}

Where (e1, e2, …,en) is a relationship

Role of an Entity in Relationship

Function of an entity in the relationship is called “Role”

Same entity can participate in a relationship with a different roles.

Such type of relationship set is called recursive relationship set, in

which explicit role names are necessary to specify

employee.employee

Id Name

1 Anand

2 Milind

3 Suraj

4 Ketan

5 Rajesh

6 Manish

7 Jay

manager.employee

Id Name

5 Rajesh

7 Jay

work-for

Relationship’s Attribute

Descriptive attribute

e.g. ‘depositor’ relationship set with entity sets ‘customer’ and

‘account’. Set the access-date with depositor relationship to

specify most recent data on which customer accessed an account

customer accountdepositor

access-datename balance

Recursive Relationship

Same entity participate more than once in relationship type

with different roles

e.g. ‘supervision’ relationship set relates an ‘Employee’ Set to a

supervisor, where both employee & supervisor entities are members

of same employee entity type. So employee participate in 2 role, one

as boss & other supervisee.

Employee Supervision

supervisor

supervisee N

1

Mapping Cardinalities

Best Beneficial for describing “Binary” Relationship

Relationship set = R entity sets = A & BOne to one:

An entity in A is associated with at most one entity in B and visa versa

Employee---manages--dept

One to many or Many to one:

An entity in A is associated with any no.(zero or more) of entities in B

but an entity in B is associated with at most one entity in A

Employee ---works for--deptMany to Many:

An entity in A is associated with any no. (zero or more) of entities in B

and visa versa employee—works on--projects

Mapping cardinalities, or cardinality ratios, express the number of

entities to which another entity can be associated via a relationship set

Mapping Cardinalities

a1

a2

a3

a4

b1

b2

b3

b4

A B

a1

a2

a3

a4

b1

b2

b3

b4

a1

a2

a3

b1

b2

b3

b4

a1

a2

a3

a4

b1

b2

b3

one:one many:many

many:oneone:many

1

2 3

4

A B

A BA B

Participation Constraints

Total Participation

Partial Participation

Entity set E in a relationship set R is said to be a total

If every entity in E participates in at least one relationship in R

If some entities of E participate in Relationships in R,

then the participation of E is said to be partial

shown by single line)

customer loanborrower

E-R Diagram with a Ternary

Relationship

University ER Diagram

Student

Class

StudentID SSN

Sex

Zip

Degree

City

Birth date

State

Name

Address

Department

DName DCode OfficeNumber

OfficePhone

College

Course

CName

CourseDesc

CNumber

CreditsSection

Instructor Year

SemesterSectionNumber

GPA

Numeric Grade

Letter Grade

Grade_Report

Belong_To

Offer

Minor In

Major In

Keys

Allows us to uniquely identify an Entity in an Entity Set

Super Key

Candidate Key

Set of one or more attributes, taken collectively, allow us to use as

key of an Entity Set

Made up of set of one or more attributes of an Entity Set

e.g. {id} is a superkey for „customer‟ entity set

e.g. {name, street} collectively is also a superkey for „customer‟ entity set

K is a superkey then superset of K is also a superkey

Superkey, for which, no proper subset is a superkey

e.g. {id} is a candidate key for customer

e.g. {name,street} also acts as a candidate key

Keys (Cont…)

Primary Key

A candidate key which is chosen as a key of an Entity Set by

Database Designer as the principal means of identifying entities

within an Entity Set is called a Primary Key

e.g. {id} acts as a primary key for customer

A Key (primary, candidate, super) is a property of the Entity

Set, rather than of an individual entities

Primary key should be chosen as set of attributes which are

never or very rarely changed

Keys

• A super key of an entity set is a set of one or more attributes

whose values uniquely determine each entity.

• A candidate key of an entity set is a minimal super key

– Customer_id is candidate key of customer

– account_number is candidate key of account

• Although several candidate keys may exist, one of the

candidate keys is selected to be the primary key.

Keys for Relationship Sets

• The combination of primary keys of the participating entity sets

forms a super key of a relationship set.

– (customer_id, account_number) is the super key of depositor

– NOTE: this means a pair of entity sets can have at most one

relationship in a particular relationship set.

• Example: if we wish to track all access_dates to each

account by each customer, we cannot assume a

relationship for each access. We can use a multivalued

attribute though

• Must consider the mapping cardinality of the relationship set when

deciding what are the candidate keys

• Need to consider semantics of relationship set in selecting the

primary key in case of more than one candidate key

Weak Entity Sets

• An entity set that does not have a primary key is referred to as a

weak entity set.

• The existence of a weak entity set depends on the existence of a

identifying entity set

– it must relate to the identifying entity set via a total, one-to-

many relationship set from the identifying to the weak entity

set

– Identifying relationship depicted using a double diamond

• The discriminator (or partial key) of a weak entity set is the set of

attributes that distinguishes among all the entities of a weak

entity set.

• The primary key of a weak entity set is formed by the primary key

of the strong entity set on which the weak entity set is existence

dependent, plus the weak entity set’s discriminator.

Weak Entity Sets (Cont.)

• We depict a weak entity set by double rectangles.

• We underline the discriminator of a weak entity set with

a dashed line.

• payment_number – discriminator of the payment entity

set

• Primary key for payment – (loan_number,

payment_number)

Weak Entity Sets (Cont.)

• Note: the primary key of the strong entity set is

not explicitly stored with the weak entity set,

since it is implicit in the identifying

relationship.

• If loan_number were explicitly stored, payment

could be made a strong entity, but then the

relationship between payment and loan would

be duplicated by an implicit relationship

defined by the attribute loan_number common

to payment and loan

More Weak Entity Set Examples

• In a university, a course is a strong entity and

a course_offering can be modeled as a weak

entity

• The discriminator of course_offering would be

semester (including year) and section_number

(if there is more than one section)

• If we model course_offering as a strong entity

we would model course_number as an

attribute.

Then the relationship with course would be

implicit in the course_number attribute

Hotel

mgmt

Extended E-R Features: Specialization

• Top-down design process; we designate

subgroupings within an entity set that are

distinctive from other entities in the set.

• These subgroupings become lower-level entity sets

that have attributes or participate in relationships

that do not apply to the higher-level entity set.

• Depicted by a triangle component labeled ISA (E.g.

customer “is a” person).

• Attribute inheritance – a lower-level entity set

inherits all the attributes and relationship

participation of the higher-level entity set to which

it is linked.

Specialization Example

Extended ER Features: Generalization

• A bottom-up design process – combine a

number of entity sets that share the same

features into a higher-level entity set.

• Specialization and generalization are simple

inversions of each other; they are represented

in an E-R diagram in the same way.

• The terms specialization and generalization

are used interchangeably.

Specialization and Generalization

(Cont.)

• Can have multiple specializations of an entity set

based on different features.

• E.g. permanent_employee vs.

temporary_employee, in addition to officer vs.

secretary vs. teller

• Each particular employee would be

– a member of one of permanent_employee or

temporary_employee,

– and also a member of one of officer, secretary,

or teller

• The ISA relationship also referred to as

superclass - subclass relationship

Aggregation

Consider the ternary relationship works_on, which we saw earlier

Suppose we want to record managers for tasks performed by an

employee at a branch

Aggregation (Cont.)

• Relationship sets works_on and manages represent overlapping

information

– Every manages relationship corresponds to a works_on relationship

– However, some works_on relationships may not correspond to any

manages relationships

• So we can’t discard the works_on relationship

• Eliminate this redundancy via aggregation

– Treat relationship as an abstract entity

– Allows relationships between relationships

– Abstraction of relationship into new entity

• Without introducing redundancy, the following diagram represents:

– An employee works on a particular job at a particular branch

– An employee, branch, job combination may have an associated

manager

E-R Diagram With Aggregation

Mapping E-R to Relational Model

1.Mapping of regular entity

2.Mapping of weak entity

3.Mapping of binary (1:1) relationship

types

4.Mapping of binary(1:N) or (N:1)

relationship types

5.Mapping of binary (M:N) types

6. Mapping of multivalued Attributes

7.Mapping if ISA relationship

8.Mapping of n-ary relationship types

1. Mapping of regular entity

1.Customer (cid, cname, cstreet, ccity)

2.Employee ( eid, ename, telephone ,startdate)

3.Branch ( bname, bcity, assets)

4.Loan ( loan_num, amount)

5.Account (act_num, balance)

2. Mapping of weak entity

1.Customer (cid, cname, cstreet, ccity)

2.Employee ( eid, ename, telephone ,startdate)

3.Branch ( bname, bcity, assets)

4.Loan ( loan_num, amount)

5.Account (act_num, balance)

6.Payment ( loan_num, payment_num, pay_date,

pay_amt)

3. Mapping of 1:1 relationship

1. Customer (cid, cname, cstreet, ccity)

2. Employee ( eid, ename, telephone ,startdate)

3. Branch ( bname, bcity, assets)

4. Loan ( loan_num, amount)

5. Account (act_num, balance)

6. Payment(loan_num ,payment_num, pay_date, pay_amt)

IN 1:1 constraints of entity e1 & e2 . choose entity having total

participation .add primary key of e1 to e2 & also add descriptive

attributes to e2.

E1 E2R

4. Mapping of 1:N relationship







7. Acc_branch (acct_num, bname)

8. Loan_branch ( loan_num, bname)

9. Cust_bank ( cid, eid, type)

10.Works for ( work_eid, manager_eid)

IN 1:N constraints of entity e1 & e2 . choose entity having 1 participation

add primary key of 1 to N entity . Or create new entity named by that

relationship

E1 E2R1 N

5. Mapping of M:N relationship










10. Works for ( work_eid, manager_eid)

11.Borrower( cid,loan_num)

12.Depositer (cid, acct_num)

IN M:N constraints of entity e1 & e2 . Create new entity e3 add primary key of

e1 & e2 & descripitive attribute of that relationship if it have .

E1 E2RM N

A

6. Mapping of Multivalued











11. Borrower( cid,loan_num)

12. Depositer (cid, acct_num)

13.Dependent_name ( eid,dname)

Create a new entity which include its primary key & that attribute

E2

A

7. Mapping of ISA













13.Dependent_name(eid, dname)

14.Saving_acct ( act_num, intrest_rate))

15.Check_act(act_num, amt)

8. Mapping of N-ary relationship







Create a new entity including all primary key of entity & attribute if any.

E1 E2R

AE1

Reduction
















reduction


2. Employee ( eid, ename, telephone ,startdate, mgr_eid)


4. Loan ( loan_num, amount,bname)

5. Account (act_num, balance,bname)








3. e r model - weebly

Documents