CAP414: Database Administration

Homework 2
Course Instructor: Lect. Sucharu Mahajan

Student’s ID: 10804670 Course Name: MCA

Student’s Roll No: RE3804A23 Section No: E3804

Declaration:

I declare that this assignment is my individual work. I have not

copied from any other student’s work or from any other source
except where due acknowledgment is made explicitly in the text,
nor has any part been written for me by another person.

Student’s Signature:

Gaurav Kakkar

Evaluator’s comments:
______________________________________________________________

Marks obtained: ___________ out of ______________________

Part A

Q1. How activity monitor could instantly view process information. Give
example to support your answer. Delineate additional “Object explorer
details “which are available in SQL Server 2008.Is there better object search
in SQL Server 2008. Give examples.

ANSWER:

Activity Monitor:

In SQL Server 2005, it was easy to find and start the Activity Monitor. You just
opened up the Management object in SSMS and double-clicked on Activity
Monitor, and you could instantly view process information. In SQL Server 2008,
they have made the Activity Monitor a little harder to find, but once you have
learned where it is, you will probably be very impressed with all the new features
that have been added.

To start Activity Monitor in SQL Server 2008 SSMS, right-click on the SQL
Server name you want to monitor, and then click on Activity Monitor. The
following screen appears:

Figure 1: The Activity Monitor has had a radical facelift, and feature boost.

Immediately, you can see that Activity Monitor looks entirely different than in
SQL Server 2005. The first things that jump out at you are the four graphs. These
display % Processor time (of the SQL Server process “sqlserv” spread over all of
the available CPUs, not for the entire server), Waiting tasks, Database I/O, and
Batch Requests/sec. In the past when you needed this information, you had to use
System Monitor or some other tool. Now, if your server is behaving strangely and
you need a quick overview of what is happening, you can get it directly from
Activity Monitor.

Below the graphs you will find four additional windows of information, the first of
which (Processes) is shown in Figure 2:

Figure 2: This looks a little more familiar. Here, we see all of the active SPIDS.

When you open the Processes window, you see the SPIDs that you are so
familiar with from the SQL Server 2005 SSMS Activity Monitor. Now, not only
can you see the SPIDS, and sort them, but you can also filter on them using the
drop-down boxes at the top of each column. And if you right-click on any of the
SPIDs, you can choose to automatically launch Profiler, which will begin a trace
on the SPID in question. This makes it very easy to begin a Profiler analysis of any
SPID that you want to investigate.

Next, we move on to the Resource Waits window, shown in Figure 3:

Figure 3: We can see the current wait states of active threads.

The ‘Resource Waits’ screen provides a snapshot of key resource waits occurring
on the server, thereby helping you to identify potential trouble with your SQL
Server. Resource waits measure the amount of time a worker thread has to wait
until it can gain access to the resources on the server that it needs, such as memory
or CPU. A high resource wait time might indicate a resource bottleneck. As with
Processes, you can sort and filter on any column.

The third window is Data File I/O, as shown in Figure 4:

Figure 4: Use the Data File I/O screen to identify databases with heavy I/O activity.

If you suspect that a particular database is being heavily hit with disk I/O, you can
quickly find out by using the Data File I/O screen. You can sort and filter on any
column with this or the other related screens.

The final screen is "Recent Expensive Queries":

Figure 5: Want to know what your most expensive queries are? Find out here.

If you are having performance problems due to resource-intensive queries, then the
Recent Expensive Queries window will show you the most recent expensive
queries (those currently in cache), allowing you to sort or filter them by any
column, making it easy to identify problem queries. If you right-click any of the
queries, you have the option of displaying the entire query (not just the small part
of the query you see in the window) and you also have the option of displaying a
graphical execution plan of the query.

When you first lay hands on a copy of SQL Server 2008, you should start by trying
out the new Activity Monitor. I guarantee it will make it much easier for you to
quickly get a high-level perspective on what might be ailing your SQL Server.
Once you know the big picture, then you can use other tools, such as DMVs,
Profiler, or System Monitor, to drill down for more details.

Object Explorer Details

