Class Number

1
Class Number CS 304
Class Name - DBMS
Instructor Sanjay Madria
Lesson Title EER Model 21th June
2
Figure 3.15 ER diagram for the COMPANY schema,
with all role names included and with structural
constraints on relationships specified using the
alternate notation (min, max).
3
5 Relationships of Higher Degree

• - Relationship types of degree 2 are called
  binary
• - Relationship types of degree 3 are called
  ternary and of degree n are called n-ary
• - In general, an n-ary relationship is not
  equivalent to n binary relationships

4
Figure 3.10 Some relationship instances of a
ternary relationship SUPPLY
5
Figure 3.16 An ER diagram for an airline
database.
6
Figure 3.17 An ER diagram for a BANK database.
7
Figure 3.18 An ER diagram for a database that
keeps track of company and employee phones.
8
Figure 3.19 An ER diagram for a database that
keeps track of textbooks used in courses.
9
6 Extended Entity-Relationship (EER)Model
10
Specialization and Generalization

• ER diagrams consider entity types to be primitive
  objects
• EER diagrams allow refinements within the
  structures of entity types
• Specialization top-down refinement into
  (super)classes and subclasses

11
Figure 4.1 EER diagram notation for representing
specialization and subclasses.
12
Figure 4.2 Some instances of the specialization
of EMPLOYEE into the SECRETARY, ENGINEER,
TECHNICIAN set of subclasses.
13

• Generalization groups entity types bottom up
  synthesis
• Subclasses inherit the attributes and
  relationships of their superclasses

14
WHY

• Relationship types only partially applicable to
  the superclass
• Attributes only partially applying to
  superclasses

15
Figure 4.3 Examples of generalization. (a) Two
entity types CAR and TRUCK. (b) Generalizing CAR
and TRUCK into VEHICLE.
16
Disjointness Constraints

• Disjoint (d)
• The subclasses must have disjoint sets of
  entities
• Overlap (o)
• The subclasses may have overlapping sets of
  entities

17
Figure 4.4 An attribute-defined specialization
on the JobType attribute of EMPLOYEE.
18
Completeness Constraints

• Partial
• An entity may not belong to any of the subclasses
  (single-line)
• Total
• Every entity in the superclass must be a member
  of some subclass (double-edge)

19
Membership Constraints

• Predicate defined subclasses
• The subclass is defined through a predicate on
  the attributes of the superclass
• Attribute defined subclasses
• The subclasses in the specialization are all
  defined by the same attribute of the superclass
• User defined subclasses
• Membership in the subclasses is determined at the
  insertion operation level

20
Figure 4.5 Notation for specialization with
overlapping (nondisjoint) subclasses.
21
Structures in Specialization

• Multiple Specializations
• Specialization Hierarchy
• Each subclass belongs to at most one class
• Lattice Specializations
• A subclass may belong to more than one class

22
Figure 4.6 A specialization lattice with the
shared subclass ENGINEERING_MANAGER.
23
Categories

• Associate more than one superclass to a subclass.
  
• In categories, different entries of the subclass
  may inherit attributes from different superclsses
  
• An entity in Category is a member of only one of
  its superclass
• In specializations, all the subclasses inherit
  all the attributes of the superclass
• Category types Total or Partial

24
Figure 4.7 A specialization lattice (with
multiple inheritance) for a UNIVERSITY database.
25
Figure 4.8 An illustration of how to represent
the UNION of two or more entity types/classes
using the category notation. Two categories are
shown OWNER and REGISTERED_VEHICLE.
26
Class Number CS 304
Class Name - DBMS
Instructor Sanjay Madria
Lesson Title ER to Relational 26th June
27
7 Mapping ER and EER Schemas into the Relational
Model

• Steps of The Algorithm
• (Chapter 9 pages 290 to 296, Elmasri/Navathe
  ed. 3)
•  
• -  STEP 1 Map Entity Types
• Each strong Entity to a table
• All simple attributes will become column in the
  table
• Include only simple attributes of the composite
  attribute in the table as columns
• Derived attribute will not become part of the
  table
• Choose key attribute as Primary key of the table
•  
•  
• - 
•  
• -

28

• -  STEP 2 Map Weak Entity Types to a table and
  draw identifier from parent entity type into weak
  entity type
• Key of weak entity will be partial key of weak
  entity and key attribute of the owner entity on
  which it depends.

29

• Map Relationship Types (STEP 3)
• 11 - options for setting up one, two or three
  relations
• Include PK of one of the entity T into other, say
  S, better to choose the PK of the entity type T
  and include that in the entity S with total
  participation in the relation.
• Include attributes of R in S
• No table for R
• Or a table for R with PK of both plus its own
  attributes or all the attributes into one
  relation

30

• 1N the many side of the relationship type T
  provides a PK to the one side, say S, no new
  relation
• - include attributes of R into S
• MN need to set up a separate relation for the
  relationship
• - include PKs of T and S , and attributes of R
  into new table
• STEP 4 Map multivalued attributes set up a new
  relation for each multi-valued attribute and the
  PK of the corresponding entity type
•  
• -

31

• STEP 5 Mapping of generalization hierarchies and
  set-subset relationships possiblity of
  collapsing into one relation vs. as many
  relations as the number of distinct classes.
• Convert each subclass S and superclass C, where
  attributes of C are k, a,b.. and k is PK of C
  into a relation using following
• Create a table L for C with attributes of L are
  k, a, b.. and PK(L) k. Create a table for
  each subclass S, with attributes of S are k U
  attributes of S, with PK of S as k
• Create a relation for S , with attributes of S as
  k,a, b.. and its own attributes and PK k.

32
Mapping Categories

• Specify a new key called a surrogate key when
  creating a relation for category.
• (Because keys for all participating classes are
  different)
• Include any attribute of its own
• Add the surrogate key as foreign key to all other
  participating relations
• If a categorys superclasses share the same key
  , there is no need for surrogate key

33

• .       Problem statement Concise but thorough
  description of the application for which you
  propose to build a database system, and why the
  database system is essential.
• 2  System requirements Define the scope of the
  system -- what it does, what it doesnt do, and
  how the system will be used.
• 3  Conceptual database design Documents the
  conceptual database design using ER/EER diagrams.

34

• 4. Functional requirements Describe the various
  retrieval and update transactions and discuss how
  they collectively meet the database system
  requirements.
• 5.    Estimate of effort Discuss the expected
  effort required (in terms of person-weeks) to
  complete the design and implementation of the
  complete system as proposed.

35

• Keep in mind that you have a limited amount of
  time to complete the project so you need to be
  aggressive but realistic in your design.
•