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.
- Catalog of user defined types.
- Consistent Data type and length
- Consistent Defined nullability
- Consistent Predefined rules *
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<span style="color: #008000;">-- Which database to use. USE [BSA] GO -- Delete existing user defined type (udt) IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'UDT_ZIP' AND ss.name = N'RECENT') DROP TYPE [RECENT].[UDT_ZIP] GO -- Add new user defined type CREATE TYPE [RECENT].[UDT_ZIP] FROM [char](5) NULL GO -- Delete existing default IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[DF_ZIP_CODE]') AND OBJECTPROPERTY(object_id, N'IsDefault') = 1) DROP DEFAULT [RECENT].[DF_ZIP_CODE] GO -- Add new default CREATE DEFAULT [RECENT].[DF_ZIP_CODE] AS '00000'; GO -- Delete existing rule IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[RL_ZIP_CODE]') AND OBJECTPROPERTY(object_id, N'IsRule') = 1) DROP RULE [RECENT].[RL_ZIP_CODE] GO -- Add new rule CREATE RULE [RECENT].[RL_ZIP_CODE] AS @UDT_ZIP NOT LIKE '999%'; GO -- Bind default to user defined type EXEC sp_bindefault '[RECENT].[DF_ZIP_CODE]', '[RECENT].[UDT_ZIP]' GO -- Bind default to user defined type EXEC sp_bindrule '[RECENT].[RL_ZIP_CODE]', '[RECENT].[UDT_ZIP]' GO -- Remove bindings -- EXEC sp_unbindefault '[RECENT].[UDT_ZIP]' -- EXEC sp_unbindrule '[RECENT].[UDT_ZIP]'</span> |
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.
- ‘A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.’
- ‘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.’
- ‘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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
<span style="color: #008000;">-- * -- * Address table -- * -- Which database to use. USE [BSA] GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_ADDRESS]') AND type in (N'U')) DROP TABLE [RECENT].[TBL_ADDRESS] GO -- Add new table CREATE TABLE [RECENT].[TBL_ADDRESS]( [ADDRESS_ID] [int] IDENTITY(1,1) NOT NULL, [STREET1_TXT] [varchar](50) NOT NULL, [STREET2_TXT] [varchar](50) NULL, [CITY_TXT] [varchar](50) NULL, [STATE_TXT] [varchar](2) NULL, [COUNTRY_TXT] [varchar](10) NOT NULL, [ZIP_CODE_TXT] [RECENT].[UDT_ZIP] NOT NULL, [ROW_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [MODIFIED_DTE] [datetime] NOT NULL, [MODIFIED_NM] [varchar](20) NOT NULL ) ON [PRIMARY] GO -- Add default values ALTER TABLE [RECENT].[TBL_ADDRESS] ADD CONSTRAINT DF_TA_COUNTRY_TXT DEFAULT ('USA') FOR [COUNTRY_TXT]; ALTER TABLE [RECENT].[TBL_ADDRESS] ADD CONSTRAINT DF_TA_ROW_GUID DEFAULT (newsequentialid()) FOR [ROW_GUID]; ALTER TABLE [RECENT].[TBL_ADDRESS] ADD CONSTRAINT DF_TA_MODIFIED_DTE DEFAULT (getdate()) FOR [MODIFIED_DTE]; ALTER TABLE [RECENT].[TBL_ADDRESS] ADD CONSTRAINT DF_TA_MODIFIED_NM DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]; GO</span> |
The snipet below defines the merit badge table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<span style="color: #008000;">-- * -- * Merit badge table -- * -- Which database to use. USE [BSA] GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_MERIT_BADGE]') AND type in (N'U')) DROP TABLE [RECENT].[TBL_MERIT_BADGE] GO -- Add new table CREATE TABLE [RECENT].[TBL_MERIT_BADGE]( [BADGE_ID] [int] IDENTITY(1,1) NOT NULL, [BADGE_NM] [varchar](50) NOT NULL, [BADGE_DESC] [varchar](500) NOT NULL, [EAGLE_IND] [char](1) NOT NULL, [ROW_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [MODIFIED_DTE] [datetime] NOT NULL, [MODIFIED_NM] [varchar](20) NOT NULL ) ON [PRIMARY] GO -- Add default values ALTER TABLE [RECENT].[TBL_MERIT_BADGE] ADD CONSTRAINT DF_TMB_ROW_GUID DEFAULT (newsequentialid()) FOR [ROW_GUID]; ALTER TABLE [RECENT].[TBL_MERIT_BADGE] ADD CONSTRAINT DF_TMB_MODIFIED_DTE DEFAULT (getdate()) FOR [MODIFIED_DTE]; ALTER TABLE [RECENT].[TBL_MERIT_BADGE] ADD CONSTRAINT DF_TMB_MODIFIED_NM DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]; GO</span> |
The snipet below defines the boy scout rank table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<span style="color: #008000;">-- * -- * Rank table -- * -- Which database to use. USE [BSA] GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_RANK]') AND type in (N'U')) DROP TABLE [RECENT].[TBL_RANK] GO -- Add new table CREATE TABLE [RECENT].[TBL_RANK]( [RANK_ID] [int] IDENTITY(1,1) NOT NULL, [RANK_DESC] [varchar](50) NOT NULL, [ROW_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [MODIFIED_DTE] [datetime] NOT NULL, [MODIFIED_NM] [varchar](20) NOT NULL ) ON [PRIMARY] GO -- Add default values; ALTER TABLE [RECENT].[TBL_RANK] ADD CONSTRAINT DF_TR_ROW_GUID DEFAULT (newsequentialid()) FOR [ROW_GUID]; ALTER TABLE [RECENT].[TBL_RANK] ADD CONSTRAINT DF_TR_MODIFIED_DTE DEFAULT (getdate()) FOR [MODIFIED_DTE]; ALTER TABLE [RECENT].[TBL_RANK] ADD CONSTRAINT DF_TR_MODIFIED_NM DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]; GO</span> |
The snipet below defines the boy scout table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<span style="color: #008000;">-- * -- * Scout table -- * -- Which database to use. USE [BSA] GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]') AND type in (N'U')) DROP TABLE [RECENT].[TBL_SCOUT] GO -- Add new table CREATE TABLE [RECENT].[TBL_SCOUT]( [SCOUT_ID] [int] IDENTITY(1,1) NOT NULL, [RANK_ID] [int] NOT NULL, [ADDRESS_ID] [int] NOT NULL, [FIRST_NM] [varchar](50) NOT NULL, [LAST_NM] [varchar](50) NOT NULL, [MIDDLE_LTR] [varchar](1) NULL, [CELL_PHONE_TXT] [varchar](12) NULL, [EMAIL_TXT] [varchar](50) NULL, [BIRTH_DTE] [datetime] NOT NULL, [JOIN_DTE] [datetime] NOT NULL, [LEFT_DTE] [datetime] NULL, [ROW_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [MODIFIED_DTE] [datetime] NOT NULL, [MODIFIED_NM] [varchar](20) NOT NULL ) ON [PRIMARY] GO -- Add default values ALTER TABLE [RECENT].[TBL_SCOUT] ADD CONSTRAINT DF_TS_ROW_GUID DEFAULT (newsequentialid()) FOR [ROW_GUID]; ALTER TABLE [RECENT].[TBL_SCOUT] ADD CONSTRAINT DF_TS_MODIFIED_DTE DEFAULT (getdate()) FOR [MODIFIED_DTE]; ALTER TABLE [RECENT].[TBL_SCOUT] ADD CONSTRAINT DF_TS_MODIFIED_NM DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]; GO</span> |
The snipet below defines a M-to-N relationship between scout and merit badge. This is commonly referred to as a junction table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<span style="color: #008000;">-- * -- * Scout earns merit badge table -- * -- Which database to use. USE [BSA] GO -- Delete existing table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]') AND type in (N'U')) DROP TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] GO -- Add new table CREATE TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE]( [BADGE_ID] [int] NOT NULL, [SCOUT_ID] [int] NOT NULL, [ROW_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [MODIFIED_DTE] [datetime] NOT NULL, [MODIFIED_NM] [varchar](20) NOT NULL ) ON [PRIMARY] GO -- Add default values ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] ADD CONSTRAINT DF_TSEB_ROW_GUID DEFAULT (newsequentialid()) FOR [ROW_GUID]; ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] ADD CONSTRAINT DF_TSEB_MODIFIED_DTE DEFAULT (getdate()) FOR [MODIFIED_DTE]; ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] ADD CONSTRAINT DF_TSEB_MODIFIED_NM DEFAULT ('BSA - SYSTEM') FOR [MODIFIED_NM]; GO</span> |
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.
Very nice, i suggest webmaster can set up a forum, so that we can talk and communicate.
Thanks for the share!
Nancy.R
Appreciated the share!
Hellen
Wohh exactly what I was looking for, thanks for posting.