In SQL Server 2005, the Object Explorer’s ‘Details’ screen, the one that appears
by default to the right of the Object Explorer, wasn't particularly useful.
Essentially, it just displayed the same information that you saw in the Object
Explorer and I generally closed it because it didn't offer me any value.

This has changed in SQL Server 2008. Instead of repeating what you see in Object
Explorer, you are generally presented with a lot of useful information about the
object selected in Object Explorer. The level of detail depends on which object in
the Object Explorer you have selected. For a quick demo, let's take a look at the
Object Explorer Details window when "Databases" has been selected in the Object
Browser.
When I click on "Databases" in Object Explorer, I can see in the Object Explorer
Details screen a variety of information about each of the databases, such as its
Policy Health State, Recover Model, Compatibility Level, Collation, and so on.
Notice the small folder icons next to System Databases and Database Snapshots in
figure 6 above. Since these are represented as folders, you can click on these to
drill down for additional information.

Q.2 Does SSMS offers the ability to query multiple servers at the same time.
Why IntelliSense feature is added to the Query Editor. Give examples. Why
T-SQL Debugger is required?

ANSWER:

While third-party tools have been offering this feature for years, SSMS now offers
the ability to query multiple servers at the same time, returning the results to a
single window. This makes it very easy for DBAs to run the same script on
multiple SQL Server instances simultaneously.

To accomplish this task, the first step is to create a Server Group from the
Registered Servers window, then add SQL Server registrations to the group. Next,
right-click on the Server Group and select "New Query." A new Query Editor
window appears where you can enter Transact-SQL code. When you click on
"Execute," the query runs on all the registered servers belonging to the group, and
returns the results in a single window.

One of the most requested features for the SSMS Query Editor has been
IntelliSense, and it's finally available in SQL Server 2008, although it does not
provide all the features you may have become accustomed to when using Visual
Studio or third-party add-ins. For example, it only neither works with SQL Server
2008 and is not backward compatible with SQL Server 2005, nor does it provide
advanced formatting capabilities, or refactoring.

You don't have to do anything to use IntelliSense in the Query Editor. All you have
to do is to begin typing. As you type, IntelliSense can automatically:

•Identify incorrect syntax, underlining it in red so you can immediately identify

and correct it.

•Complete a word as you type in a variable, command, or function once you have
typed in enough characters so that it is uniquely identified.
•List the available parameters required by a function or stored procedure.

•Open a list that provides available database objects and user-defined variables that
you have previously declared.

Another feature that has been sorely missing from SQL Server 2005 is a query
debugger. In SQL Server 2008, a debugger is now integrated within the Query
Editor, making it easy for you to debug your Transact-SQL code.

The T-SQL debugger includes many features, including the ability to:

•Step through Transact-SQL statements, line by line, or by using breakpoints.

•Step into or over Transact-SQL stored procedures, functions, or triggers that are
part of your code.

•Watch values assigned to variables, in addition to viewing system objects,

including the call stack and threads.

Q.3: How database storage of Oracle differs from SQL Server? By taking
examples delineate the various Types of data compression in SQL Server
2008. What is the role of row compression? Give example.
ANSWER:

It's time to look into Oracle Database Storage Structures. We already know that data in Oracle
Database is stored in form of tables. Beside tables, Oracle Database is composed of various
logical and physical structures, to keep things organized and easy to manage.

Physical structures are those that can be seen and operated on from the operating system, such as
the physical files that store data on disk. Logical structures are created and recognized by the
Oracle database server and are not known to the operating system.

A database is divided into logical storage units called table spaces, which group
related logical structures together. Table spaces hold data in the form of other logical objects
such as tables and indexes. When we create an Oracle database, some table spaces already exist.
Let's see where they are in our Database. Connect to the database using SQL plus and issue the
following statements:

SQL>select TS#, Name from v$tablespace;

SQL>col Name format a40

SQL>select TS#, Name from v$datafile;

Datafiles are the operating system files that hold the data within the database. The data is written
to these files in an Oracle proprietary format that cannot be read by other programs. The name of
Data files shows the complete path at OS level. Browse to that path and give a look to the files
and the directory structure.

Graphical view of Oracle Database Storage Structures

There are other logical storage structures as well that enable Oracle to have fine-grained control
of disk space use.

Segments

