ENTITY RELATIONSHIP DIAGRAM

ERD ----Entity Relationship Diagram

DATABASE ARCHITECTURE

ERD ----Entity Relationship Diagram

External level – concerned with the way individual users see the data

​

​

Conceptual level – can be regarded as a community user view a formal description of data of interest to the organization

​

​

Internal level – concerned with the way in which the data is actually stored

ERD ----Entity Relationship Diagram

• In 1967 Peter Chen developed the ER Model.

​

• It is a high level data model used for developing the

conceptual design of the database.

​

• ER diagram helps designers understand and specify the desired components of database and the relationship among them.

​

• It must be flexible enough so that it can be used and understood in practically any environment where information is modelled

ENTITY

ERD ----Entity Relationship Diagram

• It is a real world item / concept that can exist on it’s own.
• It may be an object with physical existence (

person, house) or it may be an object with conceptual existence (company ,job, university course)

​

ENTITY TYPE

• Entity type defines collection of entities that have same attribute.
• Entity type in a database is defined by it’s name and

attribute.

• Entity instance is a single occurrence of an entity type.

ENTITY SET

ERD ----Entity Relationship Diagram

• Collection of entities of a particular entity type in a database at any point of time is called entity set
• Entity set is usually referred to by same name as the

entity type

Entity type

• Entity type is represented as rectangle enclosing the type name which is singular noun.

STUDENT

ATTRIBUTE

ERD ----Entity Relationship Diagram

• Attributes are the properties that describe the entities.
• Attribute names are enclosed by ovals and connected to

their entities by single line.

• Set of attribute values of a given attribute is the value set or domain

EMPLOYEE

Ssn

Salary

Bdate

ERD ----Entity Relationship Diagram

SIMPLE ATTRIBUTE

• Cannot be split in to further attributes(indivisible)
• Also known as Atomic

attribute

• Ex: Ssn(Social Security

Number)

COMPOSITE ATTRIBUTE

• Can be divided in to smaller subparts which represent more basic attributes with independent meaning
• Even form hierarchy
• Value of the composite attribute is the composition of the constituent simple attributes
• Ex: Address

SINGLE VALUED ATTRIBUTE

ERD ----Entity Relationship Diagram

• Attributes having single value for particular entity.
• Ex - Age

MULTI VALUED ATTRIBUTE

• Attribute having set of values
• Denoted by double circled oval
• Ex: Phone-number, College- degree

DERIVED ATTRIBUTE

• Attribute values are derived from another attribute.
• Denoted by dotted oval
• Ex - Age

STORED ATTRIBUTE

• Attributes from which the values of other attributes are derived
• Ex: Bdate

IDENTIFIER ATTRIBUTE OR KEY ATTRIBUTE

ERD ----Entity Relationship Diagram

Key

Superkey Candidate key Primary key Composite key Foreign key

The name of each primary key attribute is underlined.

SIMPLE KEY ATTRIBUTE

ERD ----Entity Relationship Diagram

STUDENT

Rollno

Name

FLIGHT

No-of- passengers

COMPOSITE KEY ATTRIBUTE

Date

Flightid

Flightno

CRITERIA FOR SELECTING IDENTIFIERS

ERD ----Entity Relationship Diagram

Some entities have more than one candidate identifier, so the following criteria should be used:

​

• Choose identifier that will not change in value over the life of each instance of the entity type

​

• Choose identifier that is guaranteed to have valid values and will not be null (or unknown). If composite, make sure all parts will have valid values

ERD ----Entity Relationship Diagram

• Avoid the use of intelligent identifiers whose structure indicates classifications, locations or people that might change. e.g. the first two digits of an identifier may indicate a warehouse location, but such codes are often changed as conditions change, which renders them invalid.

Composite- hierarchy

ERD ----Entity Relationship Diagram

EMPLOYEE

Ssn

Salary

Bdate

Age

Phone- no

Address

City

State

Street- add

Street

Apartme

nt-no

