COMP 5138 Relational Database Management Systems

1
COMP 5138Relational Database Management Systems
Lecture 2 Conceptual Database Design
2
Review of Last Class

• Introduction to Database Systems
• File systems vs. DBMS
• Data models, especially relational model
• Level of abstraction
• Data independence
• Professional careers related to DBMS

3
Todays Agenda

• Introduction to Conceptual Modeling
• Entity Relationship Model
• Enhanced Entity Relationship Model
• Design Choices

4
Lifecycle

• Understand
• what data is to be stored
• what applications must be built
• what operations are most frequent

Requirements Analysis

• Develop
• high-level description of the data which closely
  matches how users think of the data

Conceptual Design

• Convert
• conceptual design into a logical schema suitable
  for the DBMS

Logical Design

• Convert
• logical schema into a physical schema suitable
  for the disk

Physical Design
5
Conceptual Data Model

• Conceptual data model
• A technique for understanding and capturing
  business information requirements, focused at the
  level of concepts that come from the domain
• Often presented graphically
• Should be independent of technology platform or
  even family
• First phase of an effective database design
• Works as a communication vehicle
• Concepts make sense to all stakeholders
• Facilitate the planning, operation, and
  maintenance of various data resources

6
Todays Agenda

• Introduction to Conceptual Modeling
• Entity Relationship Model
• Enhanced Entity Relationship Model
• Design Choices

7
Entity-Relationship Model

• First designed by Peter Chen in 1976. Other
  variations have since appeared.
• Often abbreviated as ER or ERD
• Used to interpret, specify, and document database
  systems.
• Graphical representation of what data needs to be
  contained in the system.

8
Entity Relationship Model

• A data modeling approach that depicts the
  associations among different categories of data
  within a business or information system.
• What are the entities and relationships in the
  enterprise?
• What information about these entities and
  relationships should we store in the database?
• What are the integrity constraints or business
  rules that hold?
• A database schema in the ER Model can be
  represented pictorially (ER diagrams).
• We can convert an ER diagram into a relational
  schema.
• It is about what data needs to be stored
• It does not imply how data is created, modified,
  used, or deleted.

9
Entity

• An entity is a person, place, object, event, or
  concept about which you want to gather and store
  data.
• It must be distinguishable from other entities
• An entity type (or entity set) is a collection of
  entities that share common properties or
  characteristics.
• Example STUDENT, PROFESSOR, SUBJECT
• Rectangle represent entity type
• An entity instance is a single occurrence of an
  entity type.
• Examples Jane Smith (a student), COMP5138 (a
  subject),

SUBJECT
STUDENT
PROFESSOR
10
Attribute

• Once you have identified the entities you are
  interested in, determine which characteristics or
  properties about them to gather and store.
• These characteristics are called attributes.
• A named characteristic of an entity
• Ellipses represent attributes
• All entities in a given set have the same
  attributes
• But they can have different values for the
  attributes
• The attribute(s) which uniquely identifies each
  entity is the identifier or primary key
• Underline indicates primary key attributes

11
Relationship

• Relationship Association among two or more
  entities. E.g., Alan Fekete teaches COMP5138.
• Relationship Set Collection of similar
  relationships.
• A named association between entities.
• Diamonds represent relationship sets
• Lines link attributes to entity sets and entity
  sets to relationship sets.
• In data modeling, you identify only those
  relationships among entities that are important
  for operational purposes.

PROFESSOR
SUBJECT
teaches
12
Diagrams

• Draw a diagram, showing the entities and
  relationships
• Each entity may be in several relationships
• If there is room, maybe also draw the attributes
• Often the diagram will be too complex, so draw
  several diagrams each showing the concepts from
  some part of the domain
• Eg one diagram for the concepts involved in the
  inventory management, and another for the human
  resources aspects
• Cross references where some entities must appear
  in several sections

Subject
Teaches
Professor
EnrolsIn
Student
13
More on Entity

• When we say entity in subsequent discussions, we
  mean entity types, not entity instances.
• An Entity Must
• Have a name (We use a Singular Noun as name)
• Warning the textbook often uses plural name we
  treat that as an error!
• Have an identifier
• Have descriptive information, other than its ID
• How to find an Entity?
• Look for nouns that appear in narratives
• Find data items with names ending with number,
  no, num, , or id such as customer,
  product-num, student-id, or patient-no. Take the
  postfix ( or id) off, then you probably found an
  entity
• Take the names of the frequently used management
  reports, menus, and I/O screens. Some of these
  will show entities.
• Customer, Product, Stock, etc
• Take the names of the files and data tables
  frequently accessed by the firms main
  transaction programs.

14
More on Attribute

• Attribute domain a set of values from which an
  attribute can take
