MySQL Tutorial - page 45

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

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.

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 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.

1:	A superkey is A minimal key A foreign key A set of attributes that can be used to identify a single row in a table 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 It is also in first normal form It is also in third normal form It does not contain any transitive dependencies It contains attributes that are not fully functionally dependent on the key
3:	If a table is in third normal form It is also in Boyce-Codd normal form It contains non-atomic attributes It does not contain any transitive dependencies It contains attributes that are not fully functionally dependent on the key
4:	The three kinds of anomalies are insertion, selection, deletion insertion, update, deletion selection, update, deletion
5:	A tuple is a column a row a candidate key the birthplace of Elvis Presley a foreign key

Normalize the following schema into third normal form:

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

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


A1:	Customers( customerID , customerName, customerAddress) Orders( orderID , orderDate, ) OrderItems( , , itemQuantity) Items( itemID , itemName)

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