Above extents, the level of logical database storage is a segment. A segment is a set of extents
allocated for a certain logical structure. For example, tables are stored in data segments, whereas
indexes are stored in index segments. Extents are allocated as needed, so the extents of a segment
may or may not be contiguous on disk.

We can represent the above logical and physical structures as:

[Database -> Tablespaces -> Segments [Extents -> Data Blocks] -> [Tables,Views,Indexes..]
==> [Datafiles]

Data Compression in SQL Server 2008:-

SQL Server 2008 provides two types of compression
namely, Database Backup Compression and Data Compression. We have already discussed the
Database Backup Compression feature in my article titled How to configure and Use Database
Backup Compression in SQL Server 2008.

Data Compression was first introduced in SQL Server 2005 with Service Pack 2 where Microsoft
introduced a new storage format called vardecimal to store decimal and numeric data types as
variable length columns. In SQL Server 2008 this concept is applicable even to fixed length data
types such as integer, char and float. The main advantage of using Data Compression is that it
reduces the storage cost and increases the query performance by reducing I/O. Database
Administrator needs to be very careful before using Database Compression as it is more CPU
intensive activity. If you are not facing CPU crunch and you have more I/O based activities then
it is a good idea to implement data compression, which can give you better query performance.

SQL Server 2008 supports Row Level and Page Level Compression for tables and indexes. Data
Compression is supported for the following database objects:

 Heap Table (a table which has no clustered index or non clustered index)

 Clustered Table

 Non Clustered Table

 Indexed View

 Partitioned Tables and Indexes (compression option needs to be configured for each
partition)
The important point to be noted is that the compression setting of a table is not automatically
applied to its nonclustered indexes. Compression settings on each and every index need to be set
separately. Using the CREATE TABLE or CREATE INDEX Data Definition Language (DDL)
statement you can create compression on a table or index. In order to change the compression
state of a table, index or a partition you need to use the ALTER TABLE or ALTER INDEX Data
Definition Language (DDL) statements.

Row Level Compression: Row Level compression extends the vardecimal storage format
by storing all fixed-length data types in a variable length storage format. When you are enabling
Row Level Compression you are only changing the physical storage format of the data which is
associated with a data type. There is absolutely no change required at the application level once a
DBA enables Row Level Compression on one or more tables within a database.

Now let us take a small example of a table which has a CHAR (100) column. In
the fixed length format, this column will take 100 characters irrespective of the actual value
stored in it.

For example if you are storing “Row Level Compression” or “Row Level
Compression Feature” value, then each value will take 100 character space to store the data.
However, when CHAR (100) is stored in a variable length format, then it will only take 21
characters for storing the first value (“Row Level Compression”) and 29 characters for storing
the second value (“Row Level Compression Feature”). From the above example you can see that
you were able to reduce the storage size of the first value by 79% and for the second value by
71%. This is a significant amount of valuable space which you have managed to save using
variable length storage format. Another advantage of using Row Level Compression feature is
that it does not take any disk space for zero or null values.

Part B

Q4. Design Database of student showing tables, their attributes, data types
and size with reasons. Delineate various string functions of SQL Server 2005
with syntax & suitable examples.

ANSWER:

Example SQL String Function - ASCII

- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .

Syntax - ASCII ( character)

SELECT ASCII('a') -- Value = 97

SELECT ASCII('b') -- Value = 98

SELECT ASCII('c') -- Value = 99

SELECT ASCII('A') -- Value = 65

SELECT ASCII('B') -- Value = 66

SELECT ASCII('C') -- Value = 67

SELECT ASCII('1') -- Value = 49

SELECT ASCII('2') -- Value = 50

SELECT ASCII('3') -- Value = 51

SELECT ASCII('4') -- Value = 52

SELECT ASCII('5') -- Value = 53

Example SQL String Function - SPACE

-Returns spaces in your SQL query (you can specific the size of space).

Syntax - SPACE ( integer)

SELECT ('SQL') + SPACE(0) + ('TUTORIALS')

-- Value = SQLTUTORIALS

SELECT ('SQL') + SPACE(1) + ('TUTORIALS')

-- Value = SQL TUTORIALS

Example SQL String Function - CHARINDEX

-Returns the starting position of a character string.

Syntax - CHARINDEX ( string1, string2 [ , start_location ] )

SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial')

-- Value = 27

SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20)

