Databases and Database Management

Systems

(Based on Chapters 1-2 in Fundamentals of

Database Systems by Elmasri and Navathe, Ed. 3)
Topics

• Basic Definitions
• Example of a Database
• Main Characteristics of Database Technology
• Additional Benefits of Database Technology
• When Not to Use a DBMS
• Data Models
- History of data Models
- Network Data Model
- Hierarchical Data Model
Topics

• Schemas versus Instances

• Three-Schema Architecture
• Data Independence
• DBMS Languages
• DBMS Interfaces
• DBMS Component Modules
• Database System Utilities
• Classification of DBMSs
1. 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.
Alternate Definition
• Database: An integrated collection of more-or-less permanent data.
 
• Database Management System (DBMS): A software package/ system to facilitate
the creation and maintenance of a computerized database.
• Concerns of DBMS:
integrity
consistency
redundancy (it’s bad, but “replication” is good!)
security
4. Additional Benefits of Database
Technology
• Controlling redundancy in data storage and in
development and maintenence efforts.

• Sharing of data among multiple users.

• Restricting unauthorized access to data.

• Providing multiple interfaces to different classes of users.

• Representing complex relationships among data.

• Enforcing integrity constraints on the database.

• Providing backup and recovery services.

• Potential for enforcing standards.

• Flexibility to change data structures.

• Reduced application development time.

• Availability of up-to-date information.

• Economies of scale.
Data Models
• Data Model: A set of concepts to describe the structure of
a database, and certain constraints that the database
should obey.

• Data Model Operations: Operations for specifying

database retrievals and updates by referring to the
concepts of the data model.
HISTORY OF DATA MODELS
• Relational Model: proposed in 1970 by E.F. Codd (IBM),
first commercial system in 1981-82.

• Network Model: the first one to be implemented by

Honeywell in 1964-65 (IDS System).

• Hierarchical Data Model : implemented in a joint effort by

IBM and North American Rockwell around 1965.
• Object-oriented Data Model(s) : several models have been
proposed for implementing in a database system.
• Object-Relational Models : Most Recent Trend.
Exemplified in ILLUSTRA and UNiSQL systems.
HIERARCHICAL MODEL
ADVANTAGES:
• Hierarchical Model is simple to construct and operate on
• Corresponds to a number of natural hierarchically organized domains - e.g.,
assemblies in manufacturing, personnel organization in companies
DISADVANTAGES:

• Navigational and procedural nature of processing

• Database is visualized as a linear arrangement of records
. Little scope for "query optimization"
NETWORK MODEL
ADVANTAGES:

• Network Model is able to model complex relationships

and represents semantics of add/delete on the
relationships.

• Can handle most situations for modeling using record

types and relationship types.
DISADVANTAGES:

• Navigational and procedural nature of processing

• Database contains a complex array of pointers that thread

through a set of records.
Schemas versus Instances
• Database Schema: The description of a database. Includes
descriptions of the database structure and the constraints
that should hold on the database.
 
• Schema Diagram: A diagrammatic display of (some aspects
of) a database schema.
• Database Instance: The actual data stored in a database at
a particular moment in time . Also called database state (or
occurrence).
 
• The database schema changes very infrequently . The
database state changes every time the database is updated
. Schema is also called intension, whereas state is called
extension.
Three-Schema Architecture
• Defines DBMS schemas at three levels :

- Internal schema at the internal level to describe data

storage structures and access paths. Typically uses a
physical data model.

- Conceptual schema at the conceptual level to describe

the structure and constraints for the whole database. Uses
a conceptual or an implementation data model.

- External schemas at the external level to describe the

various user views. Usually uses the same data model as
the conceptual level.
Mappings among schema levels are also
needed. Programs refer to an external
schema, and are mapped by the DBMS to
the internal schema forexecution.
• Logical Data Independence: The capacity to change the
conceptual schema without having to change the external
schemas and their application programs.
• Physical Data Independence: The capacity to change the internal
schema without having to change the conceptual schema.

