DATABASES

MANAGEMENT SYSTEM

Lecture 1

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

 Course Information

 Course Organisation
 Classroom lectures and exercices
 Text book
 Elmasri and Navathe. Fundamentals of Database
Systems. Addison-Wesley, 5th edition, 2007.
 Slides will be made available

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

Chapter 1
Introduction: Fundamental Concepts
of a Database System

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

Introduction to Database Systems
 What Is a Database?
 can be viewed as a “repository for data” or “a collection of
data.”
 Implicit properties:
 It represents aspects of a real world.
 It is collection of coherent (related) data.
 It is designed, built and populated to address a specific situation in real
world.
 Database Management System (DBMS) ?
 is then a tool for creating and managing this large amounts of data
efficiently and allowing it to persist for a long periods of time.
 Hence, DBMS is a general-purpose software that facilities the processes of
 defining, constructing, manipulating, and sharing database.

4
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Cont’d
- Defining: involves specifying data types, structure and
constraints.
- Constructing: is the process of storing the data into a
storage media.
- Manipulating: is retrieving and updating data from
and into the storage.
- Sharing: allows multiple users to access data.
 The phrase “Database System” is used to colloquially
refer to database and database management system
(DBMS).
5
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Evolution of a Database System
 1st generation was file system, such as ISAM and VSAM.
 2nd generation was hierarchical database systems, such as
IMS(Information Management System) and System 2000.
 3rd generation was the network model CODASYL
(Conference on Data Systems Languages) database systems,
such as IDS(Integrated Data Store), TOTAL, ADABAS, IDMS,
etc.
 4th generation relational database technology.
 5th generation database technology will be characterized by a
richer data model and a richer set of database facilities.

6
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Database System Requirements

 Databases evolved to take responsibility for the data

away from the application, and most importantly to
enable data to be shared.
 Hence a database system must provide:
 Consistency
 Concurrency
 Performance
 Standard Adherence
 Security
 Reliability

7
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Cont’d
- Consistency: It must ensure that the data itself is not only
consistently stored but can be retrieved and shared efficiently.
- Concurrency: It must enable multiple users and systems to all
retrieve the data at the same time and to do so logically and
consistently.
- Performance: It must support reasonable response times.
- Standard adherence: It should support a standard language for
common understanding. Standard Query Language (SQL) has
to be supported.

8
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Cont’d
- Security: It should provide away to set access permissions
(much like files at the operating system level) and specific
database mechanisms such as triggers.
- Reliability: It must keep the stored data intact. Additionally, it
must cope well when things go awry and it must, if set up
properly, be able to recover to a known consistent point.

9
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Database System versus File
System(Traditional Approach)
 In the early days, database applications were built directly on top of file systems. The
traditional file processing system is file-directory structure supported by a
conventional operating system.
 Drawbacks of using file systems to store data:
 Data redundancy and inconsistency
 Multiple file formats, duplication of information in different files
 Difficulty in accessing data
 Need to write a new program to carry out each new task
 Data isolation — multiple files and formats
 Integrity problems
 Integrity constraints (e.g. account balance > 0) become “buried” in program
code rather than being stated explicitly
 Hard to add new constraints or change existing ones

10
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Database System versus File
System(Traditional Approach)
 Drawbacks of using file systems (cont.)
 Atomicity of updates
 Failures may leave database in an inconsistent state with partial updates
carried out
 Example: Transfer of funds from one account to another should either
complete or not happen at all
 Concurrent access by multiple users
 Concurrent accessed needed for performance
 Uncontrolled concurrent accesses can lead to inconsistencies
– Example: Two people reading a balance and updating it at the same time
 Security problems
 Hard to provide user access to some, but not all, data.
 Database systems offer solutions to all the above problems

11
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Why Databases?
 Scientists (biologists) have to manage huge quantities of
data
 Results of experiments
 References to relevant publications
 DNA sequences
 …
 Organizations have to manage huge quantities of data
 Student related data
 Financial Data,
 Patient data ,
 Weather Data,
 Customer data,
 …………….

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 12

Why Databases?
 Those data need to be stored in a consistent way, shared
and analysed
 Which are the experiments on a cellular biology done at my
lab in 2009?
 Which are the publications of my group?
 Which are the genes in the X chromosome?
 Databases are a possible solution to this issue

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 13

Database vs ad hoc programming
 Data can be managed and stored using ad-hoc programs
