Transcript ALTER TABLE

IT 390 Business Database Administration
Unit 7:
MANAGING, MAINTAINING, AND
OPTIMIZING MICROSOFT SQL
DATABASE SERVER FOR BUSINESS
APPLICATIONS
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 1
Objectives
•
•
•
•
•
•
Identify Data Manipulation Statements for
Applications in a Business Environment.
Utilize Data Manipulation Statements for
Applications in a Business environment.
Explain the need of optimization to improve
database performance.
Perform database activities to optimize the SQL
Server 2000 database.
Identify basic database security features and
roles.
Describe the fundamentals of security in a
Microsoft SQL Server 200 Environment.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 2
The need for queries
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 3
“Select” is very flexible with a lot of options
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 4
Select DISTINCT clause
 Eliminates
any duplicate records from the
result of the query.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 5
Select ORDER BY clause
 This
will order the data by a selected
column. The default is ASCENDING
order………for example, abc or 123.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 6
Select WHERE clause

The WHERE clause selects records (rows of a table) based on
some condition.

There are generally 3 conditions:
Character comparison -> SSN = ‘123456789’
Numeric comparison -> price >= 100
Additional comparison operators are:
> -- greater than
< -- less than
>= -- greater than or equal to
<= -- less than or equal to
<>, != -- not equal to
Case-sensitivity comparison -> size = ‘SMALL’
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 7
View Ridge Gallery
EXAMPLE
• View Ridge Gallery is a small art gallery that has
been in business for 30 years.
• It sells contemporary European and
North American fine art.
• View Ridge has one owner, three salespeople,
and two workers.
• View Ridge owns all of the art that it sells;
it holds no items on a consignment basis.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 8
Application Requirements
• View Ridge application requirements:
 Track customers and their artist interests
 Record gallery's purchases
 Record customers' art purchases
 List the artists and works that have appeared in the
gallery
 Report how fast an artist's works have sold and at
