DBMS LAB

DBMS EXPERIMENTS

Name of the Lab: DATABASE MANAGEMENT SYSTEM LAB

 

Experiment: 1

Aim: Hands on experience on any RDBMS to implement the role of Database Administrator like creating the users, alter user, grant and revoke of rights of user.

 

THEORY:

SQL: It is structured query language, basically used to pass the query to retrieve and manipulate the information from database. Depending upon the nature of query,  SQL is divided into different components:

•  DDL (Data Definition Language )

•  DML ( Data Manipulation Language )

•  DCL (Data Control Language )

 

DDL: The Data Definition Language (DDL) is used to create the database (i.e. tables,keys,relationships etc), maintain the structure of the database and destroy databases and database objects.

 

E.g. Create,Drop, Alter, Describe, Truncate

 

1.  CREATE statements: It is used to create the table.

Syntax:

CREATE TABLE table_name(columnName1 datatype(size), columnName2 datatype(size),………);

2. DROP statements: To destroy an existing database, table, index, or view. If a table is dropped all records held within it are lost and cannot be recovered.

Syntax:

 DROP TABLE table_name;

3.ALTER statements: To modify an existing database object.

 Adding new columns:

Syntax:

Alter table table_name Add(New_columnName1 datatype(size),

New_columnName2 datatype(size),………);

 

Dropping a columns from a table :

Alter table table_name DROP column columnName:

 Modifying Existing columns:

Syntax:

Alter table table_name Modify (columnName1

Newdatatype(Newsize));

4.Describe statements: To describe the structure (column and data types) of an existing database, table, index, or view.

Syntax:

DESC table_name;

5.  Truncate statements: To destroy the data in an existing database, table, index, or view. If a table is truncated all records held within it are lost and cannot be recovered but the table structure is maintained.

Syntax :

TRUNCATE TABLE table_name;

Data Control Language (DCL) consists of various commands which are related to data sharing and security of data in database.

They are:

GRANT

REVOKE

Granting Privileges:

Objects that are created by a user are owned and controlled by that user. If user wishes to access any of the objects belonging to another user, the owner of the object will have to give permissions for such access. This is called Granting of Privileges.

Granting privileges using the GRANT statements:

The GRANT statements provide various types of access to database objects such as tables, views.

Syntax:

GRANT {object privileges}

ON object name

TO username;

Object Privileges: each object privilege that is granted authorizes the grantee to perform some operation on the object. The user can grant all the privileges or grant only specific object privileges.

The list of object privileges is as follows:

•  ALTER: allows the grantee to change the table definitions with the ALTER table                 command.

•  DELETE: allows the grantee to remove the records from the table with the DELETE                    command.

•  INDEX: allows the grantee to create an index on the table with the CREATE INDEX      command.

•  INSERT: allows the grantee to add records to the table with the INSERT command.

•  SELECT: allows the grantee to query the table with SELECT command.

•  UPDATE: allows the grantee to modify the records in the table with the UPDATE command.

Revoking privileges given:

Privileges once given can be denied to a user using the REVOKE command. The object owner can revoke privileges granted to another user. A user of an object who is not owner, but has been granted the GRANT privilege, has the power to REVOKE the privileges from the grantee.

 

Revoking permission using the REVOKE statement:

The REVOKE statement is used to deny the grant given on an object.

Syntax:

REVOKE {object privileges}

ON object name

FROM username;

The REVOKE command is used to revoke object privileges that the user previously granted to the Revoke.

The REVOKE command cannot be used to revoke the privileges granted through operating system.

Conclusion: Implemented various DDL commands. DCL statements are used to provide data sharing of data in database

 

 

Experiment: 2

Aim: Create a Database of employees and departments with the following details.

          Table name Fields name

                Emp empno (primary key), ename,

                Edetails, ebasic, salary-

                Details, e-deptno (foreign key)

           Dept deptno (primary key), dept-name, dept. Details.

Theory: 

Syntax For Create A from An Existing Table With All Fields

SQL> CREATE TABLE <TRAGET TABLE NAME> SELECT * FROM

<SOURCE TABLE NAME>;

SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;

 

Table created.

SQL> DESC EMP1

 

 

Name                                      Null?	Type
----------------------------------- --------	------------------
EMPNO                                 NOT	NUMBER(4)
ENAME	VARCHAR2(10)
DESIGNATIN	VARCHAR2(10)
SALARY	NUMBER(8,2)
DEPTNAME	VARCHAR2(10)

 

Experiment: 3

Aim: Create suitable tables to perform the following relational operations select project product join restriction union intersection difference

 

Theory:

 

SELECT COMMAND:

TYPE 1:

