Learning How To Use (MS-SQL) DDL Statements to create SQL OBJECTS(Tables, Constraints, Triggers, Procedures).
In this article, we will look at how to create SQL Objects such as Tables, Procedures, Triggers, Indexes, and Security Objects(), Also, Some DML Statements would also be employed to test our created SQL objects.
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
BASIC SQL OBJECT DEFINITIONS
The following are the basic definitions of the SQL objects described in this article.
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
CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name[, …]]
[ ANSI CREATE statements […] ]
[ ANSI GRANT statements […] ];
ENTITIES:
An entity is an object that exists. In database administration, an entity can be a single thing, a person, a place, or an object. Data can be stored about such entities. In database administration, only those things about which data will be captured or stored are considered entities. If you’re creating a database of your employees, examples of entities you may have include employees and health plan enrollment.
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
This section uses the table designs listed above in Table 1,2,3 to present the SQL solutions to the problem in question.
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
If you got to the end of this article, Quodos !! ♚ You should now be able to create SQL objects like Schema, Database, Tables, Constraints, Triggers, and Procedures.
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.