multivalued

derived

Primary-key

stored

ERD ----Entity Relationship Diagram

Address-

Phone

Address

Phone

• COMPLEX ATTRIBUTE

Composite and mutivalued attribute can also be nested arbitrarily to form complex key.

​

Areacode

Phoneno

State

Street-add

Street

Apartment no

City

​

Zip

SHOULD ‘ADDRESS’ ATTRIBUTE BE REPRESENTED AS SIMPLE ATTRIBUTE OR COMPOSITE?

ERD ----Entity Relationship Diagram

• Composite attributes are useful to model situations in which user refers to the composite attribute as a unit but other times refer specifically to it’s components.

​

• If the composite attribute is to be referenced only as a whole then there is no need to represent it in to component attributes.

​

• Ex: if there is no need to refer to the individual components of an address ( ZIP, code, street etc) then the whole address can be designated as a simple attribute.

RELATIONSHIP TYPE

ERD ----Entity Relationship Diagram

• When attribute of an entity refers to another entity type

there exists relationship

​

• Ex: attribute ‘Manager’ of DEPARTMENT refers to another employee who manages the department.

​

• Attribute ‘Controlling-dep’t refers to another department that controls the project.

​

• In ER diagram these references must be represented as

relationship and not attributes.

ERD ----Entity Relationship Diagram

• Ex: Relationship ‘WORKS-FOR’ between EMPLOYEE and DEPARTMENT associates each employee with the department for he works.

​

• The relationship is often denoted by diamond symbol and

are usually verbs.

​

• Each relationship instance in relationship set WORKS_FOR associates one EMPLOYEE entity and one DEPARTMENT entity.

EMPLOYEE

DEPARTMENT

WORKS- FOR

DEGREE OF A RELATIONSHIP

ERD ----Entity Relationship Diagram

SALESASSIST

PRODUCT

SELLS

CUSTOMER

Binary-relationship

It is the number of entity types that participate in a relationship

​

• If there are two entity types involved it is a binary

relationship type

• If there are three entity types involved it is a ternary

relationship type

• It is possible to have a n-array relationship (quaternary)

ERD ----Entity Relationship Diagram

• Unary relationships are also known as a recursive relationship.
• It is a relationship where the same entity participates

more than once in different roles.

• In the example above we are saying that employees are supervised by employees.

EMPLOYEE

SUPERVI SION

CARDINALITY CONSTRAINTS

ERD ----Entity Relationship Diagram

The number of instances of one entity that can or must be associated with each instance of another entity.

​

• If we have two entity types A and B, the cardinality constraint specifies the number of instances of entity B that can (or must) be associated with entity A.
• Four possible categories are

One to one (1:1) relationship One to many (1:m) relationship Many to one (m:1) relationship Many to many (m:n) relationship

ERD ----Entity Relationship Diagram

• one-to-one

1

1

• one to many

N

1

• many-to-many

🞄

M

N

EMPLOYEE

DEPARTMENT

MANAGES ₁

EMPLOYEE

DEPARTMENT

WORKS- FOR

EMPLOYEE

PROJECT

WORKS- ON

ROLE NAME

ERD ----Entity Relationship Diagram

• Some entities participate more than once in a relationship type in different roles.

​

Role name represents role that a participating entity from the entity type plays in the relationship.

​

• Ex: Employee plays the role of supervisor as well as

supervisee.

• If the participating entity types are distinct then there is no need for role name else role name is a must.

PARITICIPATION CONSTRAINTS(OPTIONALITY)

ERD ----Entity Relationship Diagram

Specifies if existence of an entity depends on it being related to another entity via relationship.

​

​

• Specifies minimum number of relationship instances each entity can participate in .
• This is called minimum cardinality constraint.
• Two type of the participation are : Total And Partial

TOTAL

PARTICIPATION

PARTIAL

ERD ----Entity Relationship Diagram

• Ex: if company policy says that every employee must work for the department then participation of employee in work-for is total.

⚫