When a schema at a lower level is changed, only the

mappings between this schema and higher-level
schemas need to be changed in a DBMS that fully
supports data independence. The higher-level schemas
themselves are unchanged. Hence, the application
programs need not be changed since they refer to the
external schemas.
Introduction to Database
Management System

Poonam Mehta
Lecturer in Computer Engg.
Content
• What is Database Management System?
• Why Use a DBMS?
• Purpose of DBMS
• Data models
• Levels or layers of DBMS architecture
• Components of DBMS
• Advantage of DBMS
• Disadvantage of DBMS
• DBMS Languages
What is Database Management System?

Database Management System (DBMS), or simply a Database

System (DBS) consist of :

• Collection of interrelated and persistent data (usually referred

to as the database (DB)).

• Set of application programs used to access, update and

manage that data.
Why Use a DBMS?

• Data independence and efficient access.

• Reduced application development time.
• Data integrity and security.
• Uniform data administration.
• Concurrent access, recovery from crashes.
 Purpose of DBMS
1. Data redundancy and inconsistency
• Same information may be duplicated in several places.
• All copies may not be updated properly.

2. Difficulty in new program to carry out each new task

3. Data isolation —
• Data in different formats.
• Difficult to write new application programs.
files and formats
Purpose of DBMS …
Security problems
Every user of the system should be able to access only the data
they are permitted to see.
• E.g. payroll people only handle employee records, and cannot
see customer accounts; tellers only access account data and
cannot see payroll data.
• Difficult to enforce this with application programs.
Integrity problems
• Data may be required to satisfy constraints.
• E.g. no account balance below $25.00.
• Again, difficult to enforce or to change constraints with the file-
processing approach.
 Data models

Hierarchical Model
• The hierarchical data model organizes data in a tree structure. There is a
hierarchy of parent and child data segments. This structure implies that a
record can have repeating information, generally in the child data segments.
• Hierarchical DBMSs were popular from the late 1960s, with the introduction
of IBM's Information Management System (IMS) DBMS, through the 1970s.
 Network Model

• The popularity of the network data model coincided with the

popularity of the hierarchical data model. Some data were more
naturally modeled with more than one parent per child.
• So, the network model permitted the modeling of many-to-
many relationships in data. In 1971, the Conference on Data
Systems Languages (CODASYL) formally defined the network
model.
 Relational Model

• (RDBMS - relational database management system) A

database based on the relational model developed by E.F. Code.
• A relational database allows the definition of data structures,
storage and retrieval operations and integrity constraints.
• In such a database the data and relations between them are
organized in tables. A table is a collection of records and each
record in a table contains the same fields.
Properties of Relational Tables

• Values Are Atomic

• Each Row is Unique
• Column Values Are of the Same Kind
• The Sequence of Columns is Insignificant
• The Sequence of Rows is Insignificant
• Each Column Has a Unique Name
Object-Oriented Model

• Object DBMSs add database functionality to object

programming languages. They bring much more than
persistent storage of programming language objects.
• A major benefit of this approach is the unification of the
application and database development into a seamless data
model and language environment.
 Architecture of DBMS
• There are following three levels or layers of DBMS
architecture:

• 1. External Level

• 2. Conceptual Level

• 3. Internal Level
Architecture of DBMS
 levels or layers of DBMS architecture

• External Level: - External Level is described by a schema i.e.

it consists of definition of logical records and relationship in
the external view.
• Conceptual Level: - Conceptual Level represents the entire
database. Conceptual schema describes the records and
relationship included in the Conceptual view. .
• Internal Level: - Internal level indicates hoe the data will be
stored and described the data structures and access method to
be used by the database.
Components of DBMS

1. Hardware: Can range from a PC to a network of

