COMP 5138 Relational Database Management Systems - PowerPoint PPT Presentation

1 / 53
About This Presentation
Title:

COMP 5138 Relational Database Management Systems

Description:

COMP 5138 Relational Database Management Systems Lecture 2 Conceptual Database Design – PowerPoint PPT presentation

Number of Views:174
Avg rating:3.0/5.0
Slides: 54
Provided by: Kelli183
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com