LOVELY PROFESSIONAL UNIVERSITY

MODEL HOME WORK: #2

INT401: DATABASE ADMINISTRATION

School: LST Department: CSE/IT

Name of the faculty member: Ms. Kanika Sharma

Class: B. Tech IT Term: 2nd Section: C27T1 Batch: 2007-2011

Max. Marks: DOA: DOS: 28th Feb

Submitted by:

Md. Serajul Haque

Roll No. 43

Reg. No. 1070070127

Q.1. CREATE TABLE “Sales.USA Customers” (AcctNumber int IDENTITY (1,1)
NOT NULL, “Last Name” varchar(75) NOT NULL, “First Name” varchar(75) NOT
NULL) After creating above table what will be the effect of following commands if fired
for this . Table>.> Drop table……. , Truncate table……, delete table…..
Is there any difference in three of these commands? And tell the category under which
these command falls?

Ans. The effects of the above commands are discussed below:

a. Table>>
b. Drop table sales_usa_customers: this statement delete the table from the database.
c. Truncate table sales_usa_customers: this statement only deletes the data which are
present in the table.
d. Delete table sales_usa_customers: this statement delete a row from the table.
Yes theses all three commands are different. The drop table deletes the table from database,
the truncate table deletes the data from the table and the delete table deletes only a row of the
table. The categories for the above commands are:
a. Drop- DDL.
b. Truncate- DDL.
c. Delete- DML.

Q.2. Compare different editions of SQL Server 2005 mentioning there advantage and
disadvantages. Can we have multiple PRIMARY key in single table? Is there any
advantage of doing so?

Ans. The different editions of SQL server 2005 are:

 SQL server 2005 enterprise edition. This edition used in enterprise for development
and business process. It includes the complete set of enterprise data management and
business intelligence features. This edition is not free for use.
 SQL server 2005 standard edition. This includes limited features and this edition
used in small enterprise and also student used to learn SQL server. It includes the core
functionality needed for noncritical e-commerce, data warehousing, and line-of-
business solutions. Standard Edition is optimized to run on win32, x64, and Itanium-
based servers.This edition is free.
 SQL server 2005 workgroup edition. This edition is developed for the data
management solution for small organizations and industries. This edition is not free
but available for relatively low cost.
 SQL server 2005 express edition. This edition is for developer and student because
it provides robust and reliable application for free of cost. It also provide robust
database when protecting and managing information inside and outside of
applications is critical.
 SQL server 2005 compact edition. This edition is for mobile and desktop and laptop
application. This is the lightest edition of SQL server 2005 and easily can be used and
implemented.
 SQL server 2005 developer edition. This edition is designed for developers and
users to build any type of applications which are available in SQL server 2005. It
 includes all the functionality of Enterprise Edition (win32, x64, IA64) but with a
special development and test license agreement that prohibits production deployment.

See the following table to understand and compare between all the above editions:

Enterprise Standard Workgroup Express

1. Database 1. Have 1. Unlimited 1. Database size
size- unlimited database size. upto 4 GB.
unlimited. database size. 2. Can support 2. Can support
2. Can have 2. Can support only 2 CPU. only one
unlimited maximum of CPU.
CPU. 4 CPU.
3. Operating 3. Operating 3. RAM- 3GB 3. RAM- 1GB
system system
maximum. maximum.

No, we cannot have more than one primary key in a single table. Yes we a number of
advantages by using primary key. We have the following advantages:

1. Primary key remove duplicacy in a table.

2. A primary key provide index in the table.
3. Primary key is a unique key on which all the other candidate keys are functionally
dependent.
4. When we add new records to a table that has a primary key, Microsoft Access
checks for duplicate data and doesn't let you enter duplicates for the primary key
field.

Q.3. In SQL Server, a view is a pre-written query that is stored on the database. Justify
taking example. Also write the create command for a view and discuss the benefits of
views.

Ans. In SQL server, a view is a pr-written query that is stored on the database, because view
is created if a table is exists in the database. A view is created for two or more tables to view
the details of that table. For example see the following command to creating view.

CREATE VIEW stud_info AS

SELECT stud_name, stud_address
FROM stud_details
WHERE batch=’2007’.

As we see in the above view statement, we have created views by selecting two columns
from stud_details table. So we can say that it is a pre-written query. We can use the following
query to see the view.

Select * from (No_of Student in 2007 batch)

The create command is written above.

There are a number of benefits of views. Some benefits of views are:

a. Security: It provides security to the database and table. And restrict to a access in a
specific row and column.
b. Simplicity: View hides data complexity and complex queries.
c. Views can be used to provide aliases on the column names to make them easily
understandable and memorable.
d. Views protect the data from misusing, i.e. if our table have sensitive data, then view
hide those columns from the other users.

1. Part B

Q.4. Create the DEPT table based on the following table instance.

Ans. The create statement for the following information is given below:

Create table DEPT

(
Id int NOT NULL PRIMARY KEY,

Name varchar (25),

Address varchar (25) NULL UNIQUE

With the above table we can create a table with foreign key as:

Create table DEPT1

Srl_No int FOREIGN KEY REFERENCES DEPT(Id),

Dept_name varchar (25),

Dept_No int

)
Q.5. Create tables STUDENT (reg_no, s_name, section, program) and FACULTY
(Bio_id, f_name, dept, section_teach). Fill 10 records in each table (mention query).
Write down sql query for following and give results.