SQL> select * from sub; 

NAME             AGE      MARK1            MARK2         MARK3

--------           ----------     ----------           ---------------   ------------

sachin              12                    90                    90                    90

sachin              22                    90                    89                    78

ranjan              21                    89                    78                    80

gayathri           12                    78                    89                    90

rajini                22                    90                    87                    56

TYPE 2:

SQL> select name,age from sub; 

NAME            AGE

--------             ----------

sachin              12

sachin              22

ranjan              21

gayathri           12

rajini                22

 

 

TYPE 3:

SQL> select distinct name from sub; 

NAME

-------- Gayathri

Rajini

Ranjan

Sachin

TYPE 4:

SQL> select * from sub where mark2=90; 

NAME                         AGE               MARK1          MARK2          MARK3

--------                          ----------           ----------           ----------           -----------

sachin                          12                    90                    90                    90 

SQL> delete from sub where name='sachin';2 rows deleted.SQL> select * from sub; 

NAME	AGE	MARK1		MARK2	MARK3
--------	----------	----------		---------- -	---------
ranjan	21	89		78	80
gayathri	12	78		89	90
rajini	22	90		87	56

TYPE 5:

SQL> select name from sub order by name; 

NAME

-------- 

Gayathri

Rajin

Iranjan

TYPE 6:

SQL> select name from sub order by name desc; 

NAME

Ranjan

Rajini

Gayathri

PROJECT COMMAND

 

CREATE COMMAND:

SQL> create table sub (name varchar(8),age number(5),mark1 number(4),mark2 number(4),2 mark3 number(4)); Table created.

DESC COMMAND: 

SQL> desc sub;

Name	Null?	Type
----------	------------	---------
NAME		VARCHAR2(8)
AGE		NUMBER(5)
MARK1		NUMBER(4)
MARK2		NUMBER(4)
MARK3		NUMBER(4)

ALTER COMMAND:

1)ADD COMMAND:

SQL> alter table sub add(total number(6));

Table altered.

SQL> desc sub;

Name	Null?               Type
------------	---------------- -------------
NAME	VARCHAR2(8)
AGE	NUMBER(5)
MARK1	NUMBER(4)
MARK2	NUMBER(4)
MARK3	NUMBER(4)
TOTAL	NUMBER(6)

RENAME COMMAND:

SQL> rename sub to subha;

Table renamed.

SQL> desc subha;

Name                Null?              Type

---------            ----------            ----------

NAME                                    VARCHAR2(8)           

AGE                                        NUMBER(5)

MARK1                                  NUMBER(4)

MODIFY COMMAND:

SQL> alter table sub modify(mark3 number(6)); Table altered.

SQL> desc sub;

Name		Null?		Type
---------		-------------		----------
NAME				VARCHAR2(8)
AGE				NUMBER(5)
MARK1				NUMBER(4)
MARK2				NUMBER(4)
MARK3				NUMBER(6)
TOTAL				NUMBER(4)
MARK3				NUMBER(6)
	TOTAL			NUMBER(6)

TRUNCATE COMMAND:

SQL> truncate table subha;

Table truncated.

SQL> desc sub;

ERROR:ORA-04043: object sub does not exist

JOINS:

SQL>selectemps1.ename,emps1.eno,customers1.eno,customers1.salary fromemps1,customers1 where emps1.eno=customers1.eno; 

ENAME	ENO	ENO	SALARY
----------	----------	----------	----------
naga	101	101	10000
navin	111	111	10500
raj	141	141	5000

NON EQUI JOINS:

SQL>

selectemps1.ename,emps1.eno,customers1.salary,customers1.eno,customers.locationfrom  emps1,customers1.eno<>customers1.eno;

ENAME	ENO		SALARY	ENO		LOCATION
----------	----------		----------	----------		----------
navin	111		10000	101		CHENNAI
ram	121		10000	101		CHENNAI
raj	141		10000	101		CHENNAI
naga	101		10500	111		PONDY
ram	121		10500	111		PONDY
raj	141		10500	111		PONDY
naga	101		8000	131		AVADI
navin	111		8000	131		AVADI
ram	121		8000	131		AVADI
raj	141		8000	131		AVADI
naga	101		5000	141		LOCO
navin	111		5000	141		LOCO
ram	121		5000	141		LOCO
naga	101		9800	151		CHOOLAI
navin	111		9800	151		CHOOLAI
ram	121		9800	151		CHOOLAI
raj	141		9800	151		CHOOLAI

 

 

17 rows selected.

LEFT OUTER JOINS:

SQL>select emps1.ename,emps1.eno,customers1.salary,customers1.eno fromemps1,customers1  where emps1.eno(+)=customers1.eno;

