Chapter 2 Data Model

Database Systems: Design, Implementation, and Management, Sixth Edition, Rob and Coronel 1

1

In this chapter, you will learn:

• • • • •

Why data models are important About the basic data-modeling building blocks What business rules are and how they affect database design How the major data models evolved, and their advantages and disadvantages How data models can be classified by level of abstraction

2

1

The Importance of Data Models

•

Data model

–

Relatively simple representation , usually graphical , of complex real-world data structures

–

Communications tool to facilitate interaction among the designer, the applications programmer, and the end user

•

Good database design uses an appropriate data model as its foundation

•

End-users have different views and needs for data

•

Data model organizes data for various users

3

1

Data Model Basic Building Blocks

• • •

Entity – is anything about which data are to be collected and stored Attribute – is a characteristic of an entity Relationship – describes an association among (two or more) entities

– One-to-many (1:M) relationship – Many-to-many (M:N or M:M) relationship – One-to-one (1:1) relationship 4

1

Business Rules

•

How do database designers go about determining the entities, attributes, and relationships that will build a data model?

•

Business Rules – Brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization’s environment

5

Examples of Business Rules

•

A customer may generate many invoices.

•

Each invoice is generated by only one customer.

1

6

Sources of Business Rules

• • • •

Company managers Policy makers Department managers Written documentation

–

Procedures

–

Standards

–

Operations manuals

•

Direct interviews with end users 1

7

1

Importance of Business Rules

•

Standardize company’s view of data

•

Constitute a communications tool between users and designers

•

Allow designer to understand the nature, role, and scope of data

•

Allow designer to understand business processes

•

Allow designer to develop appropriate relationship participation rules and constraints

8

The Evolution of Data Models

•

Hierarchical

•

Network

•

Relational

•

Entity relationship

•

Object oriented 1

9

Hierarchical Database Model

•

Logically represented by an upside down tree

–

Each parent can have many children

–

Each child has only one parent 1

10

Hierarchical Database Model

• •

Advantages

–

Conceptual simplicity

–

Database security and integrity

–

Data independence

–

Efficiency Disadvantages

–

Complex implementation

–

Difficult to manage and lack of standards

–

Lacks structural independence

–

Applications programming and use complexity

–

Implementation limitations 1

11

•

Network Database Model

Each record can have multiple parents

–

Composed of sets

–

Each set has owner record and member record

–

Member may have several owners 1

12

Network Database Model

•

Advantages

–

Conceptual simplicity

–

Handles more relationship types

–

Data access flexibility

–

Promotes database integrity

–

Data independence

–

Conformance to standards

•

Disadvantages

–

System complexity

–

Lack of structural independence 1

13

1

Relational Database Model

• •

Developed by Codd (IBM) in 1970 Perceived by user as a collection of tables for data storage

• •

Tables are a series of row/column intersections Tables related by sharing common entity characteristic(s) ( Figure 2.4)

•

Relational table is purely logical structure

–

How data are physically stored in the database is of no concern to the user or the designer

14

Linking Relational Tables

1

15

•

1

Relational Schema

Visual representation of relational database’s entities, attributes within those entities, and relationships between those entities.

16

Relational Database Model

•

Advantages

–

Structural independence

–

Improved conceptual simplicity

–

Easier database design, implementation, management, and use

–

Ad hoc query capability with SQL

–

Powerful database management system 1

17

1

Relational Database Model

•

Disadvantages

–

Substantial hardware and system software overhead

–

Poor design and implementation is made easy

18

1

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

19

1

The ER Model — Basic Structure

• • •

Entity relationship diagram (ERD)

–

Uses graphic representations to model database components

–

Entity is mapped to a relational table Entity instance is row (or occurrence in the relational table ) Connectivity labels types of relationships

–

Diamond connected to related entities through a relationship line

• Entity set is collection of like entities • Unfortunately, ERD designers use “ entity ” as a substitute for “ entity set ” and we ‘ll conform to that established practice.

20

Relationships: The Basic Chen ERD 1

21

Relationships: The Basic Crow’s Foot ERD 1

22

1

Entity Relationship Database Model

• • •

Complements the relational data model concepts Represented in an entity relationship diagram (ERD ) Based on entities, attributes, and relationships

23

1

Entity Relationship Database Model

• •

Advantages

– – – –

Exceptional conceptual simplicity Visual representation Effective communication tool Integrated with the relational database model Disadvantages

– –

Limited constraint representation Limited relationship (relationships between attributes within entities cannot be represented) representation

– –

No data manipulation language (DML) Loss of information content

24

1

The Object Oriented Model

•

Semantic data model (SDM) developed by Hammer and McLeod in 1981