EMPLOYEE

DEPARTMENT

WORKS- FOR

1

N

• Total participation is also called existence dependencies.
• Every entity in total set of employee must be related to a

department via WORKS-FOR

• But we can’t say that every employee must MANAGE a department .
• Hence relationship is partial.
• Total participation is indicated by double line and partial participation by single line.

Cardinality + Optionality = multiplicity

ATTRIBUTE OF RELATIONSHIP TYPE

ERD ----Entity Relationship Diagram

• Relationship can also have attributes
• Ex: Hours for WORKS-ON relationship between EMPLOYEE and PROJECT

EMPLOYEE

PROJECT

WORKS- ON

Hours

ERD ----Entity Relationship Diagram

• Attributes of 1:1 or 1:N relationship can be migrated to

one of the participating entity types.

• Ex: Start-date attributes of MANAGES can be attribute of either DEPARTMENT or EMPLOYEE though conceptually it belongs to manages.

EMPLOYEE

DEPARTMENT

MANAGES

Start-date

1

1

ERD ----Entity Relationship Diagram

• Because each EMPLOYEE MANAGES is a 1:1 relationship.

​

• So every DEPARTMENT /EMPLOYEE entity participate in atmost one relationship instance.

​

• So value of the Start-date can be determined separately either by participating DEPARTMENT entity or participating EMPLOYEE entity.

ERD ----Entity Relationship Diagram

• For 1:N relationship a relationship attribute can be migrated only to entity type on N-side of relationship
• Start-date attribute here can added only to employee.

EMPLOYEE

DEPARTMENT

WORKS-

FOR

Start-date

1

N

ERD ----Entity Relationship Diagram

• For M:N relationship types some attribute are determined by the combination of the participating entities, not by a single enity.
• Such attribute must be specified as the relationship attributes
• Ex: No.of hours an employee works on is department is determent is determined by the EMPLOYEE-PROJECT combination.

EMPLOYEE

PROJECT

WORKS- ON

Hours

N

M

WEAK ENTITY AND STRONG ENTITY

ERD ----Entity Relationship Diagram

Entity type that doesn’t have a key attribute on it’s own is called weak entity and the Regular entity types that have key value is called strong entities.

​

• Entity belonging to weak entity type is identified by being related to specific entities from another entity type in combination with one of their attribute value.
• We call this entity type as identifying or owner entity type (Parent/Dominanat Entity type)
• The relationship that connects owner entity type to weak

entity is called Identifying relationship.

• The weak entities are also called as child entity type or subordinate entity type

Weak entities have always a total participating constraint because they cannot be identified without an owner entity.

ERD ----Entity Relationship Diagram

EMPLOYEE

DEPENDENTS

HAS

N

1

Dependent-

name

Employee

Relationship

ERD ----Entity Relationship Diagram

• But converse is not true.
• Every existence dependency does not has result is weak

entities.

• Ex: Driver license entity can’t exist without person entity though it has it’s own key (license number)
• There is total participation but not weak entity
• Weak entity type normally has partial

key(descriminator)

​

Partial key are set of attributes that can uniquely identify weak entities that are related to some owner entity

ERD ----Entity Relationship Diagram

• Partial key attribute is denoted with underlined or dotted line.

​

• Weak entities can sometimes be represented as complex attributes (composite and multivalued attributes)

​

• Choice of the representation is done by DB designer.

​

• If there are many attributes then we can better represent them as weak entity.

​

• Also if it participates independently in any other relationship type other than it’s own identifying

relationship then it should not be modeled as complex attributes.

DESIGN CHOICE FOR ER CONCEPTUAL

ERD ----Entity Relationship Diagram

MODEL

• It is very difficult to determine if a particular concept must be represented as entity type or attribute or relationship type
• Some important concepts to be remembered are:

​

• Concept may be first modeled as an attribute and then refined in to a relationship because it is determined that an attribute is reference to another attribute . A pair of such attribute that are inverses of one another are refined to as binary relationship

