376a. Database Design - PowerPoint PPT Presentation

About This Presentation
Title:

376a. Database Design

Description:

Title: PowerPoint Presentation Last modified by: Lifenet5 Created Date: 1/1/1601 12:00:00 AM Document presentation format: On-screen Show Other titles – PowerPoint PPT presentation

Number of Views:86
Avg rating:3.0/5.0
Slides: 33
Provided by: vas136
Category:

less

Transcript and Presenter's Notes

Title: 376a. Database Design


1
376a. Database Design
  • Dept. of Computer Science
  • Vassar College
  • http//www.cs.vassar.edu/cs376
  • Class 2 Entity Relationship model and EER

2
Who am I
  • Bill Yoshimi
  • Office 106 Old Laundry Building
  • Phone 437-5986
  • Email yoshimi_at_cs.vassar.edu
  • Office Hours 415-515PM MW

3
Housekeeping
  • Webpage is outdated.
  • We will basically be following the same outline.
  • Verify that you can login to the CS department
    Solaris machines.

4
First Assignment
  • Questions 3.16, 3.17, 3.18, and 3.21
  • Print neatly.
  • Due 9/18 at start of class.

5
Entity-Relationship Modeling
  • From last class remember
  • Entities model real world objects or processes.
  • Entities have attributes. Attributes can be
    simple, composite, multi-valued, multi-leveled
    and stored or derived.
  • Entities are distinguishable from other entities.
  • Every entity must have a unique value for its
    key attribute. May be composite (superkey). May
    have more than one key.

6
Relationships
  • Relate two or more entities together. Binary
    two entities, Ternary three entities, N-ary N
    entities.
  • Relationships may have attributes.
    WorksOn(Person, Project, Hours) where hours is
    the number of hours Person works on Project.

7
Value Sets of Attributes
  • A Attribute
  • E Entity Type
  • V Value Set
  • AE -gt P(V)
  • P(V) is the power set of V(the set of all subsets
    of V).

8
Value Sets of Attributes cont.
  • A(e) value of attribute A for entity e
  • A(e) is a singleton for SV attributes (has only
    one element)
  • A(e) may be empty set, single element or multiple
    element for multivalued attribute.
  • A(e) for composite attribute is Cartesian product
    (all pairs mapping) of P(V1)xP(V2)xP(V3)
  • Use () and comma separated list to denote
    composite attribute.
  • Use and comma separated list to denote
    multi-valued attribute

9
Value Set of Entity cont.
  • E.g. if a person can have more than one residence
    and each residence can have more than one phone
    then the attribute AddressPhone
  • AddressPhone(Phone(AreaCode,PhoneNumber),
    Address(StreetAddress(Number, Street,
    ApartmentNumber),City,State,Zip))

10
Relationships
  • Relationship type R among N entity types E1EN
    is a set of associations among these types.
  • R is a set of ri where each ri is an n tuple of
    (e1, e2, en) and each ej in ri is a member of
    entity type Ej 1ltjltN

11
Example
EMPLOYEE
DEPARTMENT
WORKS_FOR
e1 e2 e3 e4 e5 e6 e7
r1 r2 r3 r4 r5 r6 r7
d1 d2 d3 d4 d5
12
Degree of a Relationship Model
  • Degree is number of participating Entity Types.
  • In previous example, degree is 2 or binary. (most
    used form)
  • Ternary relationship type has three Entity Types.
    (holds more information than 3 binary
    relationships).
  • Connection trap can occur when 3 binary relations
    used instead of ternary relation.

13
Relations as Attributes
  • Example entity type
  • EMPLOYEE
  • Name (Fname, Mname, Lname), SSN, Sex, Address,
    Salary, Bdate, Department, Supervisor, WorksOn
    (Project, Hours)
  • Department is attribute of entity EMPLOYEE
  • The value set of Department is set of all
    DEPARTMENT entities.
  • If Employee is attribute of DEPARTMENT, then 2
    are constrained to be inverses of each other!

14
Role Name
  • What is the role of the entity in the
    relationship. Important when entity type serves
    multiple roles in a relationship.
  • In SUPERVISION relationship there is a supervisor
    and supervisee.
  • If an entity participates in multiple
    relationships a relationship type but in
    different roles, the relationship type is
    recursive.

15
Structural relationship type constraints
  • Cardinality 11, 1N, NM constrains the number
    of relationship instances an entity can
    participate in.
  • Participation total and partial
  • Total every entity in the total set of E must
    participate in the relationship. (also called the
    Existence dependency)
  • Partial some of E must participate.
  • Can be specified as 0ltminltmax for all e in E.

16
Relationships can have attributes
  • If an attributes value is determined by a
    combination of the participating entities, it
    should be an attribute of the relationship.

17
Weak entity types
  • Entities without key attributes
  • Must be associated with an identifying owner.
    Always has a total participation constraint
    (cant exist by itself).
  • Partial key is identifying owner set of
    attributes to identify the weak entity for the
    same owner entity.
  • e.g. DEPENDANT - must be associated with
    EMPLOYEE (Dname, Bdate,Sex, Relationship).

18
Entity Relationship Diagrams
19
How to draw relations
20
Example of COMPANY database
  • (relationships given on P. 53)

21
Convention used in diagrams
  • Singular names used for entity types
  • Entity types and relationship types in UPPERCASE.
  • Attributes names have first letter capitalized.
  • Role names are lower case.

22
Demonstrate Ternary relationship
  • SUPPLIER, PART, and PROJECT
  • SUPPLY
  • Vs.
  • SUPPLIER, PART, PROJECT
  • CAN_SUPPLY, SUPPLIES, USES
  • ! Can have relationships in 2 not in 1 unless we
    impose other constraints

23
Problems defining relationships
  • Connection Traps (what dept is the employee in?)

COMPANY
1
1
HAS
HAS
N
N
DEPT
EMPLOYEE
24
Resolving traps
DEPT
N
1
HAS
HAS
1
N
COMPANY
EMPLOYEE
25
Problems with model
  • No relations between an entity and a collection
    of entity types.
  • No relation between and entity and a relation
  • No inheritance

26
Enhanced ER Model
  • Uses subclasses
  • Inherits all attributes from superclass
  • Subclass entity is a superclass entity.
  • Accomplished through specialization and
    generalization
  • Specialization defining subclasses
  • Generalization defining common abstractions

27
Subclasses
  • Subclasses can be disjoint or overlapping.
    Represent intersection of all superclasses.

PERSON
o
EMPLOYEE
STUDENT
28
Specialization Constraints
  • Defined by predicates (Grade 4)
  • Defined by attributes
  • Defined by user
  • Disjoint vs. overlapping.
  • Completeness (total or partial).

29
Multiple Inheritance
  • Entity class can be subclass of multiple
    superclasses (inherits attributes from all
    superclasses)
  • Structure of inheritance
  • Hierarchy (only one subclass/superclass
    relationship) for each subclass
  • Lattice

30
Union types
N
OWNS
OWNER
M
REGISTERED_VEHICLE
U
COMPANY
PERSON
BANK
  • OWNER is only subclass of the union (member of
    exactly one super class.)

31
Difference between subclasses and categories
(unions)
  • Subclasses inherit all attributes of all of the
    super classes.
  • Categories, subclass inherits from only one
    superclass.

32
Pages 409-424
Write a Comment
User Comments (0)
About PowerShow.com