Database Management systems and Standardized Query Language

1
Database Management systems and Standardized
Query Language

• The easy way to handle data

2
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

3
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

4
Different kind of DBMS

• Hierarchical DBMS, 60s
• Relation DBMS, RDBMS, 70s
• Object DBMS, 80s
• Object-relational DBMS, 90s
• Most databases today are RDBMS or
  Object-relational DBMS

5
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

6
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
7
The Relational Model

• Relations between tables and data

pnr car
1 Volvo
pnr name
1 Fredrik
2 Eva
8
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

9
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

10
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

11
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))

12
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)

13
SQL Basics

• A column can have restrictions and default values
• C REATE TABLE PERSON(pnr int, name char(10)
  default Unknown, surname char(10), sex char(6)
  not null, primary key(pnr))

14
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

15
SQL Basics

• Data is retrieved with the SELECT statement
• SELECT FROM PERSON
• SELECT PNR, NAME FROM PERSON
• SELECT FROM PERSON WHERE AGE gt 25 AND
  SEXMale
• Tables are joined in the SELECT statement
• SELECT PERSON.NAME, PERSON_CARS.CAR FROM PERSON,
  PERSON_CARS WHERE PERSON.PNR PERSON_CARS.PNR

16
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.PNRPERSON_C
  ARS.PNR
• Gives all person and their car. If they dont
  have a car, null is returned in that columns
  instead. In our case, Fredrik, Volvo and Eva,
  null

17
SQL Basics

• SELECT PERSON.NAME, PERSON_CARS.CAR FROM PERSON
  RIGHT OUTER JOIN PERSON_CARS ON
  PERSON.PNRPERSON_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

18
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)

19
SQL Basics

• Data can be update with the UPDATE statements
• UPDATE PERSON SET AGE22 WHERE PNR1
• Update Fredriks age to 22
• UPDATE PERSON_CAR SET CARVolvo
• Updates all cars to Volvo

20
SQL Basics

• Data is deleted with the DELETE statement
• DELETE FROM PERSON WHERE ID3
• Deletes the row with Eva Larsson

21
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.pnrp.pnr
  AND pc.carsVolvo
• Only show Volvo users
• SELECT FROM VOLVO_OWNERS

22
Advanced SQL

• Stored Procedures
• A precompiled query in the database. Entire
  systems can be built with Stored Procedures.
• Triggers
• 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

23
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

24
Transactions

• The classic example is the cash machine
• If the cash machine gives out the money, but the
  reduce balance doesnt finnish, we have too much
  mony
• If the balance is reduced but we dont get any
  money we have too little

25
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