Semantic Data Modeling

o What is Conceptual Data Modeling o Entity-Relationship (E-R ) Modeling o Limitations of E-R Modeling o Object-oriented Modeling: Another semantic model (discussed later in this course)

Semantic Modeling 1

CIS

What Is Conceptual Data Modeling?

A process that represents the entities, relationships, and activities of an enterprise in terms of a set of abstract concepts of a chosen data model for specific purposes.
Enterprise Modeling, Business Modeling

Conceptual Conceptual Perception Perception of an of an Enterprise Enterprise

Semantic Modeling 2

CIS

Bridge the Gap

STUDENT( ID, Name, Age, Address, GPA ) ) STUDENT( ID, Name, Age, Address, GPA INSTRUCTOR ( (Emp#, Name, Rank, Dept ) ) INSTRUCTOR Emp#, Name, Rank, Dept COURSE ( (Course#, Credits, Title ) ) COURSE Course#, Credits, Title CLASS ( (Emp#, ID, Course#, Time, Room ) ) CLASS Emp#, ID, Course#, Time, Room

Semantic Modeling 3

CIS

Data Semantics
o Static Information
u Data -- Entities u Associations -- Relationships among entities

o Dynamic Information
u Activities -- Operations/transactions u Integrity constraints -- Business rules/regulations and data meanings

Semantic Modeling 4

CIS

Conceptual Data Model Revisited

A conceptual data model consists of:
o A collection of formal concepts o A set of usage rules

Different model has different modeling capability

Object-Oriented Object-Oriented data modeling data modeling -- E-R -- EER -- etc.

Semantic data Semantic data modeling modeling -- Hierarchical -- Network -- Relational

Conventional (Logical Conventional (Logical data modeling) data modeling)

Semantic Modeling 5

CIS

E-R Modeling
o Introduced by Peter Chen in 1976 o Basic modeling concepts:
u Entities, entity types, and attributes u Relationships
Date
Works_for

Department

Emp#
Language

N
1
Instructor

Date
1
Assigned Office

Name

FName

Time
MInit

LName

Teaches

Location
M

Course

Semantic Modeling 6

CIS

E-R Notation

Entity

Relationship

Attribute

Primary Key

Semantic Modeling 7

CIS

Entities
o An entity is a conceptual object o Physically exists 4 Usually a noun in requirement specification
Alice CIS 2010 CIS 3730

Jose

Steve

Class Acct Student Department

Semantic Modeling 8

CIS

CIS

Entity Types
o A collection of similar entities o An abstraction of "physical" entities 4 A noun in requirement specifications 4 Having "independent" meaning
Department Department Student Student Jose Alice Steve Course Course CIS Acct

CIS 2010
Semantic Modeling 9

CIS 3730

CIS

Weak Entity Types

o Can't exist in DB independently o Must be identified by its owner
u Owner entity type u Identify relationship u Partial key u Total participation

Parking Lot# 1

Date N

Color

PNum

Student

Registers

Car

Semantic Modeling 10

CIS

Attributes
Properties or characteristics of entities and entity types
u Attribute values -- Properties of entities u Value set - All acceptable attribute values u Attributes (definitions) -- Properties of entity types

4 A noun or an adjective in requirement specifications 4 No "independent" meaning

ID Student Student Jose Age "123-45-6789" 25

Semantic Modeling 11

CIS

Key Attributes
One or a group of attributes that can uniquely identify individual entities of an entity type
u A key refers to one or a group of attributes as a whole u A key attribute is a component attribute of a key u Key changes with data semantics

An entity type may have several qualified keys

u Primary key -- One of the candidate keys u Alternate key - Candidate keys not used as the primary key u Secondary key -- An identifier of records with similar properties of interest

4 The primary key attribute(s) is(are) underlined

Semantic Modeling 12

CIS

More Attributes
o Simple attribute
Contains atomic values only degrees id Student Student age

o Composite attribute
Has component attributes __________________________________

name Lname Mname

o Single-valued attribute
Has exactly one value per entity

Fname

o Multi-valued attribute
Contains repeating values per entity __________________________________ CommEarned EmpNo CommRate Salesrep Salesrep Fname Mname Lname

o Derived attribute
Attribute values computed by means of other attributes
Semantic Modeling 13

CIS

Relationships
Associations among entities
u Relationships -- Associations among entities

4 Usually a verb in requirement specification

Occurrence Diagram or Semantic Net
Student Joseph Alice Sue Tom Peter ... Course CIS2010 CIS3210 CIS3215 CIS3730 CIS8140 ...

Takes

Student

Takes

Course

Semantic Modeling 14

CIS

Relationship Degrees
The number of entity types associated with that relationship. Each entity in a relationship is known as a participant.
Unary Employee Employee Supervises Employee Employee Binary Work Department Department

Ternary Project Project Supply Part Part

n-ary

Supplier Supplier

Semantic Modeling 15

CIS

N-ary Relationships
o A link must associate with all participants o Cardinality is with respect to individual relationships 4 A N-ary relationship is not equivalent to N binary relationships

Customer N M 1

Salesperson

ships

Order

Semantic Modeling 16

CIS

Relationship Attribute
o Describes the association 4 A adverb or noun in requirement specification

sect# Instructor

time

teaches

Course

room#

date

Semantic Modeling 17

CIS

Relationship Cardinality
How entities are connected through a relationship
u One-to-One -- An entity of E1 is connected to at most one entity of E2 and vice versa. u One-to-Many -- An entity of E1 may be connected to one or more entities of E2, but an entity of E2 can only be mapped to at most one entity of E1. u Many-to-Many -- An entity of E1 may be linked to one or more entities of E2, and vice versa.

E1
a b c ...
1

R
[] [] [] ...
1
1

E2
x y z ...

a b c ...
M

[] [] [] ...
N

x y z ...

a b c ...

...

x y z ...

Semantic Modeling 18

CIS

Relationship Types
Recursive Relationship

Relationship type where same entity type participates more than once in different roles.

Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship.

Semantic Modeling 19

CIS

Recursive Relationship called Supervises with Role Names

Semantic Modeling 20

CIS

Entities associated through two distinct Relationships with Role Names

Semantic Modeling 21

CIS

Participation Constraints
Let R be a relationship type involves entity type E as a participant. If every instance of E participates in at least one instance of R, then the participation is said to be total; other wise it is said to be partial [Date, 2004] o Partial (optional) participation
An entity of E does not have to be mapped to another entity through the relationship. Writer Writer 1

Publishes

o Total (mandatory) participation

Every entity of E must be connected through the relationship to other entity ( or entities ).
Semantic Modeling 22

M Book Book

CIS

Structural Constraints
Main type of constraint on relationships is called multiplicity. Multiplicity - number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship. Represents policies (called business rules) established by user or company.
CIS

Semantic Modeling 23

Multiplicity

Faculty
(0,4)

Teaches
(1,1)

Course
(1,45)

Takes
(?,?)

Student

Semantic Modeling 24

CIS

Other E-R Models

Relationship Entity 1 attr. 2 Entity 2

attr. 1

Entity 1

Relationship

Entity 2

( 1:m ) Entity 1 Relationship

( 0:5 )

Entity 2

Semantic Modeling 25

CIS

Pros and Cons of E-R

Advantages
u Simple and easy to understand. u Very popular. u Semantic richer than classical data models. Emp#, Name, Address Salary, Skill Project Member Works-on

Disadvantages:
u Not a formally defined data model. u Deals with some integrity constraints. u Difficult to distinguish entities from relationships. u Has redundant modeling information.

m-has ID, Name Address Birth-date

Project

Dependent

has

Manages

Project Manager

Emp#, Name, Address Salary, Skill

Semantic Modeling 26

CIS

Is-A Relationship
o Generalization and specialization hierarchy
u Supertypes -- Hide the differences of subtypes u Subtypes -- Reveal specific properties

Generalization

Person
Is-A Is-A

Student
Is-A Is-A

Staff
Is-A Is-A

Specialization

UnderGrad

Grad Faculty

Secretary

Semantic Modeling 27

CIS

Inheritance
o A supertype contains the common properties of all its subtype entities. o Subtype inherits properties of its supertype and may have its own properties.
Person Person
Is-A

ID, Name, Address

Is-A

Salary, Skill
Is-A

Employee Employee
Is-A

Dependent Dependent

Birth-date

Project Project Manager Manager Ratio-of-success, ...

Project Project Member Member Languages, ...

Semantic Modeling 28

CIS

ER Example In-class exercise

A database is being constructed to keep track of the teams and games of a baseball league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Design an ER schema diagram for this application

Assumptions: Each game in the schedule is identified by a unique Game#, and a game is also identified uniquely by the combination of Date, starting Time, and Field where it is played. A performance attribute is used to store information on the individual batting performance of each player in a game.

Semantic Modeling 29

CIS

EER Modeling Example

American Airlines Company
The American Airlines Company publishes a monthly flight log report that tracks which type of aircraft and the number of hours that were flown by an individual pilot. A separate report is prepared for each pilot and is used to monitor pilot flight proficiency for the two types of aircraft (fixed-wing and rotorcraft) which a pilot may be qualified to fly. The following business rules apply to this report. Pilots may be assigned to fly different aircraft each day by the flight scheduling manager. Each aircraft has a single crew chief permanently assigned to perform maintenance on the aircraft, although a crew chief may crew more than one aircraft. Each aircraft is identified by an aircraft number. There are several aircraft types in the fleet. An aircraft number is unique within an aircraft type. Identify the entities in this situation and draw an E-R diagram of the entities to include their relationships and any attributes identified in this example. Give examples of additional attributes that might be associated with each entity.

Semantic Modeling 30

CIS