Database management and System Development ppt

dbms helps in development of a database

1. METROPOLITAN
INTERNATIONAL UNIVERSITY
•Course Unit: Database Systems
Development
Metropolitan International University

2. Once a Logical Database structure has been
developed, it is then essential to plan and implement
the Physical Design (the actual design).
This procedure, includes the definition of each table,
including the fields, their data types, primary key and
relevant foreign keys
There are many data types used when creating tables.
The Physical Structure of a
Database

3. Physical Structure of a
Database
Data type Explanation Example
varchar Used to store variable length textName
Numeric (e.g.
Integer, Float)
Used to store numbers Patient_ID
Datetime Used to store date / time valuesDate of Birth
char Used to store fixed length textFlag (Y/N)
boolean Used to define boolean records0 / 1

4. Datetime vs Timestamp
•The datetime attribute, allows you to store dates in the
following format yyyy-mm-dd hh:mm:ss to reformat the date
you must then use specific functions in your scripting
language. datetime usually gets issues if calculations are to
be involved
•The timestamp is the number of second that have passed
since 1970-01-01 00:00:01, therefore by storing dates as
timestamps we can easily calculate if a specific date is
before or after another date.
•The drawback of using a timestamp is that in its current
incarnation it can only record data up to 2038-01-19 03:14:07

5. DATABASE SECURITY
•1
ST
-changing the database administrator's password, it is
empty by default: Use the following command to change it:
SET PASSWORD FOR
root@localhost=PASSWORD('new_password');
•2
nd
– Delete the default user:
>drop database test; //Testing database
>use mysql;
>delete from db; //table for default user
>delete from user where not (host="localhost" and
user="root");

