PHP Ch 6 Database Design

3rd Normal Form (3NF)

A relation that is in 1NF and 2NF, and in which no non-primary key attribute is transitively dependent on the primary key. That is, all non-key elements are fully dependent on the primary key

Indexes

Placed before a column 4 indexes: INDEX, UNIQUE, FULL TEXT, PRIMARY KEY INDEX_TYPE index_name (column1, column2) i.e. PRIMARY KEY (user_id)

FOREIGN KEY (column) REFERENCES parentTableName (column) Tip: This goes within a CREATE TABLE statement

Setting the foreign key constraint

SHOW ENGINES;

The ____ ____ command, when executed on the MySQL server, will reveal not only the available storage engines but also the default storage engine.

schema

The first step in creating a database is to first design the database structure; aka creating the database ___ based primarily on normalization

2nd Normal Form

The table is in 1st normal form All the non-key columns are dependent on the table's primary key. The primary key provides a means to uniquely identify each row in a table. When we talk about columns depending on the primary key, we mean, that in order to find a particular value, such as what color is Kris' hair, you would first have to know the primary key, such as an EmployeeID, to look up the answer. 1. Once you identify a table's purpose, then look at each of the table's columns and ask yourself, "Does this column serve to describe what the primary key identifies?" 2. If you answer "yes," then the column is dependent on the primary key and belongs in the table. If you answer "no," then the column should be moved different table. 3. Assign or create new primary keys When all the columns relate to the primary key, they naturally share a common purpose, such as describing an employee.

primary key

a field that uniquely identifies a record in a table Ideally an integer 1. Always have a value (NOT NULL) 2. Never changes 3. Unique value for each record

1st Normal Form

calls for the elimination of repeating groups (Autonomy) Eliminate repeating groups in individual tables. Create a separate table for each set of repeating groups. Identify each set of related data with a primary key

Relationship types:

one to one (i.e. SSN to Person); not common one to many (i.e. Male to Persons); Most common many to many - should be avoided b/c leads to redundancies. Create an intermediary table to establish a 1-to-many relationship