DB Introduction
Telerik Academy Alpha
Databases
Table of contents
-
Databases Overview
-
Relational Database Management System
-
Relational Data Modelling
Databases Overview
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
-
RDBMS
-
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
-
Tables
-
Database tables consist of data, arranged in rows and columns
-
For example (table People):
-
Id | FirstName | LastName | Employer |
---|---|---|---|
1 | Steve | Jobs | Apple |
2 | Bill | Gates | Microsoft |
3 | Jeff | Bezos | Amazon |
-
All rows have the same structure
-
Columns have name and type (number, string, date, image, or other)
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
)
Table Primary Key
-
Primary key is a column of the table that uniquely identifies its rows (usually it is a number or GUID)
-
Two records (rows) are different if and only if their primary keys are different
-
The primary key can be composed of several columns
Id | Name | CountryName |
---|---|---|
1 | Sofia | Bulgaria |
2 | Plovdiv | Bulgaria |
3 | Munich | Germany |
4 | Berlin | Germany |
5 | Moscow | Russia |
Table Relationships
-
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 |
Id | Name | CountryName |
---|---|---|
1 | Sofia | Bulgaria |
2 | Plovdiv | Bulgaria |
3 | Munich | Germany |
4 | Berlin | Germany |
5 | Moscow | Russia |
-
By using relationships we avoid repeating data in the database
-
In this example, the name of the country is not repeated for each town (its unique number is used instead)
ContinentId |
---|
... |
... |
... |
Table Relationships
-
The foreign key is an identifier of a record located in another table (usually its primary key)
-
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 |
ContinentId |
---|
... |
... |
... |
Relationships' Multiplicity
-
Relationship many-to-many
-
Records in the first table have many corresponding records in the second one and vice versa
-
Implemented through additional table
-
Id | Name |
---|---|
1 | Pesho |
2 | Gosho |
3 | Penka |
Id | Name |
---|---|
1 | MVC |
2 | Android |
3 | FE |
StudentId | CourseId |
---|---|
1 | 1 |
1 | 2 |
2 | 1 |
2 | 2 |
2 | 3 |
-
The middle table is an example for a composed primary key
Self-Relation
How do we represent trees and graphs?
Self-Relation
-
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 | Parent? |
---|---|---|
1 | Root | null |
2 | Documents | 1 |
3 | Pictures | 1 |
4 | Birthday Party | 3 |
Integrity Constraints
-
Integrity constraints ensure data integrity in the database tables. They 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
-
Indexing
-
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)
Relational Database Management System
Microsoft SQL Server
-
MS SQL Server is a Relational Database Management System (RDBMS) from Microsoft
-
The main language supported in SQL Server is Transact SQL (T-SQL), an extension of SQL
-
Powerful, trustworthy, easy-to-use DB server
-
-
The most recent version is SQL Server 2016
-
Works only on Windows systems*
-
The 2017 version now runs on Linux as well
-
-
A free distribution exists
-
SQL Server 2016 Developer Edition
-
SQL Server Services
-
SQL Server – the database engine
-
Responsible for database management, data storage, queries, data manipulation, data integrity, transactions, locking, users, security
-
Executes SQL / T-SQL queries
-
-
SQL Server Agent – DB monitoring
-
Executes scheduled tasks
-
Monitors SQL Server
-
Sends notifications about problems
-
SQL Server Databases
-
SQL Server has system and user databases
-
System databases
-
Maintain internal information about MS SQL Server as a system
-
Don't play with them!
-
-
User databases
-
Databases created by users (developers)
-
Store user's schemas and data
-
Use the system databases internally
-
-
Connecting to SQL Server
-
Connecting to SQL Server requires:
-
Database Client
-
Use Microsoft SQL Server Management Studio
-
-
The name/IP of the server
-
Username / password (if required)
-
-
Types of authentication in SQL Server
-
Windows (by using a Windows user credentials)
-
Mixed (both Windows and SQL Server)
-
SSMS
-
Microsoft SQL Server Management Studio (SSMS) is a powerful graphical DB management tool
-
Administrate databases
-
create, modify, backup / restore DB
-
-
Create and modify E/R diagrams
-
View/modify table data and other DB objects
-
Execute SQL queries
-
Free and easy to use tool
-
Works with all SQL Server versions
-
Moving a SQL Server Database
-
Necessary when we install a certain application in the customer environment
-
Ways of moving an SQL Server database:
-
By backup and restore
-
Create backup and restore it on the other server
-
-
By detaching and attaching the database files
-
The 2 servers must be the same versions!
-
-
By dumping the database as SQL script
-
Not supported in SSMS in earlier versions than 2016
-
-
Relational Data Modelling
Steps in Database Design
-
Identification of the entities
-
Identification of the columns in the tables
-
Defining a primary key for each entity table
-
Identification and modeling of relationships
-
Multiplicity of relationships
-
-
Defining other constraints
-
Filling test data in the tables
Identification of Entities
-
Entity tables represent objects from the real world
-
Most often they are nouns in the specification
-
Example: We need to develop a system that stores information about students, who are trained in various courses. The courses are held in different towns. When registering a new student the following information is entered: name, faculty number, photo and date.
-
-
Entities: Student, Course, Town
Identification of Columns
-
Columns in the tables are characteristics of the entities
-
They have name and type
-
-
Example students have:
-
Name (text)
-
Faculty number (number)
-
Photo (binary block)
-
Date of enlistment (date)
-
Choosing a Primary Key
-
Always define an additional column for the primary key
-
Don't use an existing column (for example SSN)
-
Preferable to be a GUID
-
Can be an integer
-
-
Must be declared as a primary key
-
Use identity to implement auto-increment
-
Put the primary key as a first column
-
-
Exceptions
-
Entities that have well-known ID, e.g. countries (BG, DE, US) and currencies (USD, EUR, BGN)
-
Identification of Relationships
-
Relationships are dependencies between the entities:
-
We need to develop a system that stores information about students, which are trained in various courses. The courses are held in different towns. When registering a new student the following information is entered: name, faculty number, photo and date.
-
-
Students are trained in courses – many-to-many relationship
-
Courses are held in towns – many-to-one (or many-to-many) relationship
Data Types in SQL Server
-
Numeric
-
bit (1-bit), integer (32-bit), bigint (64-bit)
-
float, real, numeric(scale, precision)
-
money – for money (precise) operations
-
-
Strings
-
char(size) – fixed size string
-
varchar(size) – variable size string
-
nvarchar(size) – Unicode variable size string
-
text/ntext – text data block (unlimited size)
-
Data Types in SQL Server
-
Binary data
-
varbinary(size) – a sequence of bits
-
image – a binary block up to 1 GB
-
-
Date and time
-
datetime – date and time starting from 1.1.1753 to 31.12. 9999, a precision of 1/300 sec.
-
smalldatetime – date and time (1-minute precision)
-
-
Other types
-
timestamp – automatically generated number whenever a change is made to the data row
-
xml – data in XML format
-
Naming Conventions
-
Tables
-
Each word is capitalized (Pascal Case)
-
In English, plural
-
Examples: Users, PhotoAlbums, Countries
-
-
Columns
-
In English, singular
-
Each word is capitalized (Pascal Case)
-
Avoid reserved words (e.g. key, int, date)
-
Examples: FirstName, OrderDate, Price
-
Naming Conventions
-
Primary key
-
Use "Id" or name_of_the_table + "Id"
-
Example: in the Users table the PK column should
called Id or UserIdbe be
-
-
Foreign key
-
Use the name of the referenced table + "Id"
-
Example: in the Users table the foreign key column that references the Groups table should be named GroupId
-
Naming Conventions
-
Relationship names
-
In English, Pascal Case
-
"FK_" + table1 + "_" + table2
-
For
example: FK_Users_Groups
-
-
Index names
-
"IX_" + table + column
-
For example: IX_Users_UserName
-
-
Unique key constraints names
-
"UK_" + table + column
-
For instance: UK_Users_UserName
-
Questions?
[Databases] DB Introduction
By telerikacademy
[Databases] DB Introduction
- 1,024