6. DATABASE SECURITY
•Change the default name of administrator's account (root),
to a different one to avoid brute-force and dictionary attacks
on the administrator's password.
•update user set user="mydbadmin" where user="root";
•Create user bit3@localhost;
•DROP USER kim@'%';
•SET PASSWORD FOR root@localhost = PASSWORD( ’must' );
•Create user bit@localhost identified by ”bit”;

7. PHYSICAL DESIGN - SQL
Most Database vendors provide tools which enable you to
easily create and design databases.
Such software lulls IT professionals into a sense of false
security as when major problems occur these tools
typically do not work.
To be a good Database Administrator (DBA), you must
know how to manage and query databases from the
command line.
We shall cover these tools in later lectures, once you are
capable of using SQL effectively.

8. What is SQL?
SQL or Structured Query Language was developed in
1970 by IBM as a method of interfacing with
Relatational Database Management Systems
(RDBMS).
Today it is the de-facto standard, an ISO standard
and ANSI standard.
This means 99.9% of RDBMS systems utilise SQL as
their interfacing language.

9. What is SQL?
The language, covers not only the ability to query
information held within a database but also:
Data definition,
Data manipulation,
Security
Referential integrity
and
Data integrity

10. The SQL command set
•SELECT – The most common command. Used to retrieve
data from a database
•INSERT, UPDATE, DELETE – These commands are used to
enter new rows, change existing rows and remove unwanted
rows from tables in the database. They are known as DML or
Data Manipulation Language commands.
•CREATE, ALTER, DROP, TRUNCATE – These commands
are used to set up, change and remove data structures such
as tables. They are known as DDL or Data Definition
Language commands
•GRANT , REVOKE – These commands are used to give or
remove access rights to the SQL database and the structures
within it.

11. The SQL command set
The Structure of SQL statements:
Each statement category has a specific syntax and
is made up of sub clauses which help you to build
up the statement for example:
SELECT select_list
[INTO new_table ]
FROM table_source
[WHERE search_condition ]
[GROUP BY group_by_expression ]
[HAVING search_condition ]
[ORDER BY order_expression [ ASC | DESC ] ]

12. Data Definition Language
•Once a Logical and Physical design for a database
have been developed, the next step is of course to
create it.
•Of course this can be done with the Tools provided
by the vendor but these tools use the commands we
shall learn today to Create and Modify the database
design.
•You can practice these commands using MySQL
and the Command Line Client

13. Data Definition Language

14. Data Definition Language -
CREATE
The CREATE statement is used to Create
content / objects, this includes:
•A new Database
•A new Table
•A new View
•A new Index
•A new Stored Procedure

15. Data Definition Language -
CREATE
Creating a Database:
The syntax for creating a database is as
follows:
CREATE {DATABASE | SCHEMA}
[IF NOT EXISTS] db_name
[create_specification]
... create_specification: [DEFAULT] CHARACTER SET [=]
charset_name | [DEFAULT] COLLATE [=] collation_name

16. Data Definition Language -
CREATE
Creating a Database:
•CREATE denotes the event / command
•DATABASE / SCHEMA denote the attribute to be
created (Schema is a synonym for Database)
•IF NOT EXISTS checks if the Database already
exists
•The db_name denotes where you should place
the name of the Database you require

17. •A character set is a set of symbols and encodings.
• A collation is a set of rules for comparing characters in a
character set.
• Let's make the distinction clear with an example of an imaginary
character set.
•Suppose that we have an alphabet with four letters: “A”, “B”, “a”,
“b”. We give each letter a number: “A” = 0, “B” = 1, “a” = 2, “b” =
3. The letter “A” is a symbol, the number 0 is the encoding for
“A”, and the combination of all four letters and their encodings is
a character set.
Data Definition Language - CREATE

18. Data Definition Language -
CREATE
Suppose that we want to compare two string values, “A” and “B”. The
simplest way to do this is to look at the encodings: 0 for “A” and 1 for
“B”. Because 0 is less than 1, we say “A” is less than “B”. What we've
just done is apply a collation to our character set. The collation is a set
of rules (only one rule in this case): “compare the encodings.” We call
this simplest of all possible collations a binary collation.
But what if we want to say that the lowercase and uppercase letters
are equivalent? Then we would have at least two rules:
(1) treat the lowercase letters “a” and “b” as equivalent to “A” and
“B”;
(2) then compare the encodings. We call this a case-insensitive
collation. It is a little more complex than a binary collation.

19. Data Definition Language -
CREATE
Creating a Database:
•The create_specification is optional but allows for
the addition of extra conditions that can be used
in different situations for example the character
set to be used, this can be DEFAULT i.e. the
installation setting or a character set installed on
the system e.g. chinese is gbk
http://dev.mysql.com/doc/refman/5.1/en/charset mysql.html

20. Data Definition Language -
CREATE
For example:
CREATE DATABASE
IF NOT EXISTS bit3204_databases
DEFAULT CHARACTER SET cp1250

21. Data Definition Language -
CREATE
To show the database was created, we can
use the SHOW command to show all
databases. e.g SHOW DATABASES

22. Creating a Table:
When you wish to CREATE a TABLE you must stipulate
the DATABASE that is to be utilised, this can be done by
changing the current database via to USE command like
so:
Data Definition Language - CREATE

23. Creating a Table:
OR by including the name of the database during the
CREATE TABLE statement like so:
Data Definition Language - CREATE

24. Creating a Table:
The Basic Syntax of the CREATE TABLE statement is
as follows:
CREATE TABLE IF NOT EXISTS database.table_name
(
row_1 datatype [NOT NULL | NULL] [AUTO_INCREMENT]
[UNSIGNED]
row_2 datatype etc…
PRIMARY KEY (row_name)
)
ENGINE = database_engine
Data Definition Language - CREATE

25. Creating a Table:
For a complete breakdown of the Syntax, please refer to
the MySQL manual or follow this link:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
Again once the SQL has been implemented the table
can be viewed with the SHOW command.
•SHOW TABLES : displays all tables
•SHOW COLUMNS FROM table_name : displays the columns
for a particular table
Data Definition Language - CREATE

26. Data Definition Language - CREATE

27. Data Definition Language -
ALTER
ALTER statements can be used to modify a number of attributes of a
database including:
•The Database itself
•Tables
•Views
and so on

28. Data Definition Language -
ALTER
ALTER DATABASE
This command allows you to modify a number of entities such
as the CHARACTER SET.
A list of available character sets can be found by using the
'show character set;' command:
An example statement would be:
Note: It is unusual for a production database to be altered as
it may effect its operation and stability

29. Data Definition Language -
ALTER
ALTER TABLE
The ALTER TABLE command allows a user to change the structure of a table. New
columns can be added with the ADD clause. Existing columns can be modified
with the MODIFY clause. Columns can be removed from a table by using the
DROP clause.
The syntax of the ALTER TABLE command
ALTER TABLE tbl_name
ADD (
column definition [ BEFORE col_name ]
{ , column definition [BEFORE col_name ] }* )
DROP ( col_name { , col_name }* )
MODIFY ( column definition { , column definition }* ) ;

30. Data Definition Language -
ALTER
The RENAME statement
Sometimes it is applicable to RENAME content, the
RENAME DATABASE & RENAME TABLE statements
exists but are deemed as dangerous therefore we use
the ALTER statement to carry out such renaming
events.
ALTER TABLE `bit3204_databases`.`new_table` RENAME
TO `bit3204_databases`.`new_table_renamed`;
Renaming a database is not recommended

31. Data Definition Language -
ALTER
For example:

32. Data Definition Language -
ALTER
The DROP statement
The DROP statement is used to DELETE databases,
tables, views etc.
Note: The DELETE statement is only used to empty
content from tables NOT remove the actual table.
The Syntax for dropping an entity is
DROP DATABASE database_name or
DROP TABLE table_name etc

33. Data Definition Language - ALTER
The DROP statement

34. Simple SQL Statements -
SELECT
•To look at the data in our tables, we use the select (SQL)
statement.
•The result of this statement is always a new table that we
can view with our database client software or use with
programming languages to build dynamic web pages or
desktop applications.
•Although the result table is not stored in the database like
the named tables are, we can also use it as part of other
select statements.
•The WHERE clause, is appended to define which rows
should be selected

35. The SELECT statement:
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
Simple SQL Statements - SELECT

36. Simple SQL Statements -
SELECT
The SELECT statement – Retrieving ALL columns
To retrieve ALL columns the asterix symbol (*) is used
The Syntax is as follows:
SELECT * FROM table_name
For example:
SELECT * FROM patients
NB: Avoid using the word “all” instead of (*)

37. The SELECT statement – Retrieving specific columns
To retrieve specific columns, each column is included
and separated by a comma (,)
The Syntax is as follows:
SELECT column1,column2,column3 FROM table
For example:
SELECT surname FROM patients
SELECT forename, surname, date_of_birth FROM patients
Simple SQL Statements - SELECT

38. The SELECT statement – Conditional retrieval
To retrieve / filter specific rows the WHERE clause is used.
This enables you to retrieve the information you desire.
A WHERE clause can :
• Use Comparison Operators (= > < !=)
• Use String Comparisons (eg. LIKE)
• Use Logical Operators (NOT ! AND OR)
• Retrieving a Range of Values (BETWEEN)
• Using a List of Values as Search Criteria (IN)
Simple SQL Statements - SELECT

39. The SELECT statement – Conditional retrieval
= Equal to
> Greater than
< Less than
<> Not equal to
=< Equal or less than
>= Greater than or equal
! NOT (used for !=, !<, !> etc.
IS [NOT] NULL Tests for a NULL entry
[NOT] BETWEEN Allows the specification of a range of values
[NOT] IN Allows the specification of a series of values
[NOT] LIKE Character string comparison
AND All conditions must apply
OR Only one condition must apply
NOT No conditions must apply
Simple SQL Statements - SELECT

40. The SELECT statement
% operator is used in searching for data in a database in
combination with the “LIKE” operator.
The Syntax is as follows:
SELECT column1,column2,column3
FROM table
WHERE [condition]
Simple SQL Statements - SELECT

41. Simple SQL Statements -
SELECT
Examples:
You can select peoples Data that start or end with any
letter, contains any set of letters by use of the "%" sign
 
both before and after when specifying a pattern.
SELECT * FROM Reg WHERE FirstName LIKE ‘R%‘;
We can select all peoples data that contains certain
characters and starts with a specific character
SELECT * FROM Reg WHERE FirstName LIKE ‘%cha%’
AND FirstName LIKE ‘r%’;
The following statement selects all names that start
with any character followed by ‘ic’
SELECT * FROM Reg WHERE FirstName LIKE '_ic%‘;

42. Simple SQL Statements -
SELECT
The SELECT statement – Ordering the results
It is sensible to ORDER data that has been retrieved. This
assists the reader. The data can be ordered by 1 or
many columns in Ascending / Descending format.
The Syntax is as follows:
SELECT column1,column2
FROM table
[WHERE clause]
ORDER BY column1 [ASC,DESC], column2 [ASC,DESC]
Note: By default data is displaced in Ascending order therefore ASC does not
need to be stipulated.

43. Simple SQL Statements -
SELECT
The SELECT statement – Ordering the results
Example
SELECT FirstName, LastName FROM Reg
WHERE Gender=‘Male’
ORDER BY FirstName DESC;

44. Simple SQL Statements -
SELECT
The SELECT statement – Special statements (DISTINCT)
The DISTINCT command can be used to ensure that non
duplicate rows can be returned, so we can for example retrieve
all unique Surnames within a system.
The Syntax is as follows:
SELECT DISTINCT column FROM table
Example:
SELECT DISTINCT FirstName FROM Reg

45. Simple SQL Statements -
SELECT
The SELECT statement – Special statements (ISNULL)
ISNULL is used to replace a NULL value in a results set.
For example if a place of birth is not known, instead of NULL
being displayed “No place of birth” can be displayed instead.
The Syntax is as follows:
SELECT ISNULL(column1, replacement value), column2
FROM table
Example:
SELECT forename, surname, ISNULL(place_of_birth, “Birthplace Unknown”)
FROM patients

46. Simple SQL Statements -
SELECT
The SELECT statement – Concatenation & Alias
A column may not be named as you require, or for a particular
result set you may wish to show two columns as a single
column.
The syntax for an alias is as follows:
SELECT column1 AS new_name FROM table_Name;
The syntax for concatenation is as follows:
SELECT concat (column1, column2) AS new_name FROM table_Name
OR
SELECT column1 + ‘ ‘ + column2 AS new_name FROM
table_Name;

47. Simple SQL Statements -
SELECT
The SELECT statement – Concatenation & Alias
For example:

48. Constraints
•Constraints enforce data Integrity on our tables
•NOT NULL specifies that the column does not accept NULL
values.
•CHECK constraints enforce domain integrity by limiting the
values that can be placed in a column. Specifies a Boolean
(evaluates to TRUE or FALSE)
• Example: shows the creation of a named constraint,
phone_limit, that further enforces the domain of the Phone
by ensuring that only numbers within a specified range are
entered for the key.
•Primary, Foreign and Unique keys enforce constraints

49. CHECK Constraint
To specify a named constraint, use the key word CONSTRAINT
followed by an identifier followed by the constraint definition
as bellow.
CREATE TABLE students (
Student_id int auto_increment PRIMARY KEY,
Student_name char(40),
Student_address char(60),
Phone varchar(15),
Age int,
CONSTRAINT phone_limit CHECK (Phone BETWEEN 7<> 15) );

50. SQL Primary key:
This constraint defines a column or combination of columns which
uniquely identifies each row in the table. The Syntax at column
level:
columnName datatype [CONSTRAINT constraint_name] PRIMARY
KEY
CREATE TABLE employee OR
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);
CREATE TABLE employee
( id number(5) CONSTRAINT
emp_id_pk PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10));

