SQL

Data - piece of information

Types

• Structural
  • tables RDBMS
    • Mysql relational database
    • SQL Server,
    • Oracle,
    • SQLite
• Semi structural
  • JSON
  • NoSQL
  • MongoDB
  • Casandra
• Unstructured
  • Photos
  • videos
  • audio
  • files
  • oneDrive
  • AWS S3
  • Azure Blobs
• RDBMS Relational Database Management System
  • based on relationship model
  • stores data in form of tables
  • provides referential integrity between rows and columns of various tables
  • uses many SQL queries and combines useful information using joins
• Entity:
  • any real world object that has data that can be stored
• Relationships
  • one to one
  • one to many
  • many to many
• Data Types
  • numeric
    • tinyint
    • smallint
    • integer
    • float
    • double
  • date and time
    • year
    • date
    • timestamp
  • string
    • char
      • character has a fixed size. occupies a fixed size space
      • performance efficient
    • varchar
      • memory efficient. denotes limit and only takes the space used

• MySQL Download

  ### SQL Statements

• DDL
  • Data definition language
    • consists of the commands that can be used to defines schema
    • It deals with description of the database schema and is used
• DML
  • Data Manipulation Language
• DQL
  • Data Query language
• TCL
  • Transactional control language

MySQL

Start the server:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld"

Stopping the server:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin" -u root shutdown

Customizing path:

mysql -u root -p

• you can change root to any user
• then enter password

To reset password:

`ALTER USER 'root'@'localhost' IDENTIFIED BY '<strong password>';`

Create a new user:

mysql> `create user 'amc' identified by '<strong password>';`

Restrict access:

mysql> `create user 'amc'@'localhost' identified by '<strong password>';`

Show all databases:

`SHOW DATABASES;`

SHOW SCHEMAS

Choose the database to work on

`use databasename;`

Show all table in a database

`SHOW TABLES;`

Show tables with table types:

`show full tables;`

Show tables from a database:

`SHOW TABLES FROM database_name;`

Create the database

mysql> `create database myCoolDB;`

Grant all access to the user amc2:

mysql> `grant all on myCoolDB.* to 'amc2';`

MySQL Constraint

• These contraint in MySQL is used to specified the rules or restrict what value / data will be store in the table
• They provide a suitable method to ensure data accuracy and integrity inside the table

Type of containts

1. Column level constraint - are applied only to the single column that limit the type of particular column data
2. Table level constraint - are applied to the entire table that limit the type of data for the whole table

NOT NULL

Check

Default

Primary Key

AUTO_INCREMENT

Unique

Enum

NOT NULL

create table employee (id integer Primary key, firstname char(50) NOT NULL,lastname char(50) NOT NULL, email char(50) NOT NULL);

describe employee;

insert into employee (id, name) values (1, 'mark');

Check

mysql> create table employee (id integer, name char(50), age integer CHECK(age >= 18));

mysql> describe employee;

mysql> insert into employee (id, name, age) values (1, 'Mark', 21);

mysql> insert into employee (id, name, age) values (2, 'paul', 15);

Default

mysql> create table employee (id integer, name char(50), country char(50) DEFAULT 'USA');

mysql> describe employee;

mysql> insert into employee (id, name, country) values (1, 'Mark', 'UK');

mysql> insert into employee (id, name) values (2, 'John');

mysql> select * from employee;

Primary key

You can have only one primary key per table and this we make the column value not null and unique

mysql> create table employee (id integer primary key, name char(50));

mysql> describe employee;

mysql> insert into employee values (1, 'maek');

mysql> insert into employee values (1, 'maek');

mysql> insert into employee values (2, 'maek');

mysql> select * from employee;

AUTO_INCREMENT

mysql> create table employee (id integer primary key AUTO_INCREMENT, name char(50));

`describe employee;`

mysql> insert into employee (name) values ('Mark');

mysql> insert into employee (name) values ('smith');

mysql> insert into employee (name) values ('john');

mysql> select * from employee;

Unique

mysql> create table employee (id integer, name char(50), email char(50) UNIQUE);

mysql> describe employee;

mysql> insert into employee values (1, 'mark', 'm@gmail.com');

mysql> insert into employee values (1, 'mark', 'm@gmail.com'); - will now throw an error for duplicate entry

mysql> insert into employee values (1, 'mark', 'm2@gmail.com');

mysql> select * from employee;

Enum

