Databases and Database Management Systems

Databases and the Relational Data Model Gilles Falquet Centre universitaire d'informatique UniGE 1 G. Falquet - UNIGE

Content Databases and Database management systems The Relational Data Model Querying Relational Databases 2 G. Falquet - UNIGE

Database A database is a collection of related data symbolic representation of known facts properties of some entities (height, weight, name, color, ) relationships between entities (x contains y, a is a friend of b, a works for u, ) A database generally has an intended group of users some preconceived applications for these users database 3 G. Falquet - UNIGE

Database management system a piece of softwarethat manages the physical storage of persistent date (on hard disks, solid state disks, RAM memory, etc.) executes application or user requests to select and retrievedata updatedata satisfies data management requirements 4 G. Falquet - UNIGE

Database Management System (single machine) user application query response DBMS read write stored data 5 G. Falquet - UNIGE

Database Management System (database server) query user application response DBMS read write client machines stored data user application database server 6 G. Falquet - UNIGE

DBMS Requirements persistent storage of data provide a description of the stored data (schema) data access and update functions content-based data retrieval (selection criteria) multiple simultaneous access (concurrency) maintain data integrity reliability (crash recovery mechanisms) prevent unauthorized access to confidential data 7 G. Falquet - UNIGE

Can't we Use a File System? persistent storage of data provide a description of the stored data (schema) data access and update functions ~the fsdoesn't know the internal structure of a file content-based data retrieval (selection criteria) multiple simultaneous access (concurrency) maintain data integrity reliability (crash recovery mechanisms) prevent unauthorized access to confidential data (not within one file) (anything can be written into a file) 8 G. Falquet - UNIGE

Data Description and Files A 4 SoCal:hangarWall // polygon # 5 28.7 9.8 -43.7 28.7 0 -43.7 28.7 0 4 28.7 9.8 4 4 SoCal:hangarIn // polygon # 6 -28.6 9.8 -43.7 -28.6 0 -43.7 -28.6 0 4 -28.6 9.8 4 What is the meaning of these symbols? A 1 version 2 20328 KSBD L26 2 0.1 0.0 0.0 0.1 N199 Speed-Bird 2 20328 KAVX KAVX 0 0.1 0.0 0.0 0.0 N9492E Speed-Bird 2 20328 KSBD KSBD 0 0.1 0.0 0.0 0.0 N9492E Glasair II-S 2 20329 KSBD KSBD 1 0.1 0.0 0.0 0.0 N9492E Glasair II-S . . . We need an explicit data description G. Falquet - UNIGE 9

Data ZK567 GVA ZRH 43 106 KL1122 AMS CDG 50 77 KL232 AMS PPP 560 230 LX441 GVA NCE 35 101 10 G. Falquet - UNIGE

Data + Schema Flight From To Duration Passagers ZK567 GVA ZRH 43 106 KL1122 AMS CDG 50 77 KL232 AMS PPP 560 230 LX441 GVA NCE 35 101 11 G. Falquet - UNIGE

Single schema principle The schema is defined in one place, bound to the data user application user application user application schema data 12 G. Falquet - UNIGE

Data Model A conceptual tool to structure data A database schema is expressed with a data model A data model is based on a set of concepts hierarchical models relational model graph models key-value models nodes, descendant links, ... tables, rows, columns, ... nodes, links, ... keys, value sets, ... 13 G. Falquet - UNIGE

Relational Model of Data E. F. Codd(1970) Based on simple mathematical constructs: sets, n-aryrelations Has a simple intuitive interpretation: tables, rows, columns Well defined operations with a semantics based on a relational algebra or relational calculus Currently the most frequently used model 14 G. Falquet - UNIGE

Relational Database = Set of Tables table = set of rows and columns Wine Region Bordeaux Year 2010 Quality Excellent each column has a name and a type (character string, number, date, ...) Bourgogne 2010 Average Order Valais 2014 Excellent Order# Quantity Region Year Valais 2013 Average 1022 455 Bordeaux 2010 Bordeaux 2011 Good 1322 112 Bordeaux 2010 0998 14 Valais 2014 15 G. Falquet - UNIGE

Formal Model Domain:a set of values considered as atomic (similar to data type in programming) Wine(Region, Year, Quality) Relationschema:?(?1,?2, ,??) a relation (or table) name ? list of attributes (or column) names, (?1,?2, ,??) for each attribute ??a domain dom(??)(or type) Wine Region Year Quality : string : integer : {"poor", "average", "good", "excellent"} 16 G. Falquet - UNIGE

Formal Model: Relation (relation state) for a relation schema ?(?1,?2, ,??) a relation?on ?is a set of n-tuplesor rows each tuple ? = (?1,?2, ,??) ?must satisfy ?1 dom ?1,?2 dom ?2, ,?? dom(??) Notation: ?.??:= ?? 17 G. Falquet - UNIGE