a. All students who are taught by any faculty of CSE/IT department.

b. Which section has minimum students?

Ans. Table for STUDENT: The insert command for student table is as follows:

Insert into STUDENT (reg_no, s_name, section, program) values

(‘1070070127’,’seraj’,’C27T1’,’B.TECH-IT’).

Reg_No. S_name Section Program

1070070127 seraj C27T1 B.TECH-IT
1070070128 daddu C27T1 B.TECH-IT
1070070129 pandu C27T1 B.TECH-IT
1070070130 muku C27T1 B.TECH-IT
1070070131 suraj C27T1 B.TECH-IT
1070070132 chona C27T2 B.TECH-CSE
1070070133 karan C27T2 B.TECH-CSE
1070070134 manish C27T3 B.TECH-CSE
1070070135 raghu C27T3 B.TECH-CSE
1070070136 ritu C27T3 B.TECH-CSE

Table for FACULTY is as follows: The insert query for FACULTY is:

Insert into FACULTY (bio_id, f_name, dept, Sect_teach) values

(‘10990066’,’pramod’,’CSE/IT’,’C27T1’).

Bio_id F_name Dept Sect_teach

10990066 pramod CSE/IT C27T1
10990069 rinku CSE/IT C27T1
10990033 chhavi CSE/IT C27T1
10990016 komal CSE/IT C27T1
10990026 chetna CSE/IT C27T2
10990266 supreet CSE/IT C27T2
10990466 dalvindar CSE/IT C27T3
10994098 manpreet CSE/IT C27T3
10990576 rekha CSE/IT C27T3
10990654 vinod CSE/IT C27T3

a. The query for all students who are taught by any faculty of CSE/IT department is:

Select * from STUDENT s inner join FACULTY p on a.section=p.sect_teach.

b. The select query for the section which have minimum students is:
 Select * from STUDENT where (section count=max).

Q.6. Add qualification attribute in FACULTY table, and add batch_year attribute in
STUDENT table, put appropriate values in new columns, then create a VIEW which
shows faculty names, Bio_id, with PhD qualification, who are taking class in 2009 batch
uniquely(Bio_id should not be repeated).

NOTE: You have to write down sql statements for all above scenarios.

Ans. The create statement for creating a FACULTY table is given below:

Create table FACULTY

Bio_id int NOT NULL PRIMARY KEY,

F_name varchar (25),

F_address varchar (150)

Now create STUDENT table as follows:

Create table STUDENT

Srl_no int NOT NULL PRIMARY KEY,

Reg_no int NOT NULL,

Student_Name varchar (20),

Program char (10)

Now use the following command to add qualification attribute in the FACULTY table.

ALTER TABLE FACULTY

 ADD qualification varchar (20);

Now write the following command to insert the values:

Insert into FACULTY (bio_id, f_name, f_address, qualification) values

(‘10996’,’pramod’,’MUMBAI’,’PhD’).

Use the following command to add the qualification attributes in the STUDENT.
Insert into STUDENT (srl_no, reg_no, student_name,program, Sect_teach) values
(‘10990066’,’pramod’,’CSE/IT’,’C27T1’).

The create view command is given here:

CREATE VIEW faculty_info AS

SELECT bio_id,faculty_name, stud_address
FROM stud_details
WHERE batch=’2009’ AND qualification=PhD.

Q.7. Database schema can be thought of as a container of objects. Justify. Give steps to
create database schema and adding a new table to that schema. Give queries to create,
use and provide different privileges on schema.

Ans. Yes, the database schema can be thought as the container of objects, because in database
schema we can store different tables and records. For example see the following database
schema diagram.

From the above schema diagram we can see that a schema have various tables and showing
the relations between all those tables. So we can see a schema is a container.

Use the following steps to create schema and then add table to that schema.

1. expand database
2. now expand the programs
3. Right click on the schema and the click new schema.
4. New schema creation wizard screen came out. Give a name to that schema and then
click next.
5. Now click on the properties and then set the permissions by clicking the check boxes.
6. Then click next. A new schema has been created.
7. Now got the table on which this schema you want to apply.
8. Click on properties and then select the database, and then select the table.
9. Now close the window.
10. Then last go to table and refresh it. You will see the table with that schema.
 The queries for the above requirement are given below:

SELECT     FACULTY, STUDENT

FROM      Info_schema.columns
WHERE     STUD BETWEEN ('DEPT) AND ('REG_NO')
ORDER BY FACULTY.

GRANT creation ON schema faculty_info to user.

Q.8. You have created new database in sql 2005 for LPU. But you forgot to create
LOGINS in your database…is it beneficial to take decision now to create same? If yes
what will be the steps to be followed to achieve the target…?

Ans. Yes, because without login our database is not secure and anyone can use and make
changes on it. Also creating new database login to protect our database. For example see the
following statement.

USE MASTER;
CREATE CERTIFICATE <permission>
WITH SUBJECT = '<loginName> certificate in master database',
EXPIRY_DATE = '12/05/2045';
GO
CREATE LOGIN <loginName> FROM CERTIFICATE <certificateName>;
GO
This will create a certificate in the database.
Also see the following example.
CREATE LOGIN <login_Name> WITH PASSWORD = '<password>' MUST_CHANGE;
GO