-- Value = 27

SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)

-- Value = 0 (Because the index is count from 30 and above)

Example SQL String Function - REPLACE

-Replaces all occurrences of the string2 in the string1 with string3.

Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )

SELECT REPLACE('All Function' , 'All', 'SQL')

-- Value = SQL Function

Example SQL String Function - QUOTENAME

-Returns a Unicode string with the delimiters added to make the input string a valid
Microsoft® SQL Server™ delimited identifier.

Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] )

SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]

Example SQL String Function - STUFF

- Deletes a specified length of characters and inserts string at a specified starting

index.

Syntax - STUFF ( string1 , startindex , length , string2 )

SELECT STUFF('SqlTutorial', 4, 6, 'Function')

-- Value = SqlFunctional

SELECT STUFF('GoodMorning', 5, 3, 'good')

-- Value = Goodgoodning

Example SQL String Function - LEFT

-Returns left part of a string with the specified number of characters.

Syntax - LEFT ( string , integer)

SELECT LEFT('TravelYourself', 6)

-- Value = Travel

SELECT LEFT('BeautyCentury',6)

-- Value = Beauty

Example SQL String Function - RIGHT

-Returns right part of a string with the specified number of characters.

Syntax - RIGHT( string , integer)

SELECT RIGHT('TravelYourself', 6)

-- Value = urself

SELECT RIGHT('BeautyCentury',6)
-- Value = Century

Example SQL String Function - REPLICATE

-Repeats string for a specified number of times.

Syntax - REPLICATE (string, integer)

SELECT REPLICATE('Sql', 2)

-- Value = SqlSql

Example SQL String Function - SUBSTRING

-Returns part of a string.

Syntax - SUBSTRING ( string, startindex , length )

SELECT SUBSTRING('SQLServer', 4, 3)

-- Value = Ser

Example SQL String Function - LEN

-Returns number of characters in a string.

Syntax - LEN( string)

SELECT LEN('SQLServer')

-- Value = 9

Example SQL String Function - REVERSE

-Returns reverse a string.

Syntax - REVERSE( string)

SELECT REVERSE('SQLServer')

-- Value = revreSLQS
Example SQL String Function - UNICODE

-Returns Unicode standard integer value.

Syntax - UNICODE( char)

SELECT UNICODE('SqlServer')

-- Value = 83 (it take first character)

SELECT UNICODE('S')

-- Value = 83

Example SQL String Function - LOWER

-Convert string to lowercase.

Syntax - LOWER( string )

SELECT LOWER('SQLServer')

-- Value = sqlserver

Example SQL String Function - UPPER

-Convert string to Uppercase.

Syntax - UPPER( string )

SELECT UPPER('sqlserver')

-- Value = SQLSERVER

Example SQL String Function - LTRIM

-Returns a string after removing leading blanks on Left side.

Syntax - LTRIM( string )

SELECT LTRIM(' sqlserver')

-- Value = 'sqlserver' (Remove left side space or blanks)

Example SQL String Function - RTRIM

-Returns a string after removing leading blanks on Right side.

Syntax - RTRIM( string )

SELECT RTRIM('SqlServer ')

-- Value = 'SqlServer' (Remove right side space or blanks)

Q.5. Create a database of university system. How can you apply check
constraint in that database?

ANSWER:

Create database lpu