mysql> create table employee (id integer, gender ENUM('male', 'female'));

mysql> describe employee;

mysql> insert into employee values (1, 'male');

mysql> insert into employee values (2, 'female');

mysql> insert into employee values (3, 'f'); mysql> insert into employee values (3, 'Female');

mysql> select * from employee;

Advance queries / Mysql Clauses

• where
• distinct
• from
• order by
• group by
• having

MySQL where clause

The where clause is used to write condition

• where - the single condition

  select * from employee where id =1;

• AND - both side condition should be true

  select * from employee where id = 1 and name = '';

• OR - either of the condition is true

  select * from employee where id = 1 or name = '';

mysql> create table users (id integer, email char(50), password char(50));

mysql> insert into users values (1, 'test1', '123');

mysql> insert into users values (2, 'test2', '123');

mysql> insert into users values (3, 'test3', '987');

mysql> select * from users;

mysql> select * from users where id = 1;

mysql> select * from users where id = 2;

mysql> select * from users where email = 'test1' and password = '123';

mysql> select * from users where email = 'test1' and password = '987';

mysql> select * from users where email = 'test1' or password = '987';

Distinct

Is used to remove duplicate records from the table and fetch only unique records

select DISTINCT id from table

mysql> create table employee (id integer, name char(50));

mysql> insert into employee values (1, 'Wetangula');

mysql> insert into employee values (2, 'Wetangula');

mysql> insert into employee values (3, 'Ruto');

mysql> insert into employee values (4, 'Karua');

mysql> insert into employee values (4, 'Watsonm');

see the table created mysql> select * from employee;

use of distinct mysql> select DISTINCT name from employee;

Order By

Is used to sort the records in ascending or descending order:

• select * from employee order by col_name DESC
• select * from employee order by col_name ASC

mysql> create table employee (id integer, name char(50), email char(50));

mysql> insert into employee values (4, 'a', 'a@gmail.com');

mysql> insert into employee values (1, 'b', 'b@gmail.com');

mysql> insert into employee values (5, 'c', 'c@gmail.com');

mysql> insert into employee values (2, 'd', 'd@gmail.com');

mysql> insert into employee values (3, 'e', 'e@gmail.com');

mysql> select * from employee;

mysql> select * from employee order by id ;

mysql> select * from employee order by id ASC;

mysql> select * from employee order by id DESC;

mysql> select * from employee order by name DESC;

Group by

• Used to collect data from multiple records and group the result by one or more column
  • Mostly use with select
  • You can also use an aggregation function like count, max, min, sum etc

select expression1, expression2 from table by expression

mysql> create table employee (id integer, name char(50), salary int, age int, job char(50));

mysql> drop table employee;

mysql> create table employee (id integer primary key AUTO_INCREMENT, name char(50), salary int, age int, job char(50));

mysql> insert into employee (name, salary, age, job) values ('mark', 5000, 20, 'admin');

mysql> insert into employee (name, salary, age, job) values ('paul', 4000, 26, 'clerk');

mysql> insert into employee (name, salary, age, job) values ('watson', 8000, 30, 'admin');

mysql> insert into employee (name, salary, age, job) values ('john', 18000, 50, 'manager');

mysql> insert into employee (name, salary, age, job) values ('stacy', 12000, 33, 'clerk');

mysql> insert into employee (name, salary, age, job) values ('rangel', 22000, 30, 'manager');

mysql> select * from employee;

mysql> select job from employee group by job;

mysql> select job, count(job) from employee group by job;

mysql> select job, sum(salary) from employee group by job;

mysql> select job, sum(salary), min(salary), max(salary), avg(salary) from employee group by job;

mysql> select job, sum(salary) total, min(salary) minSalary, max(salary) maxSalary, avg(salary) AvgSalary from employee group by job;

having

• Used with group by clause
• Returns the row where condition is true;

mysql> select * from employee;

mysql> select age, job, sum(salary) from employee group by job having age > 25;

MySQL Key

1. Unique Key
2. Primary Key
3. Foreign Key
4. Composite Key

Unique Key

• Is a single field that ensures all values to be stored in the column are unique

• Column will not have duplicate values

• You can have more than one column with unique constraints in a table and it can accept null

Rules

1. It is useful when we want a column that cannot have identical values
2. It store only distinct value that maintains the integrity and reliability of the database
3. It can contain a null value but only once

Syntax

---