what margin
 Show current inventory in a Web page
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 9
View Ridge Gallery Database Design
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 10
SQL DDL and DML
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 11
The Database Design for ARTIST and WORK
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 12
CREATE TABLE
• CREATE TABLE statement is used for creating
relations.
• Each column is described with three parts:
column name, data type, and optional
constraints.
• Example:
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 13
Data Types
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 14
Constraints
• Constraints can be defined within the CREATE
TABLE statement, or they can be added to the
table after it is created using the ALTER table
statement.
• Five types of constraints:
 PRIMARY KEY may not have null values
 UNIQUE may have null values
 NULL/NOT NULL
 FOREIGN KEY
 CHECK
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 15
Creating Relationships
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 16
Implementing Cardinalities
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 17
Default Values and Data
Constraints
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 18
SQL for Constraints
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 19
ALTER Statement (DDL)
• ALTER statement changes table structure,
properties, or constraints after it has been
created.
• Example:
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT EmployeeFK FOREIGN
KEY (EmployeeNum)
REFERENCES EMPLOYEE
(EmployeeNumber)
ON UPDATE CASCADE
ON DELETE NO ACTION;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 20
Adding and Dropping Columns
• The following statement will add a column
named MyColumn to the CUSTOMER table:
ALTER TABLE CUSTOMER ADD MyColumn
Char(5) NULL;
• You can drop an existing column with the
statement:
ALTER TABLE CUSTOMER DROP COLUMN
MyColumn;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 21
Adding and Dropping Constraints
• ALTER can be used to add a constraint as
follows:
ALTER TABLE CUSTOMER ADD CONSTRAINT
MyConstraint CHECK ([Name] NOT IN
('Robert No Pay'));
• ALTER can be used to drop a constraint:
ALTER TABLE CUSTOMER DROP CONSTRAINT
MyConstraint;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 22
Removing Tables
• SQL DROP TABLE:
DROP TABLE [TRANSACTION];
• If there are constraints:
ALTER TABLE CUSTOMER_ARTIST_INT
DROP CONSTRAINT
Customer_Artist_Int_CustomerFK;
ALTER TABLE [TRANSACTION]
DROP CONSTRAINT
TransactionCustomerFK;
DROP TABLE CUSTOMER;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 23
SQL DML - INSERT
• INSERT command:
INSERT INTO ARTIST ([Name],
Nationality, Birthdate,
DeceasedDate)
VALUES ('Tamayo', 'Mexican', 1927,
1998);
• Bulk INSERT:
INSERT INTO ARTIST ([Name],
Nationality, Birthdate)
SELECT [Name], Nationality,
Birthdate
FROM IMPORTED_ARTIST;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 24
SQL DML: UPDATE
• UPDATE command:
UPDATE CUSTOMER
SET
City = 'New York City'
WHERE CustomerID = 1000;
• Bulk UPDATE:
UPDATE CUSTOMER
SET
AreaCode = '333'
WHERE City = 'Denver';
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 25
SQL DML: DELETE
DELETE command:
DELETE FROM CUSTOMER
WHERE CustomerID = 1000;
NOTE : If you omit the WHERE clause, you will
delete every row in the table.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 26
Joins
Join
vEmpl cEmpl
oyeeID oyeeF
Name
vEmpl cDesig
oyeeID nation
0120
0120
Ron
0121
Nancy
0121
0122
Don
0122
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 27
Sales
Manag
er
Team
Leader
Team
Membe
r
JOINING Tables
You
must use table joins to extrapolate data from
several tables.
Simply
listing more than one table
(in your SELECT * FROM TABLE1, TABLE2, etc.)
will very rarely produce the expected results.
The
rows from one table must be correlated with the
rows of the others. This correlation is known as table
joining.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 28
The following example illustrates the previous point :
OUTER Join SYNTAX and Predicate Rules
table-1 { LEFT | RIGHT | FULL } OUTER
JOIN table-2 ON predicate-1
Predicate rule-1 is a join predicate for the outer
join. It can only reference columns from the
joined tables. The LEFT, RIGHT or FULL
specifiers give the type of join:
•LEFT -- only unmatched rows from the left
side table (table-1) are retained
•RIGHT -- only unmatched rows from the
right side table (table-2) are retained
FULL -- unmatched rows from both tables
(table-1 and table-2) are retained.
the result is usually same as an INNER Join.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 29
What’s happening here?…The Visual Summary!
Remember this diagram while performing joins and as
they say: “A picture is worth a 1,000 words”
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 30
Examples
EXAMPLES, EXAMPLES,
EXAMPLES!!!
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 31
JOIN ON Syntax
• JOIN ON syntax:
SELECT
FROM
ON
CUSTOMER.Name, ARTIST.Name
CUSTOMER JOIN CUSTOMER_ARTIST_INT
CUSTOMER.CustomerID =
CUSTOMER_ARTIST_INT.CustomerID
JOIN
ARTIST
ON
CUSTOMER_ARTIST_INT.ArtistID =
ARTIST.ArtistID;
• Use of aliases:
SELECT
C.Name, A.Name
FROM
CUSTOMER AS C JOIN
CUSTOMER_ARTIST_INT AS CI
ON
C.CustomerID = CI.CustomerID
JOIN
ARTIST AS A
ON
CI.ArtistID = A.ArtistID;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 32
SAMPLE View Ridge Data for ‘Outer Join’
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 33
Outer Joins
• Left Outer Join:
SELECT
C.[Name] Customer, A.[Name] Artist
FROM
CUSTOMER C LEFT JOIN
CUSTOMER_ARTIST_INT CI
ON
C.CustomerID = CI.CustomerID
LEFT JOIN ARTIST A
ON
CI.ArtistID = A.ArtistID;
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 34
Result of Outer Join
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 35
SQL Views
• SQL view is a virtual table that is constructed
from other tables or views.
• It has no data of its own, but obtains data from
tables or other views.
• SELECT statements are used to define views

A view definition may not include an ORDER BY
clause.
• SQL views are a subset of the external views

They can be used only for external views that
involve one multi-valued path through the schema.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 36
SQL Views
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 37
CREATE VIEW Command
• CREATE VIEW command:
CREATE VIEW CustomerNameView AS
SELECT
[Name] AS
CustomerName
FROM
CUSTOMER;
• To see the view use:
SELECT
FROM
ORDER BY
*
CustomerNameView
CustomerName;
• Results:
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 38
Updateable Views
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 39
Embedding SQL In Program Code
•
•
•
•
SQL can be embedded in triggers, stored
procedures, and program code.
Problem: assigning SQL table columns with
program variables.
Solution: object-oriented programming, PL/SQL
Problem: paradigm mismatch between SQL and
application programming language.
 SQL statements return sets of rows; an
applications work on one row at a time.
Solution: process the SQL results as pseudofiles.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 40
Triggers
• A trigger is a stored program that is executed
by the DBMS whenever a specified event
occurs on a specified table or view.
• Three trigger types:
BEFORE, INSTEAD OF, and AFTER


Each type can be declared for Insert, Update,
and Delete.
It will result in a total of nine trigger types.
• Oracle supports all nine trigger types.
• SQL Server supports six trigger types (only for
INSTEAD OF and AFTER triggers).
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 41
Firing Triggers
• When a trigger is fired, the DBMS supplies:
 Old and new values for the update
 New values for inserts
 Old values for deletions
