DATABASES AND

DATABASE USERS

CHAPTER 1

Acknowledgement:
Most slides for this course have been adapted
from slides made available by Addison Wesley to
accompany Elmasri and Navathe’s textbook.

1
 Textbook:
 Fundamentals of Database Systems, R. Elmasri and S. Navathe
 Reference book:
 Database System Concepts, sixth edithion, by Abraham
Silberschatz, , Henry F. Korth, S. Sudarshan

Grading Policy:

Assignments and quizzes 20%



 Midterm 30%
 Final exam 50%

2
COURSE DESCRIPTION

Basic concepts of databases.

DBMS components.
Transaction managements.
Data modelling.
Entity relationships diagrams.
Relational databases.
Database integrity constraints.
Relational Algebra.
Dependencies, schema designs normalization and redundancy elimination.
By the end of the course, students are expected to be familiar with many of
the principles and concepts related to databases and how these are
applied in real database systems by presenting small individual/group
projects.

3
LECTURE OUTLINE
 Introduction
 An Example
 Characteristics of the Database Approach
 Actors on the Scene
 Workers behind the Scene
 When Not to Use a DBMS

4
WEALTH OF DATA
 Traditional database applications
• Store numeric short textual information
• Typically for managing enterprises example:
• bank to deposit or withdraw funds;
• hotel or airline reservation
• computerized library catalog to search for a bibliographic item
 The above examples are traditional database where most of the information that is stored and
accessed is either textual or numeric
 multimedia databases
• Store digital images, audio, and video streams
 Geographic information systems (GIS)
• Store maps, weather data, and satellite images
• For route-finding, agriculture, and natural resource management
 Data warehouses and online analytical processing (OLAP) systems ( ‫مستودعات البيانات وأنظمة المعالجة‬
‫)التحليلية عبر اإلنترنت‬
• Store historical business information
• For business analytics and decision support
 Real-time and active database technology
• Store process models, constraints, and key performance indicators
• Control industrial and manufacturing processes

5
TERMINOLOGY
 Database
• Collection of related data (logically coherent)
• The data are Known facts that can be recorded and that have
implicit meaning
• Represents some aspect(s) of the real world (miniworld)
• Built for a specific purpose
• Examples of the collection of data in database: the names, telephone
numbers, and addresses of the people you know
Examples of large databases
• Amazon.com, Canadian Census, The eBay’s product inventory.
• A database may be generated and maintained manually, or it may be
computerized
• A computerized database may be created and maintained either by a group
of application programs written specifically for that task or by a database
management system