• Example name is a string
• age in an integer and ranges from 0 to 200
• An Attribute Must
• Have a unique name (singular, noun)
• Belong to a single entity
• Type of an attribute
• Simple (e.g., address) and composite ( suburb
  state zip code) attributes.
• Single-valued and multi-valued attributes
• E.g. multi-valued attribute hobbies
• Double ellipses represent multi-valued attributes
• Derived attributes
• Can be computed from other attributes
• E.g. age, given date of birth
• Dashed ellipses denote derived attributes

hobbies
Age
15
More on Attribute

• A key is a minimal set of attributes whose values
  uniquely identify an entity in the set
• A candidate key is a set of attributes that can
  be used as an identifier
• Customer-id is candidate key of customer
• account-number is candidate key of account (with
  in one bank)
• (account-number BSB) is candidate key for
  accounts among all Australian banks
• Although several candidate keys may exist, one of
  the candidate keys is selected to be the
  identifier or primary key

16
Primary Key

• Primary Key
• Must be unique across all instances
• Must not change over life of instance
• e.g. Tax File Number, Employee Number
• Must always have a valid value, not null
• Preferably use serial number or arbitrary ID
• Invented just to be a primary key
• Avoid using intelligent keys
• A combination of keys can be used as an
  identifier
• Example SUBJECT

Subject area
SUBJECT
Course
Course title
17
More on Relationship

• There are often constraints in the domain, which
  limit the structure of the instances of a
  relationship
• Capturing these is a major benefit of doing a
  conceptual data model
• The most important constraints have to do with
  the cardinality of the relationship
• How many instances a given instance can be
  related to
• This has a big impact when producing a suitable
  relational schema to represent the data
• There is special notation in an ER Diagram, to
  reflect the constraints

18
Key Constraints

• Consider Works_In An employee can work in many
  departments a dept can have many employees.
• In contrast, each dept has at most one manager,
  according to the key constraint on Manages.

Many-to-Many
1-to Many
budget
did
Department
19
Constraints

• Example

Joe
COMP5138
Andrew
CHOI
COMP5415
Frank
POON
Tracey
COMP5206
Linda
COMP5413
ROSE
Doris
LECTURER
COURSE
STUDENT
teaches
is enrolled by
20
Key Constraints

• We find examples of each style of relationship
• Think carefully about both directions
• how many instances of B can a given instance of A
  be related to?
• How many instances of A can a given instance of B
  be related to?
• Warning natural language can be confusing
• Many-to-1 means each A is related to at most 1 of
  B

1-to-1
1-to Many
Many-to-1
Many-to-Many
21
Participation Constraints

• 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).

since
since
name
dname
name
dname
ssn
lot
budget
did
budget
did
Department
Employee
Manages
Works_In
since
22
Notations

• Various books and papers use different ways of
  showing cardinality in an ERD

LECTURER
SUBJECT
one-to-many
teaches
teaches
LECTURER
SUBJECT
1M
one-to-many
LECTURER
SUBJECT
M
1
one-to-many
teaches
LECTURER
SUBJECT
one-to-many
teaches
teaches
LECTURER
SUBJECT
one-to-many
LECTURER
SUBJECT
one-to-many (textbook notation)
teaches
23
Roles

• Role
• Entity types of a relationship need not be
  distinct
• The labels manager and worker are called
  roles they specify how employee entities
  interact via the works-for relationship set.
• Roles are indicated in E-R diagrams by labeling
  the lines that connect diamonds to rectangles.
• Role labels are optional, and are used to clarify
  semantics of the relationship
• Especially important with unary relationship
  (where the related instances are from the same
  entity set)

manager
EMPLOYEE
Works-for
worker
24
Associative Entity

• Associative Entity(Relationship with attributes)

Course_Char
Student_Name
Course
STUDENT
COURSE
takes
Student
Course_Title
Student_Add

• Appropriate place for grade???

25
Associative Entity

• Example continued
• IF Grade is with STUDENT entity,

STUDENT
COURSE
Student
Student Name
Student_Add
Course
Course_Title
Course_Char
Grade
234-234-234
Andy
Mallet st.
COMP 5138
DBMS
Practical
A
234-234-235
Jack
George st.
F
COMP 5347
E-commerce
Theory

• IF Grade is with COURSE entity

STUDENT
COURSE
Student
Student Name
Student_Add
Course
Course_Title
Course_Char
Grade
234-234-234
Andy
Mallet st.
COMP 5138
DBMS
Practical
A
234-234-235
Jack
George st.
F
COMP 5347
E-commerce
Theory
26
Associative Entity

• When many-to-many relationship needs to be
  described with its own attributes, make it an
  Associative Entity and treat it like a regular
  entity

Course_Char
Student_Name
takes
Course
STUDENT
COURSE
Student
Course_Title
Student_Add
Grade
Course
Student
Grade
COMP 5138
234-234-234
A
COMP 5347
234-234-234
F
COMP 5138
234-234-235
F
COMP 5347
234-234-235
A
27
Weak Entities