51. CHECK Constraint
You can specify a constraint on as many columns as you wish,
for example
CREATE TABLE products (
product_no int primary key,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0
AND price > discounted_price) );

52. Foreign Key/Referential integrity
•Column_name datatype FOREIGN KEY REFERENCES table_1
(referenced_column) ON DELETE NO ACTION;
•Example
sales_id int FOREIGN KEY REFERENCES customers
(customer_id) ON DELETE NO ACTION
•NO ACTION specifies that the deletion fails with an error.
•CASCADE specifies that all the rows with foreign keys
pointing to the deleted row are also deleted.

53. Functions
•Functions
–Character & String functions
–Mathematical functions
–Date functions
–Other functions
•Grouping & summarising data
•Joining tables
•Sub queries

54. Complex SQL Statements –
Functions
•Functions make the basic query far more
powerful and are used to :
–Perform calculations on data
–Modify data items
–Manipulate output for rows
–Alter date formats for display
–Convert column data types

55. Complex SQL Statements –
Functions
Character & String functions
String and character functions, can be used within a statement to
handle data as and when required.
For example you may wish to capitalise certain information, ensure
that any whitespaces are removed from the string and so on.
Examples of useful functions can be found in the following slides.
A full list of functions can be found under Functions and Operators
in the MySQL manual or at the following address:
http://dev.mysql.com/doc/refman/5.1/en/functions.html