computers.
2. Software: DBMS, operating system, network software
(if necessary) and also the application programs.
3. Data: Used by the organization and a description of this
data called the schema.
4. People: Includes database designers, DBAs, application
programmers, and end-users.
5. Procedure: Instructions and rules that should be applied
to the design and use of the database and DBMS.
Advantage of DBMS
• Controlling  Redundancy 
• Sharing of Data
• Data Consistency 
• Integration of Data
• Integration Constraints
• Data Security
• Report Writers
Advantage of DBMS…
• Control Over Concurrency 
• Backup and Recovery Procedures 
• Data Independence 
Disadvantage of DBMS

• Cost of Hardware and Software

• Cost of Data Conversion
• Cost of Staff Training
• Appointing Technical Staff
• Database Damage
DBMS Languages
Data Definition Language-DDL
• Data Definition Language (DDL) statements are used to
define the database structure or schema.
Some examples:
• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all
spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object
 Data Manipulation Language (DML)

  Data Manipulation Language (DML) statements are used for

managing data within schema objects.
Some examples:
• SELECT - Retrieve data from the a database
• INSERT - Insert data into a table
• UPDATE - Updates existing data within a table
• DELETE - deletes all records from a table, the space for the
records remain
• MERGE - UPSERT operation (insert or update)
• CALL - Call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency
Thanks….!!!!!!!
DATABASE MANAGEMENT SYSTEMS

• DBMS (Data Schemas)

• HDBMS (Hierarchical Database Management System)
• NDBMS (Network Database Management System)
• RDBMS (Relational Database Management System)
• OODBMS (Object oriented Database Management System)
3.1 DBMS- Data Base Management System

Database is collection of data which is related by some aspect. Data is collection of facts and figures
which can be processed to produce information. Name of a student, age, class and her subjects can be
counted as data for recording purposes.

Mostly data represents recordable facts. Data aids in producing information which is based on facts.
For example, if we have data about marks obtained by all students, we can then conclude about
toppers and average marks etc.

A database management system stores data, in such a way which is easier to retrieve, manipulate and
helps to produce information.
3.1.7 DBMS - Data Schemas

Database schema skeleton structure of and it represents the logical view of entire database. It tells about how the data
is organized and how relation among them is associated. It formulates all database constraints that would be put on
data in relations, which resides in database.

A database schema defines its entities and the relationship among them. Database schema is a descriptive detail of the
database, which can be depicted by means of schema diagrams. All these activities are done by database designer to
help programmers in order to give some ease of understanding all aspect of database.

Database schema can be divided broadly in two categories:

 Physical Database Schema: This schema pertains to the actual storage of data and its form of storage like files, indices
etc. It defines the how data will be stored in secondary storage etc.

 Logical Database Schema: This defines all logical constraints that need to be applied on data stored. It defines tables,
views and integrity constraints etc.
3.1.7.2 Database Instance

It is important that we distinguish these two terms individually. Database schema is the skeleton of database.

It is designed when database doesn't exist at all and very hard to do any changes once the database is operational.
Database schema does not contain any data or information.

Database instances, is a state of operational database with data at any given time. This is a snapshot of
database. Database instances tend to change with time. DBMS ensures that its every instance (state) must be a valid
state by keeping up to all validation, constraints and condition that database designers
has imposed or it is expected from DBMS itself.
3.2 HDBMS (Hierarchical Database Management System)

A hierarchical database model is a data model in which the data is organized into a tree-like structure.

The data is stored as records which are connected to one another through links. A record is a collection of fields,
with each field containing only one value. The entity type of a record defines which fields the record contains.
3.2.1 Example of a hierarchical model

A record in the hierarchical database model corresponds to a row in the relational database model and
an entity type corresponds to a table.

The hierarchical database model mandates that each child record has only one parent, whereas each parent record can
have one or more child records. In order to retrieve data from a hierarchical database the whole tree needs to be
traversed starting from the root node. This model is recognized as the first database model created by IBM in the
1960s.

