SQL DDL: A Mind Map

Putting together ideas in a mind map is a very good way to correlate and connect different concepts, quickly and effectively!
Here you see a mind map of DDL, if you are studying sql, I think is would useful to consolidate most of the necessary concepts dealing with Database and Tables, about triggers, proc, etc other posts will come to address them in details…

DDL

1 physical objects

1.1 files
1.2 filegroups

2 logical objects

2.1 database
2.2 columns
2.3 view
2.4 tables
2.5 schemaA schema is a database object that includes statements for creation of tables, views, and user privileges.

3 database engine

3.1 system database
3.1.1 master
3.1.2 tempdb
3.1.3 modelDuring the creation of a new database, Database Engine uses the model database as a template.
3.1.4 msdb
3.1.5 resource
3.2 user database
3.3 db role

4 CREATE

4.1 DB
CREATE DATABASE db_name [ON [PRIMARY] { file_spec1} ,...] [LOG ON {file_spec2} ,...] [COLLATE collation_name] [FOR {ATTACH | ATTACH_REBUILD_LOG } ]
4.1.1 BP*** If you have a database object that should exist in each user database, you should create that object in the model database first. *** The name of a database object can generally contain four parts in the form: [server_name.[db_name.[schema_name.]]]object_name object_name is the name of the database object. schema_name is the name of the schema to which the object belongs. server_name and db_name are the names of the server and database to which the database object belongs.
4.1.2 db_namedb_name is the name of the database.
4.1.3 f ile_spec1f ile_spec1 represents a file specification, which includes further options such as the logical name of the file, the physical name, and the size.
4.1.4 dboA login account of Database Engine that is used to create a database is called a database owner.
4.1.5 log ondbo uses the LOG ON option to define one or more files as the physical destination of the transaction log of the database
4.1.6 collateto specify the default collation for the database.
4.1.7 for attachFOR ATTACH option specifies that the database is created by attaching an existing set of operating system files.
attachingAll data of a database can be detached and then attached to the same or another database server.
sp_attach_db
4.1.8

USE master;
CREATE DATABASE sample;
USE master;
CREATE DATABASE projects ON (NAME=projects_dat, FILENAME = 'C:\projects.mdf', SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5) LOG ON (NAME=projects_log, FILENAME = 'C:\projects.ldf', SIZE = 40, MAXSIZE = 100, FILEGROWTH = 10);
4.2 TABLE
CREATE TABLE table_name (col_name1 type1 [NOT NULL| NULL] [{, col_name2 type2 [NOT NULL| NULL]} …])
4.2.1 table_nametable_name is the name of the created base table.
4.2.2 BP*** You should always specify the table name together with the corresponding schema name. That way you can eliminate possible ambiguities. *** Using explicit constraint names is strongly recommended. The search for an integrity constraint can be greatly enhanced if an explicit name for a constraint is used.
4.2.3 IDENTITY propertyA column with the IDENTITY property allows only integer values, which are usually implicitly assigned by the system
4.2.4 Integrity constraintsconstraints, which are used to check the modification or insertion of data with benefits of handling integrity constraints by the DBMS are the following: c Increased reliability of data c Simple maintenance c Reduced programming time
Declarative integrity constraints
CONSTRAINT option
DEFAULT clausedefault value of the column
UNIQUE clauseEach candidate key is defined using the UNIQUE clause [CONSTRAINT c_name] UNIQUE [CLUSTERED | NONCLUSTERED] ({ col_name1} ,…)
candidate keysone column or group of columns of the table have unique values and therefore can be used as the primary key. All columns or groups of columns that qualify to be primary keys are called candidate keys
CLUSTEREDthe physical order of rows is specified using the indexed order of the column values.
NONCLUSTERED
PRIMARY KEY clause[CONSTRAINT c_name] PRIMARY KEY [CLUSTERED | NONCLUSTERED] ({col_name1} ,…) The primary key of a table is a column or group of columns whose values are different in every row N> PRIMARY KEY column must be NOT NULL, and its default value is CLUSTERED.
CHECK clause[CONSTRAINT c_name] CHECK [NOT FOR REPLICATION] expression The check constraint specifies conditions for the data inserted into a column.
USE sample;
CREATE TABLE customer (cust_no INTEGER NOT NULL, cust_group CHAR(3) NULL, CHECK (cust_group IN ('c1', 'c2', 'c10')));
FOREIGN KEY clause[CONSTRAINT c_name] [[FOREIGN KEY] ({col_name1} ,…)] REFERENCES table_name ({col_name2},…) [ON DELETE {NO ACTION| CASCADE | SET NULL | SET DEFAULT}] [ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}] A foreign key is a column or group of columns in one table that contains values that match the primary key values in the same or another table
referencing tableThe table that contains the foreign key is called the referencing table
parent tablethe table that contains the corresponding primary key is called the parent table or referenced table.
USE sample;
CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL, CONSTRAINT prim_works PRIMARY KEY(emp_no, project_no), CONSTRAINT foreign_works FOREIGN KEY(emp_no) REFERENCES employee (emp_no));
Referential IntegrityA referential integrity enforces insert and update rules for the tables with the foreign key and the corresponding primary key constraint
ON DELETE and ON UPDATE OptionsDatabase Engine can react differently if the values of the primary key of a table should be modified or deleted.
NO ACTION
CASCADEfrom the referencing table if the corresponding value in the primary key has been updated
SET NULL
SET DEFAULT
USE sample;
CREATE TABLE works_on1 (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL, CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no), CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no) REFERENCES employee(emp_no) ON DELETE CASCADE, CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADE);
Procedural integrity constraintshandled by triggers
4.2.5