Department

ERD ----Entity Relationship Diagram

EMPLOYEE

DEPARTMENT

WORKS- FOR

1

N

EMPLOYEE

DEPARTMENT

Employees

Can be refined to binary relation

ERD ----Entity Relationship Diagram

• B) An attribute that exists in several entity types may be elevated or promoted to an independent entity.

​

• Ex: Suppose several entity types in an UNIVERSITY DATABASE such as student , instructor & course each has an attribute named department . Then we can better keep an entity named department with a single attribute department name and relate it to 3 entity types student , instructor & course via appropriate relationship.

INSTRUCTOR

ERD ----Entity Relationship Diagram

INSTRUCTOR

COURSE

DEPARTMENT

STUDENT

Dept-name

COURSE

Can be refined as

Dept-name

Dept-name

STUDENT

WORKS

-FOR

REGISTE RED

BELONG

S-TO

ERD ----Entity Relationship Diagram

• Inverse refinement to previous case:
• If an entity type department exists in initial design with a single attribute dept-name and is related to only one other entity type student , in this case department may be reduced as an attribute of the student

ALTERNATE NOTATIONS

ERD ----Entity Relationship Diagram

• We can also associate pair of integer (min,max) with each participation of entity type in a relationship type. These are termed structural constraints

​

0<=min<=max and max>=1

Min>0 implies total particiaption Min=0 implies partial participation

CHOOSING BETWEEN BINARY AND TERNARY RELATIONSHIP

ERD ----Entity Relationship Diagram

PROJECT

SUPPLIER

PART

SUPPLY

Partno

Sname

Pname

Consider the ER diagram

​

Qty

ERD ----Entity Relationship Diagram

PROJECT

SUPPLIER

PART

SUPPLI ES

Partno

Sname

Pname

Qty

• We can refine it to three binary relations

USES

CAN SUPPLY

ERD ----Entity Relationship Diagram

• CAN_SUPPLY relationship between SUPPLIER and PART include instances (s,p) whenever SUPPLIER s can supply PART p(to any project).

​

• USES relationship between PROJECT and PART include instances (j,p) whenever PROJECT j uses PART p.

​

• SUPPLIES relationship between SUPPLIER and PROJECT include instances (s,j) whenever SUPPLIER s SUPPLIES some PART to PROJECT j.

​

• The existence of relationship instances (s,p) , (j,p) , (s,j) does not necessarily imply that the instance (s,j,p) exists in ternary relationship supply because meaning is different.

ERD ----Entity Relationship Diagram

• Solution is to include the ternary relationship plus one or more of the binary relationship if they represent different meaning and all are needed by the application.

​

• So ternary relationship supply can be represented as the

weak entity type with three identifying relationship

ERD ----Entity Relationship Diagram

PROJECT

SUPPLIER

Partno

Sname

Pname

Qty

SS

SUPPLY

SPJ

1

N

N

​

​

SP

1

N

DEALING WITH TERNARY RELATIONSHIPS

ERD ----Entity Relationship Diagram

• Consider the below relationship. ]It is no longer clear which sales assistant sold a customer a particular product.
• So try replacing the ternary relations hip with an entity type and a set of binary relationships.

PRODUCT

SALES- ASSISTANT

CUSTOMER

SELLS

SELLS

SELLS

ERD ----Entity Relationship Diagram

• The relationship sells can become the entity type sale .
• So a sales assistant can be linked to a specific customer

and both of them to the sale of a particular product.

PRODUCT

SALES-

ASSISSTANT

CUSTOMER

REQ UEST

MA

KES

SALE

INVO

LVES

REMOVING REDUNDANT RELATIONSHIP

ERD ----Entity Relationship Diagram

• Entities can be related indirectly by two relationships.

A relationship is redundant if it can be completely

represented by alternate transitive relationships

ERD ----Entity Relationship Diagram

CONFIRMING OPTIONALITY AND CARDINALITY OF RELATIONSHIP