• Every entity must have a key, but sometimes the
  key is not (entirely) kept with the entity
  itself.
• 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.
• Unique identifier is the combination of owners
  primary key, and the partial key of the weak
  entity
• Textbook notation thick boundary
• Common alternative notation double line boundary

name
cost
pname
age
ssn
lot
Dependent
Policy
Employee
28
ERD Symbols
Entity
Relationship
Associative Entity
Primary Key Attribute (Identifier)
Attribute
Multivalued Attribute
Derived Attribute
Connection
Weak Entity
29
Todays Agenda

• Introduction
• Entity Relationship Model
• Enhanced Entity Relationship Model
• Design Choices

30
Concepts of EER

• ER model in its original form did not support
• SPECIALIZATION/ GENERALIZATION
• ABSTRACTIONS
• Enhanced ER model
• Includes all modeling concepts of basic ER
• Additional concepts subclass/superclass,
  specialization/generalization, categories,
  attribute inheritance
• The resulting model is sometimes called the
  enhanced-ER or Extended ER (EER) model
• Textbook does not distinguish between ER and EER
• It is used to model applications more completely
  and accurately if needed

31
Subclass, Superclass

• An entity type may have additional meaningful
  subgroupings of its entities
• These are called superclass/subclass
  relationships
• These are also called IS-A relationships
  (SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A
  EMPLOYEE, )

32
Subclass and Superclass

• An entity that is member of a subclass represents
  the same real-world entity as some member of the
  superclass
• The subclass member is the same entity in a
  distinct specific role
• An entity cannot exist in the database merely by
  being a member of a subclass it must also be a
  member of the superclass
• A member of the superclass can be optionally
  included as a member of any number of its
  subclasses
• Example A salaried employee who is also an
  engineer belongs to the two subclasses ENGINEER
  and SALARIED_EMPLOYEE
• It is not necessary that every entity in a
  superclass be a member of some subclass
• Do not create Sub/Super hierarchy unless
• there exist at least one specific attribute or
  relationship for each subclass entity

33
Attribute Inheritance

• An entity that is member of a subclass inherits
  all attributes of the entity as a member of the
  superclass
• It also inherits all relationships

Employee_Name
Employee
Employee_Add
Commision_rate
Hourly_rate
Salary_per_week
34
Specialization

• The process of defining a set of subclasses of a
  superclass
• The set of subclasses is based upon some
  distinguishing characteristics of the entities in
  the superclass

Employee_Name
Employee
Employee_Add
Commision_rate
Hourly_rate
Salary_per_week
35
Generalization

• The reverse of the specialization process
• Several classes with common features are
  generalized into a superclass original classes
  become its subclasses
• CAR, TRUCK generalized into VEHICLE both CAR,
  TRUCK become subclasses of the superclass
  VEHICLE.
• We can view CAR, TRUCK as a specialization of
  VEHICLE
• Alternatively, we can view VEHICLE as a
  generalization of CAR and TRUCK

36
Subclass/Superclass

• Defining overlap and covering constraints for
  generalisation/specialistaion hierarchies
• Overlap
• Disjoint
• an entity can belong to only one lower-level
  entity type
• Noted in E-R diagram by writing disjoint next to
  the ISA triangle
• Example (HUMAN MAN and WOMAN)
• Overlapping
• an entity can belong to more than one lower-level
  entity type
• Example (STAR SINGER and ACTOR)
• Covering
• Total
• an entity must belong to one of the lower-level
  entity types
• Partial
• an entity need not belong to one of the
  lower-level entity types

37
Aggregation

• Consider the ternary relationship works-on
• Suppose we want to record managers for tasks
  performed by an employee at a branch
• Relationship sets works-on and manages represent
  overlapping information
• Every manages relationship corresponds to a
  works-on relationship
• However, some works-on relationships may not
  correspond to any manages relationships
• So we cant discard the works-on relationship

38
Aggregation

• Eliminate this redundancy via aggregation
• Treat a relationship as an abstract entity
• This allows relationships between relationships
• Abstraction of relationship into new entity
• Without introducing redundancy, the following
  diagram represents
• An employee works on a particular job at a
  particular branch
• An employee, branch, job combination may have an
  associated manager

39
Todays Agenda

• Introduction
• Entity Relationship Model
• Enhanced Entity Relationship Model
• Design Choices

40
Losing Information
USED-BY
VEHICLES
DRIVERS
USED-BY
DELIVERIES
Cannot tell which driver made a delivery Who made
delivery d2, p1 or p2?
41
A Better Way
MADE-BY
DELIVERIES
DRIVERS
USE
VEHICLES
42
Conceptual Designs