USE sample;
CREATE TABLE employee (emp_no INTEGER NOT NULL, emp_fname CHAR(20) NOT NULL, emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL); CREATE TABLE department(dept_no CHAR(4) NOT NULL, dept_name CHAR(25) NOT NULL, location CHAR(30) NULL); CREATE TABLE project (project_no CHAR(4) NOT NULL, project_name CHAR(15) NOT NULL, budget FLOAT NULL); CREATE TABLE works_on (emp_no INTEGER NOT NULL, project_no CHAR(4) NOT NULL, job CHAR (15) NULL, enter_date DATE NULL); 
USE sample;
CREATE TABLE projects (project_no CHAR(4) DEFAULT 'p1', project_name CHAR(15) NOT NULL, budget FLOAT NULL CONSTRAINT unique_no UNIQUE (project_no)); 
 USE sample;
CREATE TABLE employee (emp_no INTEGER NOT NULL, emp_fname CHAR(20) NOT NULL, emp_lname CHAR(20) NOT NULL, dept_no CHAR(4) NULL, CONSTRAINT prim_empl PRIMARY KEY (emp_no));
4.3 VIEWCREATE VIEW statement belongs to the data manipulation language (DML) rather than to the data definition language (DDL). a view is derived from one or more base tables.
4.4 INDEX new index on a specified table to allow efficient access to the data stored on a disk
4.5 PROCEDURE A stored procedure is a special kind of sequence of statements written in Transact-SQL
4.6 TRIGGER A trigger is a database object that specifies an action as a result of an operation
4.7 SYNONYM A synonym is a local database object that provides a link between itself and another object managed by the same or a linked database server.
4.7.1
USE sample;
CREATE SYNONYM prod FOR AdventureWorks.Production.Product;

5 MODIFY

5.1 DBThe ALTER DATABASE statement changes the physical structure of a database
5.1.1 Change the name
sp_rename
5.1.2 Add or remove one or more database files
5.1.3 Add or remove one or more log files
USE master;
GO
ALTER DATABASE projects ADD FILE (NAME=projects_dat1, FILENAME = 'C:\projects1.mdf', SIZE = 10, MAXSIZE = 100, FILEGROWTH = 5);

ADD LOG FILE

