Entity Relationship Model

Introduction to Entity-Relationship (E-R) Modeling

Notation uses three main constructs Data entities Relationships Attributes

Entity-Relationship (E-R) Diagram A detailed logical representation of the

entities associations and data elements for an organization or business

Entity-Relationship (E-R) ModelingKey Terms

Entity A person place object event or concept in the

user environment about which the organization wishes to maintain data

Represented by a rectangle in E-R diagrams Entity Type

A collection of entities that share common properties or characteristics

Attribute A named property or characteristic of an entity

that is of interest to an organization

Depicting Entities and Attributes

Conceptual Data Modeling and the E-R Diagram

Goal Capture as much of the meaning of the data as

possible If you know the rules of normalization

referential integrity foreign keys etc this is good but not as important now It is much more important to get the organizational data model correct ie to understand the actual data requirements for the organization

Result A better design that is scalable and easier to

maintain

Example of Elements of E-R Model

Entity Sets Departments Professors Students Administrators

Attributes Name of Departments Phone No Address Name SSN Address of Professors

Relationship Students and Professors are under a certain

department Admin manage the campus departments

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Introduction to Entity-Relationship (E-R) Modeling

Notation uses three main constructs Data entities Relationships Attributes

Entity-Relationship (E-R) Diagram A detailed logical representation of the

entities associations and data elements for an organization or business

Entity-Relationship (E-R) ModelingKey Terms

Entity A person place object event or concept in the

user environment about which the organization wishes to maintain data

Represented by a rectangle in E-R diagrams Entity Type

A collection of entities that share common properties or characteristics

Attribute A named property or characteristic of an entity

that is of interest to an organization

Depicting Entities and Attributes

Conceptual Data Modeling and the E-R Diagram

Goal Capture as much of the meaning of the data as

possible If you know the rules of normalization

referential integrity foreign keys etc this is good but not as important now It is much more important to get the organizational data model correct ie to understand the actual data requirements for the organization

Result A better design that is scalable and easier to

maintain

Example of Elements of E-R Model

Entity Sets Departments Professors Students Administrators

Attributes Name of Departments Phone No Address Name SSN Address of Professors

Relationship Students and Professors are under a certain

department Admin manage the campus departments

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Entity-Relationship (E-R) ModelingKey Terms

Entity A person place object event or concept in the

user environment about which the organization wishes to maintain data

Represented by a rectangle in E-R diagrams Entity Type

A collection of entities that share common properties or characteristics

Attribute A named property or characteristic of an entity

that is of interest to an organization

Depicting Entities and Attributes

Conceptual Data Modeling and the E-R Diagram

Goal Capture as much of the meaning of the data as

possible If you know the rules of normalization

referential integrity foreign keys etc this is good but not as important now It is much more important to get the organizational data model correct ie to understand the actual data requirements for the organization

Result A better design that is scalable and easier to

maintain

Example of Elements of E-R Model

Entity Sets Departments Professors Students Administrators

Attributes Name of Departments Phone No Address Name SSN Address of Professors

Relationship Students and Professors are under a certain

department Admin manage the campus departments

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Depicting Entities and Attributes

Conceptual Data Modeling and the E-R Diagram

Goal Capture as much of the meaning of the data as

possible If you know the rules of normalization

referential integrity foreign keys etc this is good but not as important now It is much more important to get the organizational data model correct ie to understand the actual data requirements for the organization

Result A better design that is scalable and easier to

maintain

Example of Elements of E-R Model

Entity Sets Departments Professors Students Administrators

Attributes Name of Departments Phone No Address Name SSN Address of Professors

Relationship Students and Professors are under a certain

department Admin manage the campus departments

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Conceptual Data Modeling and the E-R Diagram

Goal Capture as much of the meaning of the data as

possible If you know the rules of normalization

referential integrity foreign keys etc this is good but not as important now It is much more important to get the organizational data model correct ie to understand the actual data requirements for the organization

Result A better design that is scalable and easier to

maintain

Example of Elements of E-R Model

Entity Sets Departments Professors Students Administrators

Attributes Name of Departments Phone No Address Name SSN Address of Professors

Relationship Students and Professors are under a certain

department Admin manage the campus departments

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Example of Elements of E-R Model

Entity Sets Departments Professors Students Administrators

Attributes Name of Departments Phone No Address Name SSN Address of Professors

Relationship Students and Professors are under a certain

department Admin manage the campus departments

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Example of the 3 elements in ER Diagram

Entity B

Relationship

Attribute

Entity C

Entity A

Attribute

Attribute

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Classification of Constraints

1 Keys2 Single-value constraints3 Multi-valued constraints4 Mapping Cardinalities and