56. Complex SQL Statements –
Functions
Basic Text Functions:
- UPPER: When used within the SELECT statement, the UPPER
function outputs the selected fields as upper case.
-LOWER: When used within the SELECT statement, the LOWER
function outputs the selected fields as lower case.
For example the following statement will return all names in UPPER
case:

57. Complex SQL Statements –
Functions
Basic Text Functions:
SOUNDEX: This is a very useful function that can be used
alongside the WHERE clause to retrieve information that sounds
like the given clause. This is useful when there are for example a
number of way of spelling an name:

58. Complex SQL Statements –
Functions
Basic Text Functions:
Without the SOUNDEX function, the Database will display only the
specified information:
NOTE: The SOUNDEX function is not 100% accurate, as it only uses
limited algorithms but it is very useful nevertheless.

59. Complex SQL Statements –
Functions
Mathematical Functions:
Mathematical functions can also be applied via SQL, which allows for calculations to be carried out when saving and retrieving data.
Examples:
COS() – Returns the Cosine of a number
TAN () – Returns a Tangent
SIN() – Returns the sine
and so on

60. Complex SQL Statements –
Functions
Date Functions:
Date Functions enable the users to get specific information relating
to the a date in the format they require.
For example, you may want to extract only the year from a date
stored in the database. To do this the YEAR function is used:

