Introduction
There are many steps that need to be taken to design and create a well designed relational database. By following the steps provided, the general understanding of creating a database will be fulfilled. It is important to note that as a good way to code is to use the same naming conventions throughout as well as syntax in the code. Also, by putting the command GO after each segment of code will make the code run fully.
Creating the Database and Tables
The first step in creating a database is to first actually create the database in SQL with the name being DatabaseName by using the code:
‘Create Database DatabaseName’.
Once the database is created the creator will have to plan out and create the tables that will store the data. It is important to not have repeating variables in the tables as that will be poorly designed. Providing data types to the variables is important when creating the tables. We can create a table with the name of TableName with variables Var1, Var2, and Var3 by using the code:
‘Create Table TableName
(Var1 int,
Var2 nvarchar(100),
Var3 money)
Go’
Adding Constraints to Tables
Once there are tables with different variables we will add constraints to the variables within the tables. Constraints control what data can be entered into a table and what different variables can mean. In all tables we can an identifying variable which has the name of a Primary Key constraint. Then a Foreign Key constraint refers to a primary key in a different table linking the two. There are more constraints by the two different keys are the most important by being able to link relational tables together. We can make Var1 a primary key and Var2 a foreign key in the TableName table using the constraint named pkey and fkey by coding:
‘Alter Table TableName
Add Constraint pkey
Primary Key (Var1);
Go
Alter Table TableName
Add Constraint fkey
Foreign Key (Var2);
Go’
Creating Views for the Tables
Once the table is created and has the appropriate constraints for what you want to do with the database the next step is to create layers of abstraction to avoid using the data directly in the tables. The first abstraction layer to create are views to view the data in the tables. We can create a view name vTableName for our table, TableName by using the code:
‘Create View vTableName
As
Select Var1,
Var2,
Var3
From TableName;
Go’
We can view this table and those variables by using the code:
‘Select * From vTableName;
Go’
Creating Stored Procedures
The next layer of abstraction to create are stored procedures. For each table we create we will want 3 stored procedures. One to Insert data into our table, one to update data in our table, and lastly one to delete data from our table. We also include parameters in the creation of the procedures to indicate what we want to insert, update, or delete. We can create a procedure, named pInsTableName, to insert data into TableName by using the code:
‘Create Procedure pInsTableName
(@Var1 int,
@Var2 varchar(100),
@Var3 money)
As
Insert Into TableName
(Var1, Var2, Var3)
Values (@Var1, @Var2, @Var3);
Go’
We can create a procedure, named pUpdTableName, to update data in TableName by using the code:
‘Create Procedure pUpdTableName
(@Var1 int,
@Var2 varchar(100),
@Var3 money)
As
Update TableName
(Var1, Var2, Var3)
Set Var1= @Var1,
Var2 = @Var2,
Var3 = @Var3
Where Var1 = @Var1;
Go’
We can create a procedure, named pDelTableName, to delete data from TableName by using the code:
‘Create Procedure pDelTableName
(@Var1 int)
As
Delete
From TableName
Where Var1 = @Var1;
Go’
Adding Permissions to Tables, Views, and Procedures
Now that we have created a table to store our data in our database as well as ways to Insert, update, delete, and view our data we need to put permissions on each of our created items. We will use the word Grant or Deny either allow or disallow someone access to the table or layer of abstraction. Here is an example of some restricting and allowing access to our created items to the public user:
‘Deny Select, Insert, Update, Delete On TableName To Public;
Grant Select On vTableName To Public;
Grant Execute On pInsTableName To Public;
Deny Execute On pUpdTableName To Public;
Deny Execute On pDelTableName To Public;
Go’
Summary
By following the examples above the task to create a very simple 1 table database is possible. Adding another table allows there to be relations between tables, through the foreign keys mentioned above.
