Lecture2: Database Environment

Prepared by L. Nouf Almujally & Aisha AlArfaj

1

Ref. Chapter2

Colle

ge o

f C

om

pute

r and

In

form

ati

on

Sci

ence

s -

Info

rmati

on S

yst

em

s D

ep

t.

I S220 / I S422 : Database Fundamenta l s

Topics Covered

• Overviews • Three-level Architecture.• Schemas and Instances• External, conceptual, and external level

• Data Models and conceptual modeling• Some terms definitions.• Data Models• Some Example about data models

• Classification or models of DBMSs.

2

Lect

ure

2

Three-level Architecture Le

cture

2

3

Schemas and Instances• important to distinguish between description of

database and the database itself • schema (intention) • The description of the database. It rarely changes.• when we define a new database, we specify its schema –

“The structure, data types, and the constraints that describes the database”.

• instance (database state / extension)• The actual data in the database at any point of time• Changes rapidly.• When we initially load data into the database, it is said to

move into the initial state of the database.• Each write operation (insert, delete, modify) changes the

current state of the database to its new state

4

Lect

ure

2

Example

Schema Instance

Data

base

Conce

pts

5

Three-level Architecture

• The Database is provide users with an abstract view of data• Abstract database means to hide certain details

of how data is stored and manipulated.

6

Lect

ure

2

Three-level Architecture

1. External Level ( the ways users perceive the data)• Users' view of the database. • Describes that part of database that is relevant to a

particular user.

2. Conceptual Level (logical structure of entire database)• Community view of the database. • Describes what data is stored in database and

relationships among the data.

3. Internal Level ( the way DBMS and OS perceive the data)• Physical representation of the database on the computer. • Describes how the data is stored in the database.

7

Lect

ure

2

8

Lect

ure

2

Illustrating Example

9

Lect

ure

2

Reasons for Separations?

1. Each user should able to access the data, but have a different customized view of data.

2. The DBA should be able to change the DB storage structure without affecting the user’s view.

3. The internal structure of database should be unaffected by changes to the physical aspects of storage, such as change to new storage device.

10

Lect

ure

2

Three-Schema Architecture

Proposed to support DBMS characteristics of:

• Program-data independence.

• Support of multiple views of the data.

Data

base

Conce

pts

11

Data Independence

• The three-level architecture provides Data Independence, which means that upper level are unaffected by changes to lower level• Data Independence is the ability to modify a schema

definition in one level without affecting a schema definition in the next higher level.• There are two kinds of data independence:• Logical Data Independence• Physical Data Independence

12

Lect

ure

2

Data Independence

• Logical Data Independence• Refers to immunity of external schemas to changes in

conceptual schema.• Conceptual schema changes (e.g. addition/removal of

entities) should not require changes to external schema or rewrites of application programs.

13

Lect

ure

2

Data Independence

• Physical Data Independence• Refers to immunity of conceptual schema to changes

in the internal schema.• Internal schema changes (e.g. using different file

organizations, storage structures/devices) should not require change to conceptual or external schemas.

14

Lect

ure

2

Data Independence and the Three-Level Architecture

15

Lect

ure

2

Lect

ure

2

16

Data Models

Lect

ure

2

17

Terms

Terms Definition

Entity Represents a real world object or concept e.g. employee and student

Attribute Represents some property of interest that further describes an entity e.g. employee name and student number

Relationship Represents the relation among two or more entities e.g. work on relationship between employees and departments.

Primary keyA primary key value defined on a key specifies that each row in the table can be uniquely identified by the values in the key e.g. student_ID

Foreign Keys A foreign key is a key that points to the primary key of another table.

18

Terms

Term Definition

Table Names Converting of entities into tables

Column Names Converting of attributes into columns

Normalization The process of organizing data to minimize redundancy such as dividing large tables into smaller (and less redundant) tables and defining relationships between them

Denormalization The process of attempting to optimise the read performance of a databaseSuch as adding attributes to a relation from another relation with which it will be joined 19

Data Model

Integrated collection of concepts for describing data, relationships between data, and constraints on the data in an organization.• Purpose• To represent data in an understandable way.

• Data Model contains:• Conceptual data models (Object-based).• Logical data models (Record-based).• Physical data models. 20

Lect

ure

2

Data Model

• Conceptual data models: • Provide concepts that are close to the way end users

perceive data.

• Logical data models: • Provide concepts about logical structure of data, closer

to physical models.

• Physical data models • Provide concepts that describe the details of how data

is stored in the computer .

• Concepts provide by low-level data models are generally meant for computer specialists, not for typical ends users.

Lect

ure

2

21

 Conceptual Data Model 

• A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model includes the important entities and the relationships among them.

• No attribute is specified.• No primary key is specified

22

.