The Hierarchical Data Model is a way of organizing a database with multiple one to many relationships. The structure is
based on the rule that one parent can have many children but children are allowed only one parent. This structure
allows information to be repeated through the parent child relations created by IBM and was implemented mainly in
their Information Management System.
.2.1 Example of a hierarchical model
3.2.2 Advantages

The model allows easy addition and deletion of new information. Data at the top of the Hierarchy is very fast to
access. It was very easy to work with the model because it worked well with linear type data storage such as tapes.
The model relates very well to natural hierarchies such as assembly plants and employee organization in
corporations. It relates well to anything that works through a one to many relationships. For example; there is a
president with many managers below them, and those managers have many employees below them, but each
employee has only one manager.

3.2.3 Disadvantages

This model has many issues that hold it back now that we require more sophisticated relationships. It requires data
to be repetitively stored in many different entities. The database can be very slow when searching for information
on the lower entities. We no longer use linear data storage mediums such as tapes so that advantage is null.
Searching for data requires the DBMS to run through the entire model from top to bottom until the required
information is found, making queries very slow. Can only model one to many relationships, many to many
relationships are not supported. Clever manipulation of the model is required to make many to may relationships.
3.3 NDBMS-Network Database Management System

Network Database: A network databases are mainly used on large digital computers. It more connections can be made
between different types of data, network databases are considered more efficiency It contains limitations must be
considered when we have to use this kind of database. It is Similar to the hierarchical databases; network databases.

Network databases are similar to hierarchical databases by also having a hierarchical structure. A network database
looks more like a cobweb or interconnected network of records. In network databases, children are called members
and parents are called occupier. The difference between each child or member can have more than one parent. The
Approval of the network data model similar with the esteem of the hierarchical data model. Some data were more
naturally modeled with more than one parent per child. The network model authorized the modeling of many-to-
many relationships in data.

The network model is very similar to the hierarchical model really. Actually the hierarchical model is a subset of the
network model. However, instead of using a single-parent tree hierarchy, the network model uses set theory to
provide a tree-like hierarchy with the exception that child tables were allowed to have more than one parent. It
supports many-to-many relationships.
3.4 RDBMS-Relational Database Management System

In relational databases, the relationship between data files is relational. Hierarchical and network databases require
the user to pass a hierarchy in order to access needed data. These databases connect to the data in different files by
using common data numbers or a key field. Data in relational databases is stored in different access control tables,
each having a key field that mainly identifies each row. In the relational databases are more reliable than either the
hierarchical or network database structures. In relational databases, tables or files filled up with data are called
relations designates a row or record, and columns are referred to as attributes or fields.

Relational databases work on each table has a key field that uniquely indicates each row, and that these key fields can
be used to connect one table of data to another.
3.4.1 The relational database has two major reasons

1. Relational databases can be used with little or no training.

2. Database entries can be modified without specify the entire body.

3.4.2 Properties of Relational Tables

In the relational database we have to follow some properties which are given below.

• It's Values are Atomic

• In Each Row is alone
• Column Values are of the same thing.
• Columns are undistinguished.
• Sequence of Rows is Insignificant.
• Each Column has a common Name.
3.5 OODBMS – Object oriented Database Management System

In this Model we have to discuss the functionality of the object oriented Programming .It takes more than storage of
programming language objects. Object DBMS's increase the semantics of the C++ and Java .It provides full-featured
database programming capability, while containing native language compatibility. It adds the database functionality to
object programming languages. This approach is the analogical of the application and database development into a
constant data model and language environment. Applications require less code, use more natural data modeling, and
code bases are easier to maintain. Object developers can write complete database applications with a decent amount
of additional effort.

The object-oriented database derivation is the integrity of object-oriented programming language systems and
consistent systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent
data, as found in databases, and transient data, as found in executing programs.

Object-oriented databases use small, recyclable separated of software called objects. The objects themselves are
stored in the object-oriented database. Each object contains of two elements:

1. Piece of data (e.g., sound, video, text, or graphics).