61. Complex SQL Statements –
Functions
Date Functions:
Another example of a date function may be that we wish to return
the day that a give date was on, to do this we use the DAYNAME
function:

62. Complex SQL Statements –
Functions
Other Functions:
There are many additional functions that can be used in SQL. A number
of important ones are listed and explained below.
Using a SELECT statement we can see the data as encrypted

63. Grouping & Summarising Data
The GROUP BY clause:
The GROUP BY clause is used to group rows from a result set together.
On its own the result set looks much like the one returned when using the DISTINCT clause.
But when used alongside aggregate functions the GROUP BY clause allows us to return important information. For example the number of people who have a Particular Surname etc.
For example, on the next slides, we shall use 3 statements. One without a GROUP BY, one with GROUP BY and one with GROUP BY and the aggregate function COUNT.

64. Grouping & Summarising Data
The GROUP BY clause continued:
Example 1: No GROUP BY in the statement

65. Grouping & Summarising Data
The GROUP BY clause continued:
Example 2: GROUP BY in the statement – Returns
distinct values

66. Grouping & Summarising Data
The GROUP BY clause continued:
- Example 3: GROUP BY in the statement & COUNT
function

67. Grouping & Summarising Data
The HAVING clause:
The HAVING clause, works alongside the GROUP BY clause and is
essentially used as the WHERE clause when a GROUP BY is to be
used. For example, we could list only Surnames where there are more
than 1 occurrence in the database
Note: The HAVING clause only works if you have defined the field name in
the SELECT clause, unlike WHERE which does not

68. Grouping & Summarising Data
Function Use
AVG Average of values
COUNT Total number (as int)
COUNT_BIG Total number (as bigint)
MAX Maximum value
MIN Minimum value
SUM Sum of all values
The Aggregate functions used in Grouping / in General:
Note: Aggregate functions come into the “Other” grouping of functions and may be useful if working out for
example Averages and so on.
The table below shows a list of other Aggregate Functions:

69. Grouping &
Summarising Data
An example aggregate function, would be to check the Year the Latest
comment was sent in our database using the MAX and YEAR functions:

70. Views – What are they?Views – What are they?
A View in SQL is a Virtual Table based upon a result set.A View in SQL is a Virtual Table based upon a result set.
We use views to access data in a format that we access We use views to access data in a format that we access
frequently.frequently.
For example, we may want to display the daily For example, we may want to display the daily
workers/Students attendance after registration in form of a workers/Students attendance after registration in form of a
reportreport
Note: The data retrieved is always up to date as the View Note: The data retrieved is always up to date as the View
retrieves data from the original tables.retrieves data from the original tables.

71. Views – Creating a ViewViews – Creating a View
To create a view we attach a SELECT statement to the CREATE VIEW statementTo create a view we attach a SELECT statement to the CREATE VIEW statement
The syntax is as follows:The syntax is as follows:
CREATE VIEW name_of_view ASCREATE VIEW name_of_view AS
SELECT column1, column2SELECT column1, column2
FROM table_name(s)FROM table_name(s)
WHERE conditionsWHERE conditions

72. Views – Creating a ViewViews – Creating a View
For example, we may want to view customers who buy goods daily from our For example, we may want to view customers who buy goods daily from our
business. business.
This is going to be repeating daily, we need to create a view .This is going to be repeating daily, we need to create a view .
To avoid writing a complex query of selecting from various tables we can write To avoid writing a complex query of selecting from various tables we can write
a view and select from it.a view and select from it.
The first example below shows a JOIN without a view which is repeatedly The first example below shows a JOIN without a view which is repeatedly
written daily .written daily .
The second example shows how we can create a view and get the same The second example shows how we can create a view and get the same
resultsresults

73. Views – Creating a ViewViews – Creating a View
Example of a JOINExample of a JOIN
SELECT c.FirstName, c.LastName, s.Date, s.SalesAmmount SELECT c.FirstName, c.LastName, s.Date, s.SalesAmmount
FROM Sales s INNER JOIN Customers c ON C.CostomerId = s.CustomerId && FROM Sales s INNER JOIN Customers c ON C.CostomerId = s.CustomerId &&
curdate()=s.Date ORDER BY s.date DESC;curdate()=s.Date ORDER BY s.date DESC;