Participation Constraints

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Key in the ER Model

Superkey is a set of one or more attributes that taken collectively for us to identify uniquely an item in the entity set For example customer-id is a superkey

Candidate key is a minimal superkey For example customer-name and customer-street is sufficient to distinguish among members of the customer entity set Then customer-name customer-address is a candidate key

Primary key denotes a candidate key that is chosen by the database designer as the principal means of identifying items within an entity set the primary key should be chosen such that its attributes are never or very rarely changed For example Social-security numbers are guaranteed to never changed

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

address name ssn

Person

buys

makes

employs

CompanyProduct

name category

stockprice

name

price

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Keys in ER Diagrams

Every entity set must have a key a key is an attribute that has a different

value for every entity

Product

name category

price

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

What is a Relation

A mathematical definition if A B are sets then a relation R is a subset

of A x B A=123 B=abcd

R = (1a) (1c) (3b)

- makes is a subset of Product x Company

1

2

3

a

b

c

d

A=

B=

makes CompanyProduct

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

SingleMulti-valued attributes

Single-valued attributes are attributes that only have a single value for a particular entity

Multi-valued attributes refers to items that are not singled-value and Null valued For example consider an employee entity set with the attribute phone-number An employee may have zero one or several phone numbers different employee may have different numbers of phones

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Mapping Cardinalities or Cardinality ratios

Express the number of items to which another item can be associated via a relationship set

Are most useful in describing binary relationship sets For a binary relationship set R between entity sets A and B the mapping cardinality must be one of the following One to One One to Many Many to One Many to Many

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Multiplicity of ER Relations

one-one

many-one

many-many

123

abcd

123

abcd

123

abcd

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Participation Constraints

The participation of an entity set E in a relationship set R is said to be total if every item in E participates in at least one relationship in R If only some items in E participate in relationship R the participation of entity set E in relationship R is said to be partial

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Does every department have a manager If so this is a participation constraint the participation of

Departments in Manages is said to be total (vs partial) Every Departments entity must appear in an instance of

the Manages relationship

lot

name dnamebudgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Degree of a Relationship Set

Refers to number of entity sets that participate in a relationship set

Relationship sets that involve two entity sets are binary (or degree two) Generally most relationship sets in a database system are binary

Relationship sets may involve more than two entity sets

Relationships between more than two entity sets are rare Most relationships are binary

Example Suppose employees of a bank may have jobs(responsibilities) at multiple branches with different jobs

at different branches Then there is a ternary relationship set between entity sets employee job and branch

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Attributes

An entity is represented by a set of attributes that is descriptive properties possessed by all members of an entity set

1048708 Domain ndash the set of permitted values for each attribute

1048708 Attribute types1048708 Simple and composite attributes1048708 Single-valued and multi-valued attributes1048708 Example multivalued attribute phone_numbers1048708 Derived attributes Can be computed from other

attributes Example age given date_of_birth

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Compposite Attributes

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Keys for Relationship Sets

The combination of primary keys of the participating entitysets forms a super key of a relationship set1048708 (customer_id account_number) is the super key of depositorNOTE this means a pair of entity sets can have at most onerelationship 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

1048708 Must consider the mapping cardinality of the relationshipset when deciding the what are the candidate keys1048708 Need to consider semantics of relationship set in selectingthe primary key in case of more than one candidate key

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

E-R Diagrams

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Rectangles represent entity sets 1048708 Diamonds represent relationship sets 1048708 Lines link attributes to entity sets and

entity sets to relationship sets 1048708 Ellipses represent attributes 1048708 Double ellipses represent multivalued

attributes 1048708 Dashed ellipses denote derived

attributes 1048708 Underline indicates primary key

attributes

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

E-R Diagram With CompositeMultivalued and Derived Attributes

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Roles

Entity sets of a relationship need not be distinct1048708 The labels ldquomanagerrdquo and ldquoworkerrdquo are called roles they specify how employee entities interact via the works_for relationship set1048708 Roles are indicated in E-R diagrams by labeling the lines that connectdiamonds to rectangles1048708 Role labels are optional and are used to clarify semantics of the relationship

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Cardinality Constraints

We express cardinality constraints by drawingeither a directed line (rarr) signifying ldquoonerdquo or anundirected line (mdash) signifying ldquomanyrdquo betweenthe relationship set and the entity set1048708 One-to-one relationship1048708 A customer is associated with at most one loan via

the relationship borrower1048708 A loan is associated with at most one customer via

borrower

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

One-To-Many Relationship

In the one-to-many relationship a loan is associated with at most one customer via borrower a customer is associated with several (including 0) loans via borrower

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Many-To-One Relationships