2. Instructions or software programs called methods, for what to do with the data.
3.5.1 Disadvantage of Object-oriented databases

1. Object-oriented databases have these disadvantages.

2. Object-oriented database are more expensive to develop.
3. In the Most organizations are unwilling to abandon and convert from those databases.

The benefits to object-oriented databases are compelling. The ability to mix and match reusable objects provides
incredible multimedia capability
 Chapter 1

Introduction to Databases
Agenda

• File-based Systems
• History of Database
• Database Management Systems (DBMS)
File-based Definition

• Program defines and manages it’s own data

Limitations of File-based

• Separation and isolation

• Duplication
• Program & data dependence
• Fixed queries
• Proliferation of application programs
History of Database Systems
• First generation
• Hierarchical model
• Information Management System (IMS)
• Network model
• Conference on Data System Languages (CODASYL)
• Data Base Task Group (DBTG)
• Limitation
• Complex program for simple query
• Minimum data independence
• No theoretical foundation
• Second generation
• Relational model
• E. R. Codd
• DB2, Oracle
• Limitation
• Limited data modeling
• Third generation
• Object-relational DBMS
• Object-oriented DBMS
Database

• Definition
• A collection of self-describing and integrated data files
• System catalog
• Meta data
• Data dictionary
• Overhead data
• Data abstraction
Database Management System Facility

• Data definition language (DDL)

• Data manipulation language (DML)
• Structured query language (SQL)
• Security system
• Integrity system
• Concurrency control system
• Backup & recovery system
• View mechanism
DBMS Environment
• Hardware
• Client-server architecture
• Software
• dbms, os, network, application
• Data
• Schema, subschema, table, attribute
• People
• Data administrator & database administrator
• Database designer: logical & physical
• Application programmer
• End-user: naive & sophisticated
• Procedure
• Start, stop, log on, log off, back up, recovery
Advantages of DBMS
• Control redundancy
• Consistency
• Integrity
• Security
• Concurrency control
• Backup & recovery
• Data standard
• More information
• Data sharing & conflict control
• Productivity & accessibility
• Economy of scale
• Maintenance
Limitations of DBMS

• Complexity
• Size
• Cost
• Software
• Hardware
• Conversion
• Performance
• Vulnerability
 SQL
Introduction to database and SQL
Introduction to Databases
• Databases touch all aspects of our lives.

• Examples:
• Banking: all transactions.
• Hotels: reservation.
• Airlines: reservation, schedules.
• Libraries: catalog.
• Universities: registration, grades.
• Sales: customers, products, purchases.
• Manufacturing: production, inventory, orders.
• Human resources: employee records, salaries.
Traditional database applications
Most of the information is either textual or numeric.
Chapter 1: Databases and Database Users 6
What is a Database Management System (DBMS)?

• A collection of programs that enables users to create and maintain

a database.
• Facilitates the process of:
• Defining: specifying data types, structures, & constraints for data.
• Manipulating: querying the database to retrieve or update data
and generating reports from the data.
• Sharing: accessing the database concurrently.
• Protects the database and maintains it over a long period of time.

• Examples: Oracle, MS Access, DB2, Informix, MySQL, SyBase.

Chapter 1: Databases and Database Users 9

Overview of SQL
• Query: allow questions to be asked of the data and display only the
information required. It can include info from more the one table
• A Database Management system requires a Query language to enable
users to access data. Structured Query Language (SQL) is the
language used by most relational database systems.
Features of SQL
 SQL is an English-like language. It uses words such as select, insert,
delete as part of its command set
 SQL is a non-procedural language: you specify What information you
require, not how to get it . This feature makes it easier for you to
concentrate on obtaining the desired result.
 SQL Processes sets of records rather than a single record at a time.
 SQL can be used by a range of users including DBA’s, programmers,
Management Personnel, and many other types of end users
Features of SQL
 What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL is an ANSI (American National Standards Institute) standard
 What Can SQL do?
 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
Types of SQL Statements

• Data Definition Language (DDL):

