class number – cs 304
DESCRIPTION
Class Number – CS 304. Class Name - DBMS. Instructor – Sanjay Madria. Lesson Title – EER Model –21th June. 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). - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/1.jpg)
1
Class Number – CS 304Class Number – CS 304
Class Name - DBMSClass Name - DBMS
Instructor – Sanjay Madria
Instructor – Sanjay Madria
Lesson Title – EER Model –21th June
Lesson Title – EER Model –21th June
![Page 2: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/2.jpg)
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).
![Page 3: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/3.jpg)
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
![Page 4: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/4.jpg)
4
Figure 3.10 Some relationship instances of a ternary relationship SUPPLY
![Page 5: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/5.jpg)
5
Figure 3.16 An ER diagram for an airline database.
![Page 6: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/6.jpg)
6
Figure 3.17 An ER diagram for a BANK database.
![Page 7: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/7.jpg)
7
Figure 3.18 An ER diagram for a database that keeps track of company and employee phones.
![Page 8: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/8.jpg)
8
Figure 3.19 An ER diagram for a database that keeps track of textbooks used in courses.
![Page 9: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/9.jpg)
9
6 Extended Entity-Relationship (EER)Model
![Page 10: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/10.jpg)
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
![Page 11: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/11.jpg)
11
Figure 4.1 EER diagram notation for representing specialization and subclasses.
![Page 12: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/12.jpg)
12
Figure 4.2 Some instances of the specialization of EMPLOYEE into the {SECRETARY, ENGINEER, TECHNICIAN} set of subclasses.
![Page 13: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/13.jpg)
13
• Generalization groups entity types; bottom up synthesis
• Subclasses inherit the attributes and relationships of their superclasses
•
![Page 14: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/14.jpg)
14
WHY
• Relationship types only partially applicable to the superclass
• Attributes only partially applying to superclasses
![Page 15: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/15.jpg)
15
Figure 4.3 Examples of generalization. (a) Two entity types CAR and TRUCK. (b) Generalizing CAR and TRUCK into VEHICLE.
![Page 16: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/16.jpg)
16
Disjointness Constraints
• Disjoint (d) • The subclasses must have disjoint sets of
entities
• Overlap (o) • The subclasses may have overlapping sets of
entities
![Page 17: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/17.jpg)
17
Figure 4.4 An attribute-defined specialization on the JobType attribute of EMPLOYEE.
![Page 18: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/18.jpg)
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)
![Page 19: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/19.jpg)
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
![Page 20: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/20.jpg)
20
Figure 4.5 Notation for specialization with overlapping (nondisjoint) subclasses.
![Page 21: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/21.jpg)
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
![Page 22: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/22.jpg)
22
Figure 4.6 A specialization lattice with the shared subclass ENGINEERING_MANAGER.
![Page 23: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/23.jpg)
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
![Page 24: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/24.jpg)
24
Figure 4.7 A specialization lattice (with multiple inheritance) for a UNIVERSITY database.
![Page 25: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/25.jpg)
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.
![Page 26: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/26.jpg)
26
Class Number – CS 304Class Number – CS 304
Class Name - DBMSClass Name - DBMS
Instructor – Sanjay Madria
Instructor – Sanjay Madria
Lesson Title – ER to Relational –26th June
Lesson Title – ER to Relational –26th June
![Page 27: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/27.jpg)
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- - -
![Page 28: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/28.jpg)
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.
![Page 29: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/29.jpg)
29
Map Relationship Types (STEP 3):1:1 - 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
![Page 30: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/30.jpg)
30
1:N – 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 M:N – 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
-
![Page 31: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/31.jpg)
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
1. 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}
2. Create a relation for S , with attributes of S as {k,a, b..} and its own attributes and PK = k.
![Page 32: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/32.jpg)
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 category’s superclasses share the same key ,
there is no need for surrogate key
![Page 33: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/33.jpg)
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 doesn’t do, and how the system will be used.
3 Conceptual database design: Documents the conceptual database design using ER/EER diagrams.
![Page 34: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/34.jpg)
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.
![Page 35: Class Number – CS 304](https://reader036.vdocuments.us/reader036/viewer/2022062304/5681462c550346895db33b27/html5/thumbnails/35.jpg)
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.
•