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

7. The Relational Model • Relations between tables and data

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

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.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

17. 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

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 AGE=22 WHERE PNR=1 • Update Fredriks age to 22 • UPDATE PERSON_CAR SET CAR=‘Volvo’ • Updates all cars to Volvo

20. SQL Basics • Data is deleted with the DELETE statement • DELETE FROM PERSON WHERE ID=3 • 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.pnr=p.pnr AND pc.cars=‘Volvo’ • 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. Check balance Okej, give the money Reduce balance Cash machine Bank 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

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