ASSIGNMENT

OF
DBMS

Submitted to:Ms Pency Juneja Submitted by:Vandana Chandel

Sec: s1006 roll no: B28

MBA-IT
 Part A
Q 1: Discuss the differences between database management system and file
system? which one is better and why?

ANS:A database management system is a collection of interrelated data and a

set of programs to access those data.The primary goal of a dbms is to provide

a way to store and retrieve database information that is both convenient and

efficient.Database system arose in response to early methods of computerized

methods of commercial data.One way to keep the information on a computer

is to store it in operating system.To allow users to manipulate the information

the system has a number of application programes that manipulates the files and

programs to:

 Debit or credit an account.

 Add a new account.
 Find the balance of an account.
 Generate monthly statements.

System programmers wrote these programs to meet the needs of the bank.
New application programs are added to the system as the need rises.For
example suppose that a savings bank decides to offer checking accounts.
As a result the bank creates new permanent files that contain information
about all the checking accounts maintained in the bank ,and it may have
to write new application programs to deal with situations that donot arise
in savings account such as overdrafts.Thus as time goes by the system
acquires more files and more application programs.
This typical file processing system is supported by a
conventional operating system.This system stores permanent records in
various files and it needs different application programs to extract records
from ,and add records to the appropriate files.Before database
management systems came along organizations usually stored
information in such systems.
File processing system has a lot of major disadvantages:
 Data redundancy and inconsistency.
 Difficulty in accessing data.
 Data isolation.
 Integrity problem.
 Atomicity problem.
 Concurrent access anomalities.
 Security problems.
Advantages of DBMS over file system:

A true DBMS offers several advantages over file processing. The

principal advantages of a DBMS are the followings:

• Flexibility: Because programs and data are independent,

programs do not have to be modified when types of unrelated data
are added to or deleted from the database, or when physical storage
changes.

• Fast response to information requests: Because

data are integrated into a single database, complex requests can be
handled much more rapidly then if the data were located in
separate, non-integrated files. In many businesses, faster response
means better customer service.

• Multiple access: Database software allows data to be

accessed in a variety of ways (such as through various key fields)
and often, by using several programming languages (both 3GL and
nonprocedural 4GL programs).
• Lower user training costs: Users often find it easier to
learn such systems and training costs may be reduced. Also, the
total time taken to process requests may be shorter, which would
increase user productivity.
• Less storage: Theoretically, all occurrences of data items
need be stored only once, thereby eliminating the storage of
redundant data. System developers and database designers often
use data normalization to minimize data redundancy.
Q2: Define key? What are the types of keys? Explain the difference
between candidate key, super key and primary key?

Answer:

1) Alternate key - An alternate key is any candidate key which is not

selected to be the primary key
2) Candidate key - A candidate key is a field or combination of fields that
can act as a primary key field for that table to uniquely identify each
record in that table.
3) Compound key - compound key (also called a composite key or
concatenated key) is a key that consists of 2 or more attributes.
4) Primary key - a primary key is a value that can be used to identify a
unique row in a table. Attributes are associated with it. Examples of
primary keys are Social Security numbers (associated to a specific
person) or ISBNs (associated to a specific book).
5) In the relational model of data, a primary key is a candidate key chosen as
the main method of uniquely identifying a tuple in a relation.
6) Superkey - A superkey is defined in the relational model as a set of
attributes of a relation variable (relvar) for which it holds that in all
relations assigned to that variable there are no two distinct tuples (rows)
that have the same values for the attributes in this set. Equivalently a
superkey can also be defined as a set of attributes of a relvar upon which
all attributes of the relvar are functionally dependent.
7) Foreign key - a foreign key (FK) is a field or group of fields in a
database record that points to a key field or group of fields forming a key
of another database record in some (usually different) table. Usually a
foreign key in one table refers to the primary key (PK) of another table.
This way references can be made to link information together and it is an
essential part of database normalization

KEY:Key is a set of one or more columns whose combines values are

unique among all the occurrences in a given table.

The values of attribute values of a tuple must be such that they can uniquely
identify the tuple.In other words no two tuples in a relation are allowed to have
exactly the same value for all attributes.
DEFINATION: : A candidate key is a combination of attributes that can be
uniquely used to identify a database record without any extraneous data. Each
table may have one or more candidate keys. One of these candidate keys is
selected as the table primary key.

