EX-2 : CREATING A SET OF TABLES, ADD FOREIGN KEY CONSTRAINTS AND
INCORPORATE REFERENTIAL INTEGRITY
AIM:
To Create a set of tables, add foreign key constraints and incorporate referential integrity
QUERIES:
Q1) A)Create a dept table(dno,dname) Set referential integrity for the same
SQL>CREATE TABLE DEPT1(DNO NUMBER(4) PRIMARY KEY, DNAME VARCHAR2(30));
Table created.
SQL>DESC DEPT1;
Name Null? Type
------------------------- ------------ ---------------------------
DNO NOT NULL NUMBER(4)
DNAME VARCHAR2(30)
SQL>INSERT INTO DEPT1 VALUES(1,'CSE');
1 row created.
SQL>INSERT INTO DEPT1 VALUES(2,'ECE');
1 row created.
SQL>INSERT INTO DEPT1 VALUES(3,'EEE');
1 row created.
SQL>INSERT INTO DEPT1 VALUES(4,'IT');
1 row created.
SQL>SELECT * FROM DEPT1;
DNO DNAME
---------- ------------------------------
1 CSE
2 ECE
3 EEE
4 IT
B)Create a student table (rollno,name, marks,dno) Set referential integrity for the same
SQL>CREATE TABLE STUDENT1(ROLLNO NUMBER(12),NAME VARCHAR2(30),MARKS
NUMBER(3),DNO NUMBER(4), FOREIGN KEY(DNO) REFERENCES DEPT1(DNO));
Table created.
SQL>INSERT INTO STUDENT1 VALUES('311123104001','AMY',92,1);
1 row created.
SQL>INSERT INTO STUDENT1 VALUES('311123104002','RIMY',87,2);
1 row created.
SQL>INSERT INTO STUDENT1 VALUES('311123104003','JIMMY',85,3);
1 row created.
SQL>INSERT INTO STUDENT1 VALUES('311123104004','MOLLY',81,4);
1 row created.
SQL>SELECT * FROM STUDENT1;
ROLLNO NAME MARKS DNO
------------ ------------------------------ ---------- ----------
311123104001 AMY 92 1
311123104002 RIMY 87 2
311123104003 JIMMY 85 3
311123104004 MOLLY 81 4
Q2) A) Customer (Cust_id, Cust_name, Addr, ph_no,pan_no)
SQL>CREATE TABLE CUSTOMER(CUST_ID NUMBER(4) PRIMARY KEY,CUST_N
VARCHAR2(30),ADDRESS VARCHAR2(30),PH_NO VARCHAR2(10),
PAN_NOVARCHAR2(12));
Table created.
SQL>DESC CUSTOMER;
Name Null? Type
------------------ --------------- ----------------------------
CUST_ID NOT NULL NUMBER(4)
CUST_N VARCHAR2(30)
ADDRESS VARCHAR2(30)
PH_NO VARCHAR2(10)
PAN_NO VARCHAR2(12)
SQL>INSERT INTO CUSTOMER VALUES(101,'MANOJ','Gandhi street','1234567890','1
12233');
1 row created.
SQL>SELECT * FROM CUSTOMER;
CUST_ID CUST_N ADDRESS PH_NO PAN_NO
---------- --------- ---------------------- —---------------- —-------------------------
101 MANOJ Gandhi street 1234567890 112233
102 KRISH Nethaji street 1234567891 112244
103 MANASA ECR 1234567892 112255
104 VIDYA OMR 1234567893 112266
B) Loan (Loan_id, Amount. Interest, Cust_Id)
SQL>CREATE TABLE LOAN1(LOAN_ID NUMBER(4),AMT NUMBER(6),INTEREST
NUMBER(6),C
UST_ID NUMBER(4), FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER(CUST_ID));
Table created.
SQL>DESC LOAN1;
Name Null? Type
-------------------------- ------- ----------------------------
LOAN_ID NUMBER(4)
AMT NUMBER(6)
INTEREST NUMBER(6)
CUST_ID NUMBER(4)
SQL>INSERT INTO LOAN1 VALUES(1010,1000,250,101);
1 row created.
SQL>INSERT INTO LOAN1 VALUES(1011,2000,260,102);
1 row created.
SQL>INSERT INTO LOAN1 VALUES(1012,3000,280,103);
1 row created.
SQL>INSERT INTO LOAN1 VALUES(1013,4000,300,104);
1 row created.
SQL>SELECT * FROM LOAN1;
LOAN_ID AMT INTEREST CUST_ID
---------------- ------- ------------- ----------
1010 1000 250 101
1011 2000 260 102
1012 3000 280 103
1013 4000 300 104
Q3) Create the following table with the mapping given below:
A)stu_details(reg_no, stu_name, DOB, address, city)
SQL>CREATE TABLE STUDENT3(REG_NO NUMBER(5) PRIMARY KEY,
STU_NAME VARCHAR2(30), DOB VARCHAR2(15), ADDRESS VARCHAR(30), CITY
VARCHAR(20));
Table created.
SQL>INSERT INTO STUDENT3 VALUES(10001,'EMILY','1.1.2005','Gandhi street','
chennai');
1 row created.
SQL>INSERT INTO STUDENT3 VALUES(10002,'LILLY','3.5.2005','Nethaji street','
Madurai');
1 row created.
SQL>SELECT * FROM STUDENT3;
REG_NO STU_NAME DOB ADDRESS CITY
---------- ------------------------------ ----------- ------------------------------ --------------------
10001 EMILY 1.1.2005 Gandhi street Chennai
10002 LILLY 3.5.2005 Nethaji street Madurai
B)mark_details(reg_no, mark1, mark2, mark3, total)
SQL>CREATE TABLE MARKS(REG_NO NUMBER(5), FOREIGN KEY(REG_NO)
REFERENCES STUDENT3(REG_NO),MARK1 NUMBER(3),MARK2 NUMBER(3), MARK3
NUMBER(3), TOTAL NUMBER(3));
Table created.
SQL>INSERT INTO MARKS VALUES(10001,60,70,80,210);
1 row created.
SQL>INSERT INTO MARKS VALUES(10002,70,70,80,220);
1 row created.
SQL>SELECT * FROM MARKS;
REG_NO MARK1 MARK2 MARK3 TOTAL
--------------- ---------- ---------- ---------- ----------
10001 60 70 80 210
10002 70 70 80 220
Q4) Create the following tables
A)CUSTOMER(custno, custname, city, phone)
SQL>CREATE TABLE CUSTOMER5(CUSTNO NUMBER(4) PRIMARY
KEY,CUSTNAME VARCHAR2(30),CITY VARCHAR2(20),PHONE VARCHAR2(15));
Table created.
SQL>INSERT INTO CUSTOMER5 VALUES(101,'ARUN','Chennai', '1234567890');
1 row created.
SQL>INSERT INTO CUSTOMER5 VALUES(102,'ARUNA','Madurai', '1234567891');
1 row created.
SQL>INSERT INTO CUSTOMER5 VALUES(103,'Rimy','Theni', '1234567892');
1 row created.
SQL>SELECT * FROM CUSTOMER5;
CUSTNO CUSTNAME CITY PHONE
---------- ------------------ -------------- ---------------
101 ARUN Chennai 1234567890
102 ARUNA Madurai 1234567891
103 Rimy Theni 1234567892
B)ITEM(itemno, itemname, itemprice, quantity)
SQL>CREATE TABLE ITEM(ITEMNO NUMBER(4) PRIMARY KEY,ITEMNAME
VARCHAR2(30),IT
EMPRICE NUMBER(10,2),QUANTITY NUMBER(5));
Table created.
SQL>INSERT INTO ITEM VALUES(1011,'MILK',60,2);
1 row created.
SQL>INSERT INTO ITEM VALUES(1012,'CHOCOLATES',240,4);
1 row created.
SQL>INSERT INTO ITEM VALUES(1013,'MAIDA',270,3);
1 row created.
SQL>SELECT * FROM ITEM;
ITEMNO ITEMNAME ITEMPRICE QUANTITY
---------- ----------------------- --------------- —------------
1011 MILK 60 2
1012 CHOCOLATES 240 4
1013 MAIDA 270 3
C)INVOICE(invno, invdate, custno)
SQL>CREATE TABLE INVOICE(INVNO NUMBER(4) PRIMARY KEY,INVDATE
VARCHAR2(15),CUSTNO NUMBER(4),FOREIGN KEY(CUSTNO)REFERENCES
CUSTOMER5(CUSTNO));
Table created.
SQL>INSERT INTO INVOICE VALUES(1,'12.01.2025',101);
1 row created.
SQL>INSERT INTO INVOICE VALUES(2,'13.01.2025',102);
1 row created.
SQL>INSERT INTO INVOICE VALUES(3,'14.01.2025',103);
1 row created.
SQL>SELECT * FROM INVOICE;
INVNO INVDATE CUSTNO
---------- --------------- ----------
1 12.01.2025 101
2 13.01.2025 102
3 14.01.2025 103
D)INVITEM(invno, itemno, quantity)
SQL>CREATE TABLE INVITEM(INVNO NUMBER(4) NOT NULL,ITEMNO NUMBER(4) NOT
NULL
,QUANTITY NUMBER(5), PRIMARY KEY(INVNO,ITEMNO));
Table created.
SQL>INSERT INTO INVITEM VALUES(1,1011,2);
1 row created.
SQL>INSERT INTO INVITEM VALUES(2,1012,4);
1 row created.
SQL>SELECT * FROM INVITEM;
INVNO ITEMNO QUANTITY
---------- ---------- --------------
1 1011 2
2 1012 4
3 1013 3
Q5) Consider the following relational schema for a Product Sales database application
A) Product (Prodid, Prodesc, Price, Stock)
SQL>CREATE TABLE PRODUCT(PRODID NUMBER(4) PRIMARY KEY,PRODESC
VARCHAR2(20),PRICE NUMBER(5),STOCK NUMBER(10));
Table created.
SQL>INSERT INTO PRODUCT VALUES(1001,'GROCERY',10000,520);
1 row created.
SQL>INSERT INTO PRODUCT VALUES(1002,'ELECTRONICS',40000,320);
1 row created.
SQL>INSERT INTO PRODUCT VALUES(1003,'FASHION',20000,100);
1 row created.
SQL>SELECT * FROM PRODUCT;
PRODID PRODESC PRICE STOCK
---------- ----------------------- ---------- ----------
1001 GROCERY 10000 520
1002 ELECTRONICS 40000 320
1003 FASHION 20000 100
B) Purchase (Purid, Proid, qty, supplierName)
SQL>CREATE TABLE PURCHASE(PURID NUMBER(4) PRIMARY KEY,PRODID
NUMBER(4),FOREIGN KEY(PRODID)REFERENCES PRODUCT(PRODID),QUANTITY
NUMBER(4),SUPPLIERNAME VAVARCHAR2(40));
Table created.
SQL>INSERT INTO PURCHASE VALUES(2001,1001,20,'PRIYA ENTERPRISES');
1 row created.
SQL>INSERT INTO PURCHASE VALUES(2002,1002,30,'JOHNSON ENTERPRISES');
1 row created.
SQL>INSERT INTO PURCHASE VALUES(2003,1003,40,'THOMSON ENTERPRISES');
1 row created.
SQL>SELECT * FROM PURCHASE;
PURID PRODID QUANTITY SUPPLIERNAME
---------- ---------- ---------- ----------------------------------------
2001 1001 20 PRIYA ENTERPRISES
2002 1002 30 JOHNSON ENTERPRISES
2003 1003 40 THOMSON ENTERPRISES
C)Sales (Saleid, Proid, qty, custname)
SQL>CREATE TABLE SALES(SALEID NUMBER(2) PRIMARY KEY,PRODID
NUMBER(4),FOREIGN KEY(PRODID)REFERENCES PRODUCT(PRODID),QUANTITY
NUMBER(4),CUSTNAME VARCHAR2(30));
Table created.
SQL>INSERT INTO SALES VALUES(1,1001,20,'JOHNNY');
1 row created.
SQL>INSERT INTO SALES VALUES(2,1002,30,'JIMMY');
1 row created.
SQL>INSERT INTO SALES VALUES(3,1003,40,'JAMES');
1 row created.
SQL>SELECT * FROM SALES;
SALEID PRODID QUANTITY CUSTNAME
---------- ---------- ---------- ------------------------------
1 1001 20 JOHNNY
2 1002 30 JIMMY
3 1003 40 JAMES
RESULT: Thus, the set of Tables created with the given constraints and Referential integrity
is incorporated and the output is verified.
