CST363 - Week 21

"A column depends on the key, the whole key, and nothing but the key, so help me Codd." 

The 3rd normal form is when every column that is not a key depends on the primary key only. The goal of normalization is to divide and subdivide each table into its smallest useful components where each table is about one entity and each piece of data is stored once, thus reducing data redundancy and improving efficiency. The third normal form achieves while keeping the data stored in a way where it will be logical and useful for the database users' needs.

If an index will speed up data retrieval, why is it not a good idea to index every column? 

While indexes do speed up data retrieval, they decrease the efficiency of INSERT, UPDATE and DELETE statements, as the indexes must be updated when these statements are executed. Therefore, it is best to only use indexes as needed; an index should be created when a column will be used often in searches and joins and updated infrequently.

What is an SQL view and why is it useful ?

An SQL view is a SELECT statement that is stored as a database object in the database.

As outlined in Murach's MySQL, the benefits of views are as follows:

• Design Independence - With views, you can protect the tables from external users and applications.

• Data Security - Ensure security of data by restricting access to all of the data in the table by specifying columns and rows users can view.

• Simplified Queries - You can perform a retrieval operation with a view, which can hide the complexity of the statement.

• Updatability - You can perform UPDATE, INSERT, and DELETE statements on the base table using views.