---
# System prepended metadata

title: Databases

---

# Databases


# Lecture 1

## DBMS - introduction - capabilities

![](https://i.imgur.com/DWuX9kU.png)

## Functionalities

![](https://i.imgur.com/IrIAuR6.png)


## DBMS VS File system

![](https://i.imgur.com/zChiwjx.png)


## Early / Old DBMS

![](https://i.imgur.com/uy7ciI9.png)


## Relational Databases

![](https://i.imgur.com/KqxKCvp.png)


## Relational Databases - Querying DB

![](https://i.imgur.com/eBlFQJa.png)



## DB in real world - Relational Databases - examples


![](https://i.imgur.com/MkDaJr2.png)


## Database Industry and Usage

NOSQL is better for those application that data changes very frequent

![](https://i.imgur.com/hHarEQt.png)

## Evolution

![](https://i.imgur.com/dBeOBbI.png)

DB system vs other system
![](https://i.imgur.com/uh6TCkq.png)


## Client-server and Multi-tier architectures

![](https://i.imgur.com/cm627NB.png)


## Information Integration


![](https://i.imgur.com/Wa9es8x.png)

## Overview of DBMS

![](https://i.imgur.com/laDdxjK.png)


## DDL Storage and Buffer

![](https://i.imgur.com/NHbiVIO.png)


## DDL Storage and Buffer

Metadata: it is additional data about the stored data

![](https://i.imgur.com/QZqCVaw.png)

## Query Processing

![](https://i.imgur.com/Nioil0A.png)


## Execution Engine

![](https://i.imgur.com/PulF6Q1.png)


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

![](https://i.imgur.com/Sy9OVzd.png)



# Lecture 2

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

![](https://i.imgur.com/UqVQT1I.png)

## Elements of the E/R Model
![](https://i.imgur.com/EMZPph7.png)



## Entity Sets

![](https://i.imgur.com/b9u3vti.png)

## E/R Diagrams

![](https://i.imgur.com/Z7UNXrS.png)

## Relationships

![](https://i.imgur.com/Tdt4lux.png)


![](https://i.imgur.com/ltLNLX0.png)

## Relationship Set

Relationships are also tables that hold references for both parts

![](https://i.imgur.com/dy0JbvD.png)

## Multiplicity - Many-Many Relationships


![](https://i.imgur.com/zInjPMB.png)


## Many-One Relationships

![](https://i.imgur.com/5vRdjMA.png)


## One-One Relationships

![](https://i.imgur.com/dF2AhrQ.png)


## Representing Multiplicity

Drinker can have only one favorite

![](https://i.imgur.com/b0MkUX5.png)


### Referential integrity constraint

1-1 just put arrow on both sides
we have beers that not bestsellers for any manfs
![](https://i.imgur.com/WPQBWMK.png)



![](https://i.imgur.com/JYwdUeZ.png)


## Multiway Relationships

![](https://i.imgur.com/z3pxOR7.png)


![](https://i.imgur.com/Gb4Uhcp.png)


## Converting Multiway Relationships to Binary

![](https://i.imgur.com/h7fzBNw.png)


![](https://i.imgur.com/soPXn4e.png)


## Attributes on Relationships

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

![](https://i.imgur.com/cVMqUqI.png)

Do this way only if you have more information about price
it is doing more complexity
![](https://i.imgur.com/q8FiQ5K.png)

## Roles in Relationships

![](https://i.imgur.com/iCZG9Ut.png)

![](https://i.imgur.com/KwbwWLT.png)


## Subclasses in E/R Diagrams

![](https://i.imgur.com/n5X4tt8.png)


![](https://i.imgur.com/00EaLji.png)




![](https://i.imgur.com/V387GVI.png)


Represented in Both
IN OOP you get it only on the subclass
![](https://i.imgur.com/wiYl0qZ.png)



## Keys & Constraints


![](https://i.imgur.com/PqP78cf.png)

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

![](https://i.imgur.com/d5TvXlk.png)


![](https://i.imgur.com/RQYGBHy.png)

## Constraints

![](https://i.imgur.com/mimwPmG.png)


## Weak Entity Sets

![](https://i.imgur.com/uQ46006.png)

#### Example

![](https://i.imgur.com/x0f2tgw.png)


![](https://i.imgur.com/KbiQlhG.png)

![](https://i.imgur.com/ueY5eFp.png)



## Design Techniques

![](https://i.imgur.com/LIzhj7g.png)


## Avoiding Redundancy

![](https://i.imgur.com/yBewAWi.png)

![](https://i.imgur.com/TNFk9fC.png)


![](https://i.imgur.com/9Uatje6.png)


![](https://i.imgur.com/NAwO6X8.png)


## Entity Sets Versus Attributes

![](https://i.imgur.com/NFmuBPq.png)


![](https://i.imgur.com/0tNG4Lg.png)


![](https://i.imgur.com/l3tg4DW.png)


![](https://i.imgur.com/3UKeuAl.png)


## Don't Overuse Weak Entity Sets

![](https://i.imgur.com/Evw6lOs.png)

![](https://i.imgur.com/JQhjsNt.png)

# Lecture 3

## Relational Model

![](https://i.imgur.com/AMa9eVD.png)

### Basics

![](https://i.imgur.com/aWL0al7.png)

## Schema - Relation/Database Schema

![](https://i.imgur.com/zQGhFtn.png)

## Tuples

![](https://i.imgur.com/RgUMxPC.png)

## Relation Instances

![](https://i.imgur.com/XIw1UXb.png)


## From E/R Diagram to Relations

![](https://i.imgur.com/pUpeJVr.png)

## Entity Set to Relation

![](https://i.imgur.com/3WQs406.png)

## Relationships to Relation
![](https://i.imgur.com/rh5ojVg.png)

## Combining Relations

![](https://i.imgur.com/odpnnIq.png)

### Many-one

![](https://i.imgur.com/y1RYDw1.png)

![](https://i.imgur.com/pKYrQpq.png)

### Advantage - Disadvantage

![](https://i.imgur.com/ssKZeKm.png)

if we have many studios instead of one
![](https://i.imgur.com/GRxjcgC.png)

## Handling Weak Entity Sets

![](https://i.imgur.com/ulYZ76Q.png)


![](https://i.imgur.com/Uv1rPQ9.png)

![](https://i.imgur.com/ZpzmVnK.png)

## Subclasses to Relations

![](https://i.imgur.com/tupqkxP.png)

![](https://i.imgur.com/x8D5GGb.png)

### Subclasses  - Object-oriented style

if we have a movie that is both cartoon and murdermysteries we have to have that film in all tables
![](https://i.imgur.com/7oHmN9w.png)

in object oriented style We have that movie in CartonMurderMysteries table 
![](https://i.imgur.com/LKTg5Qc.png)


![](https://i.imgur.com/avWtvpJ.png)

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

![](https://i.imgur.com/0TkF5Q1.png)


## Definition
**set attributes can be one or many**
personnumber have functional dependency on all
![](https://i.imgur.com/6lcjQb5.png)

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

![](https://i.imgur.com/6014MPz.png)


## Keys of Relations 

Keys functionally determine all other attributes (not only some)

![](https://i.imgur.com/vDHsxDU.png)

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
![](https://i.imgur.com/pMDq6uZ.png)


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
![](https://i.imgur.com/1NXsPSZ.png)

title year starName is the primary key

## Keys of Relations

![](https://i.imgur.com/7IuVY8l.png)


![](https://i.imgur.com/DKZqaDo.png)


![](https://i.imgur.com/fFPl8fx.png)

we don't underline the key from Studios in Owns

## Multiway relationships

we tend to convert them to binary

![](https://i.imgur.com/9u9wE8g.png)


## Rules about FDs - transitive rule

![](https://i.imgur.com/4Vi5onb.png)


![](https://i.imgur.com/3A3cwrA.png)

## Closure of Attributes

![](https://i.imgur.com/3Liv4aj.png)

![](https://i.imgur.com/flyhAgi.png)

## Proof of the Algorithm

![](https://i.imgur.com/iasopVK.png)

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

![](https://i.imgur.com/YdaBz4B.png)


![](https://i.imgur.com/cTeAaCx.png)

R2 = DCF

## Splitting/Combining Rule

![](https://i.imgur.com/uZb51FR.png)
example for splitting and combining
we can't split the left hand side
![](https://i.imgur.com/fYs6RxJ.png)

## 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
![](https://i.imgur.com/KzXJcJY.png)



![](https://i.imgur.com/PeV7VCB.png)

## Transitive Rule

![](https://i.imgur.com/OrlSYCM.png)

![](https://i.imgur.com/GyJGOEY.png)




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

![](https://i.imgur.com/BQ43snu.png)

## Projection of FDs

![](https://i.imgur.com/LpLBB1O.png)

## Desgin of Relational Schemas

![](https://i.imgur.com/J1Ak3BZ.png)


## Anomalies

![](https://i.imgur.com/83erLbR.png)



## 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
![](https://i.imgur.com/Rao0oxs.png)


![](https://i.imgur.com/JbCW6NP.png)

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

![](https://i.imgur.com/NF1rHW6.png)

![](https://i.imgur.com/9VqrCbo.png)


## Decomposition into BCNF

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

![](https://i.imgur.com/vWklWG2.png)

![](https://i.imgur.com/DJ05CKl.png)

![](https://i.imgur.com/tUiS7oI.png)


## Third Normal Form (3NF)

![](https://i.imgur.com/LMVKLgr.png)

![](https://i.imgur.com/rbzIrR4.png)

![](https://i.imgur.com/LEQIgC3.png)

city is prime
![](https://i.imgur.com/znrojPo.png)

## 3NF VS BCNF

![](https://i.imgur.com/DkIRWYs.png)

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

logically create new table stars

![](https://i.imgur.com/YXglQLQ.png)


![](https://i.imgur.com/RTK5Te9.png)

## Definition of MVD

![](https://i.imgur.com/liS5sRY.png)

![](https://i.imgur.com/ktAohoU.png)

![](https://i.imgur.com/FrUT29m.png)

![](https://i.imgur.com/uczIunY.png)


## Rules about MVDs

![](https://i.imgur.com/dHVlAVN.png)


![](https://i.imgur.com/BURjyFC.png)


## Fourth Normal Form (4NF)

![](https://i.imgur.com/ZbUzfqh.png)

![](https://i.imgur.com/QcYrTRZ.png)


![](https://i.imgur.com/RX10eDP.png)

## Decomposition into 4NF

![](https://i.imgur.com/CeJ0KEE.png)



# Lecture 5

## Algebra

![](https://i.imgur.com/Cb0mu42.png)

## Roadmap

![](https://i.imgur.com/ix3n3AS.png)


## Core Relational Algebra

![](https://i.imgur.com/0D2gIZD.png)

## Set Operations

![](https://i.imgur.com/oSu1Te4.png)


## Selection

![](https://i.imgur.com/usTAbs7.png)


## Projection


![](https://i.imgur.com/5bdQP6o.png)


## Cartesian Product

not really used in SQL

![](https://i.imgur.com/EDvTd4c.png)

![](https://i.imgur.com/OfpbJqr.png)


## Theta-Join

![](https://i.imgur.com/f1nGuT9.png)


![](https://i.imgur.com/CTUvQDU.png)


## Natural Join

closer to SQL

![](https://i.imgur.com/z6dZ1hv.png)

## Renaming

![](https://i.imgur.com/qTNkb3l.png)


## Complex Expressions

![](https://i.imgur.com/cYJEVsl.png)


## Sequence of Assignments

![](https://i.imgur.com/fSkGjPA.png)

### Precedence of Relational Operators

if you unsure use parenthesis

![](https://i.imgur.com/ugG0YbJ.png)


## Expression Trees

![](https://i.imgur.com/1DnAv9n.png)

![](https://i.imgur.com/5ZlVjdg.png)

## Schemas for Results

![](https://i.imgur.com/Ketdv9O.png)

![](https://i.imgur.com/kR9TCmj.png)


## Relational Algebra on Bags

every set is also a bag

![](https://i.imgur.com/BnV49Hj.png)

## Operations on Bags

you might have duplicate in porjection

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

![](https://i.imgur.com/XcRERcx.png)

![](https://i.imgur.com/xMHLUyG.png)

![](https://i.imgur.com/wbMdqrl.png)

### Sets vs Bags

![](https://i.imgur.com/1QNKMfJ.png)


## The Extended Algebra

![](https://i.imgur.com/lByhm86.png)



### Delta

distinct keyword in SQL
![](https://i.imgur.com/W4wewqC.png)

### Sorting

![](https://i.imgur.com/HMxHBru.png)

### The Extended Projection

![](https://i.imgur.com/HvDl9K0.png)

## Aggregation Operators

![](https://i.imgur.com/Pn6IrZJ.png)

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

![](https://i.imgur.com/AJSL8Wq.png)

we group on studioName attribute, so we group all tuples having studioName Fox and do the average on length for those.
![](https://i.imgur.com/6HtfI5y.png)

## Outerjoin
different from natural join

we deal with all dangling couples

![](https://i.imgur.com/teijS1U.png)

![](https://i.imgur.com/IW6hQxz.png)

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

those in green are dangled couples
![](https://i.imgur.com/Dd4MbCN.png)


## Left/Right Outerjoin

they match on 2

![](https://i.imgur.com/DN2xKzV.png)

![](https://i.imgur.com/xmdGPVM.png)

if we remove R
![](https://i.imgur.com/esQkpPW.png)




#### Extra, showing full outerjoin

![](https://i.imgur.com/7bNNnE9.png)


# Lecture 6

## DBMS

![](https://i.imgur.com/ZTLPVsg.png)

![](https://i.imgur.com/eYbpOhn.png)

## What is SQL

![](https://i.imgur.com/1sYrnhF.png)

## SQL continued

![](https://i.imgur.com/2869SMo.png)

## MySQL DBMS

![](https://i.imgur.com/2sXZ8p5.png)

## Downloading and Installing MySQL

![](https://i.imgur.com/qNfi9Ki.png)

## SQL Clients

![](https://i.imgur.com/0MNuycb.png)

## DDL and MySQL

![](https://i.imgur.com/6u7Xx2C.png)

## DB Example

![](https://i.imgur.com/u6klqyY.png)

## Creating a DB in Terminal

![](https://i.imgur.com/DMuwsim.png)

![](https://i.imgur.com/rVEVR4w.png)

![](https://i.imgur.com/DquW1dS.png)

## Creating tables

![](https://i.imgur.com/EQjepZr.png)

![](https://i.imgur.com/HkbSONt.png)


![](https://i.imgur.com/ZmN3hN5.png)
is good idea to specify the order in case someone change the schema later

![](https://i.imgur.com/6sQghsc.png)



## Updating Tables

![](https://i.imgur.com/Ou3A7K8.png)


![](https://i.imgur.com/MQFqOR4.png)

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

![](https://i.imgur.com/fD91Fkz.png)

## Foreign Keys

![](https://i.imgur.com/q2LQYrU.png)

## Adding Foreign Key

![](https://i.imgur.com/CLHfQYP.png)

## Changing Tables

![](https://i.imgur.com/lvzQBvb.png)


## Query Language

![](https://i.imgur.com/YGIINSS.png)

## fIRST TWO Clauses

![](https://i.imgur.com/EXTV4sJ.png)


![](https://i.imgur.com/aiZiXpg.png)


![](https://i.imgur.com/2bQ0eFZ.png)



![](https://i.imgur.com/Tp91qCE.png)



![](https://i.imgur.com/eWKxR6H.png)


## Where

![](https://i.imgur.com/XUWnR77.png)


![](https://i.imgur.com/oFyp6Ls.png)

![](https://i.imgur.com/gauHTGy.png)


## Comparison Operators

![](https://i.imgur.com/9QjftvU.png)

## Logical Operators

![](https://i.imgur.com/c4QNplD.png)

![](https://i.imgur.com/GBwlQtx.png)


![](https://i.imgur.com/nvlpsKE.png)

## Searching Patterns

![](https://i.imgur.com/Qmfb2Ws.png)

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

![](https://i.imgur.com/gR3am0b.png)

![](https://i.imgur.com/Dl5cQXX.png)


## Order


![](https://i.imgur.com/SxTHGo4.png)



![](https://i.imgur.com/JGCjgMV.png)


![](https://i.imgur.com/0oHKCuv.png)

## Performance in DB

![](https://i.imgur.com/apFwkcz.png)


# Lecture 7

## NEW DB Example

![](https://i.imgur.com/O7CBKg1.png)


![](https://i.imgur.com/xpDQTyP.png)


![](https://i.imgur.com/k4eAwrG.png)


![](https://i.imgur.com/mW6m40D.png)

![](https://i.imgur.com/8rU1xB1.png)


## Aggregate Functions

![](https://i.imgur.com/hh73lMM.png)


![](https://i.imgur.com/Jc7VVGR.png)


![](https://i.imgur.com/jZMq9Sc.png)


![](https://i.imgur.com/VmKyHNu.png)




![](https://i.imgur.com/OEOwcDK.png)



![](https://i.imgur.com/c3H9tE9.png)


### Both AVG and SUM
![](https://i.imgur.com/d8o5Crv.png)

## Multiple Tables

![](https://i.imgur.com/4ZH3CUc.png)

![](https://i.imgur.com/NkJ1xiM.png)


![](https://i.imgur.com/Ti5tOg3.png)


![](https://i.imgur.com/Rb2MhRI.png)


![](https://i.imgur.com/WEnfcAq.png)

![](https://i.imgur.com/CP8Q5gW.png)


## Join

![](https://i.imgur.com/3m8m7dV.png)

![](https://i.imgur.com/HYwkstY.png)

![](https://i.imgur.com/LSErQKb.png)
we don't need the last join

## Joins is Cartesian product

![](https://i.imgur.com/xAHEpsl.png)


## Different Joins

![](https://i.imgur.com/oHYFY0A.png)

![](https://i.imgur.com/2ZJPrFP.png)


![](https://i.imgur.com/B73P2MV.png)

## Sub-Queries (Nested Queries)

![](https://i.imgur.com/CQ09HV1.png)


![](https://i.imgur.com/n2k4M0I.png)


![](https://i.imgur.com/Nueg0C2.png)

![](https://i.imgur.com/zhSWHnU.png)

### Not In

![](https://i.imgur.com/zLccbzg.png)


## More Filtering on nested queries

![](https://i.imgur.com/9glecOJ.png)



![](https://i.imgur.com/3UfRLON.png)

weight is less than all dell computers


## Combining Results

![](https://i.imgur.com/Kk1IOUp.png)


![](https://i.imgur.com/wex4Xys.png)


![](https://i.imgur.com/7V8SjK1.png)


![](https://i.imgur.com/zyIW9F6.png)


## Virtual Tables

![](https://i.imgur.com/9FdZXu8.png)


## Views Purpose

![](https://i.imgur.com/SYeMpzM.png)


## Creating a View

![](https://i.imgur.com/m8ylM2B.png)


## Modify or Update View Structure

![](https://i.imgur.com/4JpZWW2.png)


## Updating the content of the view

![](https://i.imgur.com/yVKaHOZ.png)


## Uncategorized

![](https://i.imgur.com/Yk9ib0D.png)

### Distinct

![](https://i.imgur.com/j5AOrTy.png)


### Alias - AS

![](https://i.imgur.com/EEyxG8p.png)

![](https://i.imgur.com/v4fduQa.png)

## Concatenation

![](https://i.imgur.com/CeTqBQi.png)

![](https://i.imgur.com/iwspv7z.png)

## Limit

![](https://i.imgur.com/JpwMoli.png)


![](https://i.imgur.com/XNpdlkg.png)



## Groupings

![](https://i.imgur.com/8uJ5ZeT.png)

## HAVING

![](https://i.imgur.com/cx0hIIO.png)

![](https://i.imgur.com/KirKNa4.png)

## Summary

![](https://i.imgur.com/mAoZL4y.png)



# Lecture 8

## SQL PROGRAMMING

![](https://i.imgur.com/q5pRFZn.png)

## PSM Persistent Stored Modules

![](https://i.imgur.com/E4I5rHK.png)


## Basic Forms
![](https://i.imgur.com/IUbVhmt.png)


![](https://i.imgur.com/8kFIFi6.png)


![](https://i.imgur.com/VTSVo2J.png)

## Invoking Procedures

![](https://i.imgur.com/MOGfuWn.png)

## Return Statement

![](https://i.imgur.com/j1pWVPC.png)


## Group of Statements

![](https://i.imgur.com/1pUfFqr.png)

### Example
![](https://i.imgur.com/rm5tN6d.png)


## Loops
loop1 is label

![](https://i.imgur.com/MJgnX99.png)

S
![](https://i.imgur.com/DLpZZ4A.png)

## Queries

![](https://i.imgur.com/W8PMaX7.png)


## Cursors

![](https://i.imgur.com/3WtXYmx.png)


![](https://i.imgur.com/O9EkUUv.png)


![](https://i.imgur.com/0C0MTvp.png)


![](https://i.imgur.com/DbXZnBY.png)

## Embedded SQL

![](https://i.imgur.com/LCLRXhn.png)


![](https://i.imgur.com/0ncrrOu.png)


## Shared Variables

![](https://i.imgur.com/ynY13xK.png)

![](https://i.imgur.com/yP1cliL.png)

## Embedded Queries

![](https://i.imgur.com/g0WhDLP.png)

## Cursor Statements

![](https://i.imgur.com/zs22cxY.png)

## Dynamic SQL

![](https://i.imgur.com/d9L9QG9.png)

![](https://i.imgur.com/m7dMC6C.png)

### Example

![](https://i.imgur.com/4hyPTy4.png)

## Call-Level Interface (JDBC)

![](https://i.imgur.com/NVuqqZI.png)

![](https://i.imgur.com/nNsWJ6O.png)


## Introduction to JDBC

![](https://i.imgur.com/Y2PQOnL.png)

## Statments

![](https://i.imgur.com/tFSsFZ5.png)


### Example

![](https://i.imgur.com/FmULlX6.png)

## How to execute

![](https://i.imgur.com/vublceg.png)


![](https://i.imgur.com/XTC2tMT.png)



## Cursor Operations

![](https://i.imgur.com/baYK4us.png)


![](https://i.imgur.com/p9PKPjQ.png)


## Parameter Passing

![](https://i.imgur.com/kcLWQlp.png)

![](https://i.imgur.com/za9Hgo3.png)


# Lecture 9

# Indexes and Security


## About DB Security

![](https://i.imgur.com/Ghn4eif.png)


## General Considerations

![](https://i.imgur.com/J2JDG5X.png)

## DB and DB administrator DBA

![](https://i.imgur.com/BwgNuW7.png)


## Security Mechanisms

![](https://i.imgur.com/28Xl0jq.png)

## Giving rights

![](https://i.imgur.com/Lw9YkgC.png)

![](https://i.imgur.com/Y0pyW9Q.png)

## Specifying fine-grained rights

![](https://i.imgur.com/Q842Cc4.png)

## Example

![](https://i.imgur.com/u9eHBvD.png)

## Create users with permissions in MySql

![](https://i.imgur.com/76rqHNE.png)

## Rights to rights

![](https://i.imgur.com/5qqYXRy.png)

## Roles

![](https://i.imgur.com/wuYrx2U.png)

## SQL Injection

![](https://i.imgur.com/O8Yyity.png)

![](https://i.imgur.com/luP1Jct.png)

![](https://i.imgur.com/NpWIQOD.png)

## Prevention

![](https://i.imgur.com/L2u1XPT.png)


## Indexing


## Indexing and performance

![](https://i.imgur.com/nMTvtBk.png)




## Indexing


![](https://i.imgur.com/KwaiOBn.png)


## What is an index?

![](https://i.imgur.com/sUfQoMW.png)

## Example

![](https://i.imgur.com/gUc7B9O.png)

![](https://i.imgur.com/dCxJnZN.png)

![](https://i.imgur.com/WQ59Vfj.png)

![](https://i.imgur.com/mfc8gPz.png)

### Performance Gains

![](https://i.imgur.com/WqFrwOB.png)

## Cons of indexes

![](https://i.imgur.com/z2H9Ota.png)

## More to consider about indexing

![](https://i.imgur.com/sgUsd7j.png)

## Summary

![](https://i.imgur.com/2hYir6e.png)


# Lecture 10 - Transactions

## Intro to Transaction Processing

![](https://i.imgur.com/8k1h487.png)

## Interleaved Processing

C and D is parallell processing

![](https://i.imgur.com/m7Mt1dG.png)

Serial processing


![](https://i.imgur.com/nUBaesY.png)

## What is Transactions

![](https://i.imgur.com/C9ZpUt7.png)


## Database Items

![](https://i.imgur.com/9rfP90k.png)

## Read and Write Operations

![](https://i.imgur.com/Jb3OrfB.png)


![](https://i.imgur.com/gqpPh1g.png)

## DBMS Buffers

![](https://i.imgur.com/5IopkFM.png)

## Concurrency Control

![](https://i.imgur.com/uXDPgbj.png)

## Lost Update Problem 

![](https://i.imgur.com/pKX2zoE.png)


## Temporary Update Problem

![](https://i.imgur.com/AMrxUqW.png)

## Incorrect Summary Problem

![](https://i.imgur.com/eNJKqa7.png)

## Unrepeatable Read Problem

![](https://i.imgur.com/Mp4xl0r.png)


## Why Recovery is Needed

![](https://i.imgur.com/7NN3iZS.png)


![](https://i.imgur.com/J92vQVQ.png)

## Transaction and System Conecpts

![](https://i.imgur.com/513Amrq.png)

![](https://i.imgur.com/sEeSQC4.png)


## The System Log

![](https://i.imgur.com/7BkbX10.png)


## Commit Point of a Transaction

![](https://i.imgur.com/dQLVii0.png)

## Desirable Properties of Transactions

![](https://i.imgur.com/pC42sgi.png)

## Characterizing Schedules Based On Recoverability

![](https://i.imgur.com/m1ssiBQ.png)

![](https://i.imgur.com/gEjdbbV.png)


![](https://i.imgur.com/00yVRJJ.png)

![](https://i.imgur.com/jKTxTMs.png)



![](https://i.imgur.com/hbQsHjn.png)

## Serializable schedules


![](https://i.imgur.com/ojhRKnv.png)

C and D are the interleaved versions of A and B
![](https://i.imgur.com/D41esDr.png)

## Problem to Serial schedules

![](https://i.imgur.com/SyCbDTM.png)


![](https://i.imgur.com/8d1Sbt8.png)


## Conflict equivalence

![](https://i.imgur.com/99wjVLn.png)


![](https://i.imgur.com/hW7El0R.png)


A and D is equivalient 

## Serializable != serial

![](https://i.imgur.com/wNqlqpt.png)

## View Equivalence and View Serializability

![](https://i.imgur.com/eHESeVO.png)

## Transaction Support in SQL

![](https://i.imgur.com/dnTCdoR.png)


![](https://i.imgur.com/JhgneYd.png)


## Snapshot Isolation

![](https://i.imgur.com/vsbNz1r.png)


## Summary

![](https://i.imgur.com/KqudKGC.png)

failure recovery uses system logs to recover from failer