The series so far:
- SQL Server Fundamentals - Part 1: Life of Databases
- SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
can keep you out of trouble- SQL Server Fundamentals - Part 3: Working with data in the Table taboos you should Break
- SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
- SQL Server Fundamentals - Part 5: Non Relational Data Types in 2 Easy Steps
Introduction
Non-Relational Database
Non-Relational Databases can be defined by storing entire car object as a single entity in the database. If we need to know about the Tyres used in the car, we can query Car and get its Tyres.
Relational Database
Relational Databases can be defined by describing a car by defining all of its components and storing each of them individually in the database. We see there is a relation defined between these components that define the car. If we need to know about Tyres used in the car, we can query Tyres Table.
Databases
Databases are used to store data in tables. Usually, we normalize these tables to create a relational schema.
UNF - Unnormalized Form
Let us consider Product table with general information stored in table as below:
Product Table
Name | Color | Supplier | Phone |
---|---|---|---|
Wu | Blue | California | 111234 |
Tom | Blue, Red | New York | 221234 |
Kim | Blue | New York | 221234 |
Normal forms are introduced to saves space by removing duplicate data.
NF1 - First Normal Form
Each Column one value. Lets split color column into multiple rows
Product Table
Name | Color | Supplier | Phone |
---|---|---|---|
Wu | Blue | California | 111234 |
Tom | Blue | New York | 221234 |
Tom | Red | New York | 221234 |
Kim | Blue | New York | 221234 |
NF2 - Second Normal Form
Non key columns to dependent on primary key.
Duplicate Data if Supplier column needs to be changed need to modify all columns
1. Split Problematic Column
Split Supplier
Product Table
Name | Color | Supplier | Phone |
---|---|---|---|
Wu | Blue | 1 | 111234 |
Tom | Blue | 2 | 221234 |
Tom | Red | 2 | 221234 |
Kim | Blue | 2 | 221234 |
Suppliers Table
SupplierId | Supplier |
---|---|
1 | California |
2 | New York |
2. Split Color
Colors Table
ColorId | Color |
---|---|
1 | Blue |
2 | Red |
Product Table
|ProductId| Name | Supplier | Phone |
|:--:|:----:|:-----:|:--------:|:-----:|
1|Wu|1|111234
2|Tom|2|221234
3|Kim|2|221234
ProductColor Table
Product | Color |
---|---|
1 | 1 |
2 | 1 |
2 | 2 |
3 | 1 |
NF3 - Third Normal Form
All columns should depend directly on the primary key. Have each row identify each column uniquely and foreign references correctly
Move phone to supplier
Color Table
ColorId | Color |
---|---|
1 | Blue |
2 | Red |
Product Table
ProductId | Name | Supplier |
---|---|---|
1 | Wu | 1 |
2 | Tom | 2 |
3 | Kim | 2 |
ProductColor Table
Product | Color |
---|---|
1 | 1 |
2 | 1 |
2 | 2 |
3 | 1 |
Supplier Table
SupplierId | Supplier | Phone |
---|---|---|
1 | California | 111234 |
2 | New York | 221234 |
Store in Database Management System
Data is then stored in Database Management System where we take care of access and authentication.
- The tables are stored on disk, and managed in a database server
- Client applications access the database across a network.
- Increasingly, the database server is hosted in the cloud.
- Access to data in a database is restricted using firewalls and authentication
Creating Database
Create a new database in Server; we need the Database name Collation combination of character set and sort order, default one is generally SQL_Latin1_General_CP1_CI_AS (Code Page 1 / Case Insensitive / Ascending Order)
In SSMS, we connect with sysadmin user
Databases folder indicates a list of databases available and running servers connected.
Security Folder indicates Login and Server Roles for authentication and access related to server.
Create Database Command
CREATE DATABASE
Default Datafiles and Transactional Logs in default location, default size and default settings.
Storing Data and Logs
Generally we mark separate folders in different drives as default for databases
D:\SQLData\Data
D:\SQLData\Backup
E:\SQLData\Logs
E:\SQLData\Backup
Data File
MDF file, we can specify the SIZE, MAXSIZE and FILEGROWTH
Transactional Log file
LDF file, we can define the SIZE, MAXSIZE and FILEGROWTH
Records history of actions in the transactional log and is crucial to SQL engine
Create Database using SSMS
Right Click Databases Folder and Select Create new Database
- Name
- Data file and log file
Note: Ideally, we should put data file, transactional logs on separate disks.
SQL Performance is typically enhanced if you place database and transaction log files on different drives (preferably separate physical drives). Read I/O for database files is generally random when reading database pages while I/O for the transaction log is typically sequential.
Furthermore, if the database is using FULL recovery, the transaction log can still be used for recovery if the drive holding the database files gets lost, but the drive for the transaction log is still available.