Transcript powerpoint document

Database Management systems
and Standardized Query
Language
The easy way to handle data
Before the Database Management Systems

The programmer had to handle all details of
data storage and retrieval





Low level programming
A new wheel invented all the time
All data in flat files with different format
Hard to maintain data integrity
Hard to handle simultaneous data access
What is a Database Management System
(DBMS)?





A system for storing data in a standardized
manner
A system for retrieving data easily
A system for protecting data against failure
and unauthorized access
A tool for simplifying system development
Relieves the programmer of physical data
storage
Different kind of DBMS





Hierarchical DBMS, 60s
Relation DBMS, RDBMS, 70s
Object DBMS, 80s
Object-relational DBMS, 90s
Most databases today are RDBMS or Objectrelational DBMS
Relational Database Systems






Started in the 70s at IBM by Cod
Several implementations by companies like
Oracle, Sybase, Upright and Microsoft
Highly optimized systems
Proven and mature technology
Several international standards
Most systems today uses a RDBMS
The Relational Model

All data is stored in tables with rows and columns
pnr*
name
surname
sex
1
Fredrik
Ålund
Male
2
Eva
Larsson
Female
The Relational Model

Relations between tables and data
pnr* car
pnr*
name
1
1
Fredrik
2
Eva
Volvo
The Relational Model



Each column contains atomic data
Views is an alternative view of a table
Normalization is used to make the data
model as flexible as possible



No column should depend on any other column in
the row
No redundant data
Several levels of normalization and the third
normal form is the most used
Query Languages





No standardized languages in the beginning
One query in Oracle would not work in Mimer
Hard for the developer to know many
languages
No portability
Locked into one vendor
Standardized Query Language, SQL






SQL is a ISO standard supported by basically
all vendors, more or less
SQL 92, SQL 99 and the new SQL 200x
SQL is used to create the data model
SQL is used to query the database
SQL is used to perform updates
Powerful language created to manipulate
data
SQL Basics

Tables can be created with the create command



C REATE TABLE PERSON(pnr int, namn char(10),
surname char(10), sex char(6))
CREATE TABLE PERSON_CARS(pnr int, car char(7))
Primary keys are defined in the create statement

C REATE TABLE PERSON(pnr int, namn char(10),
surname char(10), sex char(6), primary key(pnr))
SQL Basics

Foreign keys can also be defined in the
create statement

CREATE TABLE PERSON_CARS(pnr int, car
char(7), foreign key(pnr) references
PERSON(pnr) on delete cascade)
SQL Basics

A column can have restrictions and default
values

CREATE TABLE PERSON(pnr int, name char(10)
default ‘Unknown’, surname char(10), sex char(6)
not null, primary key(pnr))
SQL Basics

A table can be altered after has been created


ALTER TABLE PERSON_CARS ADD
CONSTRAINT person_car_pk PRIMARY
KEY(pnr, car)
ALTER TABLE PERSON ADD COLUMN AGE INT
SQL Basics

Data is retrieved with the SELECT statement




SELECT * FROM PERSON
SELECT PNR, NAME FROM PERSON
SELECT * FROM PERSON WHERE AGE > 25
AND SEX=‘Male’
Tables are joined in the SELECT statement

SELECT PERSON.NAME, PERSON_CARS.CAR
FROM PERSON, PERSON_CARS WHERE
PERSON.PNR = PERSON_CARS.PNR
SQL Basics

Joins can also be performed with the JOIN
condition


SELECT PERSON.NAME, PERSON_CARS.CAR
FROM PERSON LEFT OUTER JOIN
PERSON_CARS ON
PERSON.PNR=PERSON_CARS.PNR
Gives all person and their car. If they don’t have a
car, null is returned in that columns instead. In our
case, Fredrik, Volvo and Eva, null
SQL Basics

SELECT PERSON.NAME,
PERSON_CARS.CAR FROM PERSON
RIGHT OUTER JOIN PERSON_CARS ON
PERSON.PNR=PERSON_CARS.PNR


Gives all person and their car only if they have a
car In our case, Fredrik, Volvo
SELECT PERSON.NAME,
PERSON_CARS.CAR FROM PERSON
NATURAL JOIN PERSON_CARS

The same result as above
SQL Basics

Data is inserted with the INSERT statement


INSERT INTO PERSON(pnr, name, surname,sex,
age) VALUES(3, ‘Eva’, ‘Larsson’, ‘Female’, ’27’)
INSERT INTO PERSON_CARS(pnr, car)
VALUES(3,’Toyota’)
SQL Basics

Data can be update with the UPDATE
statements

UPDATE PERSON SET AGE=22 WHERE PNR=1


Update Fredriks age to 22
UPDATE PERSON_CAR SET CAR=‘Volvo’

Updates all cars to Volvo
SQL Basics

Data is deleted with the DELETE statement

DELETE FROM PERSON WHERE ID=3

Deletes the row with Eva Larsson
SQL Basics

Views are created with a combination of a
CREATE and a SELECT

CREATE VIEW VOLVO_OWNERS(pnr, name,
surname, sex, age) as SELECT p.pnr, name,
surname, sex, age FROM PERSON p,
PERSON_CARS pc WHERE pc.pnr=p.pnr AND
pc.cars=‘Volvo’


Only show Volvo users
SELECT * FROM VOLVO_OWNERS
Advanced SQL

Stored Procedures


Triggers


A precompiled query in the database. Entire
systems can be built with Stored Procedures.
Certain events can trigger actions, for example a
stored procedure might be started when a row is
deleted
Both Stored Procedures and Triggers are part
of SQL 99
Transactions

Transactions is the way that the RDBMS
keeps the data consistent

A transaction is supposed to have the ACID
property




Atomic
Consistent
Isolated
Durable
Transactions

The classic example is the cash machine


If the cash machine gives out the money, but the
reduce balance doesn’t finnish, we have too much
mony
If the balance is reduced but we don’t get any
money we have too little
Check balance
Cash
machine
Okej, give the money
Reduce balance
Bank
Transactions in SQL


A transaction is started with START
A transaction is commited with COMMIT


If ok, everything is secured and well
A transaction is rolled back (undone) with
ROLLBACK

All operations are undone