Before we get into second normal form (2NF), note that if the primary key is a single column, or all the columns in the table are part of the primary key, then the table is already in second normal form.

A table in second normal form must be in first normal form and have no partial functional dependencies.

A partial functional dependency is when some, but not all of a composite key’s values determine a non-key column’s value.

To avoid this, a second normal form table is fully functionally dependent. That means that all non-key attributes (or columns) must depend on all components of the key. If the table has some non-key attributes that do not depend on all components of the key, break the table up into two or more tables so that, in each table, all non-key attributes depend on all components of the primary key.

To have a table in first normal form (1NF):

> The table must be two-dimensional with rows and columns describing a table object.

> Each row must have data that relates to the table object and no two rows can be identical.
Each table must have a primary key that uniquely identifies every row.
The primary key can consist of one column (simple key) or multiple columns (composite key).

> Each column must have a unique column name and contain data that describes an attribute of the table object and all of the column values must describe only that single attribute.
Each column must be consistent with a single data type. If this column is for a numeric phone number, then all entries in this column can only be numeric phone numbers.

> Each row and column value must be only a single value.

The process of “normalizing” tables into first, second, and third normal form (1NF, 2NF, 3NF) insures that your database will be simple, logical, and not have redundacies or inconsistencies.

First, convert your tables into first normal form (1NF). The normal forms are nested.

Converting to second normal form (2NF) can only be done after doing first normal form (1NF).

Converting to third normal form (3NF) can only be done after doing second normal form (2NF).

There are fourth and fifth normal forms (4NF, 5NF) as well as a domain-key normal form (DKNF) that give a higher degree of protection against modification anomolies.

Normalization is a repetitive process that involves repeatedly splitting and rejoining tables until the database designer is happy with the result.

When designing SQL tables, you go through several logical steps:> Identify all of your tables.

> Decide what columns are to be in each table.

> Decide what column(s) will be used as the primary key so that every row is uniquely identified.
Primary key will be discussed in more detail.

> See that every table has at least one column in common with other columns in other tables in the database.
These common columns provide the logical links to the data in the other tables making this a relational database.

> Now normalize these tables to eliminate irregularities and anomalies.

A database table is a two-dimensional array of columns and rows. Tables normally describe places (such as stores), people (such as customers), objects (such as inventory), or relationships (such as sales transactions).

Let’s say that you wish to create a customer table. You might consider the following attributes:

                    Customer ID
                    Customer First Name
                    Customer Last Name
                    Customer Street Address
                    Customer City
                    Customer State
                    Customer Zip Code
                    Customer Phone Number
                    Customer Credit Limit

You can build an SQL command to create this table. For each column, you specify its name, its data type, and possibly one or more constraints as follows:

          CREATE TABLE CUSTOMER_TABLE (
                        CUST_ID                INTEGER        NOT NULL,
                        FIRST_NAME         CHAR(20),
                        LAST_NAME           CHAR(30)      NOT NULL,
                        STREET_ADDRESS CHAR(30),
                        CITY                       CHAR(20),
                        STATE                    CHAR(2),
                        ZIP_CODE              INTEGER,
                        PHONE_NUMBER    CHAR(14),
                        CREDIT_LIMIT        DECIMAL(11,2)
                        );

Now let’s consider creating a sales transaction table. If you have questions about the sale, you would like to have a phone number. If you put the phone number on the sales transaction, you will be repeating the same phone number on each sales ticket for each item processed, but the customer is buying your product and the phone number would occur only once if it is in the customer table. If you wish to change the phone number, you have only one place to change it if it is in the customer table. Look for the most logical association for locations of data.

A cluster is an SQL session accessing a uniquely named set of catalogs. It is equivalent to an installation of a vendor’s RDBMS system.

A catalog is a uniquely named set of schemas. For very large database systems, such as distributed databases, you may encounter duplicate schemas and a catalog provides a higher hierarchy for grouping.

The schema is a uniquely named set of objects, such as tables, views, system tables, and stored procedures. The system tables contain the self-defining data for describing all of the other objects. The objects and data are owned by a given user. The owner (or creator) then determines who has access privileges to the data and objects.

The tables and views contain columns and rows of data.

You can qualify an object name as follows: CATALOG_NAME.SCHEMA_NAME.TABLE_NAME

