Database Systems

(SWEG2108)

Chapter –2 Data Models

 Chapter 2 Outline
• What is Data Model
• Types of Data Models
– Hierarchical
– Network
– Relational,…
• Building Blocks of Relational Relational Data Model
• Entities
• Attributes
• Relationships

2
What is model?
A model is an informative representation of an object, person or system. Wiki

A Map is a model of the reality.

What is Data model?
o Data Model:
• A set of concepts to describe the structure of a database,
the operations for Manipulating these structures, and
certain constraints that the database should obey.

o Data Model Structure and Constraints:

• define the database structure
• define entities with attributes and their data types ,and
• their relationships.
• Specify some restrictions (or constraints) on valid data;

5
What is Data model?
– Data Model Operations include:
• Specifying database retrievals and updates of database
elements
• Applying basic operations on the data model (e.g. insert,
delete, update) and user-defined operations (e.g.
compute_student_gpa, update_inventory)

6
What are Categories/levels of Data Models?
o 1. Conceptual (high-level, semantic) data models:
• Provide concepts that are close to the way many users
perceive data. (Also called entity-based or object-based
data models.)
o 3. Physical (low-level, internal) data models:
• Provide concepts that describe details of how data is
stored in the computer. These are usually specified in an
ad-hoc manner through DBMS design and administration
manuals
o 2. Implementation (representational) data models:
• Provide concepts that fall between the above two, used by
many commercial DBMS implementations (e.g. relational
data models used in many commercial systems). 7
ANSI-SPARC Architecture and Database Design Phases

8
Example of the three levels of data models

9
What are Schemas versus Instances?
o Database Schema: The description of a database. Includes
descriptions of the database structure, data types, and the
constraints on the database.
o
Schema Construct: A component of the schema or an object within the
schema, e.g., STUDENT, COURSE.

Schema Diagram: An illustrative display of (most aspects of) a database schema.

10
What are Schemas versus Instances?...

instance

Activity: Is this database a good model ? 11

Levels of Data Abstraction - Summary

Activity: What does logical independence mean? physical independence? 12

What is distiction between Schemas versus Instances?....
o Valid State: A state that satisfies the structure and constraints of
the database.
o The database schema changes very infrequently.
o The database state changes every time the database is updated.
o Schema is also called intension.
o State is also called extension.

Activity: What’s valid state? Database state? Database schema? 13

History of Data Models
o Record based data models:
• Network Model - 1st Generation
• Hierarchical Model -1 Generation
• Relational Model - 2nd generation
o Object based data models – use concepts: entities, attributes,
relationship
• Object-oriented Data Models
• Object-Relational Models
• Entity relationship (ER) data models – most popular technique
in DB deign
History of Data Models: Summary

16
Hierarchical Data Model
o
Used in IBM’s IMS (and some other products) which still have a large
customer base worldwide.
o
Dominated the DBMS market during 1965 – 1985
o
Advantages:
• Simple to construct and operate
• Corresponds to a number of natural hierarchically organized domains,
e.g., organization (“org”) chart
• Language is simple:
• Uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT
WITHIN PARENT, etc.
o
Disadvantages:
• Navigational and procedural nature of processing
• Database is visualized as a linear arrangement of records
• Little scope for "query optimization"

17
History of Data Models: Hierarchical Data Model

18
Network Model
o The first network DBMS was implemented by Honeywell in 1964-65 (IDS
System) Later implemented in a large variety of systems
o Advantages:
• Network Model is able to model complex relationships and represents
semantics of add/delete on the relationships.
• Can handle most situations for modeling using record types and
relationship types.
• Language is navigational; uses constructs like FIND, FIND member,
FIND owner, FIND NEXT within set, GET, etc.
• Programmers can do optimal navigation through the database.
o Disadvantages:
• Navigational and procedural nature of processing
• Database contains a complex array of pointers that thread through a set of
records.
• Little scope for automated “query optimization”
19
History of Data Models: Network Model

20
Object-oriented Data Models :
o Several models have been proposed for implementing in a database system.
o One set comprises models of persistent O-O Programming Languages such as
C++ (e.g., in OBJECTSTORE or VERSANT), and Smalltalk (e.g., in
GEMSTONE).
o Additionally, systems like O2, ORION (at MCC - then ITASCA), IRIS (at
H.P.- used in Open OODB).
o Object Database Standard: ODMG-93, ODMG-version 2.0, ODMG-version
3.0.
Relational Model:
Proposed in 1970 by E.F. Codd (IBM), first commercial system in 1981-82.
Now in several commercial products (e.g. DB2, ORACLE, MS SQL Server,
SYBASE, INFORMIX).
Several free open source implementations, e.g. MySQL, PostgreSQL
Currently most dominant for developing database applications.
SQL relational standards: SQL-89 (SQL1), SQL-92 (SQL2), SQL-99, SQL3,
…
21
Example of Relational Model:

22
History of Data Models: Object-Relational Models
o Most Recent Trend. Started with Informix Universal Server.
o Relational systems incorporate concepts from object databases
leading to object-relational.
o Exemplified in the latest versions of Oracle-10i, DB2, and SQL Server
and other DBMSs.
o Standards included in SQL-99 and expected to be enhanced in future
SQL standards.

23
 The Entity Relationship Model
Widely accepted and adapted graphical tool for
data modeling
Introduced by Chen in 1976
Graphical representation of entities and their
relationships in a database structure

24
 The Entity Relationship Model
The language that we use for modeling of the requirement specification involved in an enterprise.
Relates to conceptual database design
Represents conceptual view
Main Components
Entities corresponds to entire table
Attributes
Relationships

