Database Management systems and Standardized Query Language The

Database Management systems and Standardized Query Language The easy way to handle data

Before the Database Management Systems n The programmer had to handle all details of data storage and retrieval q q q 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)? n n n 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 n n n Hierarchical DBMS, 60 s Relation DBMS, RDBMS, 70 s Object DBMS, 80 s Object-relational DBMS, 90 s Most databases today are RDBMS or Objectrelational DBMS

Relational Database Systems n n n Started in the 70 s 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 n 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 n Relations between tables and data pnr* car pnr* name 1 1 Fredrik 2 Eva Volvo

The Relational Model n n n 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 q q q 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 n n n 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 n n n SQL is a ISO standard supported by basically all vendors, more or less SQL 92, SQL 99 and the new SQL 200 x 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 n Tables can be created with the create command q q n 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 q C REATE TABLE PERSON(pnr int, namn char(10), surname char(10), sex char(6), primary key(pnr))

SQL Basics n Foreign keys can also be defined in the create statement q CREATE TABLE PERSON_CARS(pnr int, car char(7), foreign key(pnr) references PERSON(pnr) on delete cascade)

SQL Basics n A column can have restrictions and default values q CREATE TABLE PERSON(pnr int, name char(10) default ‘Unknown’, surname char(10), sex char(6) not null, primary key(pnr))

SQL Basics n A table can be altered after has been created q q ALTER TABLE PERSON_CARS ADD CONSTRAINT person_car_pk PRIMARY KEY(pnr, car) ALTER TABLE PERSON ADD COLUMN AGE INT

SQL Basics n Data is retrieved with the SELECT statement q q q n SELECT * FROM PERSON SELECT PNR, NAME FROM PERSON SELECT * FROM PERSON WHERE AGE > 25 AND SEX=‘Male’ Tables are joined in the SELECT statement q SELECT PERSON. NAME, PERSON_CARS. CAR FROM PERSON, PERSON_CARS WHERE PERSON. PNR = PERSON_CARS. PNR

SQL Basics n Joins can also be performed with the JOIN condition q q 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 n SELECT PERSON. NAME, PERSON_CARS. CAR FROM PERSON RIGHT OUTER JOIN PERSON_CARS ON PERSON. PNR=PERSON_CARS. PNR q n 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 q The same result as above

SQL Basics n Data is inserted with the INSERT statement q q 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 n Data can be update with the UPDATE statements q UPDATE PERSON SET AGE=22 WHERE PNR=1 n q Update Fredriks age to 22 UPDATE PERSON_CAR SET CAR=‘Volvo’ n Updates all cars to Volvo

SQL Basics n Data is deleted with the DELETE statement q DELETE FROM PERSON WHERE ID=3 n Deletes the row with Eva Larsson

SQL Basics n Views are created with a combination of a CREATE and a SELECT q 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’ n n Only show Volvo users SELECT * FROM VOLVO_OWNERS

Advanced SQL n Stored Procedures q n Triggers q n 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 n Transactions is the way that the RDBMS keeps the data consistent q A transaction is supposed to have the ACID property n n Atomic Consistent Isolated Durable

Transactions n The classic example is the cash machine q q 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 n n A transaction is started with START A transaction is commited with COMMIT q n If ok, everything is secured and well A transaction is rolled back (undone) with ROLLBACK q All operations are undone