SQL Tables & Normalization

What is first normal form (1NF)?

1NF: Eliminate repeating groups

What is second normal form (2NF)?

2NF: Eliminate redundant data.

What is type of output is a de-normalized database design commonly used for?

A de-normalized database design is commonly used for Reporting and OLAP workloads.

What statement do you use to verify whether or not an object exists?

IF EXISTS

3NF: Eliminate columns not dependent on the key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.

Name a couple ways to get an accurate row count of the number of records in a table

SELECT * FROM table1 SELECT COUNT(*) FROM table1

What are the key traits of normalization?

The key traits of normalization are eliminating redundant data and ensuring data dependencies.

What is the objective of normalization?

The objective of normalization is to isolate data so that additions, deletions and modifications of a field can be made in just on table and then retrieved through the rest of the database via defined relationships.

How many levels of normal form are there?

There are six levels of normal form 1NF - 5NF plus Boyce-Codd Normal Form (BCNF)

What statement do you use to create a table?

CREATE TABLE TableName

What statement do you use to create a database?

CREATE DATABASE DatabaseName

What is normalization?

Database normalization is a data design and organizational process applied to data structures based on rules that help build relational databases.

What is de-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly.

What does de-normalizing the database design allow for fewer of?

De-normalizing the database design allows for fewer joins with tables and foreign key requirements.

1NF: Eliminate repeating groups

1NF: Make a separate table for each set of related attributes and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate redundant data

2NF: If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

What is third normal form (3NF)?

3NF: Eliminate columns not dependent on the key.

What is the SQL syntax for CREATE TABLE (w/ NULL/NOT NULL)?

CREATE TABLE DatabaseName (m_ColumnOneName DataTypeForOne <primary key>, m_ColumnTwoName DataTypeForTwo <NULL or NOT NULL>, ...)

What is the basic SQL syntax for CREATE TABLE (w/o NULL/NOT NULL)?

CREATE TABLE DatabaseName (m_ColumnOneName DataTypeForOne, m_ColumnTwoName DataTypeForTwo, ...)