BCA RDBMS PRACTICAL NUMBER_4

PRACTICAL NUMBER-4

➤ Create a Database Implementing Primary & Foreign Key.

We are working here on a Oracle 10g setup which uses system Database by default in which we are trying to implement Primary and Foreign key concept practically. Let us start step by step process given below-

1. Create a Student Registration Table in which we will use Rgst_No as a Primary Key of Integer Data Type by using given command-

CREATE TABLE Student_Rgst

(

    Rgst_No INT PRIMARY KEY,

    Student_Name VARCHAR(20) NOT NULL,

    Student_Course CHAR(20) NOT NULL,

    Mobile NUMBER(10) UNIQUE,

    Student_Fees numeric(10,2)

);

After pressing enter the table created message will appeared. The column used in above table described below-

Rgst_No --> Hold Integer values and used as a Primary key means value can not be NULL and must be                      Unique.

Student_Name --> Hold variable character values of size 20 and can not be NULL due to NOT NULL                                  Constraint.

Student_Course --> Hold character values of size 20 and can not be NULL due to NOT NULL Constraint.

Mobile --> Hold Numbers of size 10 and must be unique due to UNIQUE Constraint.

Student_Fees --> Hold Floating point values of Max size 10 in which 2 are precision values.

2. Create a Student Result Table in which we will use Rgst_No of Student_Rgst table as a reference so that Referential Integrity will be achieved by given command-

CREATE TABLE Student_Result

(

    Rgst_No INT REFERENCES Student_Rgst,

    Marks INT DEFAULT 0,

);

After pressing enter the table created message will appeared. The column used in above table described below-

Rgst_No --> It references the Rgst_No of Student_Rgst table. It can not be NULL or DUPLICATE.

Marks --> It accept the integer values. If not enter marks than it will take default value 0 de to DEFAULT Constraint.

3. Insert two records of Students in Student_Rgst table by given command-

INSERT INTO Student_Rgst VALUES(1,'SHREYA','BCA',1234567890,15000.25);

INSERT INTO Student_Rgst VALUES(2,'ROSHNI','BCOM',1234567892,16000.25);

After each statement we press Enter key that will create a row. Now we can see our table by select command-

SELECT * FROM Student_Rgst;

Our table look like-

Table-1. Student_Rgst

4. Insert One record of Students Marks in Student_Result table by given command-

INSERT INTO Student_Result VALUES(1,45);

After statement we press Enter key that will create a row. Now we can see our table by select command-

SELECT * FROM Student_Result;

Our table look like-

Table-2. Student_Result

5. We can not Perform the Following Operations-

    👉can not insert marks of student who is not registered i.e. below code will generate an error-

        INSERT INTO Student_Result VALUES(3,45);

    👉can not delete student record from Student_Rgst table if result of that student is available. i.e.

        DELETE Student_Rgst where Rgst_No=1; 

➤Complete Practical Output of the above queries given below in Picture format-

Implementing the Primary Key and Foreign Key

➤Complete Practical Output of the above queries with errors-

Errors after Primary and Foreign Key Implementation