2. relational databases fundamental concepts

8/10/2019 2. Relational Databases Fundamental Concepts

1/56

Relational DatabasesFundamental Concepts

Svetlin NakovTelerik Corporation

www.telerik.com
http://www.telerik.com/http://www.telerik.com/


2/56

Table of Contents

1. Database Models

2. Relational Database Models

3. RDBMS Systems

4. Tables, Relationships,Multiplicity, E/R Diagrams

5. Normalization

6. Constraints

7. Indices

2


3/56

Table of Contents (2)

8. The SQL language

9. Stored Procedures

10. Views

11. Triggers

12. Transactions and Isolation Levels

3


4/56

RDBMS SystemsRelational Databases, Database Servers and RDBMS


5/56

Relational Databases

Database models

Hierarchical (tree)

Network / graph

Relational (table)

Object-oriented

Relational databases

Represent a bunch of tables together with therelationships between them

Rely on a strong mathematical foundation: the

relational algebra 5


6/56

Relational DatabaseManagement System (RDBMS)

Relational Database Management Systems(RDBMS) manage data stored in tables

RDBMS systems typically implement

Creating / altering / deleting tables and

relationships between them (database schema)

Adding, changing, deleting, searching and

retrieving of data stored in the tables

Support for SQL language

Transaction management (optional) 6


7/56

RDBMS Systems

RDBMS systems are also known as:

Database management servers

Or just database servers

Popular RDBMS servers:

Microsoft SQL Server

Oracle Database

IBM DB2

PostgreSQL

MySQL

Borland Interbase, SQLite, 7


8/56

Tables and RelationshipsDatabase Tables, Relationships, Multiplicity


9/56

Database tables consist of data, arranged in rowsand columns

For example (table Persons):

All rows have the same structure

Columns have name and type (number, string,

date, image, or other)

Id First Name Last Name Employer1 Svetlin Nakov Telerik

2 Stephen Forte Telerik

3 Steve Jobs Apple

Tables

9


10/56

Table Schema

The schema of a table is an ordered sequenceof column specifications (name and type)

For example the Personstable has thefollowing schema:

10

Persons (Id: number,FirstName: string,LastName: string,Employer: string

)


11/56

Primary Key

Primary key is a column of the table that uniquely

identifies its rows (usually its is a number)

Two records (rows) are different if and only if theirprimary keys are different

The primary key can be composed by several

columns (composite primary key)

Id First Name Last Name Employer

1 Svetlin Nakov Telerik

2 Stephen Forte Telerik

3 Steve Jobs Apple

11

Primary

key


12/56

Relationships

Relationships between tables are based oninterconnections: primary key / foreign key

12

Id Name CountryId

1 Sofia 1

2 Plovdiv 13 Munich 2

4 Berlin 2

5 Moscow 3

Id Name

1 Bulgaria2 Germany

3 Russia

Towns

Countries

Primary

keyPrimary

key

Foreign

key


13/56

Relationships (2)

The foreign key is an identifier of a record located

in another table (usually its primary key)

By using relationships we avoid repeating data inthe database

In the last example the name of the country is not

repeated for each town (its number is used instead)

Relationships have multiplicity:

One-to-many e.g. country / towns

Many-to-many e.g. student / course

One-to-one e.g. example human / student 13


14/56

Relationships' Multiplicity

Relationship one-to-many(or many-to-one)

A single record in the first table has many

corresponding records in the second table

Used very often

14

Id Name CountryId

1 Sofia 1

2 Plovdiv 13 Munich 2

4 Berlin 2

5 Moscow 3

Id Name

1 Bulgaria

2 Germany

3 Russia

Towns

Countries


15/56

Relationships' Multiplicity (2)

Relationship many-to-many

Records in the first table have many correspon-

ding records in the second one and vice versa

Implemented through additional table

15

Id Name

1 Pesho2 Minka

3 Gosho

4 Penka

Id Name

1 .NET

2 Java

3 PHP

StudentsCourses

StudentId CourseId

1 1

1 2

3 2

3 3

4 2

StudentsCourses


16/56

Id Title