> With most RDBMS systems, the cluster controls who gets access to the data.

> With Oracle and SQL Server, the catalog controls who gets access to the data.
For Oracle and SQL Server, you can think of the catalog as equivalent to an instance.

Database objects are tables, views, schemas, catalogs, clusters, and other objects required by your RDBMS system.

Database clusters contain catalogs.

Catalogs contain schemas.

Schemas contain tables and views.

Tables and Views contain
columns and rows.
As discussed earlier under SQL is DML, DDL, & DCL, there are three components to the SQL language:

Data Manipulation Language (DML) is the part of SQL that retrieves and updates the database tables.
This includes the SELECT, INSERT, UPDATE, and DELETE commands discussed previously.

Data Definition Language (DDL) is the part of the SQL language used to define and maintain database objects.
This includes the CREATE, ALTER, and DROP commands used in this part of the course.

Data Control Language (DCL) is used to provide security for your database.
This includes the GRANT and REVOKE commands discussed later.

All of these statements show the many ways to solve the same problems and give the same results.         

          SELECT DISTINCT V.VENDOR_NAME
            FROM VENDOR_TBL V
           INNER JOIN INVENTORY_TBL I
                ON V.VNDR_ID = I.VNDR_ID;

          SELECT DISTINCT V.VENDOR_NAME
            FROM VENDOR_TBL V, INVENTORY_TBL I
          WHERE V.VNDR_ID = I.VNDR_ID;

          SELECT VENDOR_NAME
            FROM VENDOR_TBL V
          WHERE VNDR_ID IN
                     (SELECT VNDR_ID
                        FROM INVENTORY_TBL);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL
          WHERE VNDR_ID = ANY
                     (SELECT VNDR_ID
                        FROM INVENTORY_TBL);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL
          WHERE VNDR_ID = ALL
                     (SELECT VNDR_ID
                        FROM INVENTORY_TBL);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL V
           WHERE EXISTS
                       (SELECT *
                           FROM INVENTORY_TBL I
                         WHERE V.VNDR_ID = I.VNDR_ID);

          SELECT VENDOR_NAME
            FROM VENDOR_TBL V
          WHERE 0 <
                      (SELECT COUNT(*)
                         FROM INVENTORY_TBL I
                       WHERE V.VNDR_ID = I.VNDR_ID);

With all of these choices, don’t use confusing statements to show how smart you are.
Use the simplest and most understandable solution for easy maintenance.
Trust me, your employer will appreciate your simple solutions and how quickly you can make changes.

You want to know what vehicles are available from the ‘SPACE TRANSPORTS INC’ store that are below the store’s average price.

                    SELECT DESCRIPTION, PRICE
                      FROM INVENTORY_TBL
                    WHERE PRICE <
                               (SELECT AVG(PRICE)
                                  FROM INVENTORY_TBL
                                WHERE STOR_ID IN
                                           (SELECT STOR_ID
                                              FROM STORE_TBL
                                            WHERE STORE_NAME =
                                                        ‘SPACE TRANSPORTS INC’));

First, the innermost select finds the STOR_ID for the STORE_NAME = ‘SPACE TRANSPORTS INC’.

Result: ‘S001′

The next outer select finds the average price for vehicles in store ‘S001′.

                        960,400.00
                        128,200.00
                        569,500.00
                    + 417,000.00
                     2,075,100.00 / 4 = Result: 518,775.00

The outer select retrieves the DESCRIPTION and PRICE, where the price is below the average price of 518,775.00.

Result:

Most RDBMS systems have no limit on the number of subselect levels.

EXISTS does not try to compare on values, so it has no comparison operators.

It is common to use SELECT * in an EXISTS subselect, because the columns values do not matter.
EXISTS is simply testing for the existence of any rows, so format the subselect any way you like.
It does not matter how many columns or rows are returned by the subselect — one or a thousand.

If the subselect returns at least one row, EXISTS is true and NOT EXISTS is false.

If the subselect returns no rows, EXISTS is false and NOT EXISTS is true.

If the subselect returns a row that is null, EXISTS is true and NOT EXISTS is false.

> While EXISTS can have a simple subselect, it is normally associated with a Correlated Subselect.
See correlated subselect.