Commands that define a database.
E.g. CREATE, ALTER, DROP, ...etc.

• Data manipulation language (DML):

Commands that maintain and query a database.
E.g. SELECT, INSERT, UPDATE, DELETE.

• Data Control Language (DCL):

Commands that control a database, including administering
privileges and committing data.
E.g. CONNECT, GRANT, REVOKE, ...etc.
The SQL Command SET
Command Description
SELESCT Retrieve data from the database
INSERT These 3 commands are used to Enter
new rows, Change Existing rows and
UPDATE Remove rows(Known as DML Data
DELETE Manipulation language commands)

CREATE These 3 commands used to set up,

change and remove any data
ALTER structure for example tables views
DROP indexes(Known as DDL Data
Definition Language commands)
Writing SQL Commands
• SQL commands maybe one or more lines
• Command words cannot be split across lines
• SQL Commands are not case sensitive
• Place a semi –colon(;) at the end of last clause.
CHAPTER 1: INTRODUCTION TO SQL
SQL is a relational database management system language or
syntax that is use to create, manipulate, and query dataset or
data. In MYSQL, there are three forms of SQL

• DDL :- Data Definition /Description Language

• DML :- Data Manipulation Language
• DCL :- Data Control Language
DATA DEFINITION/DESCRIPTION LANGUAGE

• DDL is an SQL Syntax or language that is use to

create view or describe a table or database.
Example is command CREATE, SHOW, &
DESCRIBE
DATA MANIPULATION LANGUAGE (DML)

• As the name implies, this is an SQL Syntax that is

used to store, view, retrieve, and manipulate or
modify data.

• Example of store is INSERT command, view and

retrieve is SELECT, and manipulate/modify are
ALTER, ALIAS, DELETE, & DROP etc.
DATA CONTROL LANGUAGE (DCL)
• This is an SQL Syntax that is use to restrict
unauthorized users from gaining access to the
database. Note however that this same language is
use to permit authorized users to gaining access to a
database.
• e.g.
• PERMISSION NO PERMISSION
• GRANT (Access to view) REVOKE (withdraw access)
INTRO TO MySQL
• MYSQL is an acronym and a personalized acronym
meaning My Structured Query Language. This is a
relational database management system that is
usually deployed as a free software, and it is also an
open source software. Aside MySQL, other popular
relation database management system are; SQL
Server, Oracle, MS-Access, MSQLite.
DEVELOPMENT ENVIRONMENT FOR MYSQL

• Basically we have three development/platform

environment for MYSQL;
• MYSQL Monitor e.g. Console
• GUI Alternatives e.g. MySQL CC
• Web-based administration e.g.
PhPMyAdmin
MySQL Data Types
• Integer
• Character and String
• Binary and Text
• Decimal
• Float and Double
• Enum and Set
• Date and Time
MySQL Engines
• 5 types
• MyISAM
• ISAM
• InnoDB
• Merge
• Heap
Cont’d
• MyISAM Table : This format is use for speed and
reliability. It supports tables in excess of 4GB in
size and it can also be used for the purpose of
compressing files.
Cont’d

• ISAM :- This is the predecessor of MyISAM .

It is primarily used for compatibility
purpose.
Cont’d
• InnoDB:- This is the successor of MyISAM table
type. It is the most sophisticated table in MySQL.
It support transaction and foreign keys and also
allows multi user access. In a nutshell, InnoDB
helps in a fast query execution and also help in
preventing data corruption.
Cont’d

• HEAP:- This table type is use for creating

and designing temporary table though
extremely fast. Table designed using this
table type is only available when server is
running and automatically erases when
server shut down.
Cont’d

•MERGE:- As the name implies this table

is use to merge a collection of MyISAM
table together to give one singular table.
Back to DDL…

• DDL : A Language used to define/describe a table

or database. Example is command CREATE,
SHOW, DESCRIBE and USE.
Back to DDL…CREATE

