Database Systems Chapter 4

CS 4210 Module 4- Database Design using Normalization

How to assess table structure

-determine multivalued dependencies, functional dependencies, candidate keys, primary keys, foreign keys, ref integrity constraints

-count number of columns and rows (i think this is the first step)

If the database is updatable, we want it normalized in

BCNF. If the database is read only we may not use BCNF

Updateable Databases

operationa databases of a company, modification anomalies and inconsistencies are priority

Advantages of Normalization

no mod anomalies, no duplicate data, data integrity, paster queries

Disadvantages of Normalization

more complicated SQL

extra DBMS work = slower applications

when not to use BCNF

zip codes

Read-Only database

nonoperational database using data extracted from operational databases in data warehouses. For querying, reporting, and data mining. Used in BI Systems and data warehouses

Denormalization

joining of the data in normalized tables prior to the storing. Used on previously normalized tables to increase performance

How is Denormalization different from unnormalized tables

The benefits of denormalized tables are only realized on data otherwise normalized. Denormalizing improves read performance at the expense of write performance

Is denormalization good for read only tables

no, processing speed is more important

customized tables

read only db designed with many copies of the same data with each copy customized for specific applications

normalization vs DB Normalization

organizing data w tables vs make relational DB without redundancies

common design problems; multivalue, multicolumn problem

occurs when multiple values of an attribute are stored in more than one column

solution: use separate table to store them

common design problems; inconsistent values

same data, slightly different forms (different coding, spelling, etc)

to check this: ref integrity check

SQL GROUP BY clause

common design problems; missing values

missing or null value hasnt been provided. ie value might be null because not appropriate, appropriate but unknown, appropriate and known, but never entered.

how to check:

SQL IS NULL operatorcommon design problems;

common design problems; general purpose remarks column

columns with ‘comments’ or ‘notes’

important data in a verbose way