1 Ph.D.

Relationships' Multiplicity (3)

Relationship one-to-one

A single record in a table corresponds to a single

record in the other table

Used to model inheritance between tables

16

Id Name Age

1 Ivan Daddy 72

2 Goiko Dude 26

3 Grand Mara 24

Persons

Id Specialty

2 Computer Science

3 Chemistry

Students

Primary & Foreignkey in the same time

Primarykey

Primary & foreignkey in the same time

Professors


17/56

RepresentingHierarchical Data

How do we represent trees and graphs?

17

Root

Documents Pictures

Birthday

Party


18/56

Self-Relationships

The primary / foreign key relationships canpoint to one and the same table

Example: employees in a company have a

manager, who is also an employee

18

Id Folder ParentId

1 Root (null)2 Documents 1

3 Pictures 1

4 Birthday Party 3

DirectoriesPrimary key Foreign keySelf-relationship


19/56

E/R DiagramsEntity / Relationship Diagrams

and DB Modeling Tools


20/56

Relational Schema

Relational schema of a DB is the collection of:

The schemas of all tables

Relationships between the tables

Any other database objects (e.g. constraints)

The relational schema describes the structureof the database

Doesn't contain data, but metadata

Relational schemas are graphically displayed in

Entity / Relationship diagrams (E/R Diagrams)

20


21/56

E/R Diagrams Examples

21

The diagram is created

with Microsoft SQL ServerManagement Studio


22/56

E/R Diagrams Examples (2)

22

The diagram

is created

with ERwin


23/56


23

The diagram is created

with fabFORCE DBDesigner for MySQL


24/56


24

The diagram is

created with MS Visio


25/56

Tools for E/R Design

Data modeling tools allow building E/Rdiagrams, generate / import DB schemas:

SQL Server Management Studio

Oracle JDeveloper

Microsoft Visio

CASE Studio

Computer Associates ERwin

IBM Rational Rose

theKompany Data Architect 25


26/56

NormalizationAvoiding Duplicated Data through

Database Schema Normalization


27/56

Normalization

Normalizationof the relational schema

removes repeating data

Non-normalized schemas can contain manydata repetitions, e.g.

27

Product Producer Price Category Shop Town

yoghurt Mlexis LTD 0.67 foodstore

"Mente"Sofia

bread"Dobrudja"

Bakery"Smoky" 0.85 food

store"Mente" Sofia

beer"Zagorka"

Zagorka CO 0.68 soft drinksstall "non-

stop"Varna

beer "Tuborg"Shumen

Drinks CO0.87 soft drinks

stall "non-stop"

Varna


28/56

Normalization (2)

1-st Normal Form

Data is stored in tables

Fields in the rows are atomic (inseparable)

values

There are no repetitions within a single row

A primary key is defined for each table

28

BookTitle ISBN (PK) Author AuthorEmail

.NET Framework 3847028437 Mr. Kiro [email protected]

Beginning SQL 7234534450 Santa [email protected]


29/56

Normalization (3)

2-nd Normal Form

Retains all requirements of 1-st Normal Form

There are no columns that depend on part of

the primary key (if it consists of several

columns)

29

BookTitle (PK) Author (PK) Price AuthorEmail

.NET Framework Mr. Kiro 37.25 [email protected]

Beginning SQL Santa Claus 19.95 [email protected]

The price dependson the book

E-mail dependson the author


30/56

Normalization (4)

3-rd Normal Form

Retains all requirements of 2-nd Normal Form

The only dependencies between columns are of

type "a column depends on the PK"

30

Id ProductProducer

IdPrice

CategoryId

ShopId

TownId

1 Yoghourt 2 0.67 2 4 12 bread "Tipov" 3 0.85 2 4 1

3 rakiya "Biserna" 6 6.38 5 2 1

4 beer "Tuborg" 4 0.87 4 1 3


31/56

Normalization (5)

4-th Normal Form

Retains all requirements of 3-rd Normal Form

There is one column at most in each table that

can have many possible values for a single key

(multi-valued attribute)

31

AuthorId Book Article