•

Modeled both data and their relationships a single structure known as an object in

•

Basis of object oriented data model (OODM)

•

OODM becomes the basis for the object oriented database management system (OODBMS)

25

1

Object-Oriented Database Model

•

An object is an abstraction of a real-world entity

–

Attributes describe properties of an object

–

Collection of similar objects is a class

•

Methods represent real world actions of classes

•

Classes are organized in a class hierarchy

–

Inheritance is ability of object to inherit attributes and methods of classes above it

26

1

OO Data Model

•

Advantages

–

Adds semantic content

–

Visual presentation includes semantic content

–

Database integrity

–

Both structural and data independence

•

Disadvantages

–

Lack of OODM standards

–

Complex navigational data access

–

Steep learning curve

–

High system overhead slows transactions

27

•

The Development of Data Models The evolution has always been driven by the search for new ways of modeling increasingly complex real-world data .

1

28

1

Database Models and the Internet

•

Characteristics of “ Internet age ” databases

–

Flexible, efficient, and secure Internet access

–

Easily used, developed, and supported

–

Supports complex data types and relationships

–

Seamless interfaces with multiple data sources and structures

–

Relative conceptual simplicity to make database design and implementation less cumbersome

–

Many database design, implementation, and application development tools

–

Powerful DBMS GUI make DBA job easier

29

1

Degrees of Data Abstraction

•

Way of classifying data models

•

American National Standards Institute/Standards Planning and Requirements Committee (ANSI/SPARC)

–

Classified data models according to their degree of abstraction (1970s):

•

Conceptual

•

External

•

Internal

30

Data Abstraction Levels

ER

1

31

1

The Conceptual Model



The conceptual model represents a global view of the data.



It is an enterprise-wide representation of data as viewed by high-level managers.



Entity-Relationship (E-R) model

is the most widely used conceptual model.



The conceptual model is_ software independence hardware independence

32

A Conceptual Model for Tiny College 1

33

1

Advantages of Conceptual Model

• •

Provides a relatively easily understood macro level view of data environment Independent of both software and hardware

–

Does not depend on the DBMS software used to implement the model

–

Does not depend on the hardware used in the implementation of the model

–

Changes in either the hardware or the DBMS software have no effect on the database design at the conceptual level

34

1

The Internal Model



Representation of the database as “ seen ” by the DBMS



Once a specific DBMS has been selected, the internal model adapts the conceptual model to the DBMS .



The internal model is

software-dependent

hardware-independence .

35

1

The External Model



End users’ views of data environment



Each external model is then represented by its own external schema .



Provides subsets of internal view



Makes application program development easier



The external model is

DBMS-dependent

hardware-independence .

•

CREATE VIEW SELECT CLASS_VIEW AS (CLASS_ID, CLASS_NAME, PROF_NAME, CLASS_TIME, ROOM_ID) FROM WHERE CLASS, PROFESSOR, ROOM CLASS.PROF_ID = PROFESSOR.PROF_ID

CLASS.ROOM_ID = ROOM.ROOM_ID

; AND

36

A Division of an Internal Model into External Models

Internal

1

External

37

The External Models for Tiny College 1

38

1

The Physical Model



The physical model operates at the lowest level of abstraction, describing the way data is saved on storage media such as disks or tapes.



Requires that database designers have a detailed knowledge of the hardware and software used to implement database design



The physical model is_ software-dependent hardware-dependent .

39

Levels of Data Abstraction

1

40

1

Database Models

•

Collection of logical constructs used to represent data structure and data relationships within the database

–

Conceptual models: logical nature of data representation

•

focus on the logical nature of the data representation. They are concerned with

what

is represented rather than

how

it is represented.

•

E.g. E-R model

–

Implementation models: emphasis on how the data are represented in the database

•

place the emphasis on

how

the data are represented in the database or on

how

the data structures are implemented.

•

E.g. relational database model

41

Summary

• • •

A good DBMS will perform poorly with a poorly designed database A data model is a (relatively) simple abstraction of a complex real-world data-gathering environment Basic data modeling components are:

–

Entities

–

Attributes

–

Relationships 1

42

Summary (

continued

)

•

Hierarchical model

–

Based on a tree structure composed of a root segment, parent segments, and child segments

–

Depicts a set of one-to-many (l:M) relationships between a parent and its children

–

Does not include ad hoc querying capability 1

43

Summary (

continued

)

• • • •

Network model attempts to deal with many of the hierarchical model’s limitations Relational model:

– –

Current database implementation standard Much simpler than hierarchical or network design Object is basic modeling structure of object oriented model Data modeling requirements are a function of different data views (global vs. local) and level of data abstraction 1

44