6
TERMINOLOGY (CONT'D.)
 Database management system (DBMS)
• Collection of programs
• Enables users to create and maintain a database
• Allows multiple users and programs to access and manipulate the
database concurrently
• Provides protection against unauthorized access and manipulation
• Provides means to evolve database and program behaviour as
requirements change over time
 Examples of database management systems
• IBM’s DB2, Microsoft’s Access and SQL Server, Oracle, MySQL,
SAP’s SQL Anywhere

7
DATA ABSTRACTION
 Since many database-system users are not computer trained, developers hide
the complexity from users through several levels of abstraction, to simplify users’
interactions with the system:
• Physical level. The lowest level of abstraction describes how the data are
actually stored. The physical level describes complex low-level data structures
in detail
• Logical level. The next-higher level of abstraction describes what data are
stored in the database, and what relationships exist among those data.
Example:

• View level:
• Even though the logical level uses simpler structures, complexity
remains because of the variety of information stored in a large database
• application programs hide details of data types. Views can also hide information
(e,g, salary)for security purposes

8
INSTANCES AND SCHEMAS
 Databases change over time as information is inserted and deleted.
 Instance: the actual content of the database at a particular point in time
 schema :The overall design of the database is called the database.
• Schemas are changed infrequently, if at all.

 Similar to types and variables in programming languages

• Schema is Analogous to type information of a variable in a program
• Instance is Analogous to the value of a variable

9
DATA MODELS
 data model: a collection of tools for describing
• data relationships
• data semantics
• Data constraints.
 A data model provides a way to describe the design of a database at
the physical, logical, and view levels.
 The data models can be classified into four different categories:
1. Relational Model. The relational model uses a collection of tables to
represent both data and the relationships among those data

10
11
DATA MODELS- CONTINUE
2. Entity-Relationship Model (E-R model) :
• Widely used for database design
• uses a collection of basic objects, called entities, and relationships among
these objects.
• An entity is a “thing” or “object” in the real world that is distinguishable
from other objects . E.g. customers, accounts, bank branch
• Relationship between entities example: Account A-101 is for
customer Johnson

12
OTHER DATA MODELS

• object-oriented data model:

• Used for Object-oriented applications
• can be seen as extending the E-R model with notions of encapsulation,
methods (functions), and object identity.
• Semistructured Data Model :
• The relational model has evolved into the semi-structured model.
• In this model, we can’t tell the difference between data and schema.
• This model can primarily be used to represent data from some data
sources that aren’t bound or constrained by the schema.
• The Extensible Markup Language (XML) is widely used to
represent semistructured data

13
DATA DEFINITION LANGUAGE (DDL)
 Defining a database involves specifying the data types, structures, and constraints for the data
to be stored in the database.
• Uses a Data Definition Language (DDL)
• The DDL gets as input some instructions (statements) and generates some output.
• The output of the DDL is placed in the data dictionary, which contains metadata

 Meta-data (data about data)

• Database definition or descriptive information
• Stored by the DBMS in the form of a database catalog or data dictionary
 Example:

 Phases for designing a database:

• Requirements specification and analysis
• Conceptual design
• e.g., using the Entity-Relationship model
• Logical design
• e.g., using the relational model
• Physical design

14
DATA MANIPULATION LANGUAGE (DML)

A data-manipulation language (DML) is a language that enables users to access

or manipulate data as organized by the appropriate data model. The types of
access are:
• Retrieval of information stored in the database
• Insertion of new information into the database
• Deletion of information from the database
• Modification of information stored in the database
• Generating reports

SQL: is non-procedural language

15
DML- EXAMPLES

16
DATA MANIPULATION LANGUAGE (DML)

Populating a database
• Inserting data to reflect the miniworld
• miniworld : are aspect(data ) of the real world, sometimes also called the
Universe of Discourse (UoD)

Query
• is a statement requesting the retrieval of information
• The portion of a DML that involves information retrieval is called a query language.

Application program
• Accesses database by sending queries and updates to DBMS
• The queries are sent from the Application program to the database is through:
1. Language extensions that allow embedded SQL
2. Application program interface (e.g. ODBC/JDBC)

•Transaction
• An atomic unit of queries and updates that must be executed as a whole
• e.g., buying a product, transferring funds, switching co-op streams

17
DATABASE ADMINISTRATOR (DBA)

 Coordinates all the activities of the database system; the database

administrator has a good understanding of the enterprise’s information
resources and needs.

1. Database administrator (DBA) responsible for:

• Authorizing access to the database
• Coordinating and monitoring its use
• Acquiring software and hardware resources
• Tuning the DBMS for best performance
2. Database designer responsible for:
• Identifying the data to be stored
• Choosing appropriate structures to represent and store this data

18
DATABASE END USERS
3. End users
• Those whose jobs require access to the database
• Naive or parametric end users
• querying and updating the database, using standard types of queries and
updates—called canned queries and updates
• Ex:
• Sophisticated end users
• include engineers, scientists, business analysts, and
others who thoroughly familiarize themselves with the facilities of the
DBMS in order to implement their own applications to meet their complex
requirements.
• Standalone users
• users of personal databases
4. System analysts
• Determine requirements of end users and develop specifications for standard
canned transactions that meet these requirements

5. Application programmers
• Implement these complex specifications (business logic) as programs
 The analysts and application programmers are commonly referred to as software
developers or software engineers—they should be familiar with the full range of
capabilities provided by the DBMS to accomplish their tasks.

19
DBMS SCHEMATIC

20
Application Architectures

- Two-tier architecture: E.g. client programs using Application program

interface standards like ODBC/JDBC to
communicate with a database server

- Three-tier architecture: E.g. web-based applications.

• the client end communicates with an application server, usually
through a forms interface. The application server in turn

21
communicates with a database system to access
EXAMPE 1
 consider a simple example : a UNIVERSITY database for maintaining
information concerning students, courses, and grades in a university
environment.
 Figure 1.2 below shows the database structure and a few sample data for
such a database.
 The database is organized as five files, each of which stores data records
of the same type.
• The STUDENT file stores data on each student,
• the COURSE file stores data on each course, the SECTION file stores
data
on each section of a course,
• the GRADE_REPORT file stores the grades that students receive in the
various sections they have completed,
• and the PREREQUISITE file stores the prerequisites of each course.
 To define this database, we must specify the structure of the records of
each file by
specifying the different types of data elements to be stored in each record.

22
EXAMPLE 1- CONTINUED
 In Figure 1.2, each STUDENT record includes data to represent the
student’s Name,
Student_number, Class (such as freshman or ‘1’, sophomore or ‘2’, and so
forth), and Database manipulation involves querying and updating.
Examples of queries are as follows:
■ Retrieve the transcript—a list of all courses and grades—of ‘Smith’
■ List the names of students who took the section of the ‘Database’ course
offered in fall 2008 and their grades in that section
■ List the prerequisites of the ‘Database’ course
Examples of updates include the following:
■ Change the class of ‘Smith’ to sophomore
■ Create a new section for the ‘Database’ course for this semester
■ Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’ section of last semester
These informal queries and updates must be specified precisely in the
query language of the DBMS before they can be processed.

23
Example 1- Continued

24
AN EXAMPLE
 Movie database
• Information concerning movies, actors, awards
 Data records
• Film
• Person
• Role
• Honours
 Define structure of each type of record by specifying data elements
to include and data type for each element
• String (sequence of alphabetic characters)
• Numeric (integer or real)
• Date (year or year-month-day)
• Monetary amount
• etc.

25
AN EXAMPLE (CONT'D.)
 Populate MOVIES database
• Store data to represent each film, actor, director, award, role
Film
title genre year director runtime budget gross
The Company
Men drama 2010 John Wells 104 15,000,000 4,439,063
Lincoln biography 2012 Steven Spielberg 150 65,000,000 181,408,467 Person
War Horse drama 2011 Steven Spielberg 146 66,000,000 79,883,359 name birth city
Argo drama 2012 Ben Affleck 120 44,500,000 135,178,251 Ben Affleck 1972 Berkeley
Fire Sale comedy 1977 Alan Arkin 88 1,500,000 0 Alan Arkin 1934 New York
Tommy Lee Jones 1946 San Saba
John Wells 1957 Alexandria
Honours
Steven Spielberg 1946 Cincinnati
movie award category winner
Daniel Day-Lewis 1957 Greenwich
Lincoln Critic's Choice actor Daniel Day-Lewis
Argo Critic's Choice director Ben Affleck
Lincoln Screen Actors Guild supporting actor Tommy Lee Jones Role
Lincoln Screen Actors Guild actor Daniel Day-Lewis actor movie persona
Lincoln Critic's Choice screenplay Tony Kushner Ben Affleck Argo Tony Mendez
Argo Screen Actors Guild cast Argo Alan Arkin Argo Lester Siegel
War Horse BMI Flim music John Williams Ben Affleck The Company Men Bobby Walker
Tommy Lee Jones The Company Men Gene McClary
Tommy Lee Jones Lincoln Thaddeus Stevens

26
Alan Arkin Fire Sale Ezra Fikus
Daniel Day-Lewis Lincoln Abraham Lincoln
AN EXAMPLE (CONT'D.)
 Manipulation involves querying and updating
 Examples of queries:
• List the cast of characters for Lincoln.
• Who directed a drama in 2012?
• Who directed a film in which he or she also played a role?
• What awards were won by War Horse?
 Examples of updates:
• Record that Argo won a Golden Globe award for best picture.
• Add another $395,533 to the gross earnings for Lincoln.
• Change the birthplace for Daniel Day-Lewis to London.
• Delete Fire Sale from the database.

27
TERMINOLOGY (CONT'D.)
 Reorganizing a database
• Changes the metadata rather than the data
• More drastic than data updates
• May require massive changes to the data
• May require changes to some application programs
• Uses the Data Definition Language again
 Examples:
• Rename gross to be domestic earnings and add a new column for
foreign earnings.
• Move director from FILM to a separate relation DIRECTOR with
columns for person and movie
• Change birth from yyyy to yyyy/mm/dd
• Split name in PERSON to separate surname from given names.
• Include column movieID in FILM (to accommodate remakes and
other duplications of film title) and update other relations
accordingly.

28
PRE-DBMS DATABASES
 Used traditional file processing
• Each user defines and implements the files needed for a specific
software application as part of programming the application.

• As the application base grows

• many shared files
• a multitude of file structures
• a need to exchange data among applications
 Eventually recognized that data is a critical corporate asset (along
with capital and personnel)

29
DATABASE APPROACH (REF2, P 4)

 Single repository maintains data that is defined once and then accessed by various users
 It Addresses a variety of problems
• Data redundancy inconsistency:
• the same information may be duplicated in several places (files)
• This redundancy leads to higher storage and access
cost. In addition, it may lead to data inconsistency
• data inconsistency: that is, the various copies of the same data may no longer agree

• Difficulty in accessing data : data can’t be retrieved in a convenient and efficient manner
• Data isolation:
• Because data are stored in various files, and may
be in different formats, writing new application programs to retrieve the
appropriate data is difficult

• Integrity problems
• The data values stored in the database must satisfy certain types of consistency constraints.

• Atomicity problems:
• atomic—it must happen in its entirety or not at all.
• It is difficult to ensure atomicity in a conventional file-processing
system.

• Concurrent-access anomalies
• Databases can be accessed simultanously. In such an environment, interaction of concurrent updates is possible and may
result in inconsistent data.

 Security problems. Not every user of the database system should be able
to access all the data

30
CHARACTERISTICS OF THE SELF - DESCRIBING
NATURE OF A DATABASE SYSTEM

 A fundamental characteristic of the database approach is that the database

system
contains not only the database itself but also a complete definition or description
of
the database structure and constraints.
 This definition is stored in the DBMS catalog, which contains information such
as the structure of each file, the type and storage format of each data item, and
various constraints on the data.
 The information stored in the catalog is called meta-data, and it describes the
structure of the primary database
 The catalog is used by the DBMS software and also by database users who
need
information about the database structure.
 The DBMS software must work equally well with any
number of database applications—for example, a university database, a
banking
database, or a company database—as long as the database definition is stored
in the
catalog

31
DATABASE CATALOG
• Figure 1.3 shows some sample
entries in a database catalog.
• These definitions are specified
by the database designer prior
to creating the actual database
and are stored in the catalog.
• Whenever a request is made to
access, say, the Name of a
STUDENT record, the DBMS
software refers to the catalog to
determine the structure of the
STUDENT file and the position
and size of the Name data item
within a STUDENT record.

32
SHARING OF DATA AND MULTIUSER TRANSACTION
PROCESSING
• A multiuser DBMS, as its name implies, must allow multiple users to access the
database at the same time.
• This is essential if data for multiple applications is to be integrated and maintained in a
single database.
• The DBMS must include concurrency control software to ensure that several users
trying to update the same data do so in a controlled manner so that the result of the
updates is correct.
• For example, when several reservation agents try to assign a seat on an airline flight,
the DBMS should ensure that each seat can be accessed by only one agent at a time
for assignment to a passenger.
• These types of applications are generally called online transaction processing
(OLTP) applications. A fundamental role of multiuser DBMS software is to
ensure that concurrent transactions operate correctly and efficiently.
• The concept of a transaction has become central to many database applications.
• A transaction is a collection of operations that performs a single logical function in a
database application
• Transaction-management component ensures that the database remains in a consistent
(correct) state despite system failures (e.g., power failures and operating system crashes) and
transaction failures.
• The DBMS must enforce several transaction properties.
• The isolation property ensures that each transaction appears to execute

33
in isolation from other transactions, even though hundreds of transactions may be executing concurrently.
• The atomicity property ensures that either all the database operations in a transaction are executed or
WORKERS BEHIND THE SCENE
 In addition to those who design, use, and administer a database, others are
associated with the design, development, and operation of the DBMS
software and system environment.
 These persons are typically not interested in the database content
itself. We call them the workers behind the scene, and they include the
following categories:

1. DBMS system designers and implementers

• Design and implement the DBMS modules and interfaces as a
software package
2. Tool developers
• Design and implement tools
3. Operators and maintenance personnel
• Responsible for running and maintenance of hardware and
software environment for database system

34
WHEN NOT TO USE A DBMS
 More desirable to use regular files for:
• Simple, well-defined applications with no expected changes at all
• Small variety of data and/or small amount of data
• Stringent, real-time requirements that cannot afford DBMS
overhead
• Only single (personal) access to data

 Unlikely that any of these apply to corporate data management.

• In fact, corporations often maintain many databases across many
database systems.

35
LECTURE SUMMARY
 Database
• Collection of related data (recorded facts)
 DBMS
• Generalized software package for implementing and maintaining a
computerized database
• Provides many services to manage data resources
 Several categories of database users

36