ENAME		ENO	SALARY		ENO
----------		----------	----------		----------
naga		101	10000		101
navin		111	10500		1118000 131
raj		141	5000		1419800 151

RIGHT OUTER JOINS:

SQL> select emps1.ename,emps1.eno,customers1.eno,customers1.salary fromemps1,customers1  where emps1.eno=customers1.eno(+);

ENAME		ENO	ENO	SALARY
----------		----------	----------	----------
naga		101	101	10000
navin		111	111	10500
ram		121
raj		141	141	5000

 

SET OPERATION

SQL> create table root (name varchar(8),age number(5),mark1 number(4),mark2,number(4),2 mark3 number(5));

Table created.

SQL> insert into root values('&name','&age','&mark1','&mark2','&mark3');

Enter value for name: Sachin

Enter value for age: 12

Enter value for mark1: 89

Enter value for mark2: 89 Enter value for mark3: 90 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('sachin','12','89','89','90')

1 row created.

SQL> /

Enter value for name: kalai

Enter value for age: 20

Enter value for mark1: 90

Enter value for mark2: 90 Enter value for mark3: 78 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('kalai','20','90','90','78')

1 row created.

SQL> /

Enter value for name: rani

Enter value for age: 18

Enter value for mark1: 78

Enter value for mark2: 77

Enter value for mark3: 67

old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('rani','18','78','77','67')

1 row created.

SQL> /

Enter value for name: kalam

Enter value for age: 32

Enter value for mark1: 90

Enter value for mark2: 90 Enter value for mark3: 89 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('kalam','32','90','90','89')

1 row created.

SQL> /

Enter value for name: gokul

Enter value for age: 20

Enter value for mark1: 90

Enter value for mark2: 90 Enter value for mark3: 90 old 1: insert into root values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into root values('gokul','20','90','90','90')

1 row created.

SQL> select * from root; 

 

ranjan	21
sachin	129

rows selected.

UNION ALL COMMAND:

SQL> select name,age from sub union all select name,age from root; 

NAME	AGE
--------	--------
ranjan	21
ayathri	12
rajini	22
sachin	12
ram	21
sachin	12
kalai	20
rani	18
kalam	32
gokul	20

10 rows selected.

INTERSECT:

SQL> select name from sub intersect select name from root; 

NAME

-------- sachin DIFFERENCE or MINUS:

SQL> select name from sub minus select name from root; 

NAME

-------- Gayathri

Rajini

Ram

ranjan

 

 

Experiment: 4

Aim:   Perform the following data manipulation operation on table created in Problem 2

(a)  insertion of records

(b)  deletion of records

(c)  Updating records

 

Theory:

DML & DCL COMMANDS

SQL> create table sub (name varchar(8),age number(5),mark1 number(4),mark2 number(4),2 mark3 number(4)); Table created.

INSERT COMMAND:

TYPE 1:

SQL> insert into sub values('&name','&age','&mark1','&mark2','&mark3');

Enter value for name: Sachin

Enter value for age: 12

Enter value for mark1: 78

Enter value for mark2: 78 Enter value for mark3: 67 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('sachin','12','78','78','67')

1 row created.SQL> /

Enter value for name: ranjan

Enter value for age: 22

Enter value for mark1: 89

Enter value for mark2: 78 Enter value for mark3: 65 old 1: insert into sub values('&name','&age','&mark1','&mark2','&mark3') new 1: insert into sub values('ranjan','22','89','78','65') 1 row created.

SQL> select * from sub; 

NAME             AGE                MARK1          MARK2           MARK3

--------             ----------           ----------           ----------           ------------

sachin              12                    78                     78                    67 ranjan         22                    89                    78                     65

TYPE 2:

SQL> insert into sub(name,age,mark1,mark2,mark3) values ('ram',20,90,90,98); 1 row created.

SQL> select * from sub; 

NAME            AGE                MARK1          MARK2	MARK3
sachin              12                    78                    78	67
ranjan              22                    89                    78	65
ram                  20                    90                    90          TYPE 3: SQL> insert into sub values ('gayathri',13,90,100,98); 1 row created. SQL> select * from sub;	98
NAME            AGE                MARK1          MARK2	MARK3
--------             ----------           ----------          ----------	------------
sachin              12                    78                    78	67
ranjan              22                    89                    78	65
ram                  20                    90                    90	98
gayathri           13                    90                    100        UPDATE COMMAND: SQL> update sub set age=13 where name='ranjan'; 1 row updated. SQL> select * from sub;	98
NAME            AGE                MARK1          MARK2	MARK3
--------             ----------           ----------           ------------	----------
sachin              12                    78                    78	67
ranjan              13                     89                   78	65
ram                  20                    90                    90	98
gayathri           13                    90                    100	98