create table employee (id integer, name char(50), email char(50) UNIQUE); create table employee (id integer, name char(50) UNIQUE, email char(50) UNIQUE); create table employee (id integer, name char(50), email char(50), UNIQUE KEY my_unique_key (name, email));

mysql> describe employee;

Primary key

• Is used to identify each record in a table uniquely
• A table with a primary key cannot be null or empty
• Primary key column can also use the AUTO_INCREMENT attribute to generate a sequential number for the row automatically
• MySQL automatically create an index for the primary column

Rules

1. Primary keycolumn value must be unique
2. Each table can contain only one primary key
3. The primary key column cannot be null
4. MySQL does not allow us to insert a new row with an existing primary key
5. It is recommended to use int as a data type for the primary key column

create table employee (id integer primary key auto_increment, name char(50))

Forign key

It is used to link one ore more then one table together It is also know as referencing key Foreign key matches the primary key of another table because the foreign key field in one table refer to the primary field of the other table It is very important to maintain referential integrity in database

A foreign key make it possible to create a parent child relationship with the table

order

• orderId
• data
• amount
• custId

customer

• custId
• name
• email
• phone

Example

Customer

• id
• name
• email

Address

• addressId
• house_number
• city
• country
• cust_id

mysql> describe customer; describe address;

mysql> alter table address add FOREIGN KEY (cust_id) REFERENCES customer(cust_id);

mysql> describe customer; describe address;

mysql> insert into customer (name, email) values ('a', 'a@gmail.com');

mysql> insert into customer (name, email) values ('b', 'b@gmail.com');

mysql> select * from customer;

mysql> describe address;

mysql> mysql> insert into address (city, country, cust_id) values ('NY', 'USA', 1);

JDBC

• Java Database Connctivity

• JDBC is an Java API to connect and execute the query with the database and is a part of Java SE.

• JDBC API uses JDBC driver to connect with the database

There are different type of JDBC drivers

1. JDBC-ODBC driver
2. Native driver
3. Network Protocol driver
4. Thin driver

A list of popular interfaces

• Driver interface
• Connection interface
• Statement Interface
• PrepareStatement interface
• ResultSet interfaces

and classes

• DriverManager
• Types Class

Why should use JDBC

1. Connect to the database
2. Execute queries and update statement to the database
3. Retrieve the result received from the database

Java database with 5 simple steps

1. register deriver
2. get connection
3. create statement
4. execute query
5. close connection

Step1: register driver

The forName() method of a class which is used to register the driver class This method is used to dynamically load the driver

class.forName(‘oracle.jdbc.driver.OracleDriver’);

Step2: create connection

getConnection() method of DriverManager class which is used to establish connection with the database

public static Connection getConnection(String url, String username, String password);

Step3: create the statement object

createStatement() method of connection interface is used to create a statement the object of the statement is responsible to execute queries with the database

public Statement createStatement()

Step4 : execute queries

executeQuery() method of statement interface is used to execute queries in the database this method return the object of ResultSet that can be used to get all records of a table

public ResultSet executeQuery(String sql)

Step5: close the connection

close() method

MySQL Indexes

• An index is a data structure that allows us to add indexes in the existing table
• It enables you to improve the performance of your quaries
• It improve the faster retrieval of records on a database table
• It create an entry for each value of the indexed columns and we use it quickly to find a record without searching each and every row in the table
• we can create an index by using one or more colum

Example

• Employee Id Name Email Age

How to create an index

Syntax ——

create index <index_name> on <table_name> (column <column_name>);

create

create index ix_name on employee(name);

drop

drop index <index_name> on <table_name>

show indexes

To get the index information of a table using show indexes statement

show indexes from <table_name> show indexes from table_name in database_name.table_name show keys from table_name in database_name

Show index query returns following information

• Table -
  • table name
• Non_Unique:
  • This returns 1 if the index contain duplicate value else it return 0
• Key_name:
  • This is the name of the index, if the table contain primary key, the index name is always PRIMARY
• Seq_in_index:
  • This is the sequence number of the column in the index that starts from 1
• Column_name:
  • Contains the name of the column
• Collation: it gives information about the column is stored in the index A - ascending D - descending Null - not sorted
• Cardinality: it gives as estimated number of unique value in the index table where higher cardinality represent a greater chance of using indexes
• Sub_part: it is a prefix of the index, it is NULL value if all the column of the table is indexed, it will return the number of indexes characters, when colum are partially indexed
• Null - it contain blank of the column does not have null value, otherwise it return true
• index_type: it contain the name of the index method like BTREE, HASH, FULLTEXT etc
• comment: it contain in index info when they are not described
• visible: it contain yes if the index is visible to query optimizer otherwise no
• expression - it contain some expression