• The way the values are supplied depends on the DBMS
product.
• Trigger applications:
 Provide default values
 Enforce data constraints
 Update views
 Perform referential integrity actions
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 42
EXAMPLE Trigger Firing
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 43
EXAMPLE Trigger Firing
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 44
Stored Procedures
• A stored procedure is a program that is stored
within the database and is compiled when used.


In Oracle, it can be written in PL/SQL or Java.
In SQL Server, it can be written in TRANSACTSQL.
• Stored procedures can receive input parameters
and they can return results.
• Stored procedures can be called from:



Programs written in standard languages, e.g.,
Java, C#
Scripting languages, e.g., JavaScript, VBScript
SQL command prompt, e.g., SQL*Plus, Query
Analyzer.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 45
Stored Procedure Advantages
• Greater security as store procedures are
always stored on the database server.
• It results in decreased network traffic.
• SQL can be optimized by the DBMS compiler.
• Code sharing resulting in:



Less work
Standardized processing
Specialization among developers
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 46
EXAMPLE Stored Procedure
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 47
Triggers vs. Stored Procedures
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 48
Examples
EXAMPLES, EXAMPLES, EXAMPLES!!!
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 49
Class Activity 1
• Identify the need for query
from the following options.




Used to create database.
Used to retrieve and modify data from
indexes.
Used to retrieve and modify data from
tables and views.
Used to retrieve data from tables only.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 50
Solution 1
• Used to retrieve and
modify data from tables
and views.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 51
Class Activity 2
•
•
The HR manager of Ethnic Blend Inc. wants to view the different
designation types from the employee table.
Create and execute queries for the activity.
vEmploye
eID
cEmploye
eFName
cEmploye
eLName
cDesgnat
ion
mSalary
cAvailabl
e
0120
Ron
Shin
Sales
Manager
5000
Yes
0121
Nancy
Jones
Team
Leader
3000
Yes
0120
3
0
0122
Don
Alien
Team
Member
2500
No
0121
3
0
0123
Jane
Wane
Team
Member
2500
Yes
0124
2
0
0124
Shelley
Mathew
Team
Leader
3000
Yes
0120
2
0
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 52
vSenior
v
D
e
p
I
D
4
0
Solution 2
The query will be:
SELECT DISTINCT cDesignation FROM Employee
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 53
Class Activity 3
•
The HR manager of Ethnic Blend Inc. needs employee names which
have ‘IN’ or ‘EN’ in their last name.
Create and execute queries for the activity.
vEmpl
oyeeID
cEmploy
eeFName
cEmploy
eeLNam
e
cDesignati
on
mSalary
cAvailabl
e
0120
Ron
Shin
Sales
Manager
5000
Yes
0121
Nancy
Jones
Team
Leader
3000
Yes
0120
30
0122
Don
Alien
Team
Member
2500
No
0121
30
0123
Jane
Wane
Team
Member
2500
Yes
0124
20
0124
Shelley
Mathew
Team
Leader
3000
Yes
0120
20
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 54
vSenior
vDeptID
40
Solution 3
The query will be:
SELECT cEmployeeLName FROM Employee
WHERE cEmployeeLName LIKE '%IN%'
OR cEmployeeLName LIKE '%EN%'
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 55
Class Activity 4
• Which of the following is true about
joins?
a.
b.
c.
d.
Joins are only used to retrieve data from
single table.
Tables used in join should be related via at
least one column.
Joins gather information from two or more
tables based on identical columns.
The columns joining the two tables need not
to be of same data type.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 56
Solution 4
b.
c.
Tables used in join should be
related via at least one column.
Joins gather information from two
or more tables based on identical
columns.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 57
Class Activity 5
• Identify the type of joins that are used in
the following join situations:



A table joined on itself.
A join retrieves only the rows that satisfies
the join condition.
A join retrieves both matched rows of the
first table and missing rows of the second
table.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 58
Solution 5
a.
b.
c.
Self Join
Inner Join
Left Outer Join
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 59
Class Activity 6
You need to create join between four
tables to retrieve some data. How many
join conditions you will use to perform
the join?
a.
Two
b.
Four
c.
Three
d.
None
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 60
Solution 6
• c. Three
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 61
Class Activity 7
The HR manager of Ethnic Blends Inc. needs name and
number of employees along with their senior’s name and
number. Identify the type of join required and write query to
retrieve the data.
vEmplo
yeeID
cEmplo
yeeFNa
me
cEmplo
yeeLNa
me
cDesign
ation
mSalary
cAvaila
ble
0120
Ron
Shin
Sales
Manager
5000
Yes
0121
Nancy
Jones
Team
Leader
3000
Yes
0120
30
0122
Don
Alien
Team
Member
2500
No
0121
30
0123
Jane
Wane
Team
Member
2500
Yes
0124
20
0124
Shelley
Mathew
Team
Leader
3000
Yes
0120
20
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 62
vSenior
vDeptID
40
Solution 7
Self join is required on employee table.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 63
Class Activity 8
• Blue Valley Travel promotes ten travel packages
in a season. This fall, each package covers a
historical city for one week. There is a big
discount when a tourist books TWO tours in a row
to two different cities. Most of the tourists have
opted for two special cities making it a TWO week
vacation. Discuss how joins would be used to
display a complete list of tourists and the
associated selected Location_name.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 64
Solution 8
• You need to conceptualize two tables based on
which the activity will be performed.
TourMasterList
TourBooked
Location_code
Location_code
Location_name
Client
Cost
Expense_paid
Hotel_code
No_of_Tourist_max_a
vailable
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 65
Duration
No_of_Tourist_Booke
d
Solution 8 (cont.)
• There will be two destination codes for
one client when the client has booked the
special discount deal. Since the table is
used twice, These two tables can be
joined using Location_code, as this
column is common in both the tables.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 66
Class Activity 9
• While using a WHERE clause, which one of
the following should occur first?


