Stored procedure is an already written Query stored in DB that we can use in our application. If we need to execute same query over and over again you can use stored procedure and it will save your time and line of code also. If you save you query as stored procedure you can execute them from DB command environment as well as from your application environment.
Stored Procedures
Ex: - Execute stored procedure from database’s command environment
EXEC strdproc_Name
Syntax of SP
Comments area
*/
CREATE PROCEDURE [PROCEDURE NAME]
/*
Variable initialize
*/
AS
/*
SQL statement*/
Comment Area
Especially in academic or Business projects it is very good to include comments about procedure. It will provide more readability.
Example comment area:-/*
Name: usp_viewrecords
Description: displays all records and columns in USERLIST table
Author: ABC
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 ABC
*/
Variable Initialize Area
· You need to declare a variable for every element that you need to pass into the procedure from outside.
· Variable must be begin with the ‘@’ Symbol.
· For the best practice use underscore (‘_’) to separate multiple words.
· You need to specify the data type of each and every variable when they declared.
· Data type assigned to the variable should match the data type assigned to the corresponding column in the database.
· Separate variables with comma (‘,’).
Ex: -
@first_name varchar(20),
@pswd varchar(20),
@age int,
@state char(2),
@pswd varchar(20),
@age int,
@state char(2),
So the complete procedure looks like following
/*
Name: usp_addData
Description: Adding records to the UserList table
Author: ABC
Modification Log: Change
Name: usp_addData
Description: Adding records to the UserList table
Author: ABC
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 ABC
*/
Created procedure 7/15/2003 ABC
*/
CREATE PROCEDURE usp_addData
AS
@first_name varchar(20),
@pswd varchar(20),
@age int,
@pswd varchar(20),
@age int,
INSERT INTO UserList(FName,pWord,age,state) VALUES(@first_name, @pswd, @age, @state)
Note:If you don’t have any variables to initialize you can simply ignore the variable initialization part from your SP.
Now you can simply excecute the procedure from DB command environment
EXEC usp_addData ’Thilini’,’123’,24
Views
View is a virtual table.
What is a View
Views generally act as a normal table in data base but the advantage is it does not allocate a memory in DBMS as table.
It only takes a very little space to keep the definition.
How to Create
Syntax:--
CREATE[REPLACE|DELETE] VIEW View_Name
AS
SQL_Statement
Ex:--
CREATE VIEW student_View
AS
SELECT Name
FROM Student_Data INNER JOIN StudentMarks
ON Student_Data.ID=StudentMarks.ID AND StudentMarks.Grade>10
you can also do replace and delete operations on views using same syntax.
AS
SELECT Name
FROM Student_Data INNER JOIN StudentMarks
ON Student_Data.ID=StudentMarks.ID AND StudentMarks.Grade>10
you can also do replace and delete operations on views using same syntax.
Advantages
- Can use to present subset of table data
- No need a n additional space to store.only need very little space for the definition.
- Can use to present multiple tables t a single one so can hide the complexity of queries.
Ex:-Think that you need to select citizen name and NIC of citizen who must be a student and grade must be greater than 10
Simple query:--
SELECT Name,NIC
FROM Citizen_Data
WHERE Name IN
(SELECT Name
FROM Student_Data
INNER JOIN StudentMarks
ON Student_Data.ID=StudentMarks.ID AND StudentMarks.Grade>10)
FROM Citizen_Data
WHERE Name IN
(SELECT Name
FROM Student_Data
INNER JOIN StudentMarks
ON Student_Data.ID=StudentMarks.ID AND StudentMarks.Grade>10)
Using Views:--
CREATE VIEW student_VIew
AS
SELECT Name
FROM Student_Data
INNER JOIN StudentMarks
ON Student_Data.ID=StudentMarks.ID
AND
StudentMarks.Grade>10
-------------------------------------------------------------------------------------------------
Then you can simply write you query using the created view
SELECT Name,NIC FROM Citizen_Data
WHERE Name IN (student_VIew)ßComplexity was hide
WHERE Name IN (student_VIew)ßComplexity was hide
- Can use to present aggregate data Ex:-SUM(mark),AVG(Salary)
- One view can aggregate data from other view.
Ex:
1st view:-
CREATE VIEW spent_View
AS
SELECT Name,spent
FROM spendings
2nd view(Use aggregate data from 1st view):-
CREATE VIEW totalspendings
AS
SELECT Name,SUM(spent) as sum
FROM spent_View
GROUP BY Name
CREATE VIEW spent_View
AS
SELECT Name,spent
FROM spendings
2nd view(Use aggregate data from 1st view):-
CREATE VIEW totalspendings
AS
SELECT Name,SUM(spent) as sum
FROM spent_View
GROUP BY Name
- Changing the data in original table directly affect to the related data in view.
Ex:-
Table
Name | Age | Grade |
Thilini | 24 | 10 |
Senaka | 26 | 11 |
View
Name |
Thilini |
Senaka |
If you change the Thilini to ABC, view data also change automatically.
Name | Age | Grade |
ABC | 24 | 10 |
Senaka | 26 | 11 |
Name |
ABC |
Senaka |
In updatable views data rows in the view must be one to one relationship with Data rows in the underlying tables.
Updatable Views & Read Only Views
For reach this you need to check following constraints before implement the view;
· Select statement must not reference more than one table
· Select statement must not use GROP BY or HAVING clause
· Select statement must not use DISTINCT in the selection list
· Select statement must not reference not updatable views (read only views).
· Select statement must not contain any functions (Ex:-SUM,AVG).
If any view fulfills these constraints we can do the insert, update and delete operations on these views. If any view doesn’t fulfills these constraints we call them as read only views.
Normalization
Normalization is the process of efficiently organizing data in a database and it can optimize the table structure well. There are two main advantages of normalization
· Minimizing redundancy
Ex:-
Table 1:Student Data
Id | Name | Age |
01 | Thilini | 24 |
02 | Vasana | 24 |
03 | Senaka | 26 |
Table 2:Marks
Id | Name | Marks |
01 | Thilini | 90 |
02 | Vasana | 85 |
03 | Senaka | 90 |
Here name of the student is appearing in two tables and it will take unnecessary amount of storage. Here you can simply delete the name column from Marks table and then user can access to marks using student id easily.
· Minimizing the insertion, deletion and update anomalies
o Update anomalies: if you are going to change some data from a table you have to change it in another table also because this data is appear in another table as well.
o Insert anomalies: if you are going to insert a record to a table, Db will not allow inserting this record until you insert another record to some other table as both tables refer the data of this record as same.
o Delete anomalies: if you are going to delete a record from a table, you will lose another data as well.
Ex:-
According to the above example there have two tables for store the student personal data and their marks. But if you need to change the student name you have to change it in both tables. This is an Update anomaly. To avoid this you can use normalization. After normalize the data you can see your table structure as follow.
Table 3: Student Data
Id | Name | Age |
01 | Thilini | 24 |
02 | Vasana | 24 |
03 | Senaka | 26 |
Table 4: Marks
Id | Marks |
01 | 90 |
02 | 85 |
03 | 90 |
First Normal Form
If your record set is in 1st normal form all attributes in the table must be atomic.
Features of the first NF
· All the key attributes are defined
· There are no repeating groups in the table
· All attributes are dependent on the primary key
Table 1.1: Products (not in normal form 1)
Product ID | Product Quantity | Supplier ID | Supplier Name | Supplier Country | Country ID |
1223A | 12 | S-01 | ABC Pvt(Ltd.) | United State | 31 |
13 | S-02 | SPC Pvt(Ltd.) | India | 28 | |
11 | S-05 | Tango Pvt(Ltd.) | Africa | 38 | |
1285G | 100 | S-04 | Richerd Pvt(Ltd.) | Sri Lanka | 22 |
320 | S-03 | Weerodara Pvt(Ltd.) | Sri Lanka | 22 | |
700 | S-02 | SPC Pvt(Ltd.) | India | 28 |
Table 1.2: Products (normal form 1)
Product ID | Product Quantity | Supplier ID | Supplier Name | Supplier Country | Country ID |
1223A | 12 | S-01 | ABC Pvt(Ltd.) | United State | 31 |
1223A | 13 | S-02 | SPC Pvt(Ltd.) | India | 28 |
1223A | 11 | S-05 | Tango Pvt(Ltd.) | Africa | 38 |
1285G | 100 | S-04 | Richerd Pvt(Ltd.) | Sri Lanka | 22 |
1285G | 320 | S-03 | Weerodara Pvt(Ltd.) | Sri Lanka | 22 |
1285G | 700 | S-02 | SPC Pvt(Ltd.) | India | 28 |
Primary Key à(Product ID + Supplier ID)
Second Normal Form
A table is in second normal form if,
· It is in 1NF
· It includes no partial dependencies (no attribute is depending on partial of the primary key- Note. NF2 only applies to tables with a concatenated primary key.)
According to the Table 1.2 supplier name is only depend on Supplier ID. So supplier name can exist without product ID key attribute. If you allow this table to stay as this when you are trying to delete a product one of the supplier data can also lost. So you need to separate this supplier data from this table. And you can keep supplier ID as the foreign key for references.
Table 1.3: Products (normal form 2)
Product ID | Product Quantity | Supplier ID |
1223A | 12 | S-01 |
1223A | 13 | S-02 |
1223A | 11 | S-05 |
1285G | 100 | S-04 |
1285G | 320 | S-03 |
1285G | 700 | S-02 |
Primary Key à(Product ID)
Foreign Key à(Supplier ID)
Table 1.4: Suppliers (normal form 2)
Supplier ID | Supplier Name | Supplier Country | Country ID |
S-01 | ABC Pvt(Ltd.) | United State | 31 |
S-02 | SPC Pvt(Ltd.) | India | 28 |
S-03 | Weerodara Pvt(Ltd.) | Africa | 38 |
S-04 | Richerd Pvt(Ltd.) | Sri Lanka | 22 |
S-05 | Tango Pvt(Ltd.) | Sri Lanka | 22 |
Primary Key à(Supplier ID)
Third Normal Form
A table is in third normal form if,
· It is in 1NF and 2NF
· No attribute of the table is depending on non-key attribute of the table.)
According to the Table 1.4 supplier country is only depend on Country ID and it is not a key attribute. If you are going to add new supplier in Sri Lanka country name and id will repeat again. So you can separate country details from your supplier table and you can add only country id for the supplier table as reference to the country table. If you need to add new supplier with new country you can add this new country to your country table otherwise you can just add your supplier data to the supplier table without repeating country data.
Table 1.5: Suppliers
Supplier ID | Supplier Name | Country ID |
S-01 | ABC Pvt(Ltd.) | 31 |
S-02 | SPC Pvt(Ltd.) | 28 |
S-03 | Weerodara Pvt(Ltd.) | 38 |
S-04 | Richerd Pvt(Ltd.) | 22 |
S-05 | Tango Pvt(Ltd.) | 22 |
Primary Key à(Supplier ID)
Foreign Key à(Country ID)
Table 1.6: Country
Country ID | Country |
31 | United State |
28 | India |
38 | Africa |
22 | Sri Lanka |
Primary Key à(Country ID)
Fourth and Fifth Normal Form
Refer the following example:
A student can have several skills and he can speak in several languages. And single language can speak by multiple students. And also single skill can relate with multiple student. So there have 2 many to many relationships. According to the 4th normal form these 2 relationships should not be represent in a single record. This will course for large number of data repetition.
Table 1.7: Student (Invalid Table)
Student ID | Student Name | Skill ID | Language ID |
These many - many relations should be representing in two separate tables.
Student-Skill Table
Student ID | Skill ID |
Student-Language Table
Student ID | Language ID |
Table Student Table
Student ID | Student Name |
No comments:
Post a Comment