Chapter 14 Semantic Modeling Prof YinFu Huang CSIE

Chapter 14 Semantic Modeling Prof. Yin-Fu Huang CSIE, NYUST

14. 1 Introduction n Database systems typically have only a very limited understanding of what the data in the database means. n Semantic modeling ideas can be useful as an aid in the process of database design, even in the absence of direct DBMS support for those ideas. Advanced Database System 2

14. 2 The Overall Approach n Four steps: 1. To identify a set of semantic concepts that seem to be useful in talking informally about the real world. e. g. , entities, entity types, instances properties, identity, relationships 2. To devise a set of corresponding symbolic objects that can be used to represent the foregoing semantic concepts. e. g. , E-relations, P-relations Advanced Database System 3

14. 2 The Overall Approach (Cont. ) 3. To devise a set of formal, general integrity rules to go along with those formal objects. e. g. , property integrity 4. To develop a set of formal operators for manipulating those formal objects. e. g. , Property operator n Semantic concepts (See Fig. 14. 1 (next slide)) n The very same object in the real world might legitimately be regarded as an entity by some people, as a property by others, as a relationship by still others. Advanced Database System 4

Fig. 14. 1 Semantic concepts Advanced Database System 5

14. 3 The E/R Model n The entity/relationship model introduced by Chen in 1976. n E/R diagrams (See Fig. 14. 2 (next slide)) n Entities: a thing which can be distinctly identified. Ø regular or strong entities Ø weak or dependent entities n Properties Ø simple or composite Ø key Ø single or multi-valued Ø missing Ø base or derived Advanced Database System 6

Fig. 14. 2 E/R diagrams Advanced Database System 7

14. 3 The E/R Model (Cont. ) n Relationships: an association among entities Ø participants Ø the degree of the relationship Ø total or partial Ø one-to-one, one-to-many, many-to-many • The many-to-many case is the only one that demands representation by means of a separate relvar. n Entity Subtypes and Supertypes Ø subtype vs. supertype Ø inheritance a. properties b. relationships Advanced System Ø entity type hierarchy (See. Database Fig. 14. 3) 8

Fig. 14. 3 Entity type hierarchy Advanced Database System 9

14. 4 E/R Diagrams n The popularity of the E/R model as an approach to database design can probably be attributed more to the existence of the E/R diagramming technique. n Entities: a rectangle Ø regular entity type a rectangle e. g. , Department, Employee, Supplier, Part, Project Ø weak entity type the border of the rectangle is doubled. e. g. , Dependent n Properties: an ellipse Ø derived dotted or dashed Ø multi-valued doubled Ø composite further ellipses Ø key underlined. Advanced Database System 10

14. 4 E/R Diagrams (Cont. ) n Relationships: a diamond Ø between a weak entity type and an entity type: the diamond border is doubled Ø one-to-one, many-to-one: the line is labeled “ 1” or “M” Ø total the line is doubled e. g. , many-to-one: Dept_Emp, Emp_Dep, Proj_Manager many-to-many: Proj_Work, Supp_Part_Proj, Supp_Part, Part_Structure n Entity Subtypes and Supertypes Ø “the isa relationship” a solid line marked with an arrowhead Advanced Database System 11

14. 5 Database Design with the E/R Model (1/5) n An E/R diagram a relational database definition n Regular entities: a base relvar e. g. , Var Dept Base Relation { Dept# …, … } Primary Key {Dept#}; Advanced Database System 12

14. 5 Database Design with the E/R Model (2/5) n Many-to-many relationships: a base relvar e. g. , Var SP Base Relation { S#. . . , P#. . . , . . . } Primary Key {S#, P#} Foreign Key {S#} References S On Delete Restrict On Update Cascade Foreign Key {P#} References P On Delete Restrict On Update Cascade; ØTwo participants two foreign keys ØIf that combination is unique, and if the designer has no objection to composite primary keys two foreign keys as the primary key Advanced Database System 13

14. 5 Database Design with the E/R Model (3/5) n Many-to-one relationships: Ø To introduce a foreign key in the relvar on the “many” side of the relationship that references the relvar on the “one” side. e. g. , Var Emp Base Relation { Emp#. . . , Dept#. . . , . . . } Primary Key {Emp#} Foreign Key {Dept#} References Dept On Delete. . . On Update. . . ; n One-to-one relationships: the same way as many-to-one relationships Advanced Database System 14

14. 5 Database Design with the E/R Model (4/5) n Weak entities: a many-to-one relationship Ø The Delete and Update rules for the relationship must be as follows: On Delete Cascade On Update Cascade e. g. , Var Dependent Base Relation { Emp#. . . , … } Primary Key { Emp#, Dep_name } Foreign Key {Emp#} References Emp On Delete Cascade On Update Cascade; • If the database designer has no objection to composite primary keys the combination of the foreign key and the weak entity “key” Advanced Database System 15

14. 5 Database Design with the E/R Model (5/5) n Properties: An attribute n Entity Supertypes and Subtypes: a base relvar e. g. Var Pgmr Base Relation { Emp#. . . , Lang. . . , . . . } Primary Key {Emp#} Foreign Key {Emp#} References Emp On Delete Cascade On Update Cascade; Advanced Database System 16

14. 6 A Brief Analysis n The E/R model as a Foundation for the Relational Model? Ø The relational model is a formal system; the E/R model is not a formal model. Ø The relational model was originally and explicitly based on some rather E/R-like ideas. n Is the E/R Model a Data Model? Ø The E/R model is indeed a data model, but one that is essentially just a thin layer on top of the relational model. Ø The fundamental E/R data object the n-ary relation Ø The E/R operators the operators of the relational algebra Ø The integrity a given relvar represents a certain kind of relationship Advanced Database System 17

14. 6 A Brief Analysis (Cont. ) n Entities vs. Relationships Ø The very same object can quite legitimately be regarded as an entity by some users and a relationship by others. Ø Example: a marriage • Relationship “Who was Elizabeth Taylor married to in 1975? ” • Entity “How many marriages have been performed in this church since April? ” Advanced Database System 18

The End. Advanced Database System 19