Type of indexes

• unique index
• clustered index
• non clustered index

Unique index

• Find an unordered list into an ordered list that allows for quick retrieval of the records it create an entry for each value that appears in the index column

we use primary key constraint to enforce the unique value in a column but we can use only one primary key for each table so if we want to make multiple sets of column with unique value then the primary key constraint will not be use

MySQL allow another constraint called unique index to enforce the uniqueness of the value in one or more column

we can create more then one unique index in a single table which is not possible with the primary key constraint

syntax

create unique index on (col1, col2)

mysql allow us another approach to enforce unique value in ore or more column by using UNIQUE KEY

if we use UNIQUE constraint in the table MySQL automatically create a unique index behind the scene

syntax

create table <table_name>(col, col2) constraint <col_name> UNIQUE KEY (col_name);

insert into employee values (1, 'mark');

create unique index ix_email_unique on employee (email);

describe employee;

Clustered index

• A clustered index is actually a table where the data for the row are stored
• It defines the order of the table data
• Each table can only have one clustered index
• If the table column contains a primary key mysql allows you to create a clustered index name PRIMARY based on specified column

Features

1. It helps us to store data and indexes at the same time
2. It stores data in only one way based on key value
3. Clustered index allows us one or more column for creating in index

Syntax

create table table_name (id integer, name char(50), primary key (id)) // clusterd index

Non-clustered index

Is also know as a secondary index The non-clustered index and table data both are stored in different place it will not sort the table data MySQL allows a table to store one or more than one non-clustered index non-clustered index improves the performance of the queries which uses key without assigning primary key

syntax

create index <index_name> on <table_name> (column ASC | DESC)

mysql> create table employee (id integer, name char(50), email char(50));

mysql> show indexes from employee;

mysql> create index ix_name on employee (name ASC);

mysql> create index ix_email on employee (email ASC);

mysql> create index ix_name_email on employee (name ASC, email ASC);

mysql> show indexes from employee;

Store Procedure

A procedure is a collection of pre-compiled SQL statement stored inside the database it is subProgram in the regular computing language a procedure always contain

• name
• parameter list
• sql statement

we can invoke procedure by using other procedures, triggers and application

Features

• Store procedure increases the performance of the application, once the store procedure are created they are compiled and stored in the database
• Store procedure reduces the interaction between a app and database server, because the application has to send only store procedure name and required parameters instead of passing multiple sql statement
• Store procedure are reusable and transparent to any app
• Procedure is always secure, you can avoid one of the critical type of attack know as SQL injection

Syntax

create procedure <procedure_name>(parameters)
begin
	declaration_section

	executable_section
end

call <procedure_name>(parameters)

procedure_name - it means the name of the procedure parameters - it means number of parameters declaration_section - it means declaration nof all variables executable_section - it means the code for function execution

Type of procedure

• In parameter
• Out parameter
• input parameter (in + out)

DELIMITER //
create procedure sp_get_employee()
BEGIN
	select id, name, email from employee;
END //
DELIMITER ;

call sp_get_employee();

In Parameter

In this procedure we have the IN Parameter of any type to accept a value from the user

select * from employee where age < 30

DELIMITER //
create procedure sp_get_employee_age(IN var1 INTEGER)
BEGIN
	select * from employee where age < var1;
END //
DELIMITER ;

call sp_get_employee_age(30);

Out parameter

In this procedure we will have out parameter

DELIMITER //
create procedure sp_max_employee_age(OUT var1 INTEGER)
BEGIN
	select MAX(age) into var1 from employee;
END //
DELIMITER ;

CALL sp_max_employee_age(@result); select @result;

INOUT parameter

In this procedure we have to use IN OUT parameters

DELIMITER //
create procedure sp_max_employee(INOUT var1 INTEGER)
BEGIN
	select age into var1 from employee where id = var1;
END //
DELIMITER ;

SET @R = '1'; CALL sp_max_employee(@R); select @R;

List of store procedure

show procedures status where db = 'demo';

drop procedure

drop procedure <name>;

alter

alter procedure <procedure_name>()

Set Operator

Is used to combine the two or more sql statement

