DBMS Practical Programs

PRACTICAL NO: 1 IMPLEMENTATION OF REFERENTIAL INTEGRITY

Aim:
To Implement the Referential Integrity.

Procedure:


Creating a Master table (course)

SQL> create table course

2 (cno number(5) primary key,

3 cname varchar2(20));

Table created.



SQL> desc course;

Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(5)
CNAME VARCHAR2(20)


SQL> insert into course values(&cno,'&cname');

Enter value for cno: 1001

Enter value for cname: BSC

old 1: insert into course values(&cno,'&cname')

new 1: insert into course values(1001,'BSC')

1 row created.
1

,SQL> /

Enter value for cno: 1002

Enter value for cname: BCOM

old 1: insert into course values(&cno,'&cname')

new 1: insert into course values(1002,'BCOM')

1 row created.



SQL> /

Enter value for cno: 1003

Enter value for cname: BCA

old 1: insert into course values(&cno,'&cname')

new 1: insert into course values(1003,'BCA')

1 row created.



SQL> /

Enter value for cno: 1004

Enter value for cname: BA

old 1: insert into course values(&cno,'&cname')

new 1: insert into course values(1004,'BA')

1 row created.

SQL> commit;

Commit complete.

2

,SQL> select * from course;



CNO CNAME

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

1001 BSC

1002 BCOM

1003 BCA

1004 BA



Creating a Child table (student)



SQL> create table student

2 (sno number(5) primary key,

3 sname varchar2(30),

4 dob date,

5 cno number(5) references course(cno));

Table created.



SQL> desc student;

Name Null? Type

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

SNO NOT NULL NUMBER(5)

3

, SNAME VARCHAR2(30)

DOB DATE

CNO NUMBER(5)



SQL> insert into student values(&sno,'&sname','&dob',&cno);

Enter value for sno: 1

Enter value for sname: Bhanu

Enter value for dob: 10-jan-1995

Enter value for cno: 1001

old 1: insert into student values(&sno,'&sname','&dob',&cno)

new 1: insert into student values(1,'Bhanu','10-jan-1995',1001)

1 row created.



SQL> /

Enter value for sno: 2

Enter value for sname: Swathi

Enter value for dob: 26-Aug-1998

Enter value for cno: 1002

old 1: insert into student values(&sno,'&sname','&dob',&cno)

new 1: insert into student values(2,'Swathi','26-Aug-1998',1002)

1 row created.



4