CHARACTERSTICS:Let (K)be set of set of attributes of relation(R)then K is

the candidate key for R if and only if it posseses both of following properties :

a.uniqueness- no legal value if R even containstwo distinct tuples with

same values of K.
b. irreducibility – no proper subset of K has uniqueness property.

DEFINATION: The primary key of a relational table uniquely identifies each

record in the table. It can either be a normal attribute that is guaranteed to be
unique (such as Social Security Number in a table with no more than one record
per person) or it can be generated by the DBMS (such as a globally unique
identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a
single attribute or multiple attributes in combination.

Imagine we have a STUDENTS table that contains a record for each student at a
university. The student's unique student ID number would be a good choice for
a primary key in the STUDENTS table. The student's first and last name would
not be a good choice, as there is always the chance that more than one student
might have the same name.

PROPERTIES:
1.Stable.
2.Minimal.
3.Accessible.

DEFINATION: A superkey is defined in the relational model of database

organization as a set of attributes of a relation variable for which it holds that in
all relations assigned to that variable, there are no two distinct tuples (rows) that
have the same values for the attributes in this set. Equivalently a superkey can
also be defined as a set of attributes of a relvar upon which all attributes of the
relvar are functionally dependent.
Imagine a table with the fields <Name>, <Age>, <SSN> and <Phone
Extension>. This table has many possible superkeys. Three of these are <SSN>,
<Phone Extension, Name> and <SSN, Name>. Of those listed, only <SSN> is a
candidate key, as the others contain information not necessary to uniquely
identify records.

Q3: Write Data definition language statements (syntax with example)?

ANSWER: A Data Definition Language or Data Description Language (DDL)

is a computer language for defining data structures. The term DDL was first
introduced in relation to the Codasyl database model, where the schema of the
database was written in a Data Description Language describing the records,
fields, and "sets" making up the user Data Model. Later it was used to refer to a
subset of SQL, but is now used in a generic sense to refer to any formal
language for describing data or information structures.

SQL Unlike many data description languages, SQL uses a collection of

imperative verbs whose effect is to modify the schema of the database by
adding, changing, or deleting definitions of tables or other objects. These
statements can be freely mixed with other SQL statements, so the DDL is not
truly a separate language. The most commonly encountered statement is
CREATE TABLE.

CREATE - To make a new database, table, index, or stored query. A

CREATE statement in SQL creates an object inside of a relational database
management system (RDBMS). The types of objects that can be created
depends on which RDBMS is being used, but most support the creation of
tables, indexes, users, synonyms and databases. Some systems (such as
PostgreSQL) allow CREATE, and other DDL commands, inside of a
transaction and thus they may be rolled back.

CREATE TABLE statement :Perhaps the most common CREATE command

is the CREATE TABLE command. The typical usage is:
CREATE [TEMPORARY] TABLE [table name] ( [column definitions] )
[table parameters].

Column Definitions: A comma-separated list consisting of any of the following

Column definition: [column name] [data type] {NULL | NOT NULL} {column
options}

Primary key definition: PRIMARY KEY ( [comma separated column list] )

CONSTRAINTS: {CONSTRAINT} [constraint definition].

DROP statements Drop - To destroy an existing database, table, index,

or view.

A DROP statement in SQL removes an object from a relational database

management system (RDBMS). The types of objects that can be dropped
depends on which RDBMS is being used, but most support the dropping of
tables, users, and databases. Some systems (such as PostgreSQL) allow DROP
and other DDL commands to occur inside of a transaction and thus be rolled
back. The typical usage is simply:

DROP object type object name

ALTER statements Alter - To modify an existing database object.

An ALTER statement in SQL changes the properties of an object inside of a

relational database management system (RDBMS). The types of objects that
can be altered depends on which RDBMS is being used. The typical usage is:
ALTER object type object name parameters .

Referential integrity statementsFinally, other kind of DDL sentence in SQL are

the statements to define referential integrity relationships, usually implemented
as primary key and foreign key tags in some columns of the tables.These two
statements can be included inside a CREATE TABLE or an ALTER TABLE
sentence.

PART - B
Q1: Write the purpose of GRANT and REVOKE and explain syntax and example of
GRANT and REVOKE?

ANSWER:GRANT: Grant privilege(Rights which are to be allocated) is used when

we want our database to share with other users, with certain type of right granted to
him. Consider that if we want our enduser to have only access privilege to our
database, we can grant it by executing command.

- Grant privilege is assigned not only on table object, but also views, synonyms,
indexes, sequences,etc.

Syntax:GRANT PRIVILEGES ON

TO :Example

1)SQL> grant select on

