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

 

  1. Identification of the entities

  2. Identification of the columns in the tables

  3. Defining a primary key for each entity table

  4. Identification and modeling of relationships

    • Multiplicity of relationships

  5. Defining other constraints

  6. 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 be be called Id or UserId

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

Made with Slides.com