Crafting Databases – Part 3

The CREATE TABLE statement is part of the Data Definition Language (DDL). It is used to define a structure to hold rows of data or tuples if we use Relational Algebra concepts. Each table is defined by a set of fields of a certain data type and precision.

Data Types can be categorized as the following.

  • Exact numerics
  • Approximate numerics
  • Character strings
  • Unicode character strings
  • Binary strings
  • Date and time
  • Other data types

The power of a database comes from creating user defined datatypes (UDT). Some of the benefits of UDT’s are listed below.

  1. Catalog of user defined types.
  2. Consistent Data type and length
  3. Consistent Defined nullability
  4. Consistent Predefined rules *
  5. Consistent Predefined defaults *

* Features to be discontinued in the future.

The snipet below creates a user defined data type for zip codes. Default values and rules are bound to the user defined data type.  The following TSQL statements are used CREATE TYPE, CREATE RULE, and CREATE DEFAULT.

Before I continue my talk with the BSA hypothetical business problem, I need to introduce database normalization. Database Normalization is the process to reduce redundant information from a logical database model, basically a set of tables with relationships between them.

Codd’s original work defined three such normal forms.

  1. ‘A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.’
  2.  ‘A relation R is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key.’
  3.  ‘A relation R is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key.’

In summary, every table should have a primary key which uniquely identifies a row.  Every field should be unique in a table and be dependent upon the primary key.  There should be no repeating like data in a row. Table relationships are expressed by primary keys in Table A and foriegn keys in Table B.

The logical model of for the Boy Scouts of America (BSA) hypothetical database is below.

The DROP TABLE, CREATE TABLE, and ALTER TABLE SQL statments are used to remove an prior tables, create a brand new table, and apply default values to fields.

The snipet below defines the address table.

The snipet below defines the merit badge table.

The snipet below defines the boy scout rank table.

The snipet below defines the boy scout table.

The snipet below defines a M-to-N relationship between scout and merit badge. This is commonly referred to as a junction table.

The key points to remember from this article is that a table should be in third normal form when possible. Fields are from either system data types or user defined data types. Defaults, rules, and constraints are used to make sure that values entered into the table adhere to certain rules.

I will continue this business solution next time by talking about how to maintain integrity within a database.

Related posts

4 Thoughts to “Crafting Databases – Part 3”

  1. Very nice, i suggest webmaster can set up a forum, so that we can talk and communicate.

  2. Thanks for the share!

  3. Appreciated the share!

  4. Wohh exactly what I was looking for, thanks for posting.

Leave a Comment