MySQL Tutorial - page 45


Summary

To round up, here's what we covered in this chapter.

Concepts

  • Entities are things, and relationships are the links between them.

  • Relations or tables hold a set of data in tabular form.

  • Columns belonging to tables describe the attributes that each data item possesses.

  • Rows in tables hold data items with values for each column in a table.

  • Keys are used to identify a single row.

  • Functional dependencies identify which attributes determine the values of other attributes.

  • Schemas are the blueprints for a database.

Design Principles

  • Minimize redundancy without losing data.

  • Insertion, deletion, and update anomalies are problems that occur when trying to insert, delete, or update data in a table with a flawed structure.

  • Avoid designs that will lead to large quantities of null values.

Normalization

  • Normalization is a formal process for improving database design.

  • First normal form (1NF) means atomic column or attribute values.

  • Second normal form (2NF) means that all attributes outside the key must depend on the whole key.

  • Third normal form (3NF) means no transitive dependencies.

  • Boyce-Codd normal form (BCNF) means that all attributes must be functionally determined by a superkey.


Quiz

1:

A superkey is

  1. A minimal key

  2. A foreign key

  3. A set of attributes that can be used to identify a single row in a table

  4. A minimal set of attributes that can be used to identify a single row in a table

2:

If a table is in second normal form

  1. It is also in first normal form

  2. It is also in third normal form

  3. It does not contain any transitive dependencies

  4. It contains attributes that are not fully functionally dependent on the key

3:

If a table is in third normal form

  1. It is also in Boyce-Codd normal form

  2. It contains non-atomic attributes

  3. It does not contain any transitive dependencies

  4. It contains attributes that are not fully functionally dependent on the key

4:

The three kinds of anomalies are

  1. insertion, selection, deletion

  2. insertion, update, deletion

  3. selection, update, deletion

5:

A tuple is

  1. a column

  2. a row

  3. a candidate key

  4. the birthplace of Elvis Presley

  5. a foreign key



Exercises

1:

Normalize the following schema into third normal form:

Orders(customerID, customerName, customerAddress, orderID, orderDate, itemID, itemName, itemQuantity)

2:

Try to design a schema that is in 3NF but not in BCNF.



Answers

Quiz

A1:

C

A2:

A

A3:

C

A4:

B

A5:

B (Elvis was born in Tupelo)


Exercises

A1:

Customers( customerID , customerName, customerAddress)

Orders( orderID , orderDate, graphics/03inl10.gif )

OrderItems( graphics/03inl09.gif , graphics/03inl01.gif , itemQuantity)

Items( itemID , itemName)

A2:

There are many possible answers ”just check that yours corresponds to the appropriate normalization rules.



Next

In Chapter 4, "Creating Databases, Tables, and Indexes," we will take a database schema and turn it into an actual MySQL database.