(e.g., in Java, c++, …)
 Need for writing ad hoc algorithms (e.g. search, sorting)
 Programs have to be changed when data change
 Need for concurrency control, backups, …
 Databases used to not re-solve the same problems every
time
 Standard (good) solutions to the most common problems
 Easy to use and to configure
 Standard programs used for specific tasks (e.g., scientific
computations) may rely on a database for data
management

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 14

Types of Databases and Database
Applications
 Traditional Applications:
 Numeric and Textual Databases
 More Recent Applications:
 Multimedia Databases
 Geographic Information Systems (GIS)
 Data Warehouses
 Real-time and Active Databases
 Many other applications
 We will focus on traditional applications, with
emphasis on scientific (biological) databases

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 15

Basic Definitions
 Database:
 A collection of related data.

 Data:
 Known facts that can be recorded and have an implicit meaning.

 Mini-world:
 Some part of the real world about which data is stored in a

database. For example, student grades and transcripts at a

university.
 Database Management System (DBMS):
 A software package/ system to facilitate the creation and

maintenance of a computerized database.

 Database System:
 The DBMS software together with the data itself. Sometimes, the

applications are also included.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 16

Simplified database system environment

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 17

Typical DBMS Functionality
 Define a particular database in terms of its data types,
structures, and constraints
 Construct or Load the initial database contents on a
secondary storage medium
 Manipulating the database:
 Retrieval: Querying, generating reports
 Modification: Insertions, deletions and updates to its content
 Accessing the database through Web applications
 Processing and Sharing by a set of concurrent users and
application programs – yet, keeping all data valid and
consistent

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 18

Typical DBMS Functionality
 Other features:
 Protection or Security measures to prevent
unauthorized access
 “Active” processing to take internal actions on data
 Presentation and Visualization of data
 Maintaining the database and associated
programs over the lifetime of the database
application

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 19

Example of a Database
(with a Conceptual Data Model)
 Mini-world for the example:
 Part of a UNIVERSITY environment.
 Some mini-world entities:
 STUDENTs
 COURSEs
 SECTIONs (of COURSEs)
 (academic) DEPARTMENTs
 INSTRUCTORs

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 20

Example of a Database
(with a Conceptual Data Model)
 Some mini-world relationships:
 SECTIONs are of specific COURSEs
 STUDENTs take SECTIONs
 COURSEs have prerequisite COURSEs
 INSTRUCTORs teach SECTIONs
 COURSEs are offered by DEPARTMENTs
 STUDENTs major in DEPARTMENTs

 Note: The above entities and relationships are typically

expressed in a conceptual data model, such as the
ENTITY-RELATIONSHIP data model (see Chapters 2)

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 21

Example of a simple database

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 22

Main Characteristics of the Database
Approach
 Self-describing nature of a database system:
 A DBMS catalog stores the description of a particular
database (e.g. data structures and types)
 The description is called meta-data.
 This allows the DBMS software to work with different
database applications.
 Insulation between programs and data:
 Called program-data independence.
 Allows changing data structures and storage organization
without having to change the DBMS access programs.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 23

Example of a simplified database catalog

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 24

Main Characteristics of the Database
Approach (continued)
 Data Abstraction:
 A data model is used to hide storage details
and present the users with a conceptual view
of the database.
 Programs refer to the data model constructs rather
than data storage details
 Support of multiple views of the data:
 Each user may see a different view of the
database, which describes only the data of
interest to that user.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 25

Main Characteristics of the Database
Approach (continued)
 Sharing of data and multi-user transaction
processing:
 Allowing a set of concurrent users to retrieve
from and to update the database
 Care is needed to avoid interferences
 Concurrency control within the DBMS guarantees
that each transaction is correctly executed or
aborted
 Recovery subsystem ensures each completed
transaction has its effect permanently recorded in
the database

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 26

Database Users
 Users may be divided into
 Those who actually use and control the database content, and

those who design, develop and maintain database

applications (called “Actors on the Scene”), and
 Those who design and develop the DBMS software and related

tools, and the computer systems operators (called “Workers

Behind the Scene”).

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 27

Database Users
 Actors on the scene
 Database Designers:

 Responsible to define the content, the structure, the constraints,

and functions or transactions against the database. They must
communicate with the end-users and understand their needs.
 Database administrators:
 Responsible for authorizing access to the database, for
coordinating and monitoring its use, acquiring software and
hardware resources, controlling its use and monitoring efficiency of
operations.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 28

