1 / 19

Advanced Database System: Semantic Modeling for Database Design

Learn how semantic modeling can aid in the process of designing a database, even without direct DBMS support. Explore the E/R model, entity types, relationships, and more.

lmullins
Download Presentation

Advanced Database System: Semantic Modeling for Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. 14.1 Introduction • Database systems typically have only a very limited understanding of what the data in the database means. • 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 • 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 • Semantic concepts (See Fig. 14.1 (next slide)) • 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 • The entity/relationship model introduced by Chen in 1976. • E/R diagrams (See Fig. 14.2 (next slide)) • Entities: a thing which can be distinctly identified. • regular or strong entities • weak or dependent entities • 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.) • 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. • Entity Subtypes and Supertypes • subtype vs. supertype • inheritance a. properties b. relationships • entity type hierarchy (See Fig. 14.3) Advanced Database System

  8. Fig. 14.3 Entity type hierarchy Advanced Database System

  9. 14.4 E/R Diagrams • 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. • 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 • 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.) • 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 • 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) • An E/R diagram  a relational database definition • 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) • 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) • 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 ...; • 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) • 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) • Properties: An attribute • 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 • 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. • 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.) • 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

More Related