ERD ----Entity Relationship Diagram

• Use an `entity set diagram' to show entity examples graphically
• Consider the example of `course is_studied_by student'.

ERD ----Entity Relationship Diagram

• Use the diagram to show all possible relationship

scenarios.

• Go back to the requirements specification and check to see if they are allowed.
• If not, then put a cross through the forbidden

relationships

• This allows you to show the cardinality and optionality of the relationship

ERD ----Entity Relationship Diagram

DERIVING THE RELATIONSHIP PARAMETERS

ERD ----Entity Relationship Diagram

• To check we have the correct parameters (sometimes also known as the degree) of a relationship, ask two questions:

​

1. One course is studied by how many students?

​

Answer = `zero or more'.

• This gives us the degree at the `student' end.
• The answer `zero or more' needs to be split into two parts.
• The `more' part means that the cardinality is `many'.
• The `zero' part means that the relationship is `optional'.
• If the answer was `one or more', then the relationship would be `mandatory'.

ERD ----Entity Relationship Diagram

2.One student studies how many courses?

​

Answer = `One'

• This gives us the degree at the `course' end of the relationship.
• The answer `one' means that the cardinality of this

relationship is 1, and is `mandatory'

• If the answer had been `zero or one', then the cardinality of the relationship would have been 1, and be `optional'.

CONSTRUCTING AN ER MODEL

ERD ----Entity Relationship Diagram

• Before beginning to draw the ER model, read the requirements specification carefully.
• Document any assumptions you need to make.

​

​

1.Identify entities

• list all potential entity types. These are the object of interest in the system. It is better to put too many entities in at this stage and them discard them later if necessary.

2.Remove duplicate entities

ERD ----Entity Relationship Diagram

• Ensure that they really separate entity types or just two names for the same thing
• Also do not include the system as an entity type
• e.g. if modelling a library, the entity types might be books, borrowers, etc.
• The library is the system, thus should not be an entity type.

3.List the attributes of each entity

ERD ----Entity Relationship Diagram

• Ensure that the entity types are really needed.
• Are any of them just attributes of another entity type?
• If so keep them as attributes a nd cross them off the entity list.
• Do not have attributes of one entity as attributes of another entity!

4.Mark the primary keys

ERD ----Entity Relationship Diagram

• Which attributes uniquely identify instances of that entity type?
• This may not be possible for some weak entities.

5.Define the relationships

• Examine each entity type to see its relationship to the others.

6.Describe the cardinality and optionality of the relationships

ERD ----Entity Relationship Diagram

• Examine the constraints betwee n participating

entities.

​

7.Remove redundant relationships

​

• Examine the ER model for redundant relationships.

​

• ER modelling is an iterative process, so draw several versions, refining each one until you are happy with it. Note that there is no one right answer to the problem, but some solutions are better than others!

EXAMPLE

ERD ----Entity Relationship Diagram

⚫

“A Country Bus Company owns a number of busses. Each bus is allocated to a particular route, although some routes may have several busses. Each route passes through a number of towns. One or more drivers are allocated to each stage of a route, which corresponds to a journey through some or all of the towns on a route. Some of the towns have a garage where busses are kept and each of the busses are identified by the registration number and can carry different numbers of passengers, since the vehicles vary in size and can be single or double-decked. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route. Drivers have an employee number, name ,

address, and sometimes a telephone number.”

ENTITIES

ERD ----Entity Relationship Diagram

• Bus - Company owns busses and will hold information about them.
• Route - Buses travel on routes and will need described.
• Town - Buses pass through towns and need to know about them
• Driver - Company employs drivers, personnel will hold

their data.

• Stage - Routes are made up of stages
• Garage - Garage houses buses, and need to know where they are.

RELATIONSHIPS

ERD ----Entity Relationship Diagram

• A bus is allocated to a route and a route may have several buses.

Bus-route (m:1) - is serviced by

• A route comprises of one or more stages. route-stage (1:m) comprises
• One or more drivers are allocated to each stage.