Type of set operators

1. union
2. union all
3. intersect

4. minus

5. union
   • The sql union is used to combine the result of two or more sql select queries.
   • All the number of data type and column must be same in both table
   • It eliminates the duplicate rows from its result set

mysql> create table employee_usa (id integer, name char(50), email char(50));

mysql> create table employee_uk (id integer, name char(50), email char(50));

mysql> insert into employee_usa values (1, 'mark', 'm@gmail.com');

mysql> insert into employee_usa values (2, 'paul', 'p@gmail.com');

mysql> insert into employee_usa values (3, 'watson', 'w@gmail.com');

mysql> insert into employee_uk values (4, 'john', 'j@gmail.com');

mysql> insert into employee_uk values (5, 'rangel', 'r@gmail.com');

mysql> insert into employee_uk values (6, 'stacy', 's@gmail.com');

mysql> select * from employee_usa; select * from employee_uk;

mysql> insert into employee_uk values (1, 'mark', 'm@gmail.com');

mysql> select * from employee_usa; select * from employee_uk;

mysql> select * from employee_usa union all select * from employee_uk;

mysql> select * from employee_usa union select * from employee_uk;

Joins

Join are used with select statement to retrieve data from multiple tables it is performed whenever you need to fetch records from two or more tables

Type of join

1. Inner join - simple join
2. left outer join - left join
3. right outer join - right join
4. self join

inner join

It is used to return all rows from multiple table where the join statement is satisfied It is one of the most common type of join

select <column> from table1 inner join table2 on table1.column=table2.column

mysql> create table student (student_id integer primary key auto_increment, name char(50), country char(50));

mysql> create table course (course_id integer primary key auto_increment, course_name char(50), student_id integer);

mysql> describe student; describe course;

mysql> alter table course add constraint student_fk foreign key (student_id) references student(student_id);

mysql> describe student; describe course;

mysql> insert into student (name, country) values ('mark', 'USA');

mysql> insert into student (name, country) values ('john', 'USA');

mysql> insert into student (name, country) values ('paul', 'USA');

mysql> select * from student;

mysql> describe course;

mysql> insert into course (course_name, student_id) values ('html', 1);

mysql> insert into course (course_name, student_id) values ('css', 2);

mysql> insert into course (course_name, student_id) values ('java', 3);

mysql> select student.student_id, student.name, student.country, course.course_name from student inner join course on student.student_id=course.student_id;

mysql> select s.student_id, s.name, s.country, c.course_name from student s inner join course c on s.student_id=c.student_id;

Inner Join with where condition

mysql> select student_id, name, course_name from student inner join course using (student_id) where course_name = 'java';

Left Join

This is similar to the inner join that can be use to select common data from left side table in the left join clause return all the rows from left side table and matching records from right side table

mysql> select s.student_id, s.name, s.country, c.course_name from student s left join course c on s.student_id=c.student_id;

Right join

mysql> select s.student_id, s.name, s.country, c.course_name from student s right join course c on s.student_id=c.student_id;

Self join

• It is a join which is used to join with in itself we can perform self join using table alias table alias allow us not to use the same table name twice with a single statement

Problem statement

I want to find out all the employee whose salary is equal to mark and i don’t know mark’s salary;

Soultion 1 select * from employee where name = mark select * from employee where salary = 5000

Soution 2 select * employee where salary = (select salary from employee where name = 'mark')

Solution3 select e1.id, e1.name, e1.salary from employee e1, employee e2 where e1.salary = e2.salary and e2.name = 'mark'

mysql> create table employee (id integer primary key auto_increment, name char(50), salary integer);

mysql> insert into employee (name, salary) values ('mark', 5000);

mysql> insert into employee (name, salary) values ('paul', 6000);

mysql> insert into employee (name, salary) values ('john', 5000);

mysql> insert into employee (name, salary) values ('rangel', 8500);

mysql> insert into employee (name, salary) values ('page', 500);

mysql> insert into employee (name, salary) values ('watson', 5000);

mysql> select * from employee;

mysql> select * from employee where name = 'mark';

mysql> select * from employee where salary = 5000;

mysql> select * from employee where salary = (select salary from employee where name = 'mark');

mysql> select e1.id, e1.name, e1.salary from employee e1, employee e2 where e1.salary = e2.salary and e2.name='mark';

View

