CST363 - Week 3

Database Normalization

Normalization in a relational database is necessary to ensure the integrity of data and avoid data anomalies. Sometimes the tables in databases are not efficient. For example, a single table should not include multiple entities, such as patient, doctor, and pharmacy. Instead, those entities should be split into 3 tables, each containing data unique to those entities. If the entities and their data are in the same table, it makes it very hard to update data and will almost certainly lead to data corruption/duplicate entries. It is better to have unique tables for the entities and link them relationally.

Third normal form (3NF) provides the near-strictest rules for designing database schemas. There is another form that is used on rare occasions, called Boyce-Codd normal form, but generally 3NF is good enough to ensure data integrity. To place a database into 3NF, it must first be placed into 1st normal form, which is a basic set of rules to follow (such as ensuring only 1 value is stored in each column of each row). Then, we place the database into 2nd normal form, which is necessary to remove partial dependencies. Partial dependencies exist when a column does not have a relation to all of the table's primary composite keys. One way to solve this is to move the partially dependent column to a table in which it fits better. Finally, 3NF occurs when all transitive dependencies are eliminated. That is, when a column does not depend at all on its table's primary composite keys, it can be moved to another table. In the end, you end up with the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help you Codd.

SQL Views

An SQL view is like a snapshot of a query. Sometimes when trying to combine tables in complex ways it is easier to create and use a view that holds a partial answer to your question rather than attempting confusing sub-selects and joins. Several views with pieces of the puzzle can be combined to answer a complicated question. Views are also useful when a set of information will be used by several related queries. Rather than repeatedly calling the same select command, a view is created by calling that select statement just one time. This saves times and allows for more efficient queries.