Database Management Systems (DBMS) Lab Program 1

CREATE TABLE person (

driverid VARCHAR(30),

name VARCHAR(30),

address VARCHAR(30),

PRIMARY KEY (driverid)

);



CREATE TABLE car (

regno VARCHAR(30),

model VARCHAR(30),

year NUMBER(4),

PRIMARY KEY (regno)

);



CREATE TABLE accident (

reportno NUMBER(10),

acccdate DATE,

location VARCHAR(30),

PRIMARY KEY (reportno)

);



CREATE TABLE owns (

driverid VARCHAR(30),

regno VARCHAR(30),

PRIMARY KEY(driverid,regno),

FOREIGN KEY(driverid) REFERENCES person(driverid),

FOREIGN KEY(regno) REFERENCES car(regno)

);



CREATE TABLE participated (

driverid VARCHAR(30),

regno VARCHAR(30),

reportno NUMBER(30),

damage NUMBER(10),

PRIMARY KEY(driverid,regno,reportno),

FOREIGN KEY(driverid) REFERENCES person(driverid),

FOREIGN KEY(regno) REFERENCES car(regno),

FOREIGN KEY(reportno) REFERENCES accident(reportno)

);



INSERT INTO person VALUES('1', 'a', 'x');

INSERT INTO person VALUES('2', 'b', 'x');

INSERT INTO person VALUES('3', 'c', 'x');

INSERT INTO person VALUES('4', 'd', 'x');

INSERT INTO person VALUES('5', 'e', 'x');

INSERT INTO person VALUES('6', 'f', 'x');



INSERT INTO car VALUES('r1', 'tata',1998);

INSERT INTO car VALUES('r2', 'uno',1998);

INSERT INTO car VALUES('r3', 'beetle',1998);

INSERT INTO car VALUES('r4', 'zen',1998);

INSERT INTO car VALUES('r5', 'maruti',1998);



INSERT INTO owns VALUES('1', 'r1');

INSERT INTO owns VALUES('2', 'r2');

INSERT INTO owns VALUES('3', 'r3');

INSERT INTO owns VALUES('4', 'r4');

INSERT INTO owns VALUES('5', 'r5');

INSERT INTO owns VALUES('1', 'r2');

INSERT INTO owns VALUES('2', 'r1');

INSERT INTO owns VALUES('3', 'r1');



INSERT INTO accident VALUES(1,'1-jan-2010','x');

INSERT INTO accident VALUES(2,'1-jan-2010','x');

INSERT INTO accident VALUES(3,'1-jan-2010','x');

INSERT INTO accident VALUES(4,'1-jan-2010','x');

INSERT INTO accident VALUES(5,'1-jan-2010','x');

INSERT INTO accident VALUES(6,'1-jan-2000','x');





INSERT INTO participated VALUES('1','r1',1,1000);

INSERT INTO participated VALUES('2','r1',1,1000);

INSERT INTO participated VALUES('3','r3',3,1000);

INSERT INTO participated VALUES('1','r2',2,1000);

INSERT INTO participated VALUES('2','r3',3,1000);

INSERT INTO participated VALUES('4','r4',4,1000);

INSERT INTO participated VALUES('6','r4',4,1000);

INSERT INTO participated VALUES('5','r3',6,1000);



DESC person;

DESC car;

DESC owns;

DESC accident;

DESC participated;



(III)

a.



UPDATE participated SET damage = 25000 WHERE regno LIKE 'r4' AND reportno=4;



b.

INSERT INTO accident VALUES(7,'1-jan-2000','x');

INSERT INTO participated VALUES('6','r5',7,1000);



(IV)



SELECT count(DISTINCT o.driverid)

FROM owns o,

(

SELECT DISTINCT p.driverid

FROM participated p,accident a

WHERE p.reportno=a.reportno AND a.acccdate LIKE '%2010%'

) X

WHERE o.driverid = X.driverid ;



(V)



SELECT count(*)

FROM car c,participated p

WHERE p.regno=c.regno AND c.model LIKE 'beetle';