SQL - Data Base Basics of SQL

SQL is a computer language used to retrieve and manipulate data from relational databases. Some of the functions of SQL:

• Modify a database's structure
• Change system security settings
• Add user permissions to databases
• Query a database 
• Update a database

Important terms

• client: a single-user computer that interfaces with a multiple-user server
• client/server database system: system that divides time between a client and a database server
• database: collection of related data stored as organized files
• server: multiple-user computer that holds the actual database and provides shared database connection, interfacing, and processing services

A DBMS (database management system) is computer software used to create, modify, and update the database. It is used to extract data and generate reports. Some other databases:

• Microsoft Access
• DB2
• Sybase
• Microsoft SQL Server
• Oracle
• MySQL, an open source database
• FileMaker Pro
• Informix from IBM
• PostgreSQL, an open source database

The most popular database is a relational database. The structure of a relational database is a collection of data items in two or more two-dimensional tables related to one another. The data is stored in tabular form and easily accessible through table names, primary key values, and column names.

http://en.wikipedia.org/wiki/Dbms

Important Terms

• field: a column that runs vertically within a table corresponding to the object attributes described by the table
• foreign key: link records of one type with those of another type in a table; used to cross-reference tables; a column in a table used to reference a primary key in another table
• keys: uniquely identify a row or record in a table
• normalization: a three-step technique to organize data attributes in an efficient, reliable, flexible, and maintainable structure; used to minimize redundancy and dependency; the three-step technique places tables in the first normal form (1nf), second normal form (2nf), and third normal form (3nf)
• primary key: a field that uniquely identifies every row in the table
• row: a record representing a collection of data relating to an item in the table
• table: a structured file containing horizontal rows and vertical columns

Normalization

A table that is well-normalized ensures all of the tables in the database are linked together. Each higher normal form achieves a better, more efficient and powerful design. Create as many tables as needed to keep the data in separate groups. These are the normal forms:

• First Normal Form
• Second Normal Form
• Third Normal Form
• Boyce Codd Normal Form
• Fourth Normal Form
• Fifth Normal Form

I will talk about 1NF, 2NF, and 3NF:

First normal form

First normal form requires one distinct group per table:

• Each column in the table must be unique
• Separate tables of related data should be created 
• Each table must have a unique key made of a single column or concatenated columns, the primary key
• No duplicated rows
• No duplicated columns
• No row or column intersections that contain a null value
• No row or column intersections containing multi-valued fields.; each cell has a single value

http://www.techopedia.com/definition/25955/first-normal-form-1nf

Second normal form

Second normal form states that all columns of a table are dependent on the full primary-key column, not just part of the key. A table is in 2NF only if it is in 1NF. Second normal form attempts to reduce the redundancy in a table by breaking the table further to separate duplicate data. If any of the fields in the table are dependent on just part of the primary key, these fields are moved out to form another table. Inserting, updating, or deleting data in the table, should not lead to any inconsistencies.

http://databases.about.com/od/specificproducts/a/2nf.htm

Third normal form

A table is in 3NF only if it meets the requirements of both 1NF and 2NF. If there is data that does not directly relate to the primary key, these columns should be removed. Non-key fields do not depend on other non-key fields in the table.

The following link is from the Microsoft support site:

Description of the database normalization basics

The following link is by Steve Litt and his tips for normalization:

Normalization

Creation of SQL

IBM developed SQL in 1970, originally called SEQUEL (Structured English Query Language). Relational Software, Inc. first implemented SQL commercially in 1979, and is known today as the Oracle Corporation. ANSI, the American National Standards Institute, published the first set of standards on SQL, and it has had several revisions since then.

SQL is predominant and accepted as the industry standard database language, supported by most major DBMSs. All SQL programs are portable among most DBMSs.

There are slightly different rules for coding SQL in some DBMSs. The code requires minimal modification for portability.

There are three major components of SQL:

1. DML, Data Manipulation Language, enables you to retrieve, update, add, or delete data in a database.
2. DDL, Data Definition Language, enables you to create and modify the database. For example, the ALTER statement lets the database table design be modified.
3. DCL, Data Control Language, maintains security for the database.

Statements/Commands

SQL keywords are considered reserved and can only be used for querying the database. The keywords cannot be used to name a database, table, column, or any other part of the database or program.

The SELECT statement is the most common SQL command. It is used for retrieving data from the database and returning it to the user. Example:

SELECT * FROM Customers

A query is a command asking about data from the database in order to extract the data. The syntax rules are very important and must be followed so as not to receive a system error. 

The following link is a portion of a presentation given at a Microsoft TechEd conference in the mid 1990's and according to the paper, still applies today:

Fundamentals of Relational Database Design by Paul Litwin

The following covers the basics of creating a database table:

Creating an Oracle database table

The following discusses upsizing an Access database, the benefits of using a MS SQL Server client/server relational database system, and the Microsoft Open Database Connectivity (ODBC) Data Source Name (DSN)

Introduction to Databases and Microsoft Access 5/8

The following pdf contains an example of a relational database:

http://www.cs.indiana.edu/classes/a114-dger/lastYear/flights.pdf