# Databases

# Lecture 1

## DBMS - introduction - capabilities


## Functionalities


## DBMS VS File system


## Early / Old DBMS


## Relational Databases


## Relational Databases - Querying DB


## DB in real world - Relational Databases - examples


## Database Industry and Usage

NOSQL is better for those application that data changes very frequent


## Evolution


DB system vs other system

## Client-server and Multi-tier architectures


## Information Integration


## Overview of DBMS


## DDL Storage and Buffer


## DDL Storage and Buffer

Metadata: it is additional data about the stored data


## Query Processing


## Execution Engine


## Transaction Processing - logging, concurrency control, deadlock, durability


# Lecture 2

## E/R Model (Entity-Relationship Model)


## Elements of the E/R Model

## Entity Sets


## E/R Diagrams


## Relationships



## Relationship Set

Relationships are also tables that hold references for both parts


## Multiplicity - Many-Many Relationships


## Many-One Relationships


## One-One Relationships


## Representing Multiplicity

Drinker can have only one favorite


### Referential integrity constraint

1-1 just put arrow on both sides
we have beers that not bestsellers for any manfs


## Multiway Relationships



## Converting Multiway Relationships to Binary



## Attributes on Relationships

You can't put price on beer because it has different prices in different bars


Do this way only if you have more information about price
it is doing more complexity

## Roles in Relationships



## Subclasses in E/R Diagrams




Represented in Both
IN OOP you get it only on the subclass

## Keys & Constraints


Only key for the superclass
Subclass get the key from Beers (name)



## Constraints


## Weak Entity Sets


#### Example




## Design Techniques


## Avoiding Redundancy





## Entity Sets Versus Attributes





## Don't Overuse Weak Entity Sets



# Lecture 3

## Relational Model


### Basics


## Schema - Relation/Database Schema


## Tuples


## Relation Instances


## From E/R Diagram to Relations


## Entity Set to Relation


## Relationships to Relation

## Combining Relations


### Many-one



### Advantage - Disadvantage


if we have many studios instead of one

## Handling Weak Entity Sets




## Subclasses to Relations



### Subclasses - Object-oriented style

if we have a movie that is both cartoon and murdermysteries we have to have that film in all tables

in object oriented style We have that movie in CartonMurderMysteries table


what films of 1990 were longer than 150min can easily answered with E/R style because it is just quering the main table "Movies" and if it is OO style we have to query all tables.

easier queries so E/R is fine but for minimize space and redundancy it is OO style but it will be harder to query

E/R has much repetition of data in subclasses

# Lecture 4

## Functional Dependencies


## Definition
**set attributes can be one or many**
personnumber have functional dependency on all

### Example

when we have title star wars and the year 1977 so we are sure that all have length 124

* title and year fuctionally determine the length
* title and year fuctionally determine the filmType
* title and year fuctionally determine the studioName

but not starName


## Keys of Relations

Keys functionally determine all other attributes (not only some)


the number 2 is not satisfied because in the personnumber and name we can find subset that fuctionally determine all other attributes and that is name

superkey kan be country capital temperature and date because superkey don't have to satisfy minimality
every key is superkey.

you can call all keys: superkey

in the example
title year and starName is KEY

## Primary vs Superkey

title year starName is the primary key

## Keys of Relations




we don't underline the key from Studios in Owns

## Multiway relationships

we tend to convert them to binary


## Rules about FDs - transitive rule



## Closure of Attributes



## Proof of the Algorithm


if the combination of CF can funcctionally determine all other attributes that means CF is key and superkey



R2 = DCF

## Splitting/Combining Rule

example for splitting and combining
we can't split the left hand side

## Trivial FDs - Trivial Dependency Rule

name lastname and DoB functional determine lastname is trival because lastname is already there
we can delete the lastname from the right side because it is trivial


## Transitive Rule



## Closing Sets of FDs - Types of FDs - Basis


## Projection of FDs


## Desgin of Relational Schemas


## Anomalies


## Decomposing Relations
stars_in instead of Movies2
repetition is fine when it is a key
we can decompose so when we have union of the two relations we will get back to the origional big relation


## Boyce-Codd Normal Form
but we can't determine starname



## Decomposition into BCNF

When we fount the it is not inot BCNF we need to split it into other tables




## Third Normal Form (3NF)




city is prime

## 3NF VS BCNF


