The series so far:

  1. SQL Server Fundamentals - Part 1: Life of Databases
  2. SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
    can keep you out of trouble
  3. SQL Server Fundamentals - Part 3: Working with data in the Table taboos you should Break
  4. SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
  5. 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.

  1. The tables are stored on disk, and managed in a database server
  2. Client applications access the database across a network.
  3. Increasingly, the database server is hosted in the cloud.
  4. 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

  1. Name
  2. 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.