Advanced Sql

Front 1

Inner join

Back 1

Same as natural join, but repeats the join column.

Front 2

Outer join

Back 2

Avoids loss of information, adds tuples that don't match and uses null values

Front 3

Natural left outer join

Back 3

Keeps all tuples from the left.

Front 4

Left outer join

Back 4

Keeps all tuples from the left and repeats the join column. The common column on the right shows null value.

Front 5

Natural right outer join

Back 5

Keeps all tuples from the right.

Front 6

Right outer join

Back 6

Keeps all tuples from the right and repeats the join column, but the right common column shows value. The right one shows null.

Front 7

Natural full outer join.

Back 7

Shows non common tuples from both tables. Doesn't have null in any value of a common column.

Front 8

View

Back 8

A mechanism to hide certain data from the view of certain users.

Create view nameofview as <SELECT query>

Note that the data IS NOT duplicated in the view (it’s just a shorthand; when querying the view, the underneath query is performed)

Some Updates cannot be Translated.

Front 9

Materializing a view:

Back 9

create a physical table containing all the tuples in the result of the query defining the view

If relations used in the query are updated, the materialized view result becomes out of date

Front 10

Integrity Constraints

Back 10

Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency.

check, foreign key etc.

Front 11

Referential Integrity

Back 11

Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Front 12

Cascading Actions in Referential Integrity

Back 12

foreign key (dept_name) references department

on delete cascade

on update cascade,

If the dept_nameis deleted in department, then erase the course

Front 13

Index Creation

Back 13

Indices are data structures used to speed up access to records with specified values for index attributes

Front 14

Transactions

Back 14

A transactionis a unit of program execution that accesses and possibly updates various data items.

Two main issues to deal with:Failures of various kinds, such as hardware failures and system crashesConcurrent execution of multiple transactions

Front 15

Properties of a Transaction:

Back 15

Atomicity. Either all operations of the transaction are properly reflected in the database or none are.

Consistency.Execution of a transaction in isolation preserves the consistency of the database.

Isolation.Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions.

Durability. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.

Front 16

Atomicity

Back 16

Atomicity requirement

If the transaction fails in steps 4 or 5, money will be “lost” leading to an inconsistent database stateFailure could be due to software or hardwareThe system should ensure that updates of a partially executed transaction are not reflected in the databas

Front 17

Durability requirement

Back 17

once the user has been notified that the transaction has completed (i.e., the transfer of the 50€has taken place), the updates to the database by the transaction must persist even if there are software or hardware failures.

Front 18

Consistency requirement

Back 18

The sum of A and B is unchanged by the execution of the transactionA+B=100

In general, consistency requirements includeExplicitly specified integrity constraints such as primary keys and foreign keys (this is done automatically by the database)Implicit integrity constraints (application-dependent consistency = this is done by the programmer)e.g., we cannot borrow more money than our cash-in-hand

Process:A transaction, when startingto execute, must see a consistentdatabase.During transactionexecution the database may be temporarily inconsistent.When the transaction completessuccessfully the database must be consistent

Front 19

Isolation requirement

Back 19

Isolation requirement—if in steps 4or 5 another transaction T2is allowed to access the partially updated database, it will see an inconsistent database (the sum A + Bwill be less than it should be).

Isolation can be ensured:trivially by running transactions serially, that is, one after the other.In parallelbut resolving potential conflicts (Concurrency control)

Front 20

Transaction state

Back 20

Active–the initial state; the transaction stays in this state while it is executing

Partially committed–after the final statement has been executed (but not successful completed yet).

Failed--after the discovery that normal execution can no longer proceed.

Aborted–after the transaction has been rolled back and the database restored to its state prior to the start of the transaction.

Committed–after successful completion.

Front 21

Transaction state image

Back 21

Front 22

Transaction Definition in SQL

Back 22

In SQL, a transaction begins implicitly.A transaction in SQL ends by:COMMIT commits current transaction and begins a new one.ROLLBACK causes current transaction to abort.

Front 23

Problems of ER-Model and properly structuring tables

Back 23

Update AnomaliesInsert AnomaliesDelete Anomalies

This isavoidedbyDatabase-NormalisationEntity-Relationship-Model & properlystructuringtablesin a relational Database

Front 24

Examples of problems

Back 24

Update ofa rowcreatesan inconsistencywithrespecttothedependencies:

Not possible to store a student without a grade orcourse registration:

As soon as I delete „Tom Tom's“ registration, thestudent "disappears" completely:

Front 25

First normal form

Back 25

formif the domains of all attributes of R are atomic

Domain is atomicif its elements are considered to be indivisible units

Variable phone number having two phone numbers.

Front 26

Second normal form

Back 26

formif it is in 1NF and every non-key attribute is irreducibly dependent on the primary key

E.g. NOTin second formWorksIn=(IDInstructor, departmentName, nationality)suppose: IDInstructornationality

Front 27

Third normal form

Back 27

A relational schema R is in third normal formif it is in 2NF and every non-key attribute is directly and not transitively dependent on the primary key

E.g. NOTin third formInstructor=(IDInstructor, nationality, departmentName, building)departmentNamebuilding (building does not directly depend on IDInstructor)