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 [phone]
• 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