Information and
Communication
Technologies (ICT)
Database Systems
Lecture 5
• Overview of Today’s Lecture
• Fundamentals of database systems: concept, characteristic,
architecture
• Data models. Normalization. Data integrity restrictions
• Query optimization and processing
• The basics of SQL. Parallel processing of data and their recovery
• Design and development of databases
• Object-relational mapping (ORM) programming technology
• Distributed, parallel and heterogeneous databases
2
Database
What is a database ?
Give examples of databases
3
Database
What is a database ?
• A collection of files storing related data
Give examples of databases
• Accounts database; payroll database; ENU’s students database;
Amazon’s products database; airline reservation database
4
Database Management System
What is a DBMS ?
Give examples of DBMS
5
Database Management System
What is a DBMS ?
• A big C/C++ program written by someone else that
allows us to manage efficiently a large database
and allows it to persist over long periods of time
Give examples of DBMS
• DB2 (IBM), SQL Server (MS), Oracle, Sybase
• MySQL, Postgres, …
6
What the Database Systems
Does
1. Create/store large datasets
2. Search/query/update
3. Change the structure
4. Concurrent access to many user
5. Recover from crashes
6. Security
7
Enters a DMBS
“Two tier system” or “client-server”
connection
(ODBC, JDBC)
Database server
(someone else’s
Data files C program) Applications
8
Data Independence
Logical
view
Directors: Movie_Directors:
id fName lName id mid
15901 Francis Ford Coppola 15901 130128
... ...
Movies: mid Title Year
130128 The Godfather 1972
...
Directors_fil Moviews_title_index_fil
e e Physical
Directors_fname_index_fil Movies_fil view 9
e e
What is Database ?
A Database is a structured collection of data
that allows people to extract information in
a way that meets their needs. The data can
include text, numbers, pictures: anything
that can be stored in a computer;
A Database a collection of data that is
organized especially to be used by a
computer;
10
Database Management System
(DBMS)
• DBMS contains information about a particular enterprise
• Collection of interrelated data
• Set of programs to access the data
• An environment that is both convenient and efficient to use
• Database Applications:
• Banking: transactions
• Airlines: reservations, schedules
• Universities: registration, grades
• Sales: customers, products, purchases
• Online retailers: order tracking, customized recommendations
• Manufacturing: production, inventory, orders, supply chain
• Human resources: employee records, salaries, tax deductions
• Databases can be very large.
• Databases touch all aspects of our lives
11
University Database Example
• Application program examples
• Add new students, instructors, and courses
• Register students for courses, and generate class rosters
• Assign grades to students, compute grade point averages
(GPA) and generate transcripts
• In the early days, database applications were built
directly on top of file systems
12
What are databases
used for?
To store information about people, for example:
• patients in a hospital
• students at a school.
To store information about things, for example:
• cars to be sold
• books in a library.
To store information about events, for example:
• hotel bookings
• results of races.
13
Concepts of Database
• Field (Column): a single piece of information. Could be
a name, or a number. In some cases, it may even be a
null or empty value.
• Record (Row): a collection of related fields. A number
of pieces of information that relate to the same
object. For example: If you keep records on an
employee, you might have their name, address, social
security number, phone number, etc…Each piece of the
information relates back to one employee. This would
be the employee’s record.
• Table (File): a collection of related records. If you put all
the employee records together, you have a table of
employees.
14
The structure of Database
Table
Record 1 Field 1 Field 2 Field 3 Field 4
Record 2 Field 1 Field 2 Field 3 Field 4
Record 3 Field 1 Field 2 Field 3 Field 4
Record 4 Field 1 Field 2 Field 3 Field 4
Record 5 Field 1 Field 2 Field 3 Field 4
15
Data Models
• A collection of tools for describing
• Data
• Data relationships
• Data semantics
• Data constraints
• Relational model
• Entity-Relationship data model (mainly for database
design)
• Object-based data models (Object-oriented and
Object-relational)
• Semistructured data model (XML)
• Other older models:
• Network model
• Hierarchical model
16
Hierarchical model.
In a hierarchical model, data is organized into
an inverted tree-like structure. This structure
arranges the various data elements in a
hierarchy and helps to establish logical
relationships among data elements of
multiple files. Each unit in the model is a
record which is also known as a node. Each
record has a single parent.
17
Hierarchical model.
18
Network model.
• The network model tends to store records with links to
other records. Each record in the database can have
multiple parents, i.e., the relationships among data
elements can have a many to many relationships. So,
this model is an expansion to the hierarchical structure,
allowing many-to-many relationships in a tree-like
structure that allows multiple parents.
• The network model provides greater advantage than the
hierarchical model in that it promotes greater flexibility
and data accessibility.
19
Network model.
20
Relational model.
The relational model for the database management is a
database model based on relations. The basic data
structure of the relational model is a table where
information about a particular entity (say, a student) is
represented in columns and rows. The columns
enumerate the various attributes (i.e. characteristics) of
an entity (e.g. student name, address, registration
_number). The rows (also called records) represent
instances of an entity (e.g. specific student).
21
Relational Model
• All the data is stored in various tables.
• Example of tabular data in the relational model Columns
Rows
22
A Sample Relational Database
23
Relational model
24
Object-Oriented model.
• In the object oriented data model the (OODM). Both
data and their relationship are contained in a single
structure known us an object.
• An object includes information about relationship
between the facts within the object, as well as
information about its relationship with other objects.
• It is said to be ‘’SEMANTIC DATA MODEL’’
• The OO Data model components An object is the
abstraction of the real- word entity. An object
represents only one occurrence of entity.
• Attributes describe the property of an object.
25
26
Normalization
Normalization of databases is a process
of transformation of database to the
kind answering the normalized forms.
27
Normalization
In the theory of relational databases, it is accepted to
distinguish four types of limitations of integrity:
• Limitation of database is name a limit on values that it is
let to accept to the indicated database.
• Limiting to the variable of relation is name a limit on
values that it is let to accept to the indicated variable of
relation.
• Limitation of attribute is name a limit on values that it is
let to accept to the indicated attribute.
• Limitation of type is not that another, as determination
of great number of values this type consists of that.
28
Data integrity restrictions
Integrity of a DB doesn't guarantee reliability (truth) of
the information which is contained in it, but provides at
least plausibility of this information, rejecting certainly
improbable, impossible values. Thus, it isn't necessary
to confuse integrity (consistency) of a DB to truth of a
DB. Truth and consistency — not same.
29
Database System (DBS)
A database management system (DBMS) is system
software for creating and managing databases. The DBMS
provides users and programmers with a systematic way to
create, retrieve, update and manage data.
The DBMS essentially serves as an interface
between the database and end users or
application programs, ensuring that data is consistently
organized and remains easily accessible.
30
Data Definition Language (DDL)
• Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
• DDL compiler generates a set of table templates stored in a
data dictionary
• Data dictionary contains metadata (i.e., data about data)
• Database schema
• Integrity constraints
• Primary key (ID uniquely identifies instructors)
• Authorization
• Who can access what
31
Data Manipulation Language
(DML)
• Language for accessing and manipulating the data
organized by the appropriate data model
• DML also known as query language
• Two classes of languages
• Pure – used for proving properties about computational
power and for optimization
• Relational Algebra
• Tuple relational calculus
• Domain relational calculus
• Commercial – used in commercial systems
• SQL is the most widely used commercial language
32
The basics of SQL
SQL stands for Structured Query Language.
SQL is used to communicate with a database. According to
ANSI (American National Standards Institute), it is the
standard language for relational database management
systems. SQL statements are used to perform tasks such as
update data on a database, or retrieve data from a
database. Some common relational database
management systems that use SQL are: Oracle, Sybase,
Microsoft SQL Server, Access, Ingres, etc.
33
The basics of SQL
In the general terms, "SQL the database" is the general
name for the relational database management system
(RDMS). For some systems, "database" also concerns to
group of tables, data, the configuration information
which are essentially separate part from other, similar
constructions. In this case, each installation of SQL of
the database can consist of several databases. In other
systems, they are mentioned as tables.
34
Here is a sample table called
"weather".
Weather
city state high low
Phoenix Arizona 105 90
Tucson Arizona 101 92
Flagstaff Arizona 88 69
San Diego California 77 60
Albuquerque New
Mexico 80 72
35
The basics of SQL
One of the major operations which are executed by
data full operation is selection of the information
which is stored in the database. For this purpose,
the user shall execute a request (query).
36
The basics of SQL
There are four main types of requests of data in SQL
which relate to so-called language of a manipulation data
(DataManipulationLanguage or DML):
• SELECT – to choose lines from tables;
• INSERT – to add a line to the table;
• UPDATE – to change lines in the table;
• DELETE – to remove lines in the table;
Each of these requests has different operators and
functions which are used to make some dataful actions.
37
Design and development of databases
Design of databases — process of creation of the
database scheme and determination of necessary
integrity constraints.
Main objectives of design of databases:
• Support of storage in a DB of all necessary information.
• A data acquisition possibility on all necessary requests.
• Abbreviation of redundancy and duplicating of data.
• Support of integrity of the database.
Main design stages of databases
38
Object-Relational Data Models
• Relational model: flat, “atomic” values
• Object Relational Data Models
• Extend the relational data model by including object
orientation and constructs to deal with added data types.
• Allow attributes of tuples to have complex types,
including non-atomic values such as nested relations.
• Preserve relational foundations, in particular the
declarative access to data, while extending modeling
power.
• Provide upward compatibility with existing relational
languages.
39
Object-relational mapping (ORM)
ORM or Object-relational mapping is a technology of
programming which allows to transform incompatible
types of models to OOP, in particular, between the
data store and subjects to programming. ORM is used
for simplification of process of saving objects in a
relational database and their extraction, in case of this
ORM itself cares for data transformation between two
incompatible statuses.
40
Object-relational mapping (ORM)
41
Object-relational mapping (ORM)
The majority of ORM tools considerably rely on meta
data of the database and objects so objects need to
know nothing about a database structure, and the
database — nothing about how data are organized in
the application. ORM provides complete division of
tasks in well programmed applications in case of which
both the database, and the application can work data
full everyone in the root form.
42
XML: Extensible Markup
Language
• Defined by the WWW Consortium (W3C)
• Originally intended as a document markup language
not a database language
• The ability to specify new tags, and to create nested
tag structures made XML a great way to exchange
data, not just documents
• XML has become the basis for all new generation
data interchange formats.
• A wide variety of tools is available for parsing,
browsing and querying XML documents/data
43
Database Users and
Administrators
Database
44
Database Architecture
The architecture of a database systems is greatly
influenced by the underlying computer system on
which the database is running:
• Centralized
• Client-server
• Parallel (multi-processor)
• Distributed
45
Distributed, parallel and
heterogeneous databases
A distributed database is a database in
which portions of the database are
stored in multiple physical locations and
processing is distributed among multiple
database nodes.
46
Distributed database
47
Parallel DB
Parallel database system seeks to improve
performance through parallelization of
various operations such as loading
data ,building indexes, and evaluating
queries by using multiple CPUs and Disks
in Parallel.
48
Parallel DB
49
Heterogeneous database
In a heterogeneous distributed database, different
sites have different operating systems, DBMS products
and data models.
Its properties are :
• Different sites use dissimilar schemas and software.
• The system may be composed of a variety of DBMSs like
relational, network, hierarchical or object oriented.
• Query processing is complex due to dissimilar schemas.
• Transaction processing is complex due to dissimilar
software.
• A site may not be aware of other sites and so there is
limited co-operation in processing user requests.
50
Heterogeneous database
51
History of Database Systems
• 1950s and early 1960s:
• Data processing using magnetic tapes for storage
• Tapes provided only sequential access
• Punched cards for input
• Late 1960s and 1970s:
• Hard disks allowed direct access to data
• Network and hierarchical data models in widespread use
• Ted Codd defines the relational data model
• Would win the ACM Turing Award for this work
• IBM Research begins System R prototype
• UC Berkeley begins Ingres prototype
• High-performance (for the era) transaction processing
52
History (cont.)
• 1980s:
• Research relational prototypes evolve into commercial systems
• SQL becomes industrial standard
• Parallel and distributed database systems
• Object-oriented database systems
• 1990s:
• Large decision support and data-mining applications
• Large multi-terabyte data warehouses
• Emergence of Web commerce
• Early 2000s:
• XML and XQuery standards
• Automated database administration
• Later 2000s:
• Giant data storage systems
• Google BigTable, Yahoo PNuts, Amazon, ..
53
Questions:
1. Database is …
2. Database management system is …
3. Examples of DBMS
4. Types of DBMS
5. Relational DBMS
6. Concept of DBMS
54
