Topics covered:
Database Models
Relational Database Model
DBMS & RDBMS Systems
Tables, Relationships, Multiplicity, E/R Diagrams
Normalization
Constraints
Indices
The SQL language
Stored Procedures
Views
Triggers
Transactions and Isolation Levels
NoSQL Databases
Video (in Bulgarian)
VIDEO
Presentation Content
Database models
Hierarchical (tree)
Network / graph
Relational (table)
Object-oriented
Relational databases
Represent a bunch of tables together with the relationships between them
Rely on a strong mathematical foundation: the relational algebra
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 the SQL language
Transaction management (optional)
RDBMS systems are also known as:
Database management servers
Or just database servers
Popular RDBMS servers:
Microsoft SQL Server
Oracle Database
MySQL
IBM DB2
PostgreSQL
SQLite
Database _tables _ consist of data, arranged in rows and 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
Employer
1
George
Ivanov
Google
2
Stephen
Forte
Microsoft
3
Steve
Jobs
Apple
The schema of a table is an ordered sequence of column specifications (name and type)
For example the Persons table has the following schema :
Persons (
Id: number,
FirstName: string,
LastName: string,
Employer: string
)
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 their primary keys are different
The primary key can be composed by several columns (composite primary key )
Id
First Name
Last Name
Employer
1
Bill
Gates
Microsoft
2
Steve
Souders
Google
3
Steve
Jobs
Apple
Relationships between tables are based on interconnections: primary key / foreign key
Id
Name
CountryId
1
Sofia
1
2
Plovdiv
1
3
Munich
2
4
Berlin
2
5
Moscow
3
Id
Name
1
Bulgaria
2
Germany
3
Russia
The _foreign key _ is an identifier of a record located in another table (usually its primary key)
By using relationships we avoid repeating data in the 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
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
Id
Name
CountryId
1
Sofia
1
2
Plovdiv
1
3
Munich
2
4
Berlin
2
5
Moscow
3
Id
Name
1
Bulgaria
2
Germany
3
Russia
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
StudentId
CourseId
1
1
1
2
3
2
3
3
4
2
Id
Name
1
Pesho
2
Minka
3
Gosho
4
Penka
Id
Name
1
.NET
2
Java
3
PHP
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
Id
Name
Age
1
Ivan Daddy
72
2
Goiko Dude
26
3
Grand Mara
24
Id
Specialty
2
Computer Science
3
Chemistry
The primary / foreign key relationships can point to one and the same table
Example: employees in a company have a manager, who is also an employee
Id
Folder
ParentId
1
Root
(null)
2
Documents
1
3
Pictures
1
4
Birthday Party
3
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 structure of the database
Doesn’t contain data, but metadata
Relational schemas are graphically displayed in Entity / Relationship diagrams (E/R Diagrams)
Data modeling tools allow building E/R diagrams, generate / import DB schemas:
SQL Server Management Studio
MySQL Workbench
Oracle JDeveloper
Microsoft Visio
CASE Studio
Computer Associates ERwin
IBM Rational Rose
Normalization of the relational schema removes repeating data
Non-normalized schemas can contain many data repetitions, e.g.
Product
Producer
Price
Category
Shop
Town
yoghurt
Mlexis Ltd.
0.67
food
store “Mente”
Sofia
bread “Dobrudja”
Bakery “Smoky”
0.85
food
store “Mente”
Sofia
beer “Zagorka”
Zagorka Corp.
0.68
soft drinks
stall “non-stop”
Varna
beer “Tuborg”
Shoumen Drinks Corp.
0.87
soft drinks
stall “non-stop”
Varna
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
BookTitle
ISBN (PK)
Author
AuthorEmail
.NET Framework
3847028437
Mr. Kiro
bai-kiro@abv.bg
Beginning SQL
7234534450
Santa Claus
dedo@mraz.org
2 -nd Normal Form
Retains all requirements of 1-st Normal Form
There are no columns that do not depend on part of the primary key (if it consists of several columns)
The price depends on the book E-mail depends on the author
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”
Id
Product
ProducerId
Price
CategoryId
ShopId
TownId
1
yoghourt
2
0.67
2
4
1
2
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
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)
One author can have many books One author can have many articles
AuthorId
Book
Article
2
.NET Programming
Regular Expressions in .NET
4
Mastering JavaScript
AJAX Performance Patterns
Example of fully normalized schema (in 4th Normal Form):
Id
Product
ProducerId
Price
CategoryId
ShopId
TownId
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
2
“Milk” Ltd.
4
“Zagorka” AD
_Integrity constraints _ ensure data integrity in the 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
_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:
(hour >= 0) AND (hour < 24) name = UPPER(name)
Indices speed up searching of values in a certain column or group of columns
Usually implemented as B-trees
Indices can be built-in the table ( clustered ) or stored externally ( non-clustered )
Adding and deleting records in indexed tables is slower!
Indices should be used for big tables only (e.g. 50 000 rows)
SQL (Structured Query Language)
SQL language supports:
Creating, altering, deleting tables and other objects in the database
Searching, retrieving, inserting, modifying and deleting table data (rows)
SQL consists of:
DDL – Data Definition Language
CREATE , ALTER , DROP commands
DML – Data Manipulation Language
SELECT , INSERT , UPDATE , DELETE commands
Example of SQL SELECT query:
SELECT Towns.Name, Countries.Name FROM Towns, Countries WHERE Towns.CountryId = Countries.Id
_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
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:
CREATE OR REPLACE PROCEDURE spInsertCountry(countryName varchar2) IS
BEGIN
INSERT INTO Countries(Name )
VALUES (countryName);END ;
Views are named SQL SELECT queries which are 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
Id
Company
TownId
1
Mente LTD
1
2
BulkSoft Inc.
2
3
HardSoft Corp.
4
4
Sputnik Corp.
3
Id
Town
CountryId
1
Sofia
1
2
New York
3
3
Moscow
2
4
Plovdiv
1
Id
Country
1
Bulgaria
2
Russia
3
USA
CREATE VIEW V_BGCompanies AS
SELECT
Companies.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
WHERE
Countries.Country = "Bulgaria" ;
Id
Company
1
Mente Ltd.
3
HardSoft Corp.
Triggers are 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
We have a table holding company names:
CREATE TABLE Companies(
Id number NOT NULL ,
Name varchar (50 ) NOT NULL )
A trigger that appends “Ltd.” at the end of the name of a new company:
CREATE OR REPLACE TRIGGER trg_Companies_INSERT
BEFORE INSERT ON Company
FOR EACH ROW
BEGIN
:NEW .Name := :NEW .Name || ' Ltd.' ;END ;
Transactions are a sequence of database operations 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
Transactions guarantee the consistency and the integrity of the database
All changes in a transaction are temporary
Changes become final when COMMIT is successfully 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
Document model
Set of documents, e.g. JSON strings
Key-value model
Hierarchical key-value
Hierarchy of key-value pairs
Wide-column model
Key-value model with schema
Object model
NoSQL (non-relational) databases
Use document-based model (non-relational)
Schema-free document storage
Still support CRUD operations(create, read, update, delete)
Still support indexing and querying
Still supports concurrency and transactions
Highly optimized for append / retrieve
Great performance and scalability
NoSQL == “No SQL” or “Not Only SQL”?
Relational databases
Data stored as table rows
Relationships between related rows
Single entity spans multiple tables
RDBMS systems are very mature, rock solid
NoSQL databases
Data stored as documents
Single entity (document) is a single record
Documents do not have a fixed structure
Redis
Ultra-fast in-memory data structures server
MongoDB
Mature and powerful JSON-document database
CouchDB
JSON-based document database with REST API
Cassandra
Distributed wide-column database
DB Ranking: http://db-engines.com/en/ranking
What database models do you know?
Which are the main functions performed by a Relational Database Management System (RDBMS)?
Define what is “table” in database terms.
Explain the difference between a primary and a foreign key.
Explain the different kinds of relationships between tables in relational databases.
When is a certain database schema normalized? What are the advantages of normalized databases?
What are database integrity constraints and when are they used?
Point out the pros and cons of using indexes in a database.
What’s the main purpose of the SQL language?
What are transactions used for? Give an example.
What is a NoSQL database?
Explain the classical non-relational data models.
Give few examples of NoSQL databases and their pros and cons.
