CREATING SAMPLE TABLES 2

Batches table :

Contains information about all the batches. These batches include batches that were
completed, that are currently running and that are scheduled but yet to start.

Column Name            Data Type                Description

BCODE                        VARCHAR2(5)       Code that is assigned to each batch.                                                                                  This is the primary key of the table.

CCODE                         VARCHAR2(5)      Course code of the course of this                                                                                       batch. This is a foreign key                                                                                                 referencing CCODE of COURSES                                                                                   table. 

FACCODE                    VARCHAR2(5)           Code of the faculty member taking                                                                                    this batch.

STDATE                        DATE                           Date on which the batch has started                                                                                  or scheduled to start if batch has not                                                                                  yet started.

ENDDATE                     DATE                           Date on which the batch has                                                                                              completed. If batch is not                                                                                                  completed this will be null.

TIMING                        NUMBER(1)                 Number indicating the timing of the                                                                                  batch. 1- morning, 2 – after noon,                                                                                      and 3-evening.

The following are the required constraints of this table. :

• BCODE is the primary key.
• CCODE is a foreign key referencing CCODE of COURSES table.
• FACCODE is a foreign key referencing FACCODE of FACULTY table.
• STDATA must be <= ENDDATE
• TIMING column must be 1, 2 or 3.

STUDENTS table :

Contains information about all the students of the institute. Each student is given a roll number. Roll number will be allotted to each student of each batch.

Column Name             Data Type          Description
ROLLNO                     NUMBER(5)       Roll number that is assigned to each                                                                                  student. This is the primary key of the table.
BCODE                       VARCHAR2(5)    Code of the batch to which student belongs.                                                                      This is the foreign key referencing BCODE                                                                       of BATCHES table.
NAME                         VARCHAR2(30)    Name of the student.
GENDER                     CHAR(1)               Gender of the student. M for male and F for
                                                                     female.
DJ                                DATE                      Date on which the student has joined.
PHONE                       VARCHAR2(10)     Contact number of the student.
EMAIL                        VARCHAR2(30)     Email address of the student

The following are the constraints of the table.

• ROLLNO is the primary key.
• BCODE is a foreign key referencing BCODE of BATCHES table.
• GENDER may be either M or F.

PAYMENTS table :

Contains information about all the payment made by students of all bathes.

Column Name         Data Type              Description

ROLLNO                 NUMBER(5)           Roll number of the student                                                                             paying the fee.
DP                            DATE                       Date on which the amount is                                                                          paid.
AMOUNT               NUMBER(5)             The amount paid by student.

The following are the constraints.

• Primary key is consisting of ROLLNO and DP.
• AMOUNT must be >= 25