## Multivalued Dependencies
it is difficult to determine using FD
street has multiple values

logically create new table stars



## Definition of MVD





## Rules about MVDs



## Fourth Normal Form (4NF)




## Decomposition into 4NF


# Lecture 5

## Algebra


## Roadmap


## Core Relational Algebra


## Set Operations


## Selection


## Projection


## Cartesian Product

not really used in SQL



## Theta-Join



## Natural Join

closer to SQL


## Renaming


## Complex Expressions


## Sequence of Assignments


### Precedence of Relational Operators

if you unsure use parenthesis


## Expression Trees



## Schemas for Results



## Relational Algebra on Bags

every set is also a bag


## Operations on Bags

you might have duplicate in porjection

here the order doesn't matter, not like relational Algebra




### Sets vs Bags


## The Extended Algebra


### Delta

distinct keyword in SQL

### Sorting


### The Extended Projection


## Aggregation Operators


## Grouping Operators
what is the average salary perposition in university
compute average on slaray
but do the grouping on employees on the university


we group on studioName attribute, so we group all tuples having studioName Fox and do the average on length for those.

## Outerjoin
different from natural join

we deal with all dangling couples



in the natural join those tuples with nulls will not be here

those in green are dangled couples

## Left/Right Outerjoin

they match on 2



if we remove R

#### Extra, showing full outerjoin


# Lecture 6

## DBMS



## What is SQL


## SQL continued


## MySQL DBMS


## Downloading and Installing MySQL


## SQL Clients


## DDL and MySQL


## DB Example


## Creating a DB in Terminal




## Creating tables



is good idea to specify the order in case someone change the schema later


## Updating Tables



## Delete Values
For columns you can set null for a whole column


## Foreign Keys


## Adding Foreign Key


## Changing Tables


## Query Language


## fIRST TWO Clauses






## Where




## Comparison Operators


## Logical Operators




## Searching Patterns


the f or v is replaced by _
% meaning that maybe be one s or two



## Order




## Performance in DB


# Lecture 7

## NEW DB Example






## Aggregate Functions







### Both AVG and SUM

## Multiple Tables







## Join



we don't need the last join

## Joins is Cartesian product


## Different Joins




## Sub-Queries (Nested Queries)





### Not In


## More Filtering on nested queries



weight is less than all dell computers

## Combining Results





## Virtual Tables


## Views Purpose


## Creating a View


## Modify or Update View Structure


## Updating the content of the view


## Uncategorized


### Distinct


### Alias - AS



## Concatenation



## Limit



## Groupings


## HAVING



## Summary


# Lecture 8

## SQL PROGRAMMING


## PSM Persistent Stored Modules


## Basic Forms



## Invoking Procedures


## Return Statement


## Group of Statements


### Example

## Loops
loop1 is label


S

## Queries


## Cursors





## Embedded SQL



## Shared Variables



## Embedded Queries


## Cursor Statements


## Dynamic SQL



### Example


## Call-Level Interface (JDBC)



## Introduction to JDBC


## Statments


### Example


## How to execute



## Cursor Operations



## Parameter Passing



# Lecture 9

# Indexes and Security

## About DB Security


## General Considerations


## DB and DB administrator DBA


## Security Mechanisms


## Giving rights



## Specifying fine-grained rights


## Example


## Create users with permissions in MySql


## Rights to rights


## Roles


## SQL Injection




## Prevention


## Indexing

## Indexing and performance


## Indexing


## What is an index?


## Example





### Performance Gains


## Cons of indexes


## More to consider about indexing


## Summary


# Lecture 10 - Transactions

## Intro to Transaction Processing


## Interleaved Processing

C and D is parallell processing


Serial processing


## What is Transactions


## Database Items


## Read and Write Operations



## DBMS Buffers


## Concurrency Control


## Lost Update Problem


## Temporary Update Problem


## Incorrect Summary Problem


## Unrepeatable Read Problem


## Why Recovery is Needed



## Transaction and System Conecpts



## The System Log


## Commit Point of a Transaction


## Desirable Properties of Transactions


## Characterizing Schedules Based On Recoverability






## Serializable schedules


C and D are the interleaved versions of A and B

## Problem to Serial schedules



## Conflict equivalence



A and D is equivalient

## Serializable != serial


## View Equivalence and View Serializability


## Transaction Support in SQL



## Snapshot Isolation


## Summary


failure recovery uses system logs to recover from failer