• CREATE: Used to create DATABASE and TABLE.

For instance:
• E.g.
• mysql>>create database (db name);
• mysql>>create database mydb;
Back to DDL…CREATE

• CREATE: Used to create DATABASE and TABLE.

For instance:
• E.g.
• mysql>>create table (table name);
• mysql>>create table studtable;
Back to DDL…USE

• USE: Having created a database, it becomes necessary to

set pointer to such database whenever it is to be used.
• To achieve this, the SQL syntax USE is employed.
• E.g.
• mysql>>USE database name;
• mysql>>USE mydb;
• This makes the specified database to be the most active. Upon
this, tables can now be created unto it and stored table can
also now be accessed.
Back to DDL…SHOW

• SHOW: Used to show/reveal/present the available

DATABASES and TABLES in a system. For instance
and assuming you have many databases in your
system:
• E.g.
• mysql>>SHOW databases;
• Will presents you all the available databases created
on your system.
Back to DDL…SHOW

• SHOW: Used to show/reveal/present

the available DATABASES and TABLES
in a system. For instance and assuming
you have many tables in your system:
• E.g.
• mysql>>SHOW tables;
• Will presents you all the available
tables created on your system under a
particular databases.
Back to DDL…DESCRIBE

• DESCRIBE: As the name sounds, this is

used to view the detail description of
your table e.g. fields and their data types.
• E.g.
• mysql>>DESCRIBE (table name);
• mysql>>DESCRIBE studtable;
• Will presents you all the details of the
specified tables in terms of its size, data
created, data types etc.
ILLUSTRATING DDL & DATA TYPES
• Creating
• Creating Tables: In creating tables in
MySQL, we use the syntax: CREATE
• e.g.
>create table (tablename)(,
>Field1 Field-data type (size),
>Field2 Field-data type (size),
>Last-Field3 Field-data type (size) //NO COMMA
>);
Example
• Personaldata File (for holding people’s data)
• To create this
>create table pfile(,
>names varchar(15),
>address varchar(25),
>id_no char(11),
>age int(2),
>sex enum(‘M’, ‘F’)
>);
DML
• Insert
• Inserting Values into created tables: To do this,
we used the syntax: INSERT…INTO…VALUES to
populate data unto a table/file. This we do
after table creation.
• e.g.
>insert into pfile(names, address, id_no, dob, sex)
values(‘Ade’, ‘22, Oroke’, ‘AAUA031213’,45,’F’);
This you can do repeatedly with your keystrokes
(up, down, left & right arrows as the case may be,
then you edit).
DML Cont’d
• Select Clause: This is used for viewing and
retrieving of stored data/files.
• The various types are:
• Select-All-From clause (the most used)
• Select-Specific-From clause
• Select-All-From-Where clause
• Select-Specific-From-Where clause
Examples I
• To view data stored into pfile,
>//use Select-All-From clause
e.g.
>Select *………. The * means All

Back to the code proper…….

>Select * From pfile;

//not case sensitive anyway

//any output there…….ok, QED I suppose
Examples II – what if I want to be specific on
the fields to view?
• To view such data stored into pfile (e.g. name and address
only)
>//use Select-Specific-From clause
e.g.

>Select name………be careful about d spelling to avoid

avoidable error(s)

How can I…….???

Simply describe the table to view and ascertain their correct
spelling.
e.g.

>describe pfile;
//something should be on your screen now….
Ok, back to the code now….

>select names, address from pfile;

QED ……right output? Yes!

…..try out more (e.g. names, age, address)

etc.
Another version of the filtration
My data no doubt consists of male and female.
Assuming I have the intention of reaching out to
male friends only, how would I?

Of course, the direct answer could have been:

filter names & their addresses from the pfile
table. But the clause is: ‘only male friends’.
Solution to the ‘puzzle’
The English-like solution statement is: ‘I want a
situation WHEREby I select only male friends’.

So, I got a clue (……SELECT-FROM-WHERE clause), let

