Learning How To Use (MS-SQL) DDL Statements to create SQL OBJECTS(Tables, Constraints, Triggers, Procedures).

For better understanding, we would be building a Shopping_dat database from scratch using Strictly SQL syntax.

The syntax used in this article will be strictly SQL syntax. This article does not treat the building as a full Application.

Scenario: The Shopping_dat database management system used in this article has the following Schema & entities :

Schema: Items, Suppliers, HumanResources, Transactions,

Entities:Employee,ItemDetails,OrderDetails,SupplierDetails,ProductCategory.

Table Designs For ALL the Entities

Table 1 : Table Design for ItemDetails and Employee
Table 2: Table Design for OrderDetails
Table 3: Table Design for Supplier & ProductCategory

BASIC SQL OBJECT DEFINITIONS

SCHEMA: A schema in SQL is a collection of database objects associated with a database. The username of a database is called a “schema owner” (owner of logically grouped structures of data). A schema always belongs to a single database, whereas a database can have a single or multiple schemas. It is also very similar to separate namespaces or containers, which store database objects. It includes various database objects, including your tables, views, procedures, indexes, etc.

Syntax

[DEFAULT CHARACTER SET char_set_name]

[PATH schema_name[, …]]

[ ANSI CREATE statements […] ]

[ ANSI GRANT statements […] ];

ENTITIES:

SQL PROCEDURES

SQL Procedures A procedure in SQL (often referred to as a stored procedure) is a reusable unit that encapsulates the specific business logic of the application. A SQL procedure is a group of SQL statements and logic that are compiled and stored together to perform a specific task.

CREATE [ OR REPLACE] PROCEDURE procedure_name [

(parameter_name [IN | OUT | IN OUT] type [ ])]

{IS | AS }

BEGIN [declaration_section]

executable_section

//SQL statement used in the stored procedure

END

GO

SQL TRIGGERS

Triggers are the SQL codes that are automatically executed in response to certain events on a particular table. These are used to maintain the integrity of the data. John is the marketing officer of a company. When a new customer’s data is entered into the company’s database, he has to send the welcome message to each new customer. If there are one or two customers, John can do it manually, but what if there are more than a thousand? Well, in such a scenario, triggers come in handy.

Create Trigger Trigger_Name

(Before | After) [ Insert | Update | Delete]

on [Table_Name]

[ for each row | for each column ]

[ trigger_body ]

👋🏻 Enjoyed this article thus far? Kindly click on the FOLLOW button on the top left of this article to follow me for more upcoming articles.

PROJECT SOLUTIONS

A)Creating The Schemas & Database

create database Shopping_dat
create Schema Items
create Schema Suppliers
create Schema HumanResources
create Schema Transactions
go

B) Creating The Tables(Solutions)
The Create statement was used to create all the tables for the database as follows:

FULL Create Table Syntaax.

CREATE TABLE [ database_name . [ schema_name ] .] table_name

(

{ <column_definition> | <computed_column_definition> }

[IDENTITY (SEED, INCREMENT)]

[ <table_constraint> ] [ ,…n ]

) [ ON { partition_scheme_name ( partition_column_name ) | filegrou| “default” } ]

[ { TEXTIMAGE_ON { filegroup | “default” } ] [ ; ]

More on how to create Database table here

C) Table Relationships
To enforce table relationships all tables represented in the database must have a unique column called the primary key column. After they can now be related using the foreign key constraints. The following steps A and B show how the table relationship is enforced in the MS SQL server.

(a) Enforcing Primary Key Relationship: Is defined on a column or a set of columns whose values uniquely identify all the rows in a table. Ensures entity integrity. Primary Key can be enforced in one of two ways (1) Table level (2) Using the ALTER STATEMENT. for this solution the Alter statement was used to enforce the Primary Key Constraint as follows:

Syntax:

ALTER TABLE [ database_name . [ schema_name ]. | schema_name . ]

table_name

{ ALTER COLUMN column_name { [ NULL | NOT NULL ] } | [ WITH { CHECK | NOCHECK } ]

ADD COLUMN <column_definition>

{ ADD CONSTRAINT constraint_name constraint_type

Read more on Alter Table Statement here

(b)Enforcing Foreign keys Constraints(Table Relationships) : Removes the inconsistency in two tables when the data in one table depends on the data in another table. Always refers to the primary key column of another table, as shown in the following figure.

D) Enforcing Check Constraints(Domain Integrity): Enforces domain integrity by restricting the values to be inserted in a column. The solutions to some of the integrity constraints such as Phone number format,(UnitPrice, QuantityReceived, ReorderLevel, ReorderQuantity greater than zero), Category Name being(Household, sports, accessories, clothing),(QuantityReceived less than QuantityOrdered),(ReceivedDate greater than OrderedDate) as outlined in the table designs using check constraint is as follows:

E)Enforcing Triggers: The check constraints couldn't provide integrity for some of the table designs business rules such as (i) QuantityReceived should be added to QuantityInHand in the Items table (ii) When a record is inserted into the table, QuantityInHand in the Items table should be updated automatically. Therefore SQL Trigger was handy in this regard.

Summary

Getting the Projects Steps in this article mastered will ensure you have Programmatical workflow knowledge of how SQL works.

Quodos!!! 💪🏾 You have come to the End of this Article.
Guess you Enjoy the article? Kindly click on the
FOLLOW button at the left corner of this page for more related and impactful articles by me.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store