Topic 2: Semantic Data Model
Ted Codd s Relational Model Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks, IBM Research Report RJ 599 ( August 19th, 1969) A Relational Model of Data for Large Shared Data Banks, CACM 13, No.6 (June 1970)
Ted Codd s Data Model Incidentally, it s not as widely known as it should be that Ted not only invented the relational model in particular, he invented the whole concept of a data model in general. See his paper: Data models in Database Management, ACM SIGMOD Record 11, No.2 (Feb 1981)
Ted Codd s essentiality to The Great Debate The Great Debate the official title was Data Models: Data-Structure-Set vs. Relational was a special event held at the 1974 SIGMOD Workshop Information Principle: The entire information content of a relational database is represented in one and only one way: namely, as attribute values within tuples within relations
See SIGMOD Record, Vol.32, No.4, Dec 2003 A new formal definition of the relation model Briefly, the relational model consists of five components: An open-ended collection of scalar types (including the type boolean or truth value) A relation type generator Facilities for defining relation variables of such generated relation types A relational assignment operation for assigning relation values to such relation variables An open-ended collection of generic relational operators ( the relational algebra ) for deriving relation values from other relation values
C.J. Date :, C J Date
Relationship( has-subtype) has-attribute) instance) Data abstraction Constraints Unstructured objects Dynamic properties of an application
1 EER RM/T
2 SHM ADD LGDM SAM SDM RM/T SHM+ TAXIS Event Model : Semantic Data Models, ACM Computing Surveys, Vol.20(3),1988
---From Lowell Report Challenge Integration of Text, Data, Code, and Streams Structured data Text, space, time, image, and multimedia data Procedural data, that is data types and the methods that encapsulate them Triggers Data Streams and queues
Entity-Relationship Model ER model was proposed by Peter Chen in 1976. Many extensions have been made (Extended Entity-Relationship model or EER model). There is a dedicated International Conference on ER Approach. ER model has become de facto standard tool for conceptual schema design.
Extended ER Model (EER Model) Various extensions to ER model exist. We introduce two extensions: More accurate connectivity description. Generalization/specialization hierarchy. Aggregation
Multiplicity for Complex Relationships(1) One in ER model means zero or one. Many in ER model means zero or more. Multiplicity: The number (or range) of possible occurrences of an entity type in an n- ary relationaship when the other(n-1) values are fixed Students (1, 5) (5, takes 60) Courses
Multiplicity for Complex General format: Relationships(2) E (min_card, max_card) R 0 <= min_card <= max_card Interpretation: Each entity in E may involve between min_card and max_card relationships in R.
Multiplicity for Complex Relationships(3) Alternative ways to represent multiplicity constraints 0,1 zero or one entity occurrence 1,1 Exactly one entity occurrence 0,* zero or many entity occurrence 1,* one or many entity occurrence 5,60 min of 5 up to max of 60 entity 0,3,6-8
Cardinality and Participation Constraint (1) Multiplicity actually consists of two separate constraints know as Cardinality and Participation constraints Cardinality: describes the max number of possible relationship occurrences for an entity participating in a given relationship type Cardinality of a binary relationship is what we previously referred to as a one-to-one, one-to-many, and many-to-many
Cardinality and Participation Constraint (2) Participation: Determines whether all or only some entity occurrences participate in a relationship A Participation constrain represents whether all entity occurrences are involved in a particular relationship (ref. to as mandatory participation) or only some (ref. to as optional participation)
Cardinality and Participation Constraint (3) Definition: If every entity in E involves at least one relationship in R (i.e., min_card >= 1), E is said to have total (or mandatory) participation in R. If min_card = 0, E is said to have partial (or optional) participation in R.
Cardinality and Participation Constraint (4) Employees has a partial participation. Departments has a total participation. One emp manages One dept Cardinality One dept is managed By one emp Employees (0, 1) (1,1) manages Departments Not all emp All dept are managed Manage dept (optional) (mandatory) Participation
One emp manages One dept Cardinality One dept is managed By one emp Employees (1, 1) (0,1) manages Departments All emp Manage dept (Mandatory) Not all dept are managed (optional) Participation
Representing 1-to-1, 1-to-m, m-to-m Relationships one-to-one: many-to-many: one-to-many: E (0, 1) R (0, 1) F E (0, m) R (0, n) F E (0, m) R (0,1) F E 1 R m F
Problems with ER Models Problems may arise when designing a conceptual data model called connection traps. Often due to a misinterpretation of the meaning of certain relationships. Two main types of connection traps are called fan traps and chasm traps.
Problems with ER Models Fan Trap Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous. Chasm Trap Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences.
An Example of a Fan Trap
Semantic Net of ER Model with Fan Trap At which branch office does staff number SG37 work?
Restructuring ER Model to Remove Fan Trap
Semantic Net of Restructured ER Model with Fan Trap Removed SG37 works at branch B003.
An Example of a Chasm Trap
Semantic Net of ER Model with Chasm Trap At which branch office is property PA14 available?
ER Model Restructured to Remove Chasm Trap
Semantic Net of Restructured ER Model with Chasm Trap Removed
Generalization (1) Definition: Generalization is the process of defining a generalized entity type from a given type of semantically related entity sets. GPA SSN Name GRE GPA SSN Name SAT G_Students UG_Students
Generalization (2) SSN Name GPA Students G_Students UG_Students GRE SAT
Generalization (3) Students is a super entity type (supertype, supperclass) G_Students and UG_Students are sub entity types (subtypes, subclass) Super entity type has only and all common attributes of sub entity types. Inheritance Principle: A sub entity type inherits all properties (attributes and relationships) from the super entity type.
Specialization Definition: Specialization is the process of defining a specialized entity types from a given entity type. IS_A semantics: every entity in a sub entity type is also an entity in the super entity type. Generalization and specialization hierarchies are called IS_A hierarchies.
AllStaff Relation Holding Details of all Staff
Specialization/Generalization of Staff Entity into Subclasses Representing Job Roles
Constraints on Specialization / Generalization Two constraints that may apply to a specialization/generalization: participation constraints, disjoint constraints. Participation constraint Determines whether every member in superclass must participate as a member of a subclass. May be mandatory or optional.
Constraints on Specialization / Generalization Disjoint constraint Describes relationship between members of the subclasses and indicates whether member of a superclass can be a member of one, or more than one, subclass. May be Disjoint (Or): can be a member of only one of the subclass nondisjoint(and): can be a member of more than one subclass
Constraints on Specialization / Generalization There are four categories of constraints of specialization and generalization: mandatory and disjoint; optional and disjoint; mandatory and nondisjoint; optional and nondisjoint.
Specialization/Generalization of Staff Entity into Subclasses Representing Job Roles
Specialization/Generalization of Staff Entity into Job Roles and Contracts of Employment
EER Diagram with Shared Subclass and Subclass with its own Subclass
DreamHome Worked Example - Staff Superclass with Supervisor and Manager Subclasses http://cwx.prenhall.com/bookbind/pubbooks/ema_he_uk_connolly_da tasys_3/chapter10/deluxe.html
DreamHome Worked Example - Owner Superclass with PrivateOwner and BusinessOwner Subclasses
DreamHome Worked Example - Person Superclass with Staff, PrivateOwner, and Client Subclasses
Guidelines for Representation of Superclass / Subclass Relationship See page 451
DreamHome Worked Example - {Mandatory, And} AllOwner(ownerNo, address, telno,fname, lname, bname, btype, pownerflag, bownerflag)
DreamHome Worked Example - {Optional, And} Owner(ownerNo, address, telno) OwnerDetail(ownerNo,,bType, pownerflag, bownerflag)
DreamHome Worked Example - {Mandatory, Or} PriviteOwner(ownerNo,Name, address, telno) BusinessOwnerDetail(ownerNo,bName,bType, address, telno)
DreamHome Worked Example - {Optional, Or} Owner(ownerNo, address, telno) PriviteOwner(ownerNo, Name, address, telno) reference Owner(ownerNo) BusinessOwnerDetail(ownerNo,bName,bType, address, telno)
Aggregation Represents a has-a or is-part-of relationship between entity types, where one represents the whole and the other the part.
Examples of Aggregation
Composition Specific form of aggregation that represents an association between entities, where there is a strong ownership and coincidental lifetime between the whole and the part.
Example of Composition
ER Diagram of Branch View of DreamHome