Questions to consider
What are the entities and relationships in the enterprise?
What information about these entities and relationships should we store in
the database?
What are the integrity constraints that hold?
.Represent this information pictorially in ER diagrams, then map ER diagram into a relational schema
25
 The Entity Relationship Model
Entity relationship diagram (ERD)
Uses graphic representations to model database components
Entity is mapped to a relational table
Entity instance (or occurrence) is row in table
Entity set is collection of like entities
Connectivity labels types of relationships
Diamond connected to related entities through a relationship line

26
The Entity Relationship Model

27
The Entity Relationship Model..

28
 Properties of Relational model

• Order of rows and columns is immaterial

• Entries with repeating groups are said to be un-
normalized
• Entries are single-valued
• Each column (field or attribute) has a distinct
name
NB: All values in a column represent the same
attribute and have the same data format
Data Models 29
 Building Blocks of Relational Model

The building blocks of the relational data model

are:
• Entities: real world physical or logical object
• Attributes: properties used to describe each Entity
or real world object.
• Relationship: the association between Entities
• Constraints: rules that should be obeyed while
manipulating the data

Data Models 30
 Building blocks of Relational Model

1. ENTITIES
• persons, places, things etc. which the organization has
to deal with.
• The name given to an entity should always be a
singular noun descriptive of each item to be stored in it.
E.g.: student NOT students

Data Models 31
 Building blocks of Relational Model

• Every relation has a schema, which describes the

columns, or fields and the relation itself
• Existence Dependency: the dependence of an entity
on the existence of one or more entities.
• Weak entity: an entity that cannot exist without the
entity with which it has a relationship

Data Models 32
 Building blocks of Relational Model

2. ATTRIBUTES –
• the items of information which characterize and
describe these entities.
• Attributes are pieces of information ABOUT entities

Data Models 33
 Building blocks of Relational Model

At this level we need to know such things as:

• Attribute name (be explanatory words or phrases)
• The domain from which attribute values are taken
• (A DOMAIN is a set of values from which attribute values may be taken.)
• Each attribute has values taken from a domain
• For example, the domain of Name is string and that for salary is real
• Whether the attribute is part of the entity identifier (attributes which just describe an
entity and those which help to identify it uniquely)
• Whether it is permanent or time-varying (which attributes may change their values
over time)
• Whether it is required or optional for the entity (whose values will sometimes be
unknown or irrelevant)
Data Models 34
 Building blocks…
Types of Attributes
(1)Simple (atomic) Vs Composite attributes
• Simple: contains a single value (not divided into sub parts)
E.g. Age, gender
• Composite: Divided into sub parts (composed of other attributes)
E.g. Name, address
(2)Single-valued Vs multi-valued attributes
Single-valued : have only single value(the value may change but has only one value
at one time)
E.g. Name, Sex, Id. No., color_of_eyes
Multi-Valued: have more than one value
E.g. Address, dependent-name Person may have several college degrees,
several languages
Data Models 35
 Building blocks…
Types of Attributes
(3)Stored vs. Derived Attribute
• Stored : not possible to derive or compute
E.g. Name, Address
• Derived: The value may be derived (computed) from the values of other attributes.
E.g. :
• Age (current year – year of birth
• Length of employment (current date- start date)
• Profit (earning-cost)
• G.P.A (grade point/credit hours)
(4) Null Values
• NULL applies to attributes which are not applicable or which do not have values.
• You may enter the value NA (meaning not applicable)
NB: Value of a key attribute cannot be null.
Default value – is the value assumed value if no explicit value
Data Models 36
 Building blocks of Relational Model

3. RELATIONSHIP
In any business processing one object may be associated with
another object due to some event.
Such kind of association is what we call a RELATIONSHIP
between entity objects.
Related entities require setting of LINKS from one part of the
database to another.
A relationship should be named by a word or phrase which
explains its function

Data Models 38
 Degree of relationship

• An important point about a relationship is how many entities participate in it.

• The number of entities participating in a relationship is called the DEGREE of
the relationship.
• UNARY/RECURSIVE RELATIONSHIP: Tuples/records of a Single entity
are related with each other.
• BINARY RELATIONSHIPS: Tuples/records of two entities are
associated in a relationship
• TERNARY RELATIONSHIP: Tuples/records of three different entities
are associated
• And a generalized one:
• N-NARY RELATIONSHIP: Tuples from arbitrary number of entity
Data Models
sets are participating in a relationship 39
Degree of Relationships:
Example
 Cardinality of a Relationship

The number of instances participating or associated

with a single instance from an entity in a relationship is
called the CARDINALITY of the relationship.
ONE-TO-ONE: one tuple is associated with only one
other tuple.
E.g. Building – Location: - as a single building will be located
in a single location and as a single location will only
accommodate a single Building.

Data Models 41
 Cardinality of a Relationship

• ONE-TO-MANY: one tuple can be associated with many other tuples, but not
the reverse.
• E.g. Department-Student: - as one department can have multiple
students.
• MANY-TO-ONE: many tuples are associated with one tuple but not the
reverse.
• E.g. Employee – Department: as many employees belong to a single
department
• MANY-TO-MANY: one tuple is associated with many other tuples and from
the other side, with a different role name one tuple will be associated with
many tuples
• E.g. Student – Course: - as a student can take many courses and a
single course can be attended by many students.
Data Models 42
Data Models 43
 Symbols: Variants

Data Models 44
Participation : Mandatory or Optional
Decomposition of M:N relationship
Thank
you!