Data Integrity

 Data Integrity

▪ Enforcing data integrity guarantees the quality of the data in the database.

▪ For example,

✓ If an employee is entered with an employee ID value of 123, the database should not

permit another employee to have an ID with the same value.

✓ If you have an employee_rating

 column intended to have values ranging from 1 to 5, the database should not accept a value

outside that range.

Types of data integrity

❑ Entity integrity

❑ Domain integrity

❑ Referential integrity

❑ User-defined integrity

Entity Integrity

❑ Entity integrity defines a row as a unique entity for a particular table.

❑ Entity integrity enforces the integrity of the identifier columns or the primary key of a

table, through UNIQUE indexes, UNIQUE constraints or PRIMARY KEY constraints.

Domain Integrity

❑ Domain integrity is the validity of entries for a specific column.

❑ You can enforce domain integrity to restrict the type by using data types, restrict the

format by using CHECK constraints and rules, or restrict the range of possible values by

using FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT

NULL definitions, and rules.

Referential Integrity

❑ Referential integrity preserves the defined relationships between tables when rows are

entered or deleted.

❑ In SQL Server, referential integrity is based on relationships between foreign keys and

primary keys or between foreign keys and unique keys, through FOREIGN KEY and

CHECK constraints.

❑ When you enforce referential integrity, SQL Server prevents users from doing the

following:

• Adding or changing rows to a related table if there is no associated row in the primary

table.

• Changing values in a primary table that causes orphaned rows in a related table.

• Deleting rows from a primary table if there are matching related rows.

SQL Server Full backups

 SQL Server Full backups

• The most common types of SQL Server backups are complete or full backups, also

known as database backups.

• These backups create a complete backup of your database as well as part of the

transaction log, so the database can be recovered.

• This allows for the simplest form of database restoration, since all of the contents are

contained in one backup.

• A full backup can be completed either using T-SQL or by using SSMS. The following

examples show you how to create a full backup.

• T-SQL

This will create a full backup of the AdventureWorks database and write the backup

contents to file "C:AdventureWorks.BAK". The .BAK extension is commonly used for

identifying that the backup is a full database backup.

• SQL Server Management Studio

o Right click on the database name

o Select Tasks > Backup

o Select "Full" as the backup type

o Select "Disk" as the destination

o Click on "Add..." to add a backup file and type "C:AdventureWorks.BAK" and

click "OK"

o Click "OK" again to create the backup

What is Database Backup ?

 What is Database Backup ?

• The word backup refers to copying and archiving the data so that it may be used to

restore in case of an event of data loss. In general, you should back up any work or data

that can’t be replaced easily.

• A database backup is a copy of data from the database, which can be used to reconstruct

the data.

• A database backup is a process to protect the data from various disasters. Disasters

cannot be completely prevented; the least we can do is to ensure we have everything

that’s needed to get up and running as soon as we can.

• Organizations target for backup, the data they feel is vulnerable to:

• Corruption

• Failed hardware including media failure, software failure, and OS errors

• Attacks on the network or other malicious actions

• Manual errors or other inadvertent actions

• Unauthorized changes

• and other undesirable events

SQL Server Backup Types

• Full backups

• Differential backups

• Transaction log backups

• File backups

• Filegroup backups

• Partial backups

• Copy-Only backups

• Mirror back

Sample Database Installation

 Sample Database Installation

You can download the SQL Server Sample Database from the below link

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server2017

SQL Server

 SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft. Like other RDBMS software, SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases. SQL server is tied to Transact-SQL, or TSQL, the Microsoft’s implementation of SQL that adds a set of proprietary programming constructs. SQL Server works exclusively on Windows environment for more than 20 years. In 2016, Microsoft made it available on Linux. SQL Server 2017 became generally available in October 2016 that ran on both Windows and Linux.