74. Views: Creating a ViewViews: Creating a View
The View name is called todaysCustomers.The View name is called todaysCustomers.
NB: A view is automatically updated each time NB: A view is automatically updated each time
data is added so u don’t need to update itdata is added so u don’t need to update it

75. Views: Selecting from a viewViews: Selecting from a view
Here the selection is from a view and the Here the selection is from a view and the
output is the same as the one in the Joinoutput is the same as the one in the Join

76. Example 2: Creating ViewsExample 2: Creating Views
We might have a promotion in our company to reduce on our We might have a promotion in our company to reduce on our
products with a discount of 2 percent per sale.products with a discount of 2 percent per sale.
Each time a customer buys a product we get the total paid Each time a customer buys a product we get the total paid
minus the amount for discount to get the total discounted priceminus the amount for discount to get the total discounted price
We can create a view to show the price for each product We can create a view to show the price for each product
bought without altering the table but working with the view bought without altering the table but working with the view
only as bellow. only as bellow.

77. Example 2:Example 2:
Discount=2% * Original PriceDiscount=2% * Original Price
Cost = Original price - discountCost = Original price - discount
Get a discounted total price.Get a discounted total price.
Without the view, it would be as in the next slide:Without the view, it would be as in the next slide:
Use Use LIMITLIMIT command to display a specified number of records. command to display a specified number of records.

78. Ex2: without viewEx2: without view

79. Ex2: Create viewEx2: Create view

80. Views – Modifying a ViewViews – Modifying a View
Sometimes it is essential to modify the content of a view, for example: We Sometimes it is essential to modify the content of a view, for example: We
may wish to also see the mobile phone numbers of the customers who have may wish to also see the mobile phone numbers of the customers who have
bought goods. bought goods.
To modify a view we use the ALTER VIEW command.To modify a view we use the ALTER VIEW command.

81. Views – Removing a ViewViews – Removing a View
To remove a VIEW we use the same To remove a VIEW we use the same
command as we do to remove a table i.e. command as we do to remove a table i.e.
we use the DROP command, we use the DROP command,
The keyword VIEW replaces the word TABLE The keyword VIEW replaces the word TABLE
however ;however ;

82. TriggersTriggers
Triggers – Creation and utilisationTriggers – Creation and utilisation

83. TriggersTriggers
Triggers are pre-written scripts that get Triggers are pre-written scripts that get
“triggered/executed” when a particular event occurs.“triggered/executed” when a particular event occurs.
In databases these are very important as they can trigger In databases these are very important as they can trigger
off a number of events as and when an event occurs. off a number of events as and when an event occurs.
You can of course simulate triggers in a programming You can of course simulate triggers in a programming
language but by pre-creating them in the database code language but by pre-creating them in the database code
works fasterworks faster

84. TriggersTriggers
A really good example of trigger use is for audit purposes. A really good example of trigger use is for audit purposes.
For example we may wish to record the name of the user For example we may wish to record the name of the user
who has added information to a database.who has added information to a database.
Note: MySQL support for triggers was only introduced in Note: MySQL support for triggers was only introduced in
version 5.0.2 and it is still relatively imperfectversion 5.0.2 and it is still relatively imperfect

85. TriggersTriggers
The general syntax of CREATE TRIGGER is :The general syntax of CREATE TRIGGER is :
CREATE TRIGGER trigger_name trigger_time CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name trigger_event ON tbl_name
FOR EACH ROWFOR EACH ROW
trigger_statementtrigger_statement
Trigger_timeTrigger_time means trigger action time. It can means trigger action time. It can
be BEFORE or AFTER. be BEFORE or AFTER.
Trigger_eventTrigger_event specifies the statement that specifies the statement that
executes the trigger. The trigger_event can be executes the trigger. The trigger_event can be
any of the DML Statement : INSERT, UPDATE, any of the DML Statement : INSERT, UPDATE,
DELETE.DELETE.

FOR EACH ROWFOR EACH ROW – loops all the records that are to be – loops all the records that are to be
returnedreturned