emp to endusers;

- here emp is the table_name whose access right is being allocated to the user who
logged on as endusers.

2)SQL>grant insert, select ,delete on

emp to operators;

- here user who logged on as operators are granted access, insertion and deletion
right on the database.

3)SQL>grant insert (emp no, e name, job) on

emp to end users;

- In some case, we require to hide the information to particular users, this can be
achieved by grant as in the above command we want to hide the detail of employee
salary to endusers, so by executing above command we can hide the information
regarding emp salary to the endusers.

REVOKE: Revoke privilege(Rights which are to be de-allocated) is used when we

want our database to stop sharing the information with other users, with certain type
of right revoked to him. Consider that if we want our operators to have only access
privilege to our database, we can revoke it by executing command.

- Revoke privilege is assigned not only on table object, but also views, synonyms,
indexes, sequences,etc.

Syntax
REVOKE PRIVILEGES ON

FROM :example

1)SQL> revoke insert, delete on

emp from operators.

Q2:-Explain Aggregate functions with example?

ANSWER:Aggregate functions are functions that take a collection of values as input

and return a single value.SQL offers five aggregate functions:

 AVERAGE – avg.
 MINIMUM – min.
 MAXIMUM- max.
 TOTAL – sum.
 COUNT – count.

OrderID OrderDate OrderPrice OrderQuantity CustomerName

1 12/22/2005 160 2 Smith
2 08/10/2005 190 2 Johnson
3 07/13/2005 500 5 Baldwin
4 07/15/2005 420 2 Smith
5 12/22/2005 1000 4 Wood
6 10/2/2005 820 4 Smith
7 11/03/2005 2000 2 Baldwin
COUNT - The SQL COUNT function returns the number of rows in a table satisfying
the criteria specified in the WHERE clause. If we want to count how many orders has
made a customer with CustomerName of Smith, we will use the following SQL
COUNT expression.

SELECT COUNT (*) FROM Sales

WHERE CustomerName = 'Smith'.

The SQL SUM function is used to select the sum of values from numeric column.
Using the Sales table, we can get the sum of all orders with the following SQL SUM
statement:

The SQL AVG function retrieves the average value for a numeric column. If we need
the average number of items per order, we can retrieve it like this:

SELECT AVG(OrderQuantity) FROM Sales.

The SQL MIN function selects the smallest number from a numeric column. In order
to find out what was the minimum price paid for any of the orders in the Sales table,
we use the following SQL expression:

SELECT MIN(OrderPrice) FROM Sales.

The SQL MAX function retrieves the maximum numeric value from a numeric
column. The MAX SQL statement below returns the highest OrderPrice from the
Sales table:

SELECT MAX(OrderPrice) FROM Sales.

Q3: Explain Relational database management system and also explain three
components?

ANSWER: A relational database management system (RDBMS) is a program that

lets you create, update, and administer a relational database. Most commercial
RDBMS's use the Structured Query Language (SQL) to access the database, although
SQL was invented after the development of the relational model and is not necessary
for its use.

The leading RDBMS products are Oracle, IBM's DB2 and Microsoft's SQL Server.
Despite repeated challenges by competing technologies, as well as the claim by some
experts that no current RDBMS has fully implemented relational principles, the
majority of new corporate databases are still being created and managed with an
RDBMS.

COMPONENTS

Interface drivers : A user or application program will begin either schema

amendment or content amendment. These drivers are built on peak of SQL. They give
methods to get ready statements, execute statements, fetch results, etc. Some examples
include DDL, DCL, DML, ODBC, and JDBC. Some vendors make available language
specific proprietary interfaces. For example MySQL provides drivers for PHP,
Python, etc.

SQL Engine : This is in charge for interpreting and executing the SQL query. It
consists of three major components.
Storage Engine : This component stores and retrieves data records. It as well
provides a method to store metadata and control information such as undo logs, redo
logs, lock tables, etc.