Categories of End-users
 Actors on the scene (continued)
 End-users: They use the data for queries, reports and some of

them update the database content.

 End-users can be categorized into:

 Casual: access database occasionally when needed.

 Naïve or Parametric: they make up a large section of the end-user
population.
 They use previously well-defined functions against the database.
 Examples are bank-tellers or university secretaries who do this activity
for an entire shift of operations.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 29

Categories of End-users (continued)
 Sophisticated:
 These include business analysts, scientists, engineers, others
thoroughly familiar with the system capabilities.
 Many use tools in the form of software packages that work closely with
the stored database.
 Stand-alone:
 Mostly maintain personal databases using ready-to-use packaged
applications.
 An example is a scientists that creates a database for its own
experiments.
 You may become sophisticated or stand-alone users

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 30

Advantages of Using the Database
Approach
 Interacting easily with data using high-level dedicated tools
 Controlling redundancy in data storage and in development and
maintenance efforts.
 Sharing of data among multiple users.

 Ensuring consistency of data.

 Restricting unauthorized access to data.

 Providing storage structures (e.g. indexes) for efficient query
processing

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 31

Advantages of Using the Database
Approach (continued)
 Providing backup and recovery services.
 Providing multiple interfaces to different classes of users.
 Representing complex relationships among data.
 Enforcing integrity constraints on the database.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 32

Additional Implications of Using the
Database Approach
 Potential for enforcing standards:
 This is very crucial for the success of database applications in

large organizations. Standards refer to data item names, display

formats, screens, report structures, Web page layouts, etc.
 Reduced application development time:
 Applications can exploit the high-level database primitives.

 Incremental time to add each new application is reduced.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 33

Additional Implications of Using the
Database Approach (continued)
 Flexibility to change data structures:
 Database structure may evolve as new requirements are defined.

 Availability of current information:

 Extremely important for on-line transaction systems such as

airline, hotel, car reservations.

 Economies of scale:
 Wasteful overlap of resources and personnel can be avoided by

consolidating data and applications across departments.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 34

Historical Development of Database
Technology
 Early Database Applications:
 The Hierarchical and Network Models were introduced in mid

1960s and dominated during the seventies.

 A bulk of the worldwide database processing still occurs using

these models, particularly, the hierarchical model.

 Relational Model based Systems:
 Relational model was originally introduced in 1970, was heavily

researched and experimented within IBM Research and

several universities.
 Relational DBMS Products emerged in the early 1980s.

 Most of the systems used today are based on it (Microsoft

Access, MySQL, …)

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 35

Historical Development of Database
Technology (continued)
 Object-oriented and emerging applications:
 Object-Oriented Database Management Systems (OODBMSs)

were introduced in late 1980s and early 1990s to serve the

need of complex data processing in CAD and other applications.
 Their use has not taken off much.
 Many relational DBMSs have incorporated object database
concepts, leading to a new category called object-relational
DBMSs (ORDBMSs)
 Extended relational systems add further capabilities (e.g. for
multimedia data, XML, and other data types)

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 36

Historical Development of Database
Technology (continued)
 Data on the Web and E-commerce Applications:
 Web contains data in HTML (Hypertext markup language) with

links among pages.

 This has given rise to a new set of applications and E-

commerce is using new standards like XML (eXtended Markup

Language).
 Script programming languages such as PHP and JavaScript

allow generation of dynamic Web pages that are partially

generated from a database.
 Also allow database updates through Web pages

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 37

Extending Database Capabilities
 New functionality is being added to DBMSs in the following areas:
 Scientific Applications

 XML (eXtensible Markup Language)

 Image Storage and Management

 Audio and Video Data Management

 Data Warehousing and Data Mining

 Spatial Data Management

 Time Series and Historical Data Management

 The above gives rise to new research and development in

incorporating new data types, complex data structures, new
operations and storage and indexing schemes in database systems.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 38

When not to use a DBMS
 Main inhibitors (costs) of using a DBMS:
 High initial investment and possible need for additional
hardware.
 Overhead for providing generality, security, concurrency
control, recovery, and integrity functions.
 When a DBMS may be unnecessary:
 If the database and applications are simple, well defined,
and not expected to change.
 If there are stringent real-time requirements that may not be
met because of DBMS overhead.
 If access to data by multiple users is not required.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 39

When not to use a DBMS
 When no DBMS may suffice:
 If the database system is not able to handle the
complexity of data because of modeling limitations
 If the database users need special operations not
supported by the DBMS.

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 1- 40