In a many-to-one relationship a loan is associated with several (including 0) customers via borrower a customer is associated with at most one loan via borrower

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Many-To-Many Relationship

A customer is associated with several (possibly 0) loans via borrower

A loan is associated with several (possibly 0) customers via borrower

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Participation of an Entity Set in aRelationship Set

Total participation (indicated by double line) every entity in the entity set participates in at least one relationship in the relationship set

Eg participation of loan in borrower is total every loan must have a customer associated to it via borrower

Partial participation some entities may not participate in any relationship in

the relationship setExample participation of customer in borrower is partial

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

E-R Diagram with a TernaryRelationship

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

E-R Diagram for Movie Databse

StarsStars-in

year

Studios

Movies

length

Title AddressName

ownsfileType

Name Address

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

ISA Hierarchies

Some objects in a class may be special define a new class better define a subclass

PRODUCTS

Software Products

EducationalProducts

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Subclasses

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Understanding Subclasses

Think in terms of records Product

SoftwareProduct

EducationalProduct

field1

field2

field1

field2

field1

field2

field3

field4field5

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Subclasses to Relations

Product

name category

price

isa isa

Educational ProductSoftware Product

Age Groupplatforms

Name PriceCategor

y

Gizmo 99 gadget

Camera 49 photo

Toy 39 gadget

Nameplatform

s

Gizmo unix

NameAge

Group

Gizmo todler

Toy retired

Product

SwProduct

EdProduct

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Example-ISA

Movie Person

ISA

Actor

id

name

address

birthday

picture Director

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Example - Requirements (1)

For actors and directors we want to store their name a unique identification number address and birthday

For actors we also want to store a photograph

For films we want to store the title year of production and type (thriller comedy etc)

We want to know who directed and who acted in each film Every film has one director We store the salary of each actor for each film

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Requirements (2)

An actor can receive an award for his part in a film We store information about who got which award for which film along with the name of the award and year

We also store the name and telephone number of the organization who gave the award Two different organizations can give an award with the same name A single organization does not give more than one award with a particular name per year

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Movie Person

ISA

Actor

id

name

birthday

picture Director

Filmyear

typetitle

Acted In Directedsalary

Award

Organization

Gives

year

name

name

phonenumber

Won

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Weak Entity Sets

Weak entity sets are entity sets that are not uniquely identified by their attributes

A weak entity set has an identifying relationship with an entity set that is the identifying owner of the weak entity set

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Weak Entity Sets

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner many weak entities)

Weak entity set must have total participation in this identifying relationship set

lot

name

agepname

DependentsEmployees

ssn

Policy

cost

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Example

Award

Organization

Gives

year

name

name

phonenumber

Won

What would be the meaning if this was not a

thick line

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Example

Suppose that you were storing information about books

Should books be modeled as a weak entity set or a regular entity set Does ISBN identify a book

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Copies of Books in Libraries

Booktitle

author

isbn

Copy

copy number condition

Copy Of

Borrowed

Person

id

Owned ByLibraryname

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Specialization

It is the process of defining a set of subclasses of a superclass

Top-down design process we designate subgroupings within an entity set that are distinctive from other entities in the set

The set of subclasses based upon some distinguishing characteristics of entities in the superclass

ExampleSECRETARYENGINEER DOCTOR is a specialization of Employee based on Job type

Attribute of Sub class are called Specific Attributes

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Specialization Example

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Aggregation

A bottom-up design process ndash combine a number of entity sets that share the same features into a higher-level entity set

Several classes with common features are generalized into a superclass oroginal classes become its subclasses

ExampleCARTRUCK generalized into Vehicle 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

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Aggregation Example

budgetdidpid

started_on

pbudgetdname

until

DepartmentsProjects Sponsors

Employees

Monitors

lotname

ssn

since

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

Constraints on Specialization and Generalization

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

EXAMPLE

Use the following business rules to write all appropriate connectivities in the E-R diagram

a A department employs many employees but each employee is employed by one department

b Some employees known as rovers are not assigned to any department

c A division operates many departments but each department is operated by one division

d An employee may be assigned to many projects and a project may have many employees assigned to it

e A project must have at least one employee assigned to it

f One of the employees manages each department and each department is managed by only one employee

g One of the employees runs each division and each division is run by one employee

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations

EXAMPLE

Given the following summary of business rules for the ROBCOR catering service draw the ERD Make sure to include all appropriate entities relationships connectivities and cardinalities

Each dinner is based on a single entree but each entree can be served at many dinners A guest can attend many dinners and each dinner can be attended by many guests Each dinner invitation can be mailed to many guests and each guest can receive many invitations