ASSIGNMENT -1

Q:1 Write the definitions : --

Database: A database is a collection of inter-related data.

DBMS: A database management system is a collection of inter related data and a set of

programs to access those data.

Q:2 Explain the ANSI /SPARC architecture.

The objective of the three-level architecture is to separate the users’ view(s) of the
database from the way that it is physically represented. This is desirable since:

• It allows independent customised user views: Each user should be able to access
the same data, but have a different customised view of the data. These should be
independent: changes to one view should not affect others.
• It hides the physical storage details from users: Users should not have to deal with
physical database storage details. They should be allowed to work with the data
itself, without concern for how it is physically stored.
• The database administrator should be able to change the database storage
structures without affecting the users’ views : From time to time rationalisations
or other changes to the structure of an organisation’s data will be required.
• The internal structure of the database should be unaffected by changes to the
physical aspects of the storage : For example, a changeover to a new disk.
 • The database administrator should be able to change the conceptual or global
structure of the database without affecting the users : This should be possible
while still maintaining the desired individual users’ views.

Internal level is the lowest level.it describes how the data are actually stored on

storage device.

Logical level is the next higher level.it describes what data are stored in the database

and what relationship exist among those data.it is also known as conceptual

level.application developers also work on this level.

View level is the highest level.it describes only part of the entire database that a

particular end user concerns.

Q:3 What are the main five function of Database Administrator?

Scema definition:The DBA is responsible to determine what data to keep in the

database.in other words the DBA defines the logical schema of a database.according to

this schema database will be developed.

Implementing integrity constraints:The DBA determines required integrity

constraints.database must satisfy such kind of constraints.

Approving data access:The DBA determines which user needs access to which part of

the database.according to this,various types of authorization are granted to different users.

Monitoring performance: The DBA monitors performance of the system.the DBA

ensures the better performance is maintained by making changes in physical or logical

schema if required.

Backup and recovery:Database is valuable asset for any organization.it should not be

lost or damaged.the DBA ensures this by periodically backing up the database on

magnetic tapes or remote servers.in case of failures,such as flood or virus attack,databse

is recovered from this backup.

Q:4 Explain the following terms:

Data: Data means known facts,that can be recorded and have implicit meanings.

Information:Information means processed data.

Schema:The overall logical structure if the database is known as Schema.

Catalog: A catalog is a directory of information about data sets, files, or a database. A

catalog usually describes where a data set, file or database entity is located and may also

include other information, such as the type of device on which each data set or file is

stored.

Data dictionary: A data dictionary is metadata repository,centralized repository of

information about data such as meaning, relationships to other data, origin, usage, and

format.

Q:5 Explain the components of SQL.

SQL consists of three components:

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)
3. Data Control Language (DCL)

The Data Definition Language (DDL). This component of the SQL language is used to
create and modify tables and other objects in the database. For tables there are three main
commands:

CREATE TABLE tablename to create a table in the database

DROP TABLE tablename to remove a table from the database
ALTER TABLE tablename to add or remove columns from a table in the database

The Data Manipulation Language (DML) component of the SQL language is used to
manipulate data within a table. There are four main commands:
SELECT to select rows of data from a table
INSERT to insert rows of data into a table
UPDATE to change rows of data in a table
DELETE to remove rows of data from a table

The Data Control Language (DCL) This component of the SQL language is used to
create privileges to allow users access to, and manipulation of, the database. There are
two main commands:

GRANT to grant a privilege to a user

REVOKE to revoke (remove) a privilege from a user.

Q:6 List and explain the tools of Oracle.

1. SQL-Structured Query Language.

A language that provides an interface to relational database systems.

Provides 9 commands,such as-

 CREATE,DROP,ALTER,for tables

 INSERT,UPDATE,DELETE for rows

 GRANT,REVOKE for permissions.

 SELECT for query.

This commands are common to all RDBMS.

2. SQL * PLUS

 The SQL * PLUS tool is made up of two distinct parts

Interactive SQL and PL/SQL

 Interactive SQL

A super set of basic SQL.It is simply referred as SQL.It provides functionalities to

create,access,and maintain all database objects such as tables,views,indexes,etc.

 PL/SQL
 A super set of basic iSQL.it provides proceduralcapabilities such as

branching,looping.it can be used to create programs for validation and

manipulation of table data.

3. IDS-Internet Development Suite

This tool was previously known as Developer 2000.

Include Following four tools:

1) Forms

2) Reports

3) Menus

4) Graphics

4. SQL * Loader

Converts files from other RDBMS to ORACLE.

5. SQL * DBA

Provides database administration functionalities.

6. Oracle Financials

Provides accounting functionalities.

7. Oracle web Browser

Provides browsing functionalities for oracle data.

8. Personal Oracle

It is a single user version of the oracle database engine.

Q: 7 Explain the advantage of DBMS.

• 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.

Q:8 Explain all Data Types in SQL.

No. Data Type Represents...

1. NUMBER(P,S) Floating point number.

p:precision precision can be up to 38 digits,s:scale-

number of digits to the right of the decimal point.

2. CHAR(size) Stores character string of fixed length.size represents
 the no. characters (length) to be stored .default size is

1.

Maximum length is 255 characters.

3. VARCHAR(size) Stores character strings of variable lenghth.More

flexible than CHAR maximum length is 2000

characters. Size must be specified explicitly.

4. LONG Stores large amount of characters strings variable

length. Maximum length is up to 2 GB.Only one

column per table can be defined as LONG.

5. RAW Stores binary type data. maximum length is up to 255

bytes
6. LONG RAW Stores large amount of binary type data. Often

referred as binary large object.(BLOB).Maximum

length is up to 2 GB.

Q:9 Explain the difference between data and information.

Data Information
Data means known facts,that can be Information means processed data

recorded and have implicit meanings.

Data are row materials to derive Information is a product derived from

information data.
Data convey something less. Information conveys something more .
Data is comparatively less useful. Information is comparatively more

useful

Q : 10 Write down uses and Syntax for all bellow Queries in SQL: --

a) Create
 CREATE TABLE table_name(

column_name1 data_type,

column_name2 data_type,

column_name3 data_type,

.... );

b) Insert

INSERT INTO table_name

VALUES (value1, value2, value3,...);

(OR)

INSERT INTO table_name (column1, column2, column3,...)

VALUES (value1, value2, value3,...);

c) Update
d) Alter (Adding New Column)

ALTER TABLE table_name

ADD (column_name datatype(size))

; e) Alter (Modifying in Existing Column)

ALTER TABLE table_name

MODIFY( column_name datatype(new size));

f) Alter(Drop column)

ALTER TABLE table_name

DROP COLUMN column_name;

f) Delete

DELETE FROM table_name

WHERE some_column=some_value;

g) Select(all rows and all columns)

Select * from table_name;
h) Select (selected rows and all columns)

SELECT column_name(s)

FROM table_name;

i) Select (selected rows and selected columns)

SELECT column_name(s)

FROM table_name;

j) Drop

DROP TABLE table_name;

k) Rename

Rename old_table_name TO new_table_name;