A Relation in Tabular Form Wine(Region, Year, Quality) Bordeaux 2010 Excellent t1 t2 t3 Bourgogne 2010 Average tuples/rows Valais 2014 Excellent t2.Region t3.Year A relation is a set of tuples, there is no order ! 18 G. Falquet - UNIGE

SQL (Structured Query Language) A standard language for defining and managing relational structures (database schema) and contents (tuples) expressing query and update operations on databases 19 G. Falquet - UNIGE

In SQL There is a set of standard types for the domains create table Wine( Definition of a relation schema (and an empty relation) Region varchar, -- string Year integer, Quality varchar, AveragePrice real) create table T (attr1 dom1, attr2 dom2, ) Addition of a tuple insert into Wine(Region, Quality, Year) values ('Bourgogne", 'good', 2022) insert into T(attr1, attr2, attrk) values (v1, v2, , vk) 20 G. Falquet - UNIGE

Logical Interpretation of a Relation Each row represents a true fact about entities of the world / system Example r y q a row in the table Wine(Region, Year, Quality) represents the fact "Wines in region r have quality q for year y" 21 G. Falquet - UNIGE

III. Querying a Relational Database Extract desired information from a database Principle: Apply the basic operations of the relational algebra : selection, projection, join Can be expresedin SQL a declarative language equivalent to the relational algebra with a more "natural"/"user friendly" syntax 22 G. Falquet - UNIGE

Relational Algebra: Selection ? a relation with schema (?1,?2, ,??) ??????????? = {? ?|?????????(?) = ????} retain the tuples of r that satisfy a given condition the condition is a booleanexpression on the relation's attributes 23 G. Falquet - UNIGE

Exemple Region Bordeaux Year 2010 Quality Excellent Wine Bourgogne 2010 Average Valais 2014 Excellent Valais 2013 Average ?Quality= Excellent Wine Bordeaux 2011 Good Region Bordeaux Year 2010 Quality Excellent Valais 2014 Excellent 24 G. Falquet - UNIGE

SQL Selection Region Bordeaux Year 2010 Quality Excellent Wine Bourgogne 2010 Average select * from Wine where Quality = 'Excellent' Valais 2014 Excellent Valais 2013 Average Bordeaux 2011 Good Region Bordeaux Year 2010 Quality Excellent Valais 2014 Excellent 25 G. Falquet - UNIGE

Exemple Region Bordeaux Year 2010 Quality Excellent Wine Bourgogne 2010 Average Valais 2014 Excellent Valais 2013 Average Quality = Excellent and Year > 2010Wine Bordeaux 2011 Good select * from Wine where Quality = 'Excellent' and Year > 2010 R gion Valais Year 2014 Quality Excellent 26 G. Falquet - UNIGE

Projection ? a relation on (?1,?2, ,??) ? = {?1,?2, ,??} {?1,?2, ,??} ?? ? = {(?.?1,?.?2, ,?.??) | ? ? } Retain only the columns ?1,?2, ,?? of ?. 27 G. Falquet - UNIGE

Exemple Region Bordeaux Year 2010 Quality Excellent Bourgogne 2010 Average Valais 2014 Excellent RegionWine Valais 2013 Average Bordeaux 2011 Good Region Bordeaux Valais Bourgogne 28 G. Falquet - UNIGE

In SQL Region Bordeaux Year 2010 Quality Excellent Bourgogne 2010 Average select Region from Wine Valais 2014 Excellent Valais 2013 Average Bordeaux 2011 Good Region Bordeaux Valais Duplicates are not removed ! a multiset (bag), not a set Valais Bordeaux Reason: performance Bourgogne 29 G. Falquet - UNIGE

In SQL (2) Region Bordeaux Year 2010 Quality Excellent Bourgogne 2010 Average select distinct Region from Wine Valais 2014 Excellent Valais 2013 Average Bordeaux 2011 Good Region Bordeaux Valais Duplicates are removed Bourgogne 30 G. Falquet - UNIGE

Selection + Projection in SQL T conditionT select A1, A2, ..., Ak from T where Condition T' A1, A2, ..., AkT 31 G. Falquet - UNIGE

Selection + Projection in SQL Wine Quality='Good'Wine select Region, Year from Wine where Quality = 'Good' Region, YearT 32 G. Falquet - UNIGE

Equi Join ? ?=?? = {???????????????? ?|? ????? ?????.? = ?.?} B A t u Databases - Relational Data Model 33 G. Falquet - UNIGE

Enrolment Student CourseId math history sociology history Student 6 3 7 6 Date 2019-12-04 2021-12-17 2019-06-06 2020-08-22 StdNo 6 3 8 Name Jean Anne Sylvie ??????? ?????=???????????????? Student. StdNo 6 6 3 Student. Name Jean Jean Anne Enrolment. CourseId Enrolment. Student Enrolment. Date math history history 6 6 3 2019-12-04 2020-08-22 2021-12-17 34 G. Falquet - UNIGE

Enrolment Student CourseId math history sociology history Student 6 3 7 6 Date 2019-12-04 2021-12-17 2019-06-06 2020-08-22 StdNo 6 3 8 Name Jean Anne Sylvie SQL select * from Student, Enrolment where Student.StdNo = Enrolment.Student or select * from Student join Enrolment on Student.StdNo = Enrolment.Student 35 G. Falquet - UNIGE

Theta Join r A Bs = {t concatenated with u |t r and u s and t.A s.B} B A t u Databases - Relational Data Model is any comparison operator: =, <>, <=, >=, like, G. Falquet - UNIGE 36

Join +Selection + Projection in SQL T2 T1 T1.Ai = T2.Bj select T1.A1, ... from T1, T2, ... where Condition and T1.Ai = T2.Bj T' conditionT' T'' T1.A1, ...T'' 37 G. Falquet - UNIGE

Join +Selection + Projection in SQL Enrolment Student StdNo = Student select Student.Name, Enrolment.CourseId from Student, Enrolment where Enrolment.Date > 2020-01-01 and Student.StdNo, Enrolment.Student T' Date>2020-01-01T' T'' Student.Name, Enrolment.CourseIdT'' 38 G. Falquet - UNIGE

Exercises on the Enviro database location forestid iso3 Database Schema country forest id name sovereign subregion iso2 iso3 uncode geoname area pop co2ems name cccode year co2 id name type subRegion name region 39 G. Falquet - UNIGE

SQL selections List the information corresponding to countries in the subregion Caribbean List the countries that are not sovereign, i.e. those whose geographical name (geoName) is different from that of their sovereign (the "different from" operator is <>in SQL) List the CO2 emission data for the year 2019 for countries with CO2 emissions above 100 List the information corresponding to 'Mangrove' type forests List the countries whose geoNamecontains the word Republic. use geoNamelike '%Republic%'(% represents any string of characters) 40 G. Falquet - UNIGE

Projections List all regions What are the iso3 codes of countries with forests? Display the names of the countries and their population density a column of the result can be obtained by calculation, e.g. select pop/area as density ... 41 G. Falquet - UNIGE

Selection + Projection What are the sub-regions that make up the Asia + Pacific region? To which region does the Arctic sub-region belong? 42 G. Falquet - UNIGE

Selection, projection, join What are the name and geoNameof the countries in the West Asia region? Find the names of countries with Mangrove forests Find all the forest types of the country whose geoNameis Australia Display for each country the number of tonnes of CO2 emitted per capita for the year 2007 The co2ems.co2 column is in thousands of tonnes; use an arithmetic expression in the select 43 G. Falquet - UNIGE

Using the enviro database on the kr server Navigate to http://kr.unige.ch/phpmyadmin Connect with user = etu, password = Once connected, select the envirodatabase from the list on the left. Click the SQL tab to enter a SQL query To execute your query click (Go) (bottom right) You can modify an already executed query by clicking [Edit] or [Edit inline] 44 G. Falquet - UNIGE

Exercises on the Enviro database location forestid iso3 forest co2ems name ccode year eco2 country id name type id name sovereign subregion iso2 iso3 uncode geoname area pop subRegion name region Display the names of the countries and their population density select name, pop/area as density from country 45 G. Falquet - UNIGE

Exercises on the Enviro database location forestid iso3 forest co2ems name ccode year eco2 country id name type id name sovereign subregion iso2 iso3 uncode geoname area pop subRegion name region What are the sub-regions that make up the Asia + Pacific region? select name from subRegion where region = 'Asia + Pacific' 46 G. Falquet - UNIGE

Exercises on the Enviro database location forestid iso3 forest co2ems name ccode year eco2 country id name type id name sovereign subregion iso2 iso3 uncode geoname area pop subRegion name region What are the name and geoNameof the countries in the West Asia region? select country.name, country.geoname from country, subRegion where subRegion.region = 'West Asia' and country.subregion = subRegion.name 47 G. Falquet - UNIGE

Exercises on the Enviro database location forestid iso3 forest co2ems name ccode year eco2 country id name type id name sovereign subregion iso2 iso3 uncode geoname area pop subRegion name region Find all the forest types of the country whose geoNameis Australia select distinct forest.type from country, location, forest where country.name = 'Australia' and country.iso3 = location.iso3 and location.forestid = forest.id G. Falquet - UNIGE 48

Exercises on the Enviro database location forestid iso3 forest co2ems name cccode year co2 country id name type id name sovereign subregion iso2 iso3 uncode geoname area pop subRegion name region Display for each country the number of tons of CO2 emitted per capita for the year 2007 select distinct country.name, (co2ems.eco2/country.pop)*1000 as co2pc from country, co2ems where country.iso3 = co2ems.code and co2ems.year = 2007 49 G. Falquet - UNIGE