CREATE FILEGROUP
5.1.4 Add or remove filegroups
5.1.5 Modify file or filegroup properties
Change the logical name of a file
NEWNAME optio
MODIFY FILE clause
Increase the value of the SIZE property
Change the FILENAME, MAXSIZE, or FILEGROWTH property
Mark the file as OFFLINE
5.1.6 Set database options
DByou can alter the model database to change the default values of specific options.
State options
User access to the database
SINGLE_USER
RESTRICTED_USER
MULTI_USER
status of the database
ONLINE, OFFLINE
EMERGENCY
read/write modus
READ_ONLY
READ_WRITE
Auto options
AUTO_CLOSE
SQL options
sp_dboption
5.2 TABLEThe ALTER TABLE statement modifies the schema of a table.
5.2.1 Add or drop one or more new columns
ALTER TABLE
USE sample;
ALTER TABLE employee ADD telephone_no CHAR(12) NULL;
USE sample;
ALTER TABLE employee DROP COLUMN telephone_no;
5.2.2 Modify column properties
ALTER COLUMN
Data type
Nullability
USE sample;
ALTER TABLE department ALTER COLUMN location CHAR(25) NOT NULL;
5.2.3 Add or remove integrity constraints
ADD CONSTRAINT
USE sample;
CREATE TABLE sales (order_no INTEGER NOT NULL, order_date DATE NOT NULL, ship_date DATE NOT NULL);
ALTER TABLE sales ADD CONSTRAINT order_check CHECK(order_date <= ship_date);
USE sample;
ALTER TABLE sales ADD CONSTRAINT primaryk_sales PRIMARY KEY(order_no);
USE sample;
ALTER TABLE sales DROP CONSTRAINT order_check;
5.2.4 Enable or disable constraints
sp_helpconstraint
WITH NOCHECK
5.2.5 Rename tables and other database objects
sp_rename
USE sample;
EXEC sp_rename @objname = department, @newname = subdivision ***
5.3 PROCEDURE
5.4 VIEW
5.5 SCHEMA
5.6 TRIGGER

6 REMOVE

All Transact-SQL statements that are used to remove a database object have the following general form: DROP object_type object_name

6.1
DROP DATABASE database1 {, ...}

DROP TABLE table_name1 {, ...}

7 Domains

A domain is the set of all possible legitimate values that columns of a table may contain.

7.1 Alias Data TypesAn alias data type is a special kind of data type that is defined by users using the existing base data types.
7.2 CREATE TYPECREATE TYPE [ type_schema_name. ] type_name { [ FROM base_type [ ( precision [ , scale ] ) ] [ NULL | NOT NULL ] ] | [ EXTERNAL NAME assembly_name [ .class_name ] ]}
7.2.1 user-defined table typesUSE sample; CREATE TYPE person_table_t AS TABLE ( name VARCHAR(30), salary DECIMAL(8,2));
7.2.2 CLR Data Typesyou can use one of the .NET languages like C# or Visual Basic to implement the new data type
7.3
USE sample;
CREATE TYPE zip FROM CHAR(5) NOT NULL;
USE sample;
CREATE TABLE customer (cust_no INT NOT NULL, cust_name CHAR(20) NOT NULL, city CHAR(20), zip_code ZIP, CHECK (zip_code BETWEEN 601 AND 99950)),

Reference

Buy this book:

Microsoft SQL Server 2008: A Beginner’s Guide

http://www.amazon.com/Microsoft-SQL-Server-2008-Beginners/dp/0071546383

5 thoughts on “SQL DDL: A Mind Map

  1. Pingback: SQL Queries: A Mind Map « Obar1 aka Mario Amatucci

  2. Pingback: SQL Queries: Table manipulation « Obar1 aka Mario Amatucci

  3. Pingback: SQL DDL: Proc and User Defined Functions « Obar1 aka Mario Amatucci

  4. Pingback: SQL System Catalog: A Mind Map « Obar1 aka Mario Amatucci

  5. Pingback: SQL Views: A Mind Map « Obar1 aka Mario Amatucci

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s