1 / 25

Databases 2013/2014

Databases 2013/2014. Introduction. The menu for today. Organisational aspects Introduction to database technology The relational model. About using laptops in classes. Organisation. Hoorcollege Donderdag a.s. in Went- blauw , eenmalig Werkcolleges (beginnen volgende week dinsdag)

faith-rutledge
Download Presentation

Databases 2013/2014

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Databases 2013/2014 Introduction

  2. The menu for today • Organisational aspects • Introduction to database technology • The relational model

  3. About using laptops in classes

  4. Organisation • Hoorcollege • Donderdag a.s. in Went-blauw, eenmalig • Werkcolleges (beginnen volgende week dinsdag) • Huiswerkopgaven (3x) • Practica • Opgave 1: casusbeschrijving, modelleren, schema-ontwerp • Opgave 2: vulling van de database, SQL queries • Voor elke opgave twee practicumsessies op tijden werkcollege

  5. Introduction to database tech. • What are databases? • Relational data model (short version) • Why should we look at databases? • Short history of databases • Some aspects of database technology • Query languages • Database applications: 4GL, constraints, reports • ER-modeling • Normalisation • Transaction processing

  6. What are databases? (1) • Example: library system • Books, readers, loans, reservations • Loaning books, returning books, searching, making reservations, subscribing readers Book Reader Loan

  7. What are databases? (2) • Manipulation of data using a query language • For example SQL • Integrated in a 4GL • Often client/server architecture • Application logic in the client Client Database server (DBMS) SELECT Title FROM Book WHERE Author = ‘Kasparov’ Database

  8. What are databases? (3) • Characteristics of a database environment • Stable structure of data • Large volumes (external memory, persistency) • Good performance • More than one user at a time • Reliability and integrity of data • Conceptual approach

  9. Why look at databases? • Databases are omnipresent • Database technology is directly applicable • Database technology is the backbone of most information systems • Studying database technology provides insight in general principles of computer science • Layered software architecture • Application of predicate logic • Mathematical modeling

  10. (Pre)History of databases (1) • Magnetic tapes were available in 1945 • searching in external memory • IBM introduced the RAMAC system in 1957 • hard disk • In 1961, Integrated Data Store was the first general DataBase Management Systeem (DBMS)

  11. (Pre)History of databases (2) • The Information Management System by IBM followed in the mid-sixties • Hierarchical data model • In the same era the Network Data model became popular (CODASYL standard) • During the seventies, the commercial use of databases was rapidly growing • Codd proposed the relational data model in 1970 • The basis of almost all modern DBMSes

  12. History of databases (3) • In 1976, Chen introduced the Entity-Relationship (ER) model • Conceptual modeling for databases • Simplified the design of databases • Query languages such as SEQUEL (SQL), QBE and QUEL were designed

  13. History of databases (4) • During the early eighties, the relational data model received widespread commercial attention • In 1983, more than 100 RDBMSes existed • DB2, ORACLE, SYBASE, INFORMIX, INGRES • DBASE, PARADOX, MS-ACCESS • SQL became a “standard” in 1986 • SQL92/SQL2, SQL3, SQL2003: ANSI standards

  14. History of databases (5) • The first 4GL languages appeared during the eighties • Object-oriented databases were introduced at the end of that decade • Focus shifted to extending features and better performance • Multimedia databases, web databases, parallel processing • Core database technology is now quite “stable” • Databases + …

  15. Query languages • From “how” to “what” • SQL is declarative Book.Title := ‘Fun-fishing’; FIND FIRST Book USING Title; WHILE DB-Status = 0 DO BEGIN FIND FIRST Loan WITHIN Book_Loan; WHILE DB-Status = 0 DO BEGIN FIND OWNER WITHIN Reader_Loan; GET Reader; PRINT(Reader.Name); FIND NEXT Loan WITHIN Book_Loan; END; FIND NEXT Book USING Title; END SELECT Name FROM Book, Loan, Reader WHERE Book.Title = ‘Fun-fishing’ AND Book.Bno = Loan.Bno AND Loan.Rno = Reader.Rno

  16. Database applications (1) PROCEDURE Loan; begin $today := call_system(‘current_date’); read($x); # read Rno if call(Rnocheck($x)) = 0 then begin message(“ticket invalid”); exit end; read($y); # read Bno while ($y <> EndOfLoan) do begin call(Register_loan($today, $x, $y)); read($y); end end {Loan} PROCEDURE Rnocheck($x); begin SELECT COUNT (*) FROM Reader WHERE Rno = $x; end {Rnocheck} PROCEDURE Register_loan ($today, $x, $y); begin INSERT INTO Loan VALUES ($y, $x, $today, NULL); end {Register_loan}

  17. Database applications (2) CONSTRAINT constr1 (SELECT COUNT (*) FROM Loan WHERE Return_date IS NULL GROUP BY Rno) <= 6 ON VIOLATION … CONSTRAINT constr3 (SELECT Bno FROM Loan) IS CONTAINED IN (SELECT Bno FROM Book) ON VIOLATION … CONSTRAINT constr2 (SELECT COUNT (*) FROM Loan WHERE Return_date IS NULL GROUP BY Bno) <= 1 ON VIOLATION …

  18. Database applications (3) SELECT Name, Address, … FROM Loan, Reader, Book WHERE Loan.Rno = Reader.Rno AND Loan_date < ‘01.12.2010’ AND Return_date IS NULL • Report writing @name @address Dear mr/mrs @name, On @loan_date you have borrowed the following book from our library: @title by @author. We kindly request you to return this book as soon as possible.

  19. Database applications (4) Menus Screens Reports  User interface Application programs Naive user SQL Data  SQL Database server (DBMS) Queries Updates Sophisticated user (DataBase Administrator, developer) File access Data Database

  20. ER modeling Loan date Return date Bno Rno (0, n) (0, m) Book Reader Loan Author Title Address Name Book(Bno, Author, Title) Reader(Rno, Name, Address) Loan(Bno, Rno, Loan_date, Return_date)

  21. Normalisation (1) • Why don’t we put everything in one table? • Manageability • Prevent redundancy and inconsistency • Adequate representation (without NULLs) • Normalisation • “Vertical” splitting of tables • Several distinct normal forms and algorithms

  22. Normalisation (2)

  23. Transaction processing (1) • Transactions are important in case of crashes and simultaneous use of the database by multiple users • Transactions transform multiple database operations into one single atomic operation (as seen from the outside) Read balance accno. 1234567 Read balance accno. 7654321 Withdraw € 50,- from 1234567 Deposit € 50,- on 7654321 Write balance accno. 1234567 Write balance accno. 7654321 CRASH!

  24. Transaction processing (2) • Concurrency problem • Solved by locking based techniques 1. Read balance accno. 1234567 2. Read balance accno. 1234567 1. Withdraw € 500,- from 1234567 2. Withdraw € 500,- from 1234567 1. Write balance accno. 1234567 2. Write balance accno. 1234567

  25. Why relational databases? • Software Engineering • High level data specification and manipulation • Philosophy with regard to system development • Start with rigorous design of tables • Stable; detailed inventarisation is possible • Development of operations is secondary • Difficult to analyse, rapid prototyping, continuous adaptation • Successful application of computer science • Set theory, predicate logic, optimisation, design theory

More Related