2 .NET Programming Regular Expressions in .NET

4 Mastering JavaScript AJAX Performance Patterns

One author canhave many books

One author canhave many articles


32/56

Normalization (6)

Example of fully normalized schema

(in 4thNormal Form):

32

Id Name

2 "Milk" Ltd.

4 "Zagorka" AD

Id Name

4 beer

2 food

Id Product ProducerId PriceCatego

ryIdShop

IdTown

Id

1 Youghurt 2 0.67 2 4 1

2 bread "Dobrudja" 3 0.55 2 4 1

3 rakia "Peshtera" 6 4.38 5 2 1

4 beer "Tuborg" 4 0.67 4 1 3

Id Name

1 Billa

4 METRO

Id Name

1 Sofia

3 Varna

Products

Producers Categories Shops Towns


33/56

Other Database ObjectsConstraints, Indices, SQL, Stored

Procedures, Views, Triggers


34/56

Integrity Constraints

Integrity constraints ensure data integrity inthe database tables

Enforce data rules which cannot be violated

Primary key constraint Ensures that the primary key of a table has

unique value for each table row

Unique key constraint Ensures that all values in a certain column (or a

group of columns) are unique

34


35/56

Integrity Constraints (2)

Foreign key constraint

Ensures that the value in given column is a key

from another table

Check constraint Ensures that values in a certain column meet

some predefined condition

Examples:

35

(hour >= 0) AND (hour < 24)

name = UPPER(name)


36/56

Indices

Indicesspeed up searching of values in acertain column or group of columns

Usually implemented as B-trees

Indices can be built-in the table (clustered) orstored externally

Adding and deleting records in indexed tables

is slower! Indices should be used for big tables only (e.g.

50000rows)

36


37/56

The SQL Language

SQL(Structured Query Language)

Standardized declarative language for

manipulation of relational databases

SQL-99is currently in use in most databases

SQL language supports:

Creating, altering, deleting tables and other

objects in the database

Searching, retrieving, inserting, modifying and

deleting table data (rows)37

h


38/56

The SQL Language (2)

SQL consists of:

DDLData Definition Language

CREATE, ALTER, DROPcommands

DMLData Manipulation Language SELECT, INSERT, UPDATE, DELETEcommands

Example of SQL SELECTquery:

38

SELECT Towns.Name, Countries.Name

FROM Towns, Countries

WHERE Towns.CountryId = Countries.Id

d d


39/56

Stored Procedures

Stored procedures (database-level procedures)

Consist of SQL-like code stored in the database

Code executed inside the database server

Much faster than an external code

Data is locally accessible

Can accept parameters

Can return results

Single value

Record sets39

S d d ( )


40/56

Stored Procedures (2)

Stored procedures are written in a language

extension of SQL

T-SQL in Microsoft SQL Server

PL/SQL in Oracle Example of stored procedure in Oracle PL/SQL:

40

CREATE OR REPLACE PROCEDURE

spInsertCountry(countryName varchar2) ISBEGININSERT INTO Countries(Name)VALUES(countryName);

END;

Vi


41/56

Views

Viewsare named SQL SELECTqueries whichare used as tables

Simplify data access

Facilitate writing of complex SQL queries Used also to apply security restrictions:

E.g. a certain user isn't given permissions on

any of the tables in the database The user is given permissions on few views

(subset of DB) and few stored procedures only

41

Vi E l


42/56

Views Example

42

Id Company TownId

1 Mente LTD 1

2 BulkSoft Inc. 2

3 HardSoft Corp. 44 Sputnik Corp. 3

Id Town CountryId

1 Sofia 1

2 New York 3

3 Moscow 24 Plovdiv 1

Companies Towns

Id Country1 Bulgaria

2 Russia

3 USA

Countries

Vi E l ( )


43/56

Views Example (2)

43

CREATE VIEW V_BGCompanies AS

SELECTCompanies.Id AS Id,Companies.Company AS Company

FROM Companies INNER JOIN(Towns INNER JOIN Countries ON

Towns.CountryId = Countries.Id)ON Companies.TownId = Towns.Id