From the figure, we can see that the only information shown via the conceptual data model is the entities that describe the data and the relationships between those entities. No other information is shown through the conceptual data model.

Lect

ure

2

 Logical Data Model • A logical data model describes the data in as much detail as possible,

without regard to how they will be physical implemented in the database.

• Features of a logical data model include:1. Includes all entities and relationships among them.2. All attributes for each entity are specified.3. The primary key for each entity is specified.4. Foreign keys (keys identifying the relationship between different

entities) are specified.5. Normalization occurs at this level.

• The steps for designing the logical data model are as follows:1. Specify primary keys for all entities.2. Find the relationships between different entities.3. Find all attributes for each entity.4. Resolve many-to-many relationships.5. Normalization.

23

Lect

ure

2

• Comparing the logical data model shown above with the conceptual data model diagram, we see the main differences between the two:1. in a logical data model, primary keys are present, whereas in a

conceptual data model, no primary key is present.2. In a logical data model, all attributes are specified within an entity. No

attributes are specified in a conceptual data model.3. Relationships between entities are specified using primary keys and

foreign keys in a logical data model. In a conceptual data model, the relationships are simply stated, not specified, so we simply know that two entities are related, but we do not specify what attributes are used for this relationship.

24

The figure is an example of a logical data model.

Lect

ure

2

Physical Data Model • Physical data model represents how the model will be built in the database. A

physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables.

• Features of a physical data model include:1. Specification all tables and columns.2. Foreign keys are used to identify relationships between tables.3. Denormalization may occur based on user requirements.4. Physical considerations may cause the physical data model to be quite

different from the logical data model.5. Physical data model will be different for different RDBMS. For example, data

type for a column may be different between MySQL and SQL Server.• The steps for physical data model design are as follows:

1. Convert entities into tables.2. Convert relationships into foreign keys.3. Convert attributes into columns.4. Modify the physical data model based on physical constraints / requirements.

Lect

ure

2

25

• Comparing the logical data model shown above with the logical data model diagram, we see the main differences between the two:1. Entity names are now table names.2. Attributes are now column names.3. Data type for each column is specified. Data types can be

different depending on the actual database being used.

26

The figure below is an example of a physical data model.

Lect

ure

2

Conceptual, Logical, And Physical Data Models 

• The three level of data modeling, conceptual data model, logical data model, and physical data model, were discussed in prior sections.

• Here we compare these three types of data models. The table below compares the different features:

Lect

ure

2

27

Feature Conceptual Logical Physical

Entity Names ✓ ✓  

Entity Relationships ✓ ✓  

Attributes   ✓  

Primary Keys   ✓ ✓

Foreign Keys   ✓ ✓

Table Names     ✓

Column Names     ✓

Column Data Types     ✓

Conceptual Model Design Logical Model Design Physical Model Design

• Summary: We can see that the complexity increases from conceptual to logical to physical. This is why we always:

1. first start with the conceptual data model (so we understand at high level what are the different entities in our data and how they relate to one another),

2. then move on to the logical data model (so we understand the details of our data without worrying about how they will actually implemented),

3. and finally the physical data model (so we know exactly how to implement our data model in the database of choice).

Lect

ure

2

28

Data Models

Conceptual Data Models (Object-Based):• Entity-Relationship.• Semantic.• Functional.• Object-Oriented.

Logical Data Models (Record-Based):• Relational Data Model.• Network Data Model.• Hierarchical Data Model.

Physical Data Models.

Data

base

Conce

pts

29

Lect

ure

2

30

Classification of DBMSs Le

cture

2

31

Classification or models of DBMSs

1. first generation• Network, Hierarchical

2. second generation• Relational

3. third generation• Object-oriented, Object-relational

32

Network Data Model

Hierarchical Data Model

Disadvantages: 1. Required complex programs for even simple

queries.2. Minimal data independence.3. No widely accepted theoretical foundation

First Generation

33

Second Generation

34

• Relational Data Model

Third Generation

• Object-oriented Data Model• Response to increasing complexity of DB applications

35

Relational Data Model (second generation)

• Computer database in which all data is stored in Relations which are tables with rows and columns.• Each table is composed of records (called Tuples) and

each record is identified by a field (attribute containing a unique value).

36

Advantages of Relational model

The benefits of a database that has been designed according to the relational model are numerous. Some of them are:1. Data entry, updates and deletions will be efficient.2. Data retrieval, summarization and reporting will also be

efficient.3. Since the database follows a well-formulated model, it

behaves predictably.4. Since much of the information is stored in the database

rather than in the application, the database is somewhat self-documenting.

5. Changes to the database schema are easy to make.37

Lect

ure

2

38

References

• “Database Systems: A Practical Approach to Design, Implementation and Management.”Thomas Connolly, Carolyn Begg.5th Edition, Addison-Wesley, 2009.

Lect

ure

2

39