Nulls And Normal Form

Induction Program 07/07/08

DATABASES

Contents 1. Introduction to Database • Text File • Spread Sheet • Relational Database

2. Data Types • Numeric • Date & Time • String

3. Introduction to Normalization • Why Normalize • Normal Forms – 1st, 2nd & 3rd Normal Form

4. Introduction to SQL 5. Q & A

What is a Database? 



A database is a collection of data. A database could be as simple as a text file with a list of names. Or it could be as complex as a large, relational database management system, complete with in-built tools to help you maintain the data.

Text File Example:    

ID, Name, Department, Company 1,Martin,PHP, VSplash 2,Laxmikant,PHP, VSplash 3,Deepali, PHP, VSplash

Spread Sheet Example: ID

Name

Department

Company

1

Martin

PHP

VSplash

2

Laxmikant

PHP

VSplash

3

Deepali

PHP

VSplash

Relational Database Employee ID

Name

DepartmentID

CompanyID

1

Martin

1

1

2

Laxmikant

1

1

3

Deepali

1

1

Deparment

Company

ID

Name

ID

Name

1

PHP

1

VSplash

2

HR

3

Design

Data Types 

Numeric



Date and time



String

Numeric  

 

 

 





TINYINT( ) -128 to 127 normal 0 to 255 UNSIGNED. SMALLINT( ) -32768 to 32767 normal 0 to 65535 UNSIGNED. MEDIUMINT( ) -8388608 to 8388607 normal 0 to 16777215 UNSIGNED. INT( ) -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED. BIGINT( ) -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.



FLOAT



DOUBLE( , ) A large number with a floating decimal point.



A small number with a floating decimal point.

DECIMAL( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.

Date and Time 

DATE '1000-01-01' to '9999-12-31‘ YYYY-MM-DD



DATETIME '1000-01-01 00:00:00' to '9999-12-31 23:59:59‘



TIMESTAMP '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07‘ is returned as YYYY-MM-DD HH:MM:SS



TIME '-838:59:59' to '838:59:59' HH:MM:SS



YEAR[(2|4)] 1901 to 2155, and 0000 / 70 to 69

String      







CHAR( ) A VARCHAR( ) A TINYTEXT A TEXT BLOB

fixed section from 0 to 255 characters long. variable section from 0 to 255 characters long.

string with a maximum A string with a maximum A string with a maximum

length of 255 characters. length of 65535 characters. length of 65535 characters.

MEDIUMTEXT A string with a maximum length of 16777215 characters. MEDIUMBLOB A string with a maximum length of 16777215 characters. LONGTEXT A string with a maximum length of 4294967295 characters. LONGBLOB A string with a maximum length of 4294967295 characters.

Why Normalize? 

Flexibility • Structure supports many ways too look at the data



Data Integrity • Modification Anomalies Deletion  Insertion  Update 

• Efficency 

Eliminate redundant data and save space

Normalization Defined 





“In relational database design, the process of organizing data to minimize duplication” Normalization usually involves dividing a database into two or more tables and defining relationships between tables The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships

Normal Forms 

  

A series of logical steps to take to normalize data tables First Normal Form Second Normal Form Third Normal Form

First Normal Form 

All columns (fields) must be atomic • Means: no repeating items in columns Order Data

Customer Items

06/07/2008

Martin

Hammer,Saw,Nails

Order Data

Customer

Item1

Item2

Item3

06/07/2008

Martin

Hammer

Saw

Nails

Solution: Make a separate table for each set of attributes with a primary key Customers CustomerID Name

Orders OrderID Item CustomerID OrderDate

Second Normal Form In 1NF and every non-key column is fully dependant on the (entire) primary key



• Means: Do(es) the key field(s) imply the rest of the fields? Do we need to know both OrderId and item to know the Customer and the Date? Clue: repeating fields. OrderID

Item

CustomerID

OrderDate

1

Hammer

1

06/07/2008

1

Saw

1

06/07/2008

1

Nails

1

06/07/2008

Solution: Remove to a separate table Orders OrderId CustomerId OrderDate

OrderDetails OrderId Item

Third Normal Form 

In 2NF and every non-key column is mutually independent • Means: Calculations

Item

Quantity

Price

Total

Hammer

2

10

20

Saw

5

40

200

Nails

8

1

8

Solution: Put calculations in queries and forms OrderDetails OrderId Item Quantity Price

Put expression in text controll or in query: =Quantity * Price

SQL (Structured Query Language) 



SQL is a standard interactive and programming language for querying and modifying data and managing databases The core of SQL is formed by a command language that allows the retrieval, insertion, updating, and deletion of data, and performing management and administrative functions.

Questions?

Thank you