SQL> alter table sub add(total number(5)); Table altered.

SQL> select * from sub; 

NAME            AGE                MARK1          MARK2	MARK3	TOTAL
--------             ----------           ----------          ----------	---------- -	-----------
sachin              12                    78                    78	67
ranjan              13                    89                    78	65
ram                  20                    90                     90	98
gayathri           13                    90                    100        SQL> update sub set total=mark1+mark2+mark3; 4 rows updated. SQL> select * from sub;	98
NAME            AGE                MARK1          MARK2	MARK3	TOTAL
--------             ----------           ----------          ----------	----------	-----------
sachin              12                    78                    78	67	223
ranjan              13                    89                    78	65	232
ram                  20                    90                    90	98	278
gayathri           13                    90                    100	98	288

SQL> select avg(mark1) from sub;

AVG(MARK1)

----------

86.75

SQL> select count(name) from sub;

COUNT(NAME)

-----------

4

SQL> select sum(mark2) from sub;

SUM(MARK2)

----------

346

DELETE COMMAND:

SQL> delete from sub where name='ram'; 1 row deleted.

SQL> select * from sub; 

 

 

NAME AGE MARK1 MARK2 MARK3 TOTAL

-------- ---------- ---------- ---------- -------- -----------

sachin  12	78	78	67	223
ranjan  13	89	78	65	232
gayathri 13	90	100	98	288

SQL> delete from sub; 3 rows deleted. SQL> select * from sub; no rows selected

 

Experiment: 5

Aim- Creation and modification of databases through ER diagram, nornmalisation Creation, updation, insertion and deletion of tables.

 

THEORY:

• Database :

A Database is a collection of interrelated data and a Database Management

System is a a software system that enables users to define, create and maintain the database and which provides controlled access to the database.

•  ER- Diagram :

It is an Entity –Relationship diagram which is used to represent the relationship between different entities. An entity is an object in the real world which is distinguishable from other objects. The overall logical structure of a database can be expressed graphically by an ER diagram, which is built up from following components.

Rectangles: represent entity sets.

Ellipses: represent attributes.

Diamonds: represent relationships among entity sets.

Lines: link attribute to entity sets and entity sets to relationships.

•  Mapping Cardinalities :

It expresses the number of entities to which another entity can be associated via a relationship set. For a binary relationship set R between entity sets A and B.

The Mapping Cardinalities must be one of the following.

•  One to one

•  One to many

•  Many to one

•  Many to many

Conversion from ER diagram to tables

Step 1: Mapping of Regular Entity Types:

For each regular(strong) entity type E in the ER schema create a relation R that includes all the simple attributes of E. Include only the simple component attributes of a composite ,the set of simple attributes that form it will together form the primary key of R.

Step 2: Mapping of Weak Entity Types:

For each weak entity type W in the ER schema with owner entity type E, create a

relation R and include all the attributes of W and the specific attributes of R. In addition include the Primary key attribute(s) of the owner entity type. The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the Weak Entity Type W.

Step 3 : Mapping of Binary 1:1 Relationship Types :

For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the Entity Types participating in R. Choose one of the relations- S, say – and include as a Foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. Include all the simple attributes (or simple components of a composite attributes) of the 1:1 relationship type R as attributes of S.

Step 4: Mapping of Binary 1:N Relationship Types :

For each regular binary 1:N relationship type R, identifying the relation S that represent the participating Entity Type at the N side of the relationship type. Include as Foreign key in S the primary key of T that represents the other entity type participating in R; this is done because each entity instance on the N side is related to at most one entity instance on the 1-side of the relationship Type. Include any simple attributes (or simple components of composite attributes) of the 1:N relationship type as attributes of S.

Step 5: Mapping of Binary M:N Relationship Types :

For each binary M:N relationship type R, create a new relation S to represent R. Include the primary key of the participating entity types; their combination will form the primary key of S. Also include any simple attributes of the M:N relationship types (or simple components of a composite attributes) as attributes of S.

Step 6: Mapping of Multivalued Attributes:

For each Multivalued Attribute A, create a new relation R. This relation R will

include an attribute corresponding to A, plus the primary key attribute K- of the relation that represents the entity type or relationship type that has A as an attribute. The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components.

Step 7: Mapping of N-ary Relationship Types:

For each N-ary relationship type R, where N>2, create a new relation S to represent

R. Include the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the N-ary relationship types (or simple components of a composite attributes) as attributes of S.

Conclusion:

Referring to the problem statement the ER diagram was designed and converted into the tables.