Kszlt a HEFOP3'3'1P'20040600181'0 projekt keretben

1
Készült a HEFOP-3.3.1-P.-2004-06-0018/1.0 projekt
keretében
A szakirány neve

• DATABASE MANAGEMENT SYSTEMS

Modul 1 1.2 Entity/Relationship Model (1
Lecture)
Az Európai Szociális Alap támogatásával
2
Outline

• ? Role of Entity Realtionship Model
• ? Units in Entity Relationship MOdel
• -entitiy set
• -attributes
• - relationship set
• - specialties for each
• ? Keys
• ? Summary notation

3
One of the 3 levels of database design

• Conceptual level
• Object based models we are dealing with
• Entity Relationship model (grafic standard)
• UML (if we will have enough time)

4
Building a database

• The process could be

Getting information on microworld
E/R Model
ODL model
Relational model normalization
Obj. Oriented DBMS
Relational DBMS
5
Entity sets

• Entity set
• its elements are the entities, sharing same
  properties, called attributes ( like a class)
• Entity is an object distinguishable from other
  object in the microworld through its attributes
• Example person, real object, notion, etc.
• Notation rectangle, example

person
6
Attributes

• Attribute(s) entities share the same
  propertyattribute
• Example each person has name, address
• Notation oval, joint by a line segment to an
  entity, relationship or attribute
• Example

7
Attribute

• Specialties composite attribute
• May have other (sub)attributes
• Notation the same oval, joint to the super
  attribute
• Example

8
Relationship

• Relationship set, briefly relationship
• a logical connection, association between two
  or more entity sets
• Notation diamond,
• joint to the participating entity sets. An
  arrow is used, if exactly one entitiy is
  associated to the other
• Example the entity set children and the entity
  set female may be associated through a
  mother-of relationship. An arrow pointed to the
  female entity set, because each child has
  EXACTLY ONE mother

9
Relationship

• Degree of a relationship set number of
  participating entitiy sets
• Binary relationship sets only two entity sets
  are associated by it
• Types
• - one to one (wife-husband)
• - one to many (mother-children)
• - many to many (account-bank_customer)

10
KEY PROBLEM ? KEYS, KEYS, KEYS!!

• Superkey set of attributes uniquely identify
  the entity (there are always many)
• (Candidate) key minimal set of attributes
  uniquely identify the entity
• (could be many)
• Primary key arbitrarily chosen
• (candidate) key (only one)
• Notation primary key attributes are
  underlined
• Example I_am_the_primary_key

11
KEYS

• Primary key arbitrarily chosen
• (candidate) key (only one)
• Notation primary key attributes are
  underlined (is it really a PK?)

12
Special entity sets

• Weak entity set has not enough attributes
  for identifying the entities
• Notation rectangle bordered by doubled line

13
Special relationship sets

• A weak entity set must joint to another
  (strong) entity set through a many to 1
  relationship, whose one side is the strong
  entity. This relationship is called also weak.
• Notation for weak relationship sets
• Diamond bordered by doubled lines

14
Key for weak entitiy

• Usually weak entities at least have a so called
  discriminator-unique in that context only
• The discriminator and the primary keys of all
  strong entities of participating a one to many
  weak relationship will form the key.

15

• Example Keys for contract

Source Ullman Widom Databas system, First
course
Salary
Contract
Plays_in
Works_for
of
Color
Actors
Studio
Film
Address
lenght
Name
Year
Address
Name
Title
16
Special entity sets specialization,
generalization ISA

• Source Ullman Widom Databas system, First
  course

Sound
gun
Cartoon
Crime
Crime cartoon
17
ISA

• Bottom up generalization
• Top down specialization
• Sub entitiy set inherits all the attributes of
  the superset
• Important to know wether the specialization is a
  partition-it influences the implementation

18
Example Analize the figure below

Relationship
Birth-date
Name
Dependent
City
Street
Number
Name
Dep-emp
Location
Address
Works-for
Salary
employee
department
Number
SSN
Manages
Name
ISA
Start-date
Bonus
manager
CELL-phone
Company car licence number
Stock option (price)
19
Design principles

• Correct reflection of the microworld
• Simpliest solution is the best
• Avoiding redundancy
• Correct decision about the type of the stored
  object
• - Is it an entity?
• - Is it an attribute?
• - Is it a relationship?
• The answer may be different in different
  enterprises

20
Summary Entity Relationship Model

• Notations

Entity set
Weak entity set
ISA generalization, specification
Attribute, with primary key
___
Composite attribute
Relationship set
Weak relationship set
Multivalued attribute
N1 relationship set, pointing to 1 side Total
participation
Derived attribute