86. TriggersTriggers
Trigger_statementTrigger_statement have the statement that executes when the have the statement that executes when the
trigger fires trigger fires
if you want to execute multiple statement the you have to use the if you want to execute multiple statement the you have to use the
BEGIN…ENDBEGIN…END compound statement compound statement
We can refer to the columns of the table that are associated with We can refer to the columns of the table that are associated with
trigger by using the trigger by using the OLDOLD and and NEWNEW keywords. keywords.
OLD.column_name OLD.column_name is used to refer the column of an existing row is used to refer the column of an existing row
before it is before it is deleteddeleted or or updatedupdated
and and
NEW.column_nameNEW.column_name is used to refer to the column of a new row that is used to refer to the column of a new row that
is is insertedinserted or an or an after updateafter update existing row. existing row.

87. TriggersTriggers
In INSERT trigger we can use only In INSERT trigger we can use only
NEW.column_nameNEW.column_name because there is no old because there is no old
row row
In DELETE trigger we can use only In DELETE trigger we can use only
OLD.column_nameOLD.column_name because there is no new because there is no new
row. row.
In UPDATE trigger we can use both, In UPDATE trigger we can use both,
OLD.column_nameOLD.column_name is used to refer the is used to refer the
columns of a row before it is updated and columns of a row before it is updated and
NEW.Column_nameNEW.Column_name is used to refer the is used to refer the
column of the row after it is updated. column of the row after it is updated.

88. TriggersTriggers
We can not have the two trigger for a given We can not have the two trigger for a given
table, which have the same trigger table, which have the same trigger action timeaction time
and and eventevent. .
For Instance : we cannot have two BEFORE For Instance : we cannot have two BEFORE
INSERT triggers for same table. But we can INSERT triggers for same table. But we can
have a BEFORE INSERT and BEFORE UPDATE have a BEFORE INSERT and BEFORE UPDATE
trigger for a same table.trigger for a same table.

89. Triggers: ExampleTriggers: Example
We have a table called We have a table called employeesemployees and we and we
want to reflect the changes made by an want to reflect the changes made by an
update statement in a separate table called update statement in a separate table called
employees_edit employees_edit by using a trigger to do this by using a trigger to do this
function:function:
CREATE TABLE employees ( CREATE TABLE employees (
employeeNumberemployeeNumber int(11) NOT NULL, int(11) NOT NULL,
lastNamelastName varchar(50) NOT NULL, varchar(50) NOT NULL,
firstNamefirstName varchar(50) NOT NULL, varchar(50) NOT NULL,
extensionextension varchar(10) NOT NULL, varchar(10) NOT NULL,
emailemail varchar(100) NOT NULL, varchar(100) NOT NULL,
officeCodeofficeCode varchar(10) NOT NULL, varchar(10) NOT NULL,
reportsToreportsTo int(11) default NULL, int(11) default NULL,
jobTitlejobTitle varchar(50) NOT NULL, varchar(50) NOT NULL,
PRIMARYPRIMARY KEYKEY (employeeNumber) (employeeNumber)
) ;) ;

90. Triggers: ExampleTriggers: Example
To keep the changes of employee's data in another table To keep the changes of employee's data in another table
whenever data of an employee's record is changed we whenever data of an employee's record is changed we
create a new table called create a new table called employees_audit employees_audit  
to keep track
 
to keep track
the changes.the changes.
Create table employees_audit(Create table employees_audit(
id int(11) AUTO_INCREMENT,id int(11) AUTO_INCREMENT,
employeeNumber int(11) NOT NULL,employeeNumber int(11) NOT NULL,
lastname varchar(50) NOT NULL,lastname varchar(50) NOT NULL,
changedon datetime DEFAULT NULL,changedon datetime DEFAULT NULL,
action varchar(50) DEFAULT NULL,action varchar(50) DEFAULT NULL,
PRIMARY KEY (id)) ;PRIMARY KEY (id)) ;

91. Example: Trigger creationExample: Trigger creation
In order to keep track of the changes for In order to keep track of the changes for last namelast name of of
employees we can create a trigger that is fired before we employees we can create a trigger that is fired before we
make any update on the make any update on the employeesemployees table. >>> table. >>>Nxt slideNxt slide
NB: A trigger only works after an action or event has NB: A trigger only works after an action or event has
occurred depending on what it was created on i.e either occurred depending on what it was created on i.e either
an UPDATE, INSERT or DELETEan UPDATE, INSERT or DELETE
You can use a trigger to perform various actions that You can use a trigger to perform various actions that
would complicate work for example you might decide to would complicate work for example you might decide to
reduce/increase the salaries of employees by a certain reduce/increase the salaries of employees by a certain
percentage for a specific period of timepercentage for a specific period of time

92. Triggers: ExampleTriggers: Example
DELIMITER $$ DELIMITER $$
CREATE TRIGGER employee_update
 