driver-stage (m:1) is allocated

• A stage passes through some or all of the towns on a route.

stage-town (m:n) passes-through

ERD ----Entity Relationship Diagram

• A route passes through some or all of the towns

route-town (m:n) passes-through

• Some of the towns have a garage garage-town (1:1) is situated
• A garage keeps buses and each bus has one `home' garage garage-bus (m:1) is garaged

ER DIAGRAM

ERD ----Entity Relationship Diagram

ATTRIBUTES

ERD ----Entity Relationship Diagram

• Bus (reg- no,make,size,deck,no-pass)
• Route (route-no,avg-pass)
• Driver (emp - no,name,address,tel-no)
• Town (name)
• Stage (stage - no)
• Garage (name,address)

ALTERNATIVE DIAGRAMATIC NOTATIONS FOR ER MODELS

ERD ----Entity Relationship Diagram

ERD ----Entity Relationship Diagram

ERD ----Entity Relationship Diagram

ERD ----Entity Relationship Diagram

ERD ----Entity Relationship Diagram

Displaying attributes

Symbols for entity type attribute and relationship

ERD ----Entity Relationship Diagram

Various (min, max) notations

CROW-FOOT NOTATION

ERD ----Entity Relationship Diagram

ENHANCED ER DIAGRAM

ERD ----Entity Relationship Diagram

The EER diagram includes the concepts of

​

​

Subclass and super class Specialization and Generalization Categorization

Attribute and relationship inheritance.

SUBCLASS SUPERCLASS AND INHERITANCE

ERD ----Entity Relationship Diagram

• In many cases entity types has numerous sub grouping of it’s entities that are meaningful and need to be represented explicitly because of their significance to database application.

ERD ----Entity Relationship Diagram

• Here each of the sub groupings is called subclass of the EMPLOYEE entity type.
• EMPLOYEE entity type is called super class for each of these subclasses
• The relationship between super class and any one of it’s subclass is called the super class/subclass or simply class/subclass relationship.

​

Member entity of the subclass represents the same real world entity as some member of superclass and the Subclass entity has specific distinct role

ERD ----Entity Relationship Diagram

• An entity that is a member of subclass is also a member of super class and can optionally be member of any number of subclasses.
• Important concept associated with subclass is type inheritance.
• Subclass entity possess it’s own attributes as well inherits all the attributes possessed by it’s superclass.
• The entity also inherits the relationships that superclass participtes

SPECIALIZATION

ERD ----Entity Relationship Diagram

Specialisation is a process of defining set of subclasses of an entity type.

​

• This entity type is called superclass of the specialisation.
• Set of subclasses that form specialisation is defined based on some distinguishing characters of entities in the superclass.
• Ex: Job type , method-of-pay

ERD ----Entity Relationship Diagram

• The subclass that defines a specialisation are attached by line to circle to represent the specialisation,which is connected to superclass.

​

• The subset symbol on each line connecting a subclass to the circle indicates direction of the superclass/subclass relationship.

​

• Attributes that apply only to entities of particular entity types – specific/local attributes.

​

• Subclass also participates in specific relationships.

WHY INCLUDE SPECIALISATION CONCEPT?

ERD ----Entity Relationship Diagram

1. Certain attributes may apply only to specific entity.

• A subclass is defined to group the entities

to which the attributes apply.

2. Some relationship types may be participated in by only specific entities.

IN SPECIALISATION:

ERD ----Entity Relationship Diagram

1.Define a set of subclass of entity type

2. Establish additional specific attributes with each

subclass

​

​

3. Establish additional specific relationship types between each subclass and other entity types or other subclass

GENERALISATION

ERD ----Entity Relationship Diagram

The reverse process of abstraction in which we suppress the differences among several entity types, identify their common features and generalize them in to single super class of which original entity types are special subclasses.

CAR

Vehicle- id

Price

Licens

e-no

Max-

speed

No-of-

pass

TRUCK

Vehicle-id

Price

License

-no

Tonnage

No-of- axles

Licens e-no

ERD ----Entity Relationship Diagram

TRUCK

Tonnage

No-of-axles

CAR

Max- speed

No-of- pass

Vehicle- id

Price

VEHICLE

d

CONSTRAINTS ON SPECIALIZATION AND GENERALIZATION

ERD ----Entity Relationship Diagram

Predicate-defined subclasses

• Determine exactly those entities that will become members of each subclass by a condition.

Attribute defined- specialization

• Membership condition for subclasses on same attribute of the super class
• Attribute is called the defining attribute of the specialization
• Example: JobType

User-defined subclass

• no condition determines membership
• Membership in a subclass is determined by the database users by applying an operation to add an entity to the subclass

ERD ----Entity Relationship Diagram

• Hence, we have four types of specialization/generalization based on the paticipation of superclass entities in sub classes:

​

• Disjoint, total
• Disjoint, partial
• Overlapping, total
• Overlapping, partial

Generalization usually is total because the superclass is derived from the subclasses.

Example of disjoint partial Specialization

ERD ----Entity Relationship Diagram

SPECIALIZATION / GENERALIZATION HIERARCHIES, LATTICES

ERD ----Entity Relationship Diagram

• A subclass may itself have further subclasses specified on it
• Forms a hierarchy or a lattice.

Hierarchy has a constraint that every subclass has only one superclass (called single inheritance)

​

​

In a lattice, a subclass can be subclass of more than one superclass (called multiple inheritance)

ERD ----Entity Relationship Diagram

• In a lattice or hierarchy, a subclass inherits attributes not only of its direct superclass, but also of all its predecessor superclasses

​

• In specialization, start with an entity type and then define subclasses of the entity type by successive specialization (top down conceptual refinement process)

​

• In generalization, start with many entity types and generalize those that have common properties (bottom up conceptual synthesis process).

​

• A subclass with more than one superclass is called a

shared subclass

SPECIALIZATION / GENERALIZATION LATTICE

ERD ----Entity Relationship Diagram

EXAMPLE (UNIVERSITY)

CATEGORIES (UNION TYPES)

ERD ----Entity Relationship Diagram

Model a single super class /subclass relationship with more than one super class

​

• Super classes represent different entity types .
• Example:

Database for vehicle registration, vehicle owner can be a person, a bank (holding a lien on a vehicle) or a company.

• Category (subclass) OWNER is a subset of the union of

the three super classes COMPANY, BANK, and PERSON

• A category member must exist in at least one of its super classes

​

This is difference from shared subclass, which is subset of the intersection of its super classes (shared subclass member must exist in all of its super classes).

ERD ----Entity Relationship Diagram

CASE IN POINT: E-R MODEL FOR ONLINE SALES FOR BIGHIT VIDEO

ERD ----Entity Relationship Diagram

“ BigHit Video Inc. wants to create an information system for online sales of movies in both DVD and videotape format. People will be allowed to register as customers of the online site and to update their stored information. Informa-tion must be maintained about customers ’ shipping addresses, e-mail ad- dresses and credit cards. In a single sale, customers will be allowed to purchase any quantity of videos. The items in a single sale will be shipped to a single address and will have a single credit card charge A customer will be provided with a virtual shopping cart to store items to be purchased. As each item is selected, it is added to the shopping cart. When the customer finishes shopping, he will be directed to a checkout area where he can purchase all of the items in the shopping cart. At this time, payment and shipping information is entered. Once the sale is complete, the shopping cart will be deleted and the customer will be sent a receipt by e-mail.”

ERD ----Entity Relationship Diagram

IDENTIFY ENTITY AND ATTRIBUTES

ERD ----Entity Relationship Diagram

ERD ----Entity Relationship Diagram

ERD ----Entity Relationship Diagram

RELATIONSHIPS

ERD ----Entity Relationship Diagram

THANK YOU

ERD ----Entity Relationship Diagram