• Design choices
• Should a concept be modeled as an entity or an
  attribute?
• Should a concept be modeled as an entity or a
  relationship?
• Identifying relationships Binary or ternary?
  Aggregation?
• Constraints in the ER Model
• A lot of data semantics can (and should) be
  captured.
• But some constraints cannot be captured in ER
  diagrams.

43
Entity vs. Attribute

• Should address be an attribute of Employees or an
  entity (connected to Employees by a
  relationship)?
• Depends upon the use we want to make of address
  information, and the semantics of the data
• If we have several addresses per employee,
  address must be an entity (since attributes
  cannot be set-valued).
• If the structure (city, street, etc.) is
  important, e.g., we want to retrieve employees in
  a given city, address must be modeled as an
  entity (since attribute values are atomic).

44
Entity vs. Attribute
to
from

• Works_In4 does not allow an employee to work in a
  department for two or more periods.
• Similar to the problem of wanting to record
  several addresses for an employee We want to
  record several values of the descriptive
  attributes for each instance of this
  relationship. Accomplished by introducing new
  entity set, Duration.

budget
Department
Works_In4
name
ssn
lot
Works_In4
Department
Employee
45
Entity vs. Relationship

• First ER diagram OK if a manager gets a separate
  discretionary budget for each dept.
• What if a manager gets a discretionary budget
  that covers all managed depts?
• Redundancy dbudget stored for each dept managed
  by manager.
• Misleading Suggests dbudget associated with
  department-mgr combination.

since
dbudget
name
dname
ssn
did
lot
budget
Employee
Department
Manages2
name
ssn
lot
dname
since
did
budget
Employee
Department
Manages2
ISA
This fixes the problem!
Manager
dbudget
46
Ternary Relationship
pname
age

• If each policy is owned by just 1 employee, and
  each dependent is tied to the covering policy,
  first diagram is inaccurate.
• What are the additional constraints in the 2nd
  diagram?

Dependent
Covers
Bad design
pname
age
Dependent
Purchaser
Better design
47
Ternary Relationship

• Previous example illustrated a case when two
  binary relationships were better than one ternary
  relationship.
• An example in the other direction

EMPLOYEE
EMPLOYEE
works on
has
is associated with
is connected with
SKILL
PROJECT
SKILL
PROJECT
POOR DESIGN!
BETTER DESIGN!
48
ERD Checks

• Every entity, relationship, and attribute should
  be named.
• Each ERD must have at least one entity.
• Each entity may appear only once in an ERD.
• Each ERD must have at least one relationship.
• Each entity must be connected to a relationship.
• Each attribute must be connected to an entity or
  a relationship.
• Each relationship must be connected to either at
  least 2 entities (binary or ternary) or to one
  entity in two roles (unary).

49
ER Summary

• Conceptual design follows requirements analysis,
• Yields a high-level description of data to be
  stored
• ER model popular for conceptual design
• Constructs are expressive, close to the way
  people think about their applications.
• Basic constructs entities, relationships, and
  attributes (of entities and relationships).
• Some additional constructs weak entities, ISA
  hierarchies, and aggregation.
• Note There are many variations on ER model, and
  on notation.

50
Summary of ER (Contd.)

• Several kinds of integrity constraints can be
  expressed in the ER model key constraints,
  participation constraints, and overlap/covering
  constraints for ISA hierarchies. Some foreign
  key constraints are also implicit in the
  definition of a relationship set.
• Some constraints (notably, functional
  dependencies) cannot be expressed in the ER
  model.
• Constraints play an important role in determining
  the best database design for an enterprise.

51
Summary of ER (Contd.)

• ER design is subjective. There are often many
  ways to model a given scenario! Analyzing
  alternatives can be tricky, especially for a
  large enterprise. Common choices include
• Entity vs. attribute, entity vs. relationship,
  binary or n-ary relationship, whether or not to
  use ISA hierarchies, and whether or not to use
  aggregation.
• Ensuring good database design resulting
  relational schema should be analyzed and refined
  further. FD information and normalization
  techniques are especially useful.

52
Conceptual Data Models

• Entity Relationship Model (ER)
• Object-oriented Data Models
• Unified Modelling Language (UML)
• Brief summary in section 2.7 of the textbook
• UML Class diagrams have mostly equivalent power
  to ERDs
• But different notation
• Not required in COMP5138
• Object Modelling Technique (OMT), Booch,
• Others in the literature
• Object Role Model (ORM)
• Semantic Object Model (SOM)
• Semantic Data Model (SDM)

53
Wrap-Up

• Conceptual Modeling
• ERD
• Entity
• Entity set
• Attribute
• Identifier
• Relationship
• Constraints
• Ternary relationships
• Design choices
• EERD
• Generalization/Specialization
• Inheritance
• Aggregation
• Design Choices