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)

Presentation Content

Relational Databases

  • 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 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 the SQL language
    • Transaction management (optional)

RDBMS Systems

  • 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, Relationships, Multiplicity

  • 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

Table Schema

  • 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

  • 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

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
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
  • 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 Title
1 Ph.D.
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

  • 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)

Tools for E/R Design

  • 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
Beginning SQL 7234534450 Santa Claus
  • 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
BookTitle (PK) Author (PK) Price AuthorEmail
.NET Framework Mr. Kiro 37.25
Beginning SQL Santa Claus 19.95
  • 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
Id Name
4 beer
2 food
Id Name
1 Billa
Id Name
1 Sofia
3 Varna

Integrity Constraints

  • _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)

The SQL Language

  • 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

  • _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 sets
  • 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
  INSERT INTO Countries(Name)


  • 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

Views – Example

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
    Companies.Id AS Id,
    Companies.Company AS Company
    (Towns INNER JOIN Countries ON
     Towns.CountryId = Countries.Id)
    ON Companies.TownId = Towns.Id
    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

Triggers – Example

We have a table holding company names:

  Id number NOT NULL,
  Name varchar(50) NOT NULL)

A trigger that appends “Ltd.” at the end of the name of a new company:

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


  • 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 Behavior

  • 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

Non-Relational Data Models

  • Document model
    • Set of documents, e.g. JSON strings
  • Key-value model
    • Set of key-value pairs
  • Hierarchical key-value
    • Hierarchy of key-value pairs
  • Wide-column model
    • Key-value model with schema
  • Object model
    • Set of OOP-style objects

What is NoSQL Database?

  • 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 vs. NoSQL Databases

  • 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

NoSQL Database Systems

  • 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:


  1. What database models do you know?
  2. Which are the main functions performed by a Relational Database Management System (RDBMS)?
  3. Define what is “table” in database terms.
  4. Explain the difference between a primary and a foreign key.
  5. Explain the different kinds of relationships between tables in relational databases.
  6. When is a certain database schema normalized? What are the advantages of normalized databases?
  7. What are database integrity constraints and when are they used?
  8. Point out the pros and cons of using indexes in a database.
  9. What’s the main purpose of the SQL language?
  10. What are transactions used for? Give an example.
  11. What is a NoSQL database?
  12. Explain the classical non-relational data models.
  13. Give few examples of NoSQL databases and their pros and cons.