WHERECountries.Country = "Bulgaria";

Id Company

1 Mente Ltd.

3 HardSoft Corp.

V_BGCompanies

T i


44/56

Triggers

Triggersare special stored procedures that are

activate when some event occurs, for instance:

When inserting a record

When changing a record When deleting a record

Triggers can perform additional data

processing of the affected rows, e.g. To change the newly added data

To maintain logs and history

44

T i E l


45/56

Triggers Example

We have a table holding company names:

A trigger that appends "Ltd." at the end of thename of a new company:

45

CREATE TABLE Companies(Id number NOT NULL,Name varchar(50) NOT NULL)

CREATE OR REPLACE TRIGGER trg_Companies_INSERTBEFORE INSERT ON Company

FOR EACH ROWBEGIN

:NEW.Name := :NEW.Name || ' Ltd.';END;


46/56

TransactionsACID Transactions and Isolation

T ti


47/56

Transactions

Transactionsare a sequence of databaseoperations which are executed as a single unit:

Either all of them execute successfully

Or none of them is executed at all

Example:

A bank transfer from one account into another

(withdrawal + deposit)

If either the withdrawal or the deposit fails the

entire operation should be cancelled

47

DB T ti Lif l


48/56

Rollback

Commit

DB Transactions Lifecycle

48

Read Write

WriteDurablestarting

state

Durable,consistent,

ending state

Sequence ofreads and

writes

T ti B h i


49/56

Transactions Behavior

Transactions guarantee the consistency and

the integrity of the database

All changes in a transaction are temporary

Changes become final when COMMITissuccessfully executed

At any time all changes done in the transaction

can be canceled by executing ROLLBACK All operations are executed as a single unit

Either all of them pass or none of them

49

T ti E l


50/56

Transactions: Examples

Withdraw $1001. Read savings acc.

2. New savings =

current -1003. Read checking acc.

4. New checking =

current +1005. Write the savings

6. Write the checking

1. Read the currentbalance

2. New balance =current -100

3. Write the newbalance

4. Dispense cash

Transfer $100

50

ACID Transactions


51/56

ACID Transactions

Atomicity

Either execute everything or nothing

Consistency

The database remains consistent with logically

correct data (e.g. no constraint is violated)

Isolation

Different transactions are isolated from each other

depending on the selected isolation level

Durability

If a transaction is confirmed, it cannot be lost51

Transactions Example


52/56

Transactions Example

We have a table with bank accounts:

We use a transaction to transfer money fromone account into another

52

CREATE TABLE Accounts(Id int NOT NULL,Balance decimal NOT NULL)

CREATE OR REPLACE PROCEDURE sp_Transfer_Funds(

fromAccount IN INT,toAccount IN INT,ammount IN NUMBER) IS

BEGIN

Transactions Example (2)


53/56

Transactions Example (2)

53

UPDATE Accounts set Balance = Balance - ammountWHERE id = fromAccount;

IF SQL % ROWCOUNT = 0 THENROLLBACK;RAISE_APPLICATION_ERROR(-20001, 'Invalid src account!');

END IF;

UPDATE Accounts set Balance = Balance + ammountWHERE id = to_account;

IF SQL % ROWCOUNT = 0 THENROLLBACK;RAISE_APPLICATION_ERROR(-20002, 'Invalid dst account!');

END IF;

COMMIT;END;

Transactions and Isolation


54/56

Transactions and Isolation

Transactions can define different isolation

levels that affect other parallel transactions

Stronger isolation ensures better consistencybut works slower and the data is locked longer

Level ofIsolation

DirtyReads

RepeatableReads

PhantomReads

Read Uncommitted Yes Yes Yes

Read Committed No Yes Yes

Repeatable Read No No Yes

Serializable No No No

54

Transactions Usage


55/56

Transactions Usage

When transactions should be used?

Always use transactions when a businessoperation modifies more than one table

Example:

At the cash-desk in a supermarket: we buy acart of products

We either buy all of them and pay for them orwe buy nothing and pay no money

If any of the operations fail we cancel the entiretransaction (the purchase)

55



56/56


Questions?