DBMS EXAMPLES

HERE ARE SOME OF THE EXAMPLES IN DATABASE MANAGEMENT SYSTEMS.



1. Consider the Insurance database given below. The primary keys are underlined and
the data types are specified.

PERSON (DRIVER_ID#: STRING, NAME: STRING, ADDRESS: STRONG)
CAR (REGNO: STRING, MODEL: STRING, YEAR: INT)
ACCIDENT (REPORT_NO: INT, ADATE: DATE, LOCATION: STRING)
OWNS (DRIVER_ID#: STRING. REGNO: STRING)
PARTICIPATED(DRIVER_ID#: STRING, REGNO: STRING, REPORT_NO:INT,
DAMAGE_AMT: INT)


SOLUTION:

1) Create the above tables by properly specifying the primary keys and the foreign
keys.

CREATE TABLE PERSON
(
DRIVER_ID# VARCHAR2(10),
NAME VARCHAR2(20),
ADDRESS VARCHAR2(15),
PRIMARY KEY(DRIVER_ID#)
);

CREATE TABLE CAR
(
REGNO VARCHAR2(10),
MODEL VARCHAR2(10),
YEAR NUMBER(4),
PRIMARY KEY(REGNO)
);

CREATE TABLE ACCIDENT
(
REPORT_NO NUMBER(5),
ADATE DATE,
LOCATION VARCHAR2(15),
PRIMARY KEY(REPORT_NO)
);

CREATE TABLE OWNS
(
DRIVER_ID# VARCHAR2(10),
REGNO VARCHAR2(10),
PRIMARY KEY(DRIVER_ID#,REGNO),
FOREIGN KEY(DRIVER_ID#) REFERENCES PERSON(DRIVER_ID#),
FOREIGN KEY(REGNO) REFERENCES CAR(REGNO)
);

CREATE TABLE PARTICIPATED
(
DRIVER_ID# VARCHAR2(10),
REGNO VARCHAR2(10),
REPORT_NO NUMBER(5),
DAMAGE_AMT NUMBER(7,2),
PRIMARY KEY(DRIVER_ID#,REGNO,REPORT_NO),
FOREIGN KEY(DRIVER_ID#,REGNO) REFERENCES OWNS(DRIVER_ID#,REGNO) ON
DELETE CASCADE,
FOREIGN KEY(REPORT_NO) REFERENCES ACCIDENT(REPORT_NO) ON
DELETE CASCADE
);


2) Enter at least five tuples for each relation.

INSERT INTO PERSON VALUES
('&DRIVER_ID#','&NAME','&ADDRESS');
INSERT INTO CAR VALUES
('&REGNO','&MODEL',&YEAR);
INSERT INTO ACCIDENT VALUES
(&REPORT_NO,'&ADATE','&LOCATION');
INSERT INTO OWNS VALUES
('&DRIVER_ID#','&REGNO');
INSERT INTO PARTICIPATED VALUES
('&DRIVER_ID#','&REGNO','&REPORT_NO',&DAMAGE_AMT);


3)Demonstrate how you update damage amount for the car with a specific regno in
accident with reportno 12 to 25000

UPDATE PARTICIPATED
SET DAMAGE_AMT=25000
WHERE REPORT_NO =12 AND REGNO='KA04Q2301';


4)Find the total no of people who owned cars that were involved in accidents 2002.

SELECT COUNT(O.DRIVER_ID#) FROM OWNS O,ACCIDENT A,PARTICIPATED P
WHERE A.REPORT_NO=P.REPORT_NO AND O.DRIVER_ID#=P.DRIVER_ID# AND A.ADATE
LIKE ’__-__-2002’;


5)Find the number of accidents in which cars belonging to a specific model were involved .

SELECT COUNT(*),MODEL
FROM ACCIDENT A, PARTICIPATED P, CAR C
WHERE A.REPORT_NO=P.REPORT_NO
AND
P.REGNO=C.REGNO
AND
C.MODEL='MARUTHI-DX';



2)Consider the following relations for an Order Processing Database application in a
company. The primary keys are underlined and the data types are specified.

CUSTOMER (CUST #: INT, CNAME: STRING, CITY: STRING)
ORDER (ORDER #: INT, ODATE: DATE, CUST #: INT, ORD-AMT: INT)
ITEM (ITEM #: INT, UNIT PRICE: INT)
ORDER - ITEM (ORDER #: INT, ITEM #: INT, QTY: INT)
SHIPMENT (ORDER #: INT, WAREHOUSE#: INT, SHIP-DATE: DATE)
WAREHOUSE (WAREHOUSE #: INT, CITY: STRING)


SOLUTION:


CREATE TABLE CUSTOMER
(
CUST# NUMBER(5),
CNAME VARCHAR2(15) NOT NULL,
CITY VARCHAR2(15),
PRIMARY KEY(CUST#)
);

CREATE TABLE CUSTORDER
(
ORDER# NUMBER(5) CONSTRAINT PK1 PRIMARY KEY,
ODATE DATE,
CUST# NUMBER(5) REFERENCES CUSTOMER(CUST # ),
ORD_AMT NUMBER(5)
);

CREATE TABLE ITEM
(
ITEM NUMBER(5) CONSTRAINT PK2 PRIMARY KEY,
UNITPRICE NUMBER(9,2) NOT NULL
);

CREATE TABLE ORDER_ITEM
(
ORDER# NUMBER (5),
ITEM NUMBER (5),
QTY NUMBER(4),
FOREIGN KEY(ORDER # ) REFERENCES CUSTORDER(ORDER # ),
FOREIGN KEY(ITEM) REFERENCES ITEM(ITEM),
PRIMARY KEY(ORDER # ,ITEM)
);

CREATE TABLE WAREHOUSE
(
WAREHOUSE# NUMBER(5),
CITY VARCHAR2(15),
PRIMARY KEY(WAREHOUSE# )
);

CREATE TABLE SHIPMENT
(
ORDER# NUMBER(5),
WAREHOUSE# NUMBER(5),
SHIP_DATE DATE,
FOREIGN KEY(ORDER# ) REFERENCES CUSTORDER(ORDER# ),
FOREIGN KEY(WAREHOUSE # ) REFERENCES
WAREHOUSE(WAREHOUSE #) ,
PRIMARY KEY(ORDER# ,WAREHOUSE# )
);


2)Insert the records into the relations.

INSERT INTO CUSTOMER VALUES(&CUSTNO,'&CNAME','&CITY');
INSERT INTO CUSTORDER
VALUES(&ORDER_NO,'&ODATE',&CUSTNO,&ORD_AMT);
INSERT INTO ITEM VALUES(&ITEM_NO,&UNITPRICE);
INSERT INTO ORDER_ITEM VALUES(&ORDER_NO,&ITEM_NO,&QTY);
INSERT INTO WAREHOUSE VALUES(&WAREHOUSE_NO,'&CITY');
INSERT INTO SHIPMENT
VALUES(&ORDER_NO,&WAREHOUSE_NO,'&SHIPDATE');

3)Producing the listing: custname, order#, avg_order_amt, where the
middle column is the total no of orders made by the customer, and the last column is the
average order amount for the customer.

SELECT C.CNAME, COUNT(*),AVG(CO.ORD_AMT)
FROM CUSTOMER C, ORDER O
WHERE
C.CUST#=O.CUST# GROUP BY C.CNAME;


4)List the order# for orders that where shipped from all the warehouse
that the company has in specific city.

SELECT ORDER#
FROM WAREHOUSE W, SHIPMENT S
WHERE W.WAREHOUSE#=S.WAREHOUSE# AND CITY='BLORE';


5)Demonstrate how to delete item 10 from the item table and make that field null in
the ORDER-ITEM table

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='ORDER_ITEM';

ALTER TABLE ORDER_ITEM DROP CONSTRAINT SYS_C002736;
ALTER TABLE ORDER_ITEM ADD CONSTRAINT FK1 FOREIGN KEY(ITEM#)
REFERENCES ITEM(ITEM#) ON DELETE SET NULL;