Informatik fur Okonomen IIFall 2010

Database Systems(Summary)

◮ Relational Model: schema, tuple, relation, ...

◮ Relational Algebra: σ, π,∪,×,−, ρ, and 1,←,∩, ...◮ SQL: query specification, duplicate, null, subquery, ...

◮ ER Model: entity, relationship, ...

◮ Exam

Inf4Oec10, SL02 1/12 M. Bohlen, ifi@uzh

Syllabus

◮ Relational model, relational algebra◮ The relational model, relational algebra, selection, projection,

Cartesian product, union, difference◮ Elmasri and Navathe, 4th ed: chapters 5 and 6

◮ SQL◮ Query expressions, query specifications, subqueries, duplicates, null,

data definition language (DDL), data manipulation language (DML)◮ Elmasri and Navathe, 4th ed: chapter 8

◮ Conceptual database design◮ The design process, the entity-relationship (ER) model,

entity-relationship to relational model mapping◮ Elmasri and Navathe, 4th ed: chapters 3 and 7

Inf4Oec10, SL02 2/12 M. Bohlen, ifi@uzh

Database Systems

◮ Data Models◮ data model = structures + operations + constraints

◮ Schemas and Instances◮ schema = intension; changes infrequently◮ relation instance = relation = extension; changes often

◮ Three-Schema Architecture◮ external, conceptual, and internal schema

◮ Data Independence◮ reorganize internal schema without changing conceptual schema

◮ History of Data Models◮ network, hierarchical, relational, object-oriented, object-relational

Inf4Oec10, SL02 3/12 M. Bohlen, ifi@uzh

SQL, Relational Algebra, ER Model

Comparison of concepts and terminology:

SQL Relational Algebra ER Model

table relation entity set, relation-ship set

column attribute attribute

row tuple entity, relationship

query relational algebraexpression

-

Inf4Oec10, SL02 4/12 M. Bohlen, ifi@uzh

The Relational Data Model

◮ A domain D is a set of atomic data values.◮ phone numbers, names, grades, birthdates, departments◮ each domain includes the special value null

◮ An attribute Ai describes the role of a domain in a relation schema.◮ PhoneNr, Age, DeptName

◮ A relation schema R(A1, ...,An) is made up of a relation name R and alist of attributes.

◮ employee(Name,Dept,Salary), department(DName,Manager ,Address)

◮ A tuple t is an ordered list of values t = (v1, ..., vn) with vi ∈ dom(Ai ).◮ t = (Tom, SE , 23K)

◮ A relation r ⊆ D1 × ...× Dn over schema R(A1, ...,An) is a set of n-arytuples.

◮ r = {(Tom, SE , 23K), (Lene,DB, 33K)} ⊆ Names × Departments × Integer

◮ A database DB is a set of relations.◮ DB = {r , s}◮ r = {(Tom, SE , 23K), (Lene,DB, 33K)}◮ s = {(SE ,Tom,Boston), (DB, Lena,Tucson)}

Inf4Oec10, SL02 5/12 M. Bohlen, ifi@uzh

Relational Algebra

◮ The Relational Model◮ attribute, domain, tuple, relation, database, schema

◮ Constraints, keys

◮ Basic Relational Algebra Operators◮ Selection σ: σA=B∧D>5(r)◮ Projection π: πA,C (r)◮ Union ∪: r ∪ s◮ Difference −: r − s◮ Cartesian product ×: r × s◮ Rename ρ: ρs(X ,Y ,U,V )(r)

◮ Additional Relational Algebra Operators◮ Join (theta, natural) 1◮ Assignment ←

Inf4Oec10, SL02 6/12 M. Bohlen, ifi@uzh

SQL/1

◮ Building block of SQL are query specifications and queryexpressions.

◮ Conceptual evaluation/semantics of query expressions:

1. Form the cross product of the tables in the from clause

2. Eliminate tuples that do not satisfy the where clause

3. Group the remaining tuples according to the group clause

4. Eliminate groups that do not satisfy the having clause

5. Evaluate the expressions in the select clause

6. With aggregation one result tuple is produced for each group

7. Eliminate duplicates if distinct is specified

8. Compute query expressions independently and apply set operations(union, except, intersect)

9. Sort all result tuples of order clause is specified

Inf4Oec10, SL02 7/12 M. Bohlen, ifi@uzh

SQL/2

◮ Subqueries: exists, not exists, in, any, some, all

◮ Duplicates: SQL is based on bags/multisets

◮ Null values: true, false, unknown

◮ Derived tables: instead of a table name we can write a queryexpression

◮ Modification statements: insert, delete, update

◮ Creating and dropping tables

Inf4Oec10, SL02 8/12 M. Bohlen, ifi@uzh

Conceptual Database Design (ER Model)

◮ Basic Entity-Relationship (ER) Model Concepts◮ Entities and attributes, entity types, entity sets◮ Relationships and relationship types◮ Weak entity types

◮ ER-to-Relational Mapping Algorithm◮ Step 1: Mapping of Regular Entity Types◮ Step 2: Mapping of Weak Entity Types◮ Step 3: Mapping of Binary 1:1 Relation Types◮ Step 4: Mapping of Binary 1:N Relationship Types◮ Step 5: Mapping of Binary M:N Relationship Types◮ Step 6: Mapping of Multivalued attributes◮ Step 7: Mapping of N-ary Relationship Types

Inf4Oec10, SL02 9/12 M. Bohlen, ifi@uzh

The Exam (Database Module)

◮ Slides, exercises, solutions are available at the web site

◮ The textbook is Fundamentals of Database Systems by Elmasri andNavathe, 4th edition.

◮ The exercises and examples are important

◮ What is important◮ Being precise about key concepts of relational database systems◮ Being able to apply your knowledge on relevant examples

◮ The final exam is written

◮ The exam is in German; answers can be in German or English

◮ No auxiliary material; use notations from lectures and exercises

◮ Prepare by solving examples as done in exercises

Inf4Oec10, SL02 10/12 M. Bohlen, ifi@uzh

Inf4Oec10, SL02 11/12 M. Bohlen, ifi@uzh

Thanks.

All the best!

Inf4Oec10, SL02 12/12 M. Bohlen, ifi@uzh