• A view is a database object that has no values
• it content are based on the base table
• it contain row and column similar to the real table
• view can also be called as a virtual table
• it operates similar to the base table but does not contain any data of its own

syntax

create view view_name as select * from table

mysql> create table employee (id integer primary key auto_increment, name char(50), age integer, status integer);

mysql> describe employee;

mysql> insert into employee (name, age, status) values ('mark', 24, 1);

mysql> insert into employee (name, age, status) values ('paul', 26, 1);

mysql> insert into employee (name, age, status) values ('smith', 32, 1);

mysql> insert into employee (name, age, status) values ('page', 22, 0);

mysql> insert into employee (name, age, status) values ('rangel', 28, 1);

mysql> insert into employee (name, age, status) values ('watson', 23, 0);

mysql> select * from employee;

mysql> select * from employee where status = 1;

mysql> create view emp_active as select * from employee where status = 1;

mysql> select * from emp_active;

mysql> create view emp_age as select * from employee where age < 30;

mysql> select * from emp_age;

mysql> create view emp_age_active as select * from employee where age < 30 and status= 1;

mysql> select * from emp_age_active;

mysql> select * from employee;

mysql> insert into emp_age_active (name, age, status) values ('a', 50, 1);

mysql> select * from employee;

TCL - transaction control language

Transaction consist of four main properties which referred to as ACID properties

A - atomicity C - consistency I - isolation D - durability

1. Atomicity All statement or operation in transaction unit must be executed successfully otherwise operation is failed. All or nothing

2. Consistency The database must be consistent before and after the transaction

3. Isolation Multiple transactions occur independently without interference

4. durability The changes of a successful transaction occurs even if system failure occurs

CAP theorem

• The CAP, also called Brewer’s theorem implies that “…a distributed system can deliver only two of the three desired characteristics: C - consistency A - Availability P - partition …” it is important to understand this when designing a cloud application in order to build a data management system that delivers the characteristics you need.

DML

(Data Manipulation Language) is used for adding (inserting), deleting, and modifying (updating) data in a database. To define, modify, and delete databases objects, such as databases, tables, and views. Statements

• CALL Statement
• DELETE Statement
• DO Statement
• HANDLER Statement
• IMPORT TABLE Statement
• INSERT Statement
• LOAD DATA Statement
• LOAD XML Statement
• REPLACE Statement
• SELECT Statement
• Subqueries
• TABLE Statement
• UPDATE Statement
• VALUES Statement
• WITH (Common Table Expressions) and these operation are by default auto commit (changes saved permanently)

TCL

Transaction control enforces database integrity by ensuring that batches of SQL operations run completely or not at all. The transaction control commands are BEGIN, COMMIT, and ROLLBACK. begin transaction commit rollback

Bank

A - 100 B - 100

update bank set balance = balance-10 where account_number=1; update bank set balance = balance+10 where account_number=20;

mysql> create table bank (account_number integer, account_name char(50), balance integer);

mysql> describe bank;

mysql> insert into bank values (1, 'A', 100);

mysql> insert into bank values (2, 'B', 100);

mysql> select * from bank;

mysql> update bank set balance = balance-10 where account_number=1;

mysql> update bank set balance = balance+10 where account_number=2;

mysql> select * from bank;

mysql> select * from bank;

mysql> start transaction;

mysql> update bank set balance = balance-10 where account_number=1;

mysql> update bank set balance = balance+10 where account_number=20;

mysql> select * from bank;

mysql> rollback;

mysql> select * from bank;

Normalization

• It is the process of separating data and creating primary keys in order to avoid duplicates and redundancy.
• creating duplicate values in two different tables, for examples makes it harder or longer to update one piece of information because you have to change it in both tables.
• “…The goal is to remove certain kinds of data redundancy and therefore avoid update anomalies…” docstore.mik.ua/orelly/linux/sql/ch02_02.htm

type of normal forms

1. 1NF - a relation in 1NF if ot contain an atomic value ie- one value for each entity.
2. 2NF - a relation will be in 2NF of it in 1NF and all non key attributes are fully function dependent on primary key
3. 3NF - a relation will be in 3NF if it is in 2NF and no transaction dependency exist

Random side Note - ` MySQL essentially implements the semantics defined by MATCH SIMPLE, which permits a foreign key to be all or partially NULL. In that case, a (child table) row containing such a foreign key can be inserted even though it does not match any row in the referenced (parent) table. (It is possible to implement other semantics using triggers.)`