database management systems and standardized query language the easy way to handle data
Post on 18-Dec-2015
222 Views
Preview:
TRANSCRIPT
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 Object-
relational 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
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 C REATE 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 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
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
Okej, give the money
Reduce balance
Cash machine
Bank
top related