CREATE TRIGGER employee_update
 
BEFORE UPDATE ON employees BEFORE UPDATE ON employees
FOR EACH ROW FOR EACH ROW
BEGIN BEGIN
INSERT INTO employees_audit SET action = INSERT INTO employees_audit SET action =
'update', employeeNumber = 'update', employeeNumber =
OLD.employeeNumber, lastname = OLD.employeeNumber, lastname =
OLD.lastname, changedon = NOW(); OLD.lastname, changedon = NOW();
END$$ END$$
DELIMITER ;DELIMITER ;

93. Triggers: Delimiter keywordTriggers: Delimiter keyword
In Sql, each statement ends with a delimiter In Sql, each statement ends with a delimiter
usually called Termination(;)usually called Termination(;)
If you are working with nomal If you are working with nomal
queries(update,delete,insert…), you might not queries(update,delete,insert…), you might not
specify the delimiter keyword.specify the delimiter keyword.
With SQL, DELIMETER is specified to alert sql With SQL, DELIMETER is specified to alert sql
where your trigger starts from by use of where your trigger starts from by use of
DELIMITER$$ and closing it with DELIMITER DELIMITER$$ and closing it with DELIMITER
[space] ;[space] ;
Secondly, it is used in triggers because we can Secondly, it is used in triggers because we can
write multiple sql statements with the (;) write multiple sql statements with the (;)
termination inside a trigger so sql will see these termination inside a trigger so sql will see these
as statements in a trigger as statements in a trigger

94. Triggers: Testing the triggerTriggers: Testing the trigger
UPDATE employeesUPDATE employees
SET jobTitle=“Sales Manager”SET jobTitle=“Sales Manager”
WHERE officeCode=2456;WHERE officeCode=2456;
To view employees information that has To view employees information that has
been changed, we select from the been changed, we select from the
employees_audit table.employees_audit table.

95. Example 2: TriggersExample 2: Triggers
We can Use IF…THEN and ELSEIF statement in We can Use IF…THEN and ELSEIF statement in
a trigger.a trigger.
If it is students Marks and you need to assign If it is students Marks and you need to assign
them grades basing on the marks inserted, you them grades basing on the marks inserted, you
might still use a trigger to automatically do might still use a trigger to automatically do
that by use of that by use of procedure programmingprocedure programming
But in our example, we want to But in our example, we want to
reduce/Increase the salaries of employees reduce/Increase the salaries of employees
before adding them to the table before adding them to the table

96. Example 2: TriggersExample 2: Triggers
Structure of salaries tables is:Structure of salaries tables is:

97. Example 2: Create triggerExample 2: Create trigger
Insert salaries In the table and select to see Insert salaries In the table and select to see
the outputthe output

98. TriggersTriggers
Triggers are stored in the database as plain Triggers are stored in the database as plain
text files in the database folder, you can view text files in the database folder, you can view
the trigger by using the following command.the trigger by using the following command.
Show triggers;Show triggers;

99. Dropping a triggerDropping a trigger
The syntax isThe syntax is
DROP TRIGGER table_name.Trigger_nameDROP TRIGGER table_name.Trigger_name
DROP TRIGGER salaries.add_reduceDROP TRIGGER salaries.add_reduce

100. Assignment 2 & take Home testAssignment 2 & take Home test
Bellow is an EER diagram for “Bellow is an EER diagram for “BIT Employee Database”, BIT Employee Database”,
use it to answer the questions in the next slideuse it to answer the questions in the next slide

101. AssignmentAssignment
In groups of 3, Implement the above logical design in MS-Access 2007/2010, In groups of 3, Implement the above logical design in MS-Access 2007/2010,
MySQL and Java and implement the following;MySQL and Java and implement the following;
◦Connect the MS-Access Database to MySQL using Open Database Connectivity Connect the MS-Access Database to MySQL using Open Database Connectivity
(ODBC) drivers(ODBC) drivers
In MySQL, perform the following,In MySQL, perform the following,
◦ Use views to perform repetitive tasksUse views to perform repetitive tasks
◦ Generate a trigger that will capture deleted, updated and new inserted Generate a trigger that will capture deleted, updated and new inserted
information.information.
◦Develop a html interface, use php to submit records in a databaseDevelop a html interface, use php to submit records in a database
◦Create a stored procedure of your choiceCreate a stored procedure of your choice
Deadline: 2 weeks from now – Hand in a soft copyDeadline: 2 weeks from now – Hand in a soft copy