me try out the codes:

>select names, address from pfile where sex=‘male’;

//any luck?

Try out more on your own…..

Other versions of the WHERE clause

Aside the WHERE-Assignment Clause (e.g.

Where age=…..), also more proficiently used
is the WHERE-LOGICAL Clause. This includes:
WHERE >, WHERE< etc.
RELATED PROBLEM
I want to know friends that are aging. WHY?
May be I just want to know their job status.
They should be nearing retirement now &
we should plan for that. Shouldn’t we?

So, if yes, how do I?

Very simple my friend:

>select * from pfile where age>=60;

END OF TODAY’S LECTURE
•Upper levels of the data integration problem

•Basic Steps in Query Processing

3.6 Query Processing

3.6.1 Upper levels of the data integration problem

• How to construct mappings from sources to a single mediated schema

• How queries posed over the mediated schema are reformulated over the sources

3.6.2 Basic Steps in Query Processing

1. Parsing and translation

2. Optimization

3. Evaluation
Parsing and translation

Translate the query into its internal form. This is then translated into relational algebra. Parser checks syntax, verifies
relations.

Evaluation

The query-execution engine takes a query-evaluation plan, executes that plan, and returns the answers
to the query.A relational algebra expression may have many equivalent expressions. Each relational
algebra operation can be evaluated using one of several different algorithms. Correspondingly, a
relational-algebra expression can be evaluated in many ways. Annotated expression specifying
detailed evaluation strategy is called an evaluation-plan.

Query Optimization

Amongst all equivalent evaluation plans choose the one with lowest cost. Cost is estimated using statistical
information from the database catalog
Basic Steps in Query Processing

1. Parsing and translation

2. Optimization
3. Evaluation
Basic Steps in Query Processing (cont.)

• Parser and translator

• translate the (SQL) query into relational algebra
• Parser checks syntax, verifies relations

• Evaluation engine
• The query-execution engine takes a query-evaluation plan, executes that plan, and returns the answers to
the query executes that plan, and returns the answers to the query

• Optimizer (in a nutshell -- more details in the next slides)

• Chooses the most efficient implementation to execute the query
• Produces equivalent relational algebra expressions
• Annotates them with instructions (algorithms)
3.7 SQL

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored
in relational database.

SQL is the standard language for Relation Database System. All relational database management systems like MySQL,
MS Access, and Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

3.7.1 Why SQL?

•  Allows users to access data in relational database management systems.

•  Allows users to describe the data.
•  Allows users to define the data in database and manipulate that data.
•  Allows to embed within other languages using SQL modules, libraries & pre-compilers.
•  Allows users to create and drop databases and tables.
•  Allows users to create view, stored procedure, functions in a database.
•  Allows users to set permissions on tables, procedures, and views
3.7.2 History

• 1970 -- Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational
model for databases.
• 1974 -- Structured Query Language appeared.
• 1978 -- IBM worked to develop Codd's ideas and released a product named System/R.
• 1986 -- IBM developed the first prototype of relational database and standardized by ANSI.

The first relational database was released by Relational Software and its later becoming Oracle

3.7.3 SQL Process

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your
request and SQL engine figures out how to interpret the task.

There are various components included in the process. These components are Query Dispatcher, Optimization Engines,
Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries but SQL query engine
won't handle logical files.
3.7.4 SQL Commands

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT,
UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature.

3.7.4.1 DDL - Data Definition Language

Command Description

• CREATE Creates a new table, a view of a table, or other object in database

• ALTER Modifies an existing database object, such as a table.

• DROP Deletes an entire table, a view of a table or other object in the database.
3.7.4.2 DML - Data Manipulation Language 3.7.4.3 DCL - Data Control Language
Command Description Command Description
• SELECT Retrieves certain records from one or • GRANT Gives a privilege to user
more tables
• REVOKE Takes back privileges granted from user
• INSERT Creates a record

• UPDATE Modifies records

• DELETE Deletes records