Create table person(

Address varchar2(50),

First_Name varchar2(50),

Last_Name varchar2(50),

City varchar2(50),

Postelcode number(70),

INSERT INTO Persons.Address

VALUES ('Nilsen', 'Johan', 'Bakken 22', 'silma',223324),

INSERT INTO Persons

VALUES ('Nil', 'Aan', 'Dakken 2', 'india',654392)

USE lpu

GO
ALTER TABLE Person.Address

ADD CONSTRAINT CK_Address_PostalCode

CHECK (LEN (PostalCode) < 11)

GO

UPDATE Person.Address

SET PostalCode = '12345-12345-123'

WHERE AddressID = 1

GO

Result Set:

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with the CHECK constraint

“CK_Address_PostalCode”.

The conflict occurred in database “lpu”, table “Person.Address”,

column ‘PostalCode’.

The statement has been terminated.

Q.6. Take one suitable example of banking database and apply the concept of
primary and foreign key in it. Give suitable example to implement Referential
integrity.

ANSWER:

These SQL command used in banking database.

SAVEPOINT a; First savepoint of this transaction

DELETE...; First DML statement of this transaction
SAVEPOINT b; Second savepoint of this transaction
INSERT Second DML statement of this transaction
INTO...;
SAVEPOINT c; Third savepoint of this transaction
UPDATE...; Third DML statement of this transaction.
ROLLBACK TO UPDATE statement is rolled back, savepoint C remains defined
c;
ROLLBACK TO INSERT statement is rolled back, savepoint C is lost, savepoint B remains defined
b;
ROLLBACK TO ORA-01086 error; savepoint C no longer defined
c;
INSERT New DML statement in this transaction
INTO...;
COMMIT; Commits all actions performed by the first DML statement (the DELETE
statement) and the last DML statement (the second INSERT statement)

All other statements (the second and the third statements) of the transaction were
rolled back before the COMMIT. The savepoint A is no longer active.

Primary Key: A primary key is a field or combination of fields that uniquely

identify a record in a table, so that an individual record can be located without
confusion.

Foreign Key: A foreign key (sometimes called a referencing key) is a key used to
link two tables together.

More complicated but fuller explanation:

Employee Table
EmployeeID (PK) FirstName LastName Department Manager

001 Stan Smithers IT Support Stan Smithers

002 Joe Bloggs Sales Joe Bloggs

003 Mark Richards Sales Joe Bloggs

004 Jenny Lane Marketing Jenny Lane

005 Sally Holmes Sales Joe Bloggs

006 John Lee IT Support Stan Smithers

A primary key is the field(s) (a primary key can be made up of more than one
field) that uniquely identifies each record, i.e. the primary key is unique for each
record and the value is never duplicated in the same table, so in the above table the
EmployeeID field would be used. A constraint is a rule that defines what data is
valid for a given field. So a primary key constraint is a rule that says that the
primary key fields cannot be null and cannot contain duplicate data.
The problem with the above table is that you have repeating information in the
manager field, this causes all sorts of problems, e.g. Fred Bloggs leaves and Jenny
Smith becomes sales manager, you now have to replace all entries that say Fred
Bloggs with Jenny Smith.
If however you split the last two fields out to make a department table you would
only need one entry for each department, when a manager changes you only need
to make the change in one place, if you setup a primary key of DeptID in the
department table you would have the following.
Department Table
DeptID (PK) Department Manager

01 IT Support Stan Smithers

02 Sales Joe Bloggs

03 Marketing Jenny Lane

Employee Table
EmployeeID (PK) FirstName LastName

001 Stan Smithers

002 Joe Bloggs

003 Mark Richards

004 Jenny Lane

005 Sally Holmes

006 John Lee

You now need to link the two table together so you know which department each
employee is in, so what you do is take the primary key from the department table
and insert it into the employee table (where it becomes a foreign key as a foreign
key is the primary key from one table inserted into another table to link them).
Employee Table
EmployeeID (PK) FirstName LastName DeptID (FK)

001 Stan Smithers 01

002 Joe Bloggs 02

003 Mark Richards 02

004 Jenny Lane 03

005 Sally Holmes 02

006 John Lee 01

Referential integrity is a method for ensuring the "correctness" of data within a

DBMS.

Let’s look at a simple example using two tables: SALES_REPS and OFFICES.
The following SQL statement is syntactically correct, and with the current state of
our example database this statement would execute and add a new sales rep, "Doug
Henry", who works in office number 45:

INSERT INTO SALES_REPS (EMPL_NUM, NAME, REP_OFFICE)

VALUES (69, ‘Doug Henry’, 45)

No validity checking has been enforced, and even if office number 45 does not
exist in the OFFICES table, Doug Henry will still exist in our database.

Another example is:

create trigger title_del

on titles for delete

as

if rowcount = 0

return

delete titleauthor

from titleauthor, deleted, title

where titles.title_id = deleted.title_id

return