DBMS LABS

                                   

          

MANAV RACHNA UNIVERSITY, FARIDABAD 

Department of Computers Science And Engineering

 

NOTE:U MUST DO FROM 0 TO END AND ON UR OWN PC  

THIS POST IS FOR HELP NOT DIRECT CUT COPY PASTE

 

Blooms Taxonomy Level: BT2, BT3

 

Create the following relations along with the constraints specified:

1.      Client (clno char(3), clname varchar(30), cladd varchar(40))

·        PRIMARY KEY- clno

·        clname can not be left blank

 

2.    Project (pno char(3), pname varchar(30), pstdate date, clno char(3))

·        PRIMARY KEY- pno

·       FOREIGN KEY – clno refers to Client relation

·       pname can not be left blank

 

3.      Department (dno char(3), dname varchar(20), dloc varchar(20), dhead char(3))

·       PRIMARY KEY – dno

·        FOREIGN KEY – dhead refers to eno of Employee relation

·        dname can not be left blank

·        default dloc is delhi

 

 Download solved lab file 1

Download lab file -0

1. Employee (eno char(3), ename varchar(20), eadd varchar(30), ephone char(8), esal int, grade char(1), edoj date, dno char(3), emgrno char(3))

· PRIMARY KEY – eno

· FOREIGN KEY – dno refers to Department relation

· FOREIGN KEY – emgrno refers to eno of Employee relation

· salary can hold values between 1000 and 60000

· grade can be A – if salary less than 20000, B – if salary is between 20000 and 40000, C – if salary is greater than 40000

· doj should be more than 01-jan-1990

· ephone can have unique values only

2. Proj_alloc (eno char(3), pno char(3), hrs int)

· PRIMARY KEY – eno, pno

· FOREIGN KEY – eno refers to Employee relation

· FOREIGN KEY – pno refers to Project relation

· hrs can have values more than 2

3. Dependent (eno char(3), dpname varchar(20), dpdob datetime, dprelation varchar(20))

· PRIMARY KEY – eno, dpname

· FOREIGN KEY – eno refers to Employee relation

· Dprelation can have only one of the values {son,daughter,father,mother}

DBMS-LAB-2 SOLVED

1. Client (clno char(3), clname varchar(30), cladd varchar(40))

CLNO CLNAME CLADD

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

c01 xyz Delhi

c02 abc Fbd

c03 def Delhi

c04 ghi Ggn

c05 klm Fbd

c06 nop Delhi

2. Project (pno char(3), pname varchar(30), pstdate date, clno char(3))

PNO PNAME PSTDATE CLNO

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

p01 ab 01-JAN-06 c01

p02 bc 01-FEB-06 c02

p03 cd 20-APR-05 c02

p04 de 01-JAN-04 c03

p05 ef 02-MAY-05 c04

p06 fg 03-FEB-06 c03

3. Department (dno char(3), dname varchar(20), dloc varchar(20), dhead char(3))

DNO DNAME DLOC DHEAD

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

d01 finance Delhi e01

d02 accounts Fbd e03

d03 personal Ggn e05

d04 marketing Delhi e06

DBMS LAB-3 SOLVED

 

A.     Insert the records in the following relations:

 

1.    Employee (eno char(3), ename varchar(20), eadd varchar(30), ephone char(8), esal int, grade char(1), edoj date, dno char(3), emgrno char(3))

 

ENO ENAME EADD EPHONE ESAL  GRADE EDOJ      DNO EMGRNO

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

e01        a             Delhi    123453     60000      C      01-JAN-06 d01

e02        b             Ggn      231456     20000      B      02-JAN-06 d01   e01

e03        c             Fbd       341566     15000     A       08-FEB-05   d02   e02

e04        d             Fbd       789012     5000       A       03-JUN-04    d03   e03

e05        e             Ggn       345234     6000       A      03-AUG-04   d04   e07

e06        f              Delhi     123876     25000     B      03-MAR-05  d03

e07        g              Fbd       785634     9000       A      08-MAR-06  d04   e08

e08        h              Ggn       895634    18000       A    02-JAN-06   d02

2.    Proj_alloc (eno char(3), pno char(3), hrs int)

 

ENO PNO        HRS

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

e01        p01          4

e02        p02          6

e02        p03          3

e03        p03          2

e04        p02          8

e06        p01          9

e05        p02         10

e05        p03         20

e07        p05          4

e08        p05          6

e08        p06          8

e04        p03          7

3.    Dependent (eno char(3), dpname varchar(20), dpdob date, dprelation varchar(20))

 

ENO DPNAME             DPDOB        DPRELATION

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

e02        ab                        01-MAR-65  father

e02        bc                          02-AUG-80  brother

e03        dc                          02-JUL-89   son

e03        bc                          02-JUL-89   daughter

e05        ef                           06-JAN-60    mother

e06        fg                          07-JUL-50   daughter

B.     Update the following information in the relations:

 

1.      Update the salary of employee e06 by increasing it by 10%.

2.      Update the dependent relationship of e03 employee with dependent named fg.

3.      Update the location of department no d04 to Ggn.

4.      Update the hours allocated for employee e07 for project p05 to 6 hrs.

5.      Update the name of project no p06 to gh.

6.      Update the client address of client no c06 to Ggn.

 

 

 

DBMS LAB-4 SOLVED