The join
The condition
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 67
Solution 9
The joins precede the condition.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 68
Class Activity 10
• Identify the type of join for the following
statement:

Each row from one table is joined with each
row of the other table. The number of rows
in the result set is the number of rows in the
first table multiplied by the number of rows
in the second table.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 69
Solution 10
• This is cross join (cartesian product).
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 70
Database Security Fundamentals
• Database security ensures that only authorized
users can perform authorized activities at
authorized times.
• Developing database security
 Determine users’ processing rights and
responsibilities
 Enforce security requirements using security
features from both DBMS and application
programs
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 71
DBMS Security
• DBMS products provide security facilities.
• They limit certain actions on certain
objects to certain users or groups (also
called roles).
• Almost all DBMS products use some form
of user name and password security.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 72
DBMS Security Model
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 73
DBMS Basic Security Guidelines
•
•
•
•
Run DBMS behind a firewall, but plan as though the firewall has
been breached.
Apply the latest operating system and DBMS service packs and
fixes.
Use the least functionality possible
 Support the fewest network protocols possible.
 Delete unnecessary or unused system stored procedures.
 Disable default logins and guest users, if possible.
 Unless required, never allow all users to log on to the DBMS
interactively.
Protect the computer that runs the DBMS
 No user should be allowed to work at the computer that runs
the DBMS.
 DBMS computer should be physically secured behind locked
doors.
 Access to the room containing the DBMS computer should be
recorded in a log.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 74
DBMS Basic Security Guidelines
• Manage accounts and passwords







Use a low privilege user account for the DBMS service.
Protect database accounts with strong passwords.
Monitor failed login attempts.
Frequently check group and role memberships.
Audit accounts with null passwords.
Assign accounts the lowest privileges possible.
Limit DBA account privileges.
• Planning


Develop a security plan for preventing and detecting
security problems.
Create procedures for security emergencies and practice
them.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 75
Application Security
• If DBMS security features are inadequate,
additional security code could be written in
application program.
 Application security in Internet applications is
often provided on the Web server computer.
• However, you should use the DBMS security
features first.
 The closer the security enforcement is to the
data, the less chance there is for infiltration.
 DBMS security features are faster, cheaper,
and probably result in higher quality results
than developing your own.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 76
SQL Injection Attack –
an Example of what to worry about
• SQL injection attack occurs when data from the user is used
to modify a SQL statement.
• User input that can modify a SQL statement must be carefully
edited to ensure that only valid input has been received and
that no additional SQL syntax has been entered.
• Example: Users are asked to enter their names into a Web
form textbox.
 User input: Benjamin Franklin ' OR TRUE '
SELECT * FROM EMPLOYEE
WHERE EMPLOYEE.Name = 'Benjamin Franklin' OR
TRUE;
 Result: Every row of the EMPLOYEE table will be
returned.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 77
Database Recovery
• In the event of system failure, that
database must be restored to a usable
state as soon as possible.
• Two recovery techniques:


Recovery via reprocessing
Recovery via rollback/rollforward
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 78
Summary

Managing, Maintaining and Optimizing a
database requires a wide variety of skills while
using SQL language.

Some of the commands an administrator must
be familiar with in-depth are SELECT, CREATE,
ALTER, INSERT, UPDATE, DELETE, JOIN,
CREATE VIEW, CREATE TRIGGER, and
CREATE PROCEDURE.

A DBA should have a good grasp on basic
security guidelines and what kinds of things can
go wrong without that understanding and
implementation.
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 79
Summary
 Did
you understand the key points from
the Lesson?
 Do
you have any questions?
© 2006 ITT Educational Services Inc.
Course Name: IT390 Business Database
Administration Unit 7 Slide 80