Create a Result database using Oracle PL/SQL

Consider a situation under an University Management System where Student, Department, Course, Payment, Teacher, Registration and Result Entity are available. You have to the consider these Entity(s) and relation(s) as like the below picture. Few attributes and constraints are stated with this picture. You may add/modify/drop necessary attribute and constraint, if required.

1. Construct DDL for the above E-R Diagram
2. Use all the Constraint Properly
3. Prove all the Constraint Setting with an Error Message. You may collect message while entering values to the database.

17 comments:

  1. CREATE table DEPARTMENT
    (dept_id varchar2(10) primary key,
    dept_name varchar2(20) not null
    );

    desc department;
    -----------

    CREATE table STUDENT
    (std_id number(10) primary key,
    std_name varchar2(30) not null,
    std_add varchar2(50) not null,
    dept_id varchar2(10),
    city varchar2(15),
    constraint dept_id_fk_STUDENT foreign key(dept_id) references DEPARTMENT(dept_id),
    constraint std_chk check(city in('Dhaka','Rajshahi','Khulna'))
    );


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

    CREATE table STUDENT_PHONE
    (std_id number(10),
    std_phn varchar2(15),
    constraint std_id_fk_STUDENT_PHONE foreign key(std_id)references STUDENT(std_id),
    constraint std_phn_uq unique(std_phn)
    );

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

    CREATE table COURSE
    (course_id varchar2(10) primary key,
    course_name varchar2(30),
    dept_id varchar2(10),
    constraint dpt_id_fk_COURSE foreign key(dept_id)references DEPARTMENT(dept_id),
    constraint course_uq unique(course_name)
    );

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

    CREATE table TEACHER
    (t_id varchar(10) primary key,
    t_name varchar2(30) not null,
    dept_id varchar2(10),
    constraint dpt_id_fk_TEACHER foreign key(dept_id)references DEPARTMENT(dept_id)
    );

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

    CREATE table PAYMENT
    (receit_id varchar2(10) primary key,
    debit varchar2(1000),
    credit varchar2(1000),
    p_date date,
    std_id number(10),
    constraint std_id_fk_PAYMENT foreign key(std_id)references STUDENT(std_id)
    );

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

    CREATE table REGISTRATION
    (sl_no varchar2(10) primary key,
    std_id number(10),
    course_id varchar2(10),
    semester varchar2(10),
    year number(4),
    retake varchar2(5) not null,
    receit_id varchar2(10),
    constraint std_id_fk_REGISTRATION foreign key(std_id)references STUDENT(std_id),
    constraint course_id_fk_REGISTRATION foreign key(course_id)references COURSE(course_id),
    constraint receit_id_fk_REGISTRATION foreign key(receit_id)references PAYMENT(receit_id),
    constraint REGISTRATION_uq unique(std_id,course_id,semester,year)
    );

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

    CREATE table RESULT
    (sl_no_result varchar2(10) primary key,
    t_id varchar(10),
    std_id number(10),
    course_id varchar2(10),
    sl_no varchar2(10),
    marks number(3) not null,
    constraint std_id_fk_RESULT foreign key(std_id)references STUDENT(std_id),
    constraint t_id_fk_RESULT foreign key(t_id)references TEACHER(t_id),
    constraint course_id_fk_RESULT foreign key(course_id)references COURSE(course_id),
    constraint sl_no_fk_RESULT foreign key(sl_no)references REGISTRATION(sl_no)
    );

    ReplyDelete
  2. CREATE TABLE student
    (std_id number(12),std_name varchar2(30),std_add varchar2(50),dept_id varchar2(15),
    city varchar2(15));
    DESC student;
    alter table student
    add constraint s_pk primary key(std_id);
    alter table student
    modify std_name varchar2(30) not null;
    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);
    alter table student
    add constraint s_ck check (city in('Dhaka','Rajshahi','Khulna'));
    alter table student
    add constraint s_uq unique(std_add);
    insert into student
    values(280,'sss','ban','cse','Dhaka');
    select * from student;
    CREATE TABLE department
    (dept_id varchar2(15),dept_name varchar2(15));
    DESC department;
    alter table department
    add constraint dpk primary key(dept_id);
    alter table department
    modify dept_name varchar2(30) not null;
    insert into department
    values('cse','computer science');
    select * from department;
    ===============
    CREATE TABLE student_phone
    (std_id number(12),std_phn varchar2(15));
    DESC student_phone;
    alter table student_phone
    add constraint sp_fk foreign key(std_id) references student(std_id);
    alter table student_phone
    add constraint sp_uq unique(std_phn);
    insert into student_phone
    values(280,'8888800');
    select * from student_phone;
    ========================------------------

    CREATE TABLE registration
    (sl_no number(12),std_id number(12),course_id varchar2(15),semester varchar2(10),
    year number(10),retake varchar2(5),receit_id varchar2(10));
    DESC registration;
    alter table registration
    add constraint r_pk primary key (sl_no);
    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);
    alter table registration
    add constraint r_fk foreign key (receit_id)references payment(receit_id);
    alter table registration
    modify retake varchar2(5) not null;
    insert into registration
    values(001,280,'cse331','fall',2017,'null','0001');
    select * from registration;
    --======================
    CREATE TABLE payment
    (receit_id varchar2(10),debit number(30),credit number(30),p_date varchar2(15),std_id number(12));
    DESC payment;
    alter table payment
    add constraint p_pk primary key (receit_id);
    alter table payment
    add constraint p_fk foreign key (std_id) references student (std_id);
    insert into payment
    values('0001',15109,16109,'20 jan',280);
    select * from payment;
    ==================
    CREATE TABLE course
    (course_id varchar2(15),
    course_name varchar2(30),
    dept_id varchar2(15));
    DESC course;
    alter table course
    add constraint c_pk primary key (course_id);
    alter table course
    add constraint c_uq unique(course_name);
    insert into course
    values('cse331','dbms','cse');
    select * from course;
    -------------------------------------
    CREATE TABLE teacher
    (t_id number(15),t_name varchar2(30),dept_id varchar2(15));
    DESC teacher;
    alter table teacher
    add constraint t_pk primary key (t_id);
    alter table teacher
    modify t_name varchar2(30) not null;
    alter table teacher
    add constraint t_fk foreign key(dept_id)references department(dept_id);
    insert into teacher
    values(100280,'arif','cse');
    select * from teacher;
    ----------------------=====
    drop table result;
    CREATE TABLE result
    (sl_no_r varchar2(15),t_id number(15),std_id number(12),course_id varchar2(15),sl_no number(12),
    marks number(13));
    DESC result;
    alter table result
    add constraint rs_pk primary key(sl_no_r);
    alter table result
    add constraint rs_fk foreign key(t_id) references teacher(t_id);
    alter table result
    add constraint rs_fk foreign key(std_id) references student(std_id);
    alter table result
    add constraint rs_fk foreign key(course_id) references course(course_id);
    insert into result
    values('00011',100280,280,'cse311',001,98);
    insert into result
    values('00012',100280,280,'cse311',001,98);
    select * from result;

    ReplyDelete
  3. desc department;
    desc student;
    desc student_phone;
    desc course;
    desc payment;
    desc registration;
    desc teacher;
    desc result;
    drop table student;

    create table department
    (
    dept_id varchar2(10) not null primary key,
    dept_name varchar2(100)
    );

    create table student
    (
    std_id number(15) not null primary key,
    std_name varchar2(50) not null,
    std_add varchar2(200) not null,
    dept_id varchar2(10) not null ,
    city varchar2(30),
    constraint std_fk foreign key(dept_id) references department(dept_id),
    constraint stdCity_chk check (city in('Dhaka','Rajshahi','Sylhet','Borishal'))
    );

    create table student_phone
    (
    std_id number(15) not null,
    std_phone varchar2(12) not null unique,
    constraint std_phn_fk foreign key(std_id) references student(std_id)
    );

    create table course
    (
    course_id varchar2(20) not null primary key,
    course_name varchar2(50) not null unique,
    dept_id varchar2(10) not null,
    constraint crs_fk foreign key(dept_id) references department(dept_id)
    );

    create table payment
    (
    recit_id number(30) not null primary key,
    debit number(30),
    credit number(30),
    pay_date date,
    std_id number(15) not null,
    constraint pay_fk foreign key(std_id) references student(std_id)
    );

    create table registration
    (
    sl_no number(20) primary key,
    std_id number(15) not null unique,
    course_id varchar2(20) not null unique,
    semester varchar2(20) not null unique,
    year number(5) not null unique,
    recit_id number(30) not null,
    constraint reg_fk foreign key(recit_id) references payment(recit_id)
    );

    create table teacher
    (
    tech_id number(15) primary key,
    tech_name varchar2(50) not null,
    dept_id varchar2(10) not null,
    constraint tech_fk foreign key(dept_id) references department(dept_id)
    );

    create table result
    (
    sl_no_res number(30) primary key,
    tech_id number(15) not null,
    std_id number(15) not null,
    course_id varchar2(20) not null,
    sl_no number(20) not null,
    marks number(3) not null,
    constraint res_fk1 foreign key(tech_id) references teacher(tech_id),
    constraint res_fk2 foreign key(std_id) references student(std_id),
    constraint res_fk3 foreign key(course_id) references course(course_id),
    constraint res_fk4 foreign key(sl_no) references registration(sl_no)
    );
    insert into department
    values('CSE','Computer science and Engineering');
    select *from department;

    insert into student
    values(201510082,'Shuma','Gazipur','CSE','Dhaka');
    select *from student;

    insert into student_phone
    values(201510082,'01625513323');
    select *from student_phone;

    insert into course
    values(4813,'Database','CSE');
    select *from course;

    insert into payment
    values(4568,5000,25000,'12312017',201510082);
    select *from payment;

    insert into registration
    values(324684,201510082,4813,'Spring`17',2017,4568);
    select *from registration;


    insert into teacher
    values(1234567,'Ahsan Arif','CSE');
    select *from teacher;


    insert into result
    values(9874654,1234567,201510082,4813,324684,95.5);
    select *from result;

    ReplyDelete
  4. create table student
    (
    std_id number(20),
    std_name varchar2(30),
    std_address varchar2(50),
    dept_id number(20),
    city varchar2(60)
    );

    desc student;

    create table student_phone
    (
    std_id number(20),
    std_phone varchar2(30)
    );

    desc student_phone;

    create table registration
    (
    sl_no number(30),
    std_id number(20),
    course_id number(20),
    semister varchar2(20),
    year number(25),
    retake varchar2(30),
    recit_id number(20)
    );
    drop table registration;

    desc registration;

    create table department
    (
    dept_id number(30),
    dept_name varchar2(30)
    );

    desc department;

    create table course
    (
    course_id number(20),
    course_name varchar2(30),
    department_id number(30)
    );

    desc course;

    create table teacher
    (
    t_id number(20),
    t_name varchar2(30),
    department_id number(30)

    );

    desc teacher;

    create table payment
    (
    recit_id number(20),
    debit number(38),
    credit number(30),
    payment_date number(35),
    std_id number
    );

    desc payment;

    create table result
    (
    sl_no_recit number(30),
    t varchar2(25),
    std_id number(20),
    course_id number(20),
    sl_no number(30),
    marks number(38)
    );


    desc result;

    desc student;

    alter table student
    add constraint std_id_pk primary key(std_id);

    alter table student
    modify std_name varchar2(30)not null;


    alter table student
    modify std_address varchar2(50)not null;

    alter table department
    add constraint d_pk primary key(dept_id);

    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);

    alter table student
    add constraint s_chk check (city in('dhaka','rajshahi'));

    alter table department
    modify dept_name varchar2(30)not null;

    alter table student_phone
    add constraint sp_fk foreign key(std_id)references student(std_id);

    alter table student_phone
    add constraint sp_uq unique(std_phone);

    alter table registration
    add constraint s_pk primary key(sl_no);

    alter table course
    add constraint c_pk primary key(course_id);

    alter table registration
    add constraint c_fk foreign key(course_id)references course(course_id);

    alter table registration
    modify retake varchar2(30)not null;

    alter table payment
    add constraint r_pk primary key(recit_id);

    alter table registration
    add constraint r_fk foreign key(recit_id)references payment(recit_id);

    alter table payment
    add constraint s_fk foreign key(std_id)references student(std_id);

    alter table department
    modify dept_id number(30)not null;

    alter table course
    add constraint c_uq unique(course_name);

    alter table course
    add constraint dept_fk foreign key(department_id)references course(course_id);

    alter table teacher
    add constraint t_pk primary key(t_id);

    alter table teacher
    modify t_name varchar2(30)not null;

    alter table teacher
    add constraint dept_fk foreign key(department_id)references department(dept_id);

    alter table result
    add constraint res_pk primary key(sl_no_recit);

    alter table result
    add constraint res_fk foreign key(teacher_id)references teacher(teacher_id);

    alter table result
    add constraint res_fk foreign key(std_id)references student(std_id);

    alter table result
    add constraint res_fk foreign key(course_id)references course(course_id);

    alter table result
    add constraint res_fk foreign key(sl_no)references registration(sl_no);

    alter table result
    modify marks number(38) not null;

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

    insertinto student
    values(201510105,'ont','uttora','CSE','dhaka');
    select*from student;

    insertinto student_phone
    values(201510105,01684622474);
    select*from student_phone;

    insertinto registration
    values(10,201510105,4568,'spring',2017,2588);
    select*from registration;

    insertinto payment
    values(2588,120045,45000,101217,201510105);
    select*from payment;

    insertinto department
    values(12345,'cse');
    select*from department;

    insertinto course
    values(4568,'database','cse');
    select*from result;


    insertinto teacher)
    values(10105,'ahsan arif','cse');
    select*from teacher;

    insertinto result
    values(20,10105,2010105,4568,10,56);
    select*from result;




    ReplyDelete
  5. sumaya akter moushume id-201510542February 1, 2017 at 11:44 PM

    create table department
    (
    dept_id varchar2(10),
    dept_name varchar2(20)
    );

    desc department;


    create table student
    (
    std_id number(12),
    std_name varchar2(30),
    std_add varchar2(40),
    dept_id varchar2(20),
    city varchar2(20)
    );

    desc student;

    create table student_phone
    (
    std_id number(12),
    std_phone varchar2(12)
    );

    desc student_phone;

    create table course
    (
    course_id varchar2(10),
    course_name varchar2(20),
    dept_id varchar2(10)
    );

    desc course;

    create table registration
    (
    std_id number(12),
    course_id varchar2(10),
    semister varchar2(10),
    year number(10),
    re_take varchar2(20),
    recite_id number(20)
    );
    desc registration;


    create table payment
    (
    result_id number(20),
    debit number(20),
    credit number(20),
    p_date number(10),
    std_id number(10)

    );

    desc payment;

    create table teacher
    (
    T_id number(12),
    T_name varchar2(20),
    dept_id varchar2(10)
    );
    desc teacher;

    create table result
    (
    sl_no_r number(10),
    T_id number(12),
    std_id number(10),
    course_id varchar2(10),
    sl_no number(10),
    markes number(10)
    );

    desc result;



    select table_name from tabs;

    desc department;

    alter table department
    add constraint dept_id pk primary key(dept_id);



    desc student;

    alter table student
    add constraint std_id_pk primary key (std_id);

    alter table student
    modify std_name varchar2(30) not null;


    alter table student
    add constraint dept_id_fk_student foreign key(dept_id) references department(dept_id);

    alter table student
    add constraint s_ck check (city in('Dhaka,comilla,gazipur));

    alter table student
    add constraint s_uq unique(std_add);


    desc student_phone;


    alter table student_phone
    add constraint std_id_fk foreign key (std_id) references student (std_id);

    alter table student_phone
    add constraint std_phone_uq unique(std_phone);


    desc course;

    alter table course
    add constraint course_id_pk primary key (course_id);

    alter table course
    add constraint course_name_uq unique(course_name);



    alter table course
    add constraint dept_id_fk_department foreign key (dept_id) references department(dept_id);


    desc registration;


    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);

    alter table registration
    modify re_take varchar2(5) not null;





    desc payment;

    alter table payment
    add constraint res_id_pk primary key (result_id);

    alter table payment
    add constraint std_id_fk foreign key(std_id) references student (std_id);

    desc teacher ;

    alter table teacher
    add constraint t_id_pk primary key (t_id);

    alter table teacher
    modify t_name varchar2(20) not null;

    alter table teacher
    add constraint dept_id_fk foreign key (dept_id) references department(dept_id);


    desc result ;

    alter table result
    add constraint sl_no_r_pk primary key (sl_no_r);

    alter table result
    add constraint t_id_fk foreign key (t_id) references teacher(t_id);
    alter table result
    add constraint std_id_fk foreign key (std_id) references student (std_id);

    alter table result
    add constraint course_id_fk foreign key (course_id) references course (course_id);

    alter table result
    modify markes number(10) not null;

    ReplyDelete
  6. CREATE TABLE student
    (std_id number(12),
    std_name varchar2(35),
    std_add varchar2(40),
    dept_id varchar2(20)
    city varchar2(25));

    DESC student;

    alter table student
    add constraint s_pk primary key(std_id);
    alter table student
    modify std_name varchar2(35) not null;
    alter table student
    modify std_add varchar2(40) not null;
    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);
    alter table student
    add constraint s_ck check (city in('Gazipur','Dhaka','Bangladesh'));
    insert into student
    values(992,'nzs','ban','cse','Dhaka');
    select * from student;

    CREATE TABLE department
    (dept_id varchar2(20)
    dept_name varchar2(25));

    DESC department;

    alter table department
    add constraint dpk primary key(dept_id);
    alter table department
    modify dept_name varchar2(25) not null;
    insert into department
    values('cse','computer science and engineering');
    select * from department;

    CREATE TABLE student_phone
    (std_id number(12)
    std_phn varchar2(20));

    DESC student_phone;

    alter table student_phone
    add constraint sp_fk foreign key(std_id) references student(std_id);
    alter table student_phone
    add constraint sp_uq unique(std_phn);
    insert into student_phone
    values(992,'88018469');
    select * from student_phone;
    CREATE TABLE course
    (course_id varchar2(10),
    dept_id varchar2(20)
    course_name varchar2(35));

    DESC course;

    alter table course
    add constraint c_pk primary key (course_id);
    alter table course
    add constraint sp_fk foreign key(dept_id) references department (dept_id);
    alter table course
    modify course_name varchar2(35) not null;
    insert into course
    values('cse3523','dbms','cse');
    select * from course;

    CREATE TABLE payment
    (std_id number(12),
    (recit_id varchar2(15),
    debit number(35),
    credit number(30)
    p_date varchar2(20));

    DESC payment;
    alter table payment
    add constraint p_fk foreign key (std_id) references student (std_id);
    alter table payment
    add constraint p_pk primary key (receit_id);
    insert into payment
    values(992,'00234',161109,265613,'17 feb');
    select * from payment;

    CREATE TABLE teacher
    (teach_id number(17),
    teach_name varchar2(30)
    dept_id varchar2(20));

    DESC teacher;

    alter table teacher
    add constraint t_pk primary key (t_id);
    alter table teacher
    modify t_name varchar2(30) not null;
    alter table teacher
    add constraint t_fk foreign key(dept_id)references department(dept_id);
    insert into teacher
    values(2010467,'ahsan','cse');

    CREATE TABLE registration
    (course_id varchar2(10),
    std_id number(12),
    semester varchar2(12),
    year number(17),
    retake varchar2(2),
    receit_id varchar2(15)
    sl_no number(12));

    DESC registration;

    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);
    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    modify retake varchar2(5) not null;
    alter table registration
    add constraint r_fk foreign key (receit_id)references payment(receit_id);
    alter table registration
    add constraint r_pk primary key (sl_no);
    insert into registration
    values(‘cse3523’,992,’spring’,2017,’null’,002,'0001');
    select * from registration;

    CREATE TABLE result
    (sl_n_r varchar2(15),
    teach_id number(17),
    std_id number(12),
    course_id varchar2(10),
    sl_no number(12)
    marks number(20));

    DESC result;

    alter table result
    add constraint rs_pk primary key(sl_no_r);
    alter table result
    add constraint rs_fk foreign key(t_id) references teacher(t_id);
    alter table result
    add constraint rs_fk foreign key(std_id) references student(std_id);
    alter table result
    add constraint rs_fk foreign key(course_id) references course(course_id);
    alter table result
    add constraint rs_fk foreign key(sl_no) references serial(sl_no);
    alter table result
    modify marks varchar2(20) not null;
    insert into result
    values('0001',2010467,992,'cse3523',002,84);
    select * from result;

    ReplyDelete
  7. CREATE TABLE student
    (std_id number(10),
    std_name varchar2(40),
    std_add varchar2(50),
    dept_id varchar2(20),
    city varchar2(15));

    DESC student;


    CREATE TABLE department
    (dept_id varchar2(20),
    dept_name varchar2(20));

    DESC department;


    CREATE TABLE student_phone
    (std_id number(10),
    std_phn varchar2(15));

    DESC student_phone;


    CREATE TABLE registration
    (sl_no number(15),
    std_id number(10),
    course_id varchar2(15),
    semester varchar2(10),
    year number(10),
    retake varchar2(10),
    receit_id varchar2(10));

    DESC registration;


    CREATE TABLE payment
    (receit_id varchar2(10),
    debit number(38),
    credit number(38),
    p_date varchar2(15),
    std_id number(10));

    DESC payment;


    CREATE TABLE course
    (course_id varchar2(15),
    course_name varchar2(30),
    dept_id varchar2(20));

    DESC course;


    CREATE TABLE teacher
    (t_id number(10),
    t_name varchar2(30),
    dept_id varchar2(20));

    DESC teacher;


    CREATE TABLE result
    (sl_no_r varchar2(15),
    t_id number(10),
    std_id number(10),
    course_id varchar2(15),
    sl_no number(15),
    marks number(15));

    DESC result;


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

    alter table student
    add constraint s_pk primary key(std_id);
    alter table student
    modify std_name varchar2(40) not null;
    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);
    alter table student
    add constraint s_ck check (city in('Dhaka','kaligonj','Kaligonj'));
    alter table student
    add constraint s_uq unique(std_add);
    insert into student
    values(385,'atoshy','bangladesh','CSE','Dhaka');

    select * from student;

    ----------

    alter table department
    add constraint dpk primary key(dept_id);
    alter table department
    modify dept_name varchar2(20) not null;
    insert into department
    values('CSE','Computer Science');

    select * from department;

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


    alter table student_phone
    add constraint sp_fk foreign key(std_id) references student(std_id);
    alter table student_phone
    add constraint sp_uq unique(std_phn);
    insert into student_phone
    values(385,'2441139');

    select * from student_phone;

    --------


    alter table registration
    add constraint r_pk primary key (sl_no);
    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);
    alter table registration
    add constraint r_fk foreign key (receit_id)references payment(receit_id);
    alter table registration
    modify retake varchar2(10) not null;
    insert into registration
    values(414,385,'CSE385','spring',2017,'---','4534');

    select * from registration;

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

    alter table payment
    add constraint p_pk primary key (receit_id);
    alter table payment
    add constraint p_fk foreign key (std_id) references student (std_id);
    insert into payment
    values('4534',3698,9638,'26 january',385);

    select * from payment;

    -----------

    alter table course
    add constraint c_pk primary key (course_id);
    alter table course
    add constraint c_uq unique(course_name);
    insert into course
    values('CSE385','DBMS','CSE');

    select * from course;

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

    alter table teacher
    add constraint t_pk primary key (t_id);
    alter table teacher
    modify t_name varchar2(30) not null;
    alter table teacher
    add constraint t_fk foreign key(dept_id)references department(dept_id);
    insert into teacher
    values(36524,'Ahsan arif','CSE');

    select * from teacher;

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


    alter table result
    add constraint rs_pk primary key(sl_no_r);
    alter table result
    add constraint rs_fk foreign key(t_id) references teacher(t_id);
    alter table result
    add constraint rs_fk foreign key(std_id) references student(std_id);
    alter table result
    add constraint rs_fk foreign key(course_id) references course(course_id);
    insert into result
    values('4534',36524,385,'CSE385',414,85);
    insert into result
    values('4535',36524,385,'CSE385S',414,88);
    select * from result;

    ReplyDelete
  8. create table Student
    (
    std_id number(16),
    std_name varchar2(30),
    std_add varchar2(35),
    dept_id varchar2(26),
    city varchar2(25)
    );
    DESC Student;
    alter table student
    add constraint s_pk primary key(std_id);
    alter table student
    modify std_name varchar2(35) not null;
    alter table student
    modify std_add varchar2(40) not null;
    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);
    alter table student
    add constraint s_ck check (city in('Uttara','Dhaka','Bangladesh'));
    insert into student
    values(018,'mf','ban','cse','Dhaka');
    select * from student;

    create table Department
    (
    dept_id varchar2(26),
    dept_name varchar2(25)
    );
    DESC Department;
    alter table department
    add constraint dpk primary key(dept_id);
    alter table department
    modify dept_name varchar2(25) not null;
    insert into department
    values('cse','computer science and engineering');
    select * from department;

    create table Std_Phone
    (
    std_id number(16),
    std_phone varchar2(26)
    );
    DESC Std_Phone;
    alter table student_phone
    add constraint sp_fk foreign key(std_id) references student(std_id);
    alter table student_phone
    add constraint sp_uq unique(std_phn);
    insert into student_phone
    values(018,'880987654321');
    select * from student_phone;

    create table Course
    (
    course_id varchar2(10),
    course_name varchar2(30),
    dept_id varchar2(20)
    );
    DESC Course;
    alter table course
    add constraint c_pk primary key (course_id);
    alter table course
    add constraint sp_fk foreign key(dept_id) references department (dept_id);
    alter table course
    modify course_name varchar2(35) not null;
    insert into course
    values('cse3523','dbms','cse');
    select * from course;

    create table Registration
    (
    sl_no number(16),
    std_id number(16),
    course_id varchar2(10),
    semester varchar2(12),
    year number(17),
    retake varchar2(2),
    recit_id varchar2(15)
    );
    DESC Registration;
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);
    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    modify retake varchar2(5) not null;
    alter table registration
    add constraint r_fk foreign key (receit_id)references payment(receit_id);
    alter table registration
    add constraint r_pk primary key (sl_no);
    insert into registration
    values(‘cse3523’,018,’spring’,2017,’null’,001,'0002');
    select * from registration;

    create table Payment
    (
    result_id varchar2(15),
    debit number(35),
    credit number(30),
    p_date varchar2(26),
    std_id number(16)
    );
    DESC Payment;
    alter table payment
    add constraint p_fk foreign key (std_id) references student (std_id);
    alter table payment
    add constraint p_pk primary key (receit_id);
    insert into payment
    values(018,'01234',345487,567897,'5 feb');
    select * from payment;

    create table Teacher
    (
    t_id number(17),
    t_name varchar2(30),
    dept_id varchar2(26)
    );
    DESC Teacher;
    alter table teacher
    add constraint t_pk primary key (t_id);
    alter table teacher
    modify t_name varchar2(30) not null;
    alter table teacher
    add constraint t_fk foreign key(dept_id)references department(dept_id);
    insert into teacher
    values(2017890,'ahsanarif','cse');

    create table Result
    (
    sl_no_r varchar2(15),
    t_id number(17),
    std_id number(16),
    course_id varchar2(10),
    sl_no number(16),
    marks number(26)
    );
    DESC Result;
    alter table result
    add constraint rs_pk primary key(sl_no_r);
    alter table result
    add constraint rs_fk foreign key(t_id) references teacher(t_id);
    alter table result
    add constraint rs_fk foreign key(std_id) references student(std_id);
    alter table result
    add constraint rs_fk foreign key(course_id) references course(course_id);
    alter table result
    add constraint rs_fk foreign key(sl_no) references serial(sl_no);
    alter table result
    modify marks varchar2(26) not null;
    insert into result
    values('0002',2017890,018,'cse3523',001,68);
    select * from result;

    ReplyDelete
  9. CREATE TABLE student
    (std_id number(10),
    std_name varchar2(40),
    std_add varchar2(50),
    dept_id varchar2(20),
    city varchar2(15));

    desc student;


    CREATE TABLE department
    (dept_id varchar2(20),
    dept_name varchar2(20));

    desc department;


    CREATE TABLE student_phone
    (std_id number(10),
    std_phn varchar2(15));

    desc student_phone;


    CREATE TABLE registration
    (sl_no number(15),
    std_id number(10),
    course_id varchar2(15),
    semester varchar2(10),
    year number(10),
    retake varchar2(10),
    receit_id varchar2(10));

    desc registration;


    CREATE TABLE payment
    (receit_id varchar2(10),
    debit number(38),
    credit number(38),
    p_date varchar2(15),
    std_id number(10));

    desc payment;


    CREATE TABLE course
    (course_id varchar2(15),
    course_name varchar2(30),
    dept_id varchar2(20));

    desc course;


    CREATE TABLE teacher
    (t_id number(10),
    t_name varchar2(30),
    dept_id varchar2(20));

    desc teacher;

    CREATE TABLE result
    (sl_no_r varchar2(15),
    t_id number(10),
    std_id number(10),
    course_id varchar2(15),
    sl_no number(15),
    marks number(15));
    desc result;

    alter table student
    add constraint s_pk primary key(std_id);
    alter table student
    modify std_name varchar2(40) not null;
    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);
    alter table student
    add constraint s_ck check (city in('Dhaka','Gazipur','Kaligonj'));
    alter table student
    add constraint s_uq unique(std_add);
    insert into student
    values(385,'atoshy','Bangladesh','CSE','Dhaka');
    select * from student;

    alter table department
    add constraint dpk primary key(dept_id);
    alter table department
    modify dept_name varchar2(20) not null;
    insert into department
    values('CSE','Computer Science');
    select * from department;

    alter table student_phone
    add constraint sp_fk foreign key(std_id) references student(std_id);
    alter table student_phone
    add constraint sp_uq unique(std_phn);
    insert into student_phone
    values(385,'201520405');
    select * from student_phone;

    alter table registration
    add constraint r_pk primary key (sl_no);
    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);
    alter table registration
    add constraint r_fk foreign key (receit_id)references payment(receit_id);
    alter table registration
    modify retake varchar2(10) not null;
    insert into registration
    values(405,3525,'CSE3525','spring',2017,'993','4534');
    select * from registration;

    alter table payment
    add constraint p_pk primary key (receit_id);
    alter table payment
    add constraint p_fk foreign key (std_id) references student (std_id);
    insert into payment
    values('4534',3698,9638,'14 feb',3525);
    select * from payment;

    alter table course
    add constraint c_pk primary key (course_id);
    alter table course
    add constraint c_uq unique(course_name);
    insert into course
    values('CSE3525','DBMS','CSE');
    select * from course;

    alter table teacher
    add constraint t_pk primary key (t_id);
    alter table teacher
    modify t_name varchar2(30) not null;
    alter table teacher
    add constraint t_fk foreign key(dept_id)references department(dept_id);
    insert into teacher
    values(26524,'Ahsan arif','CSE');
    select * from teacher;

    alter table result
    add constraint rs_pk primary key(sl_no_r);
    alter table result
    add constraint rs_fk foreign key(t_id) references teacher(t_id);
    alter table result
    add constraint rs_fk foreign key(std_id) references student(std_id);
    alter table result
    add constraint rs_fk foreign key(course_id) references course(course_id);
    insert into result
    values('9993',26524,385,'CSE3525',405,84);
    insert into result
    values('9994',26524,385,'CSE3525S',405,88);
    select * from result;

    ReplyDelete
  10. Student ID = 201430852 (47th)

    CREATE TABLE student
    (student_id number(12),std_name varchar2(30),std_add varchar2(50),dept_id varchar2(15),
    city varchar2(15));
    DESC student;
    alter table student
    add constraint s_pk primary key(student_id);
    alter table student
    modify std_name varchar2(30) not null;
    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);
    alter table student
    add constraint s_ck check (city in('Dhaka','Rajshahi','Khulna'));
    alter table student
    add constraint s_uq unique(std_add);
    insert into student
    values(280,'sss','ban','cse','Dhaka');
    select * from student;
    CREATE TABLE department
    (dept_id varchar2(15),dept_name varchar2(15));
    DESC department;
    alter table department
    add constraint dpk primary key(dept_id);
    alter table department
    modify dept_name varchar2(30) not null;
    insert into department
    values('cse','computer science');
    select * from department;


    CREATE TABLE student_phone
    (student_id number(12),std_phn varchar2(15));
    DESC student_phone;
    alter table student_phone
    add constraint sp_fk foreign key(student_id) references student(student_id);
    alter table student_phone
    add constraint sp_uq unique(std_phn);
    insert into student_phone
    values(280,'8888800');
    select * from student_phone;



    CREATE TABLE registration
    (sl_no number(12),student_id number(12),course_id varchar2(15),semester varchar2(10),
    year number(10),retake varchar2(5),receit_id varchar2(10));
    DESC registration;
    alter table registration
    add constraint r_pk primary key (sl_no);
    alter table registration
    add constraint r_fk foreign key(student_id)references student(student_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);
    alter table registration
    add constraint r_fk foreign key (receit_id)references payment(receit_id);
    alter table registration
    modify retake varchar2(5) not null;
    insert into registration
    values(001,280,'cse331','fall',2017,'null','0001');
    select * from registration;




    CREATE TABLE payment
    (receit_id varchar2(10),debit number(30),credit number(30),p_date varchar2(15),student_id number(12));
    DESC payment;
    alter table payment
    add constraint p_pk primary key (receit_id);
    alter table payment
    add constraint p_fk foreign key (student_id) references student (student_id);
    insert into payment
    values('0001',15109,16109,'20 jan',280);
    select * from payment;




    CREATE TABLE course
    (course_id varchar2(15),
    course_name varchar2(30),
    dept_id varchar2(15));
    DESC course;
    alter table course
    add constraint c_pk primary key (course_id);
    alter table course
    add constraint c_uq unique(course_name);
    insert into course
    values('cse331','dbms','cse');
    select * from course;



    CREATE TABLE teacher
    (t_id number(15),t_name varchar2(30),dept_id varchar2(15));
    DESC teacher;
    alter table teacher
    add constraint t_pk primary key (t_id);
    alter table teacher
    modify t_name varchar2(30) not null;
    alter table teacher
    add constraint t_fk foreign key(dept_id)references department(dept_id);
    insert into teacher
    values(100280,'arif','cse');
    select * from teacher;



    drop table result;
    CREATE TABLE result
    (sl_no_r varchar2(15),t_id number(15),student_id number(12),course_id varchar2(15),sl_no number(12),
    marks number(13));
    DESC result;
    alter table result
    add constraint rs_pk primary key(sl_no_r);
    alter table result
    add constraint rs_fk foreign key(t_id) references teacher(t_id);
    alter table result
    add constraint rs_fk foreign key(student_id) references student(student_id);
    alter table result
    add constraint rs_fk foreign key(course_id) references course(course_id);
    insert into result
    values('00011',100280,280,'cse311',001,98);
    insert into result
    values('00012',100280,280,'cse311',001,98);
    select * from result;

    ReplyDelete
  11. drop table student;
    create table student
    (
    std_id number(10),
    std_name varchar2(30),
    std_add varchar2(30),
    dep_id number(10),
    city varchar2(15)
    );
    desc student;
    --drop table Department;
    create table Department
    (
    dept_id number(10),
    dept_name varchar2(30)
    );
    desc department;
    create table std_phone
    (
    std_id number(10),
    std_phone varchar2(30)
    );
    desc std_phone;
    drop table course;
    create table course
    (
    course_id number(10),
    course_name varchar2(30),
    dept_id number(10)
    );
    desc course;
    create table teacher
    (
    T_id number(10),
    T_name varchar2(30),
    dept_id number(10)
    );
    desc teacher;
    create table registration
    (
    sl_no number(10),
    std_id number(10),
    course_id number(10),
    semester varchar2(10),
    year number(10),
    retake varchar2(5),
    recite_id number(10)

    );
    desc registration;

    create table payment
    (
    recite_id number(10),
    debit number(10),
    credit number(10),
    p_date varchar2(30),
    std_id number(10)

    );
    desc payment;
    create table result
    (
    sl_no_r number(10),
    T_id number(10),
    course_id number(10),
    sl_no number(10),
    marks number(10)
    );
    desc result;





    alter table department
    add constraint dpk primary key(dept_id);

    alter table department
    add dept_name varchar2(30) not null;

    desc student;

    alter table student
    add constraint s_pk primary key(std_id);
    alter table student
    modify std_name varchar2(30) not null;

    alter table student
    add constraint s_fk foreign key(dept_id) references department(dept_id);

    alter table student
    add constraint s_ck check (city in('Dhaka','Rajshahi','Khulna'));
    desc std_phone;

    alter table std_phone
    add constraint sp_fk foreign key(std_id) references student(std_id);

    alter table std_phone
    add constraint sp_uq unique(std_phone);

    desc student;
    desc department;

    insert into department
    values(3434,'computer sciece and Eng');
    select * from department;

    ReplyDelete
  12. sumaya akter moushume id-201510542February 5, 2017 at 11:36 PM

    create table department
    (
    dept_id varchar2(10),
    dept_name varchar2(20)
    );

    desc department;


    create table student
    (
    std_id number(12),
    std_name varchar2(30),
    std_add varchar2(40),
    dept_id varchar2(20),
    city varchar2(20)
    );

    desc student;

    create table student_phone
    (
    std_id number(12),
    std_phone varchar2(12)
    );

    desc student_phone;

    create table course
    (
    course_id varchar2(10),
    course_name varchar2(20),
    dept_id varchar2(10)
    );

    desc course;

    create table registration
    (
    std_id number(12),
    course_id varchar2(10),
    semister varchar2(10),
    year number(10),
    re_take varchar2(20),
    recite_id number(20)
    );
    desc registration;


    create table payment
    (
    result_id number(20),
    debit number(20),
    credit number(20),
    p_date number(10),
    std_id number(10)

    );

    desc payment;

    create table teacher
    (
    T_id number(12),
    T_name varchar2(20),
    dept_id varchar2(10)
    );
    desc teacher;

    create table result
    (
    sl_no_r number(10),
    T_id number(12),
    std_id number(10),
    course_id varchar2(10),
    sl_no number(10),
    markes number(10)
    );

    desc result;



    select table_name from tabs;

    desc department;

    alter table department
    add constraint dept_id pk primary key(dept_id);



    desc student;

    alter table student
    add constraint std_id_pk primary key (std_id);

    alter table student
    modify std_name varchar2(30) not null;


    alter table student
    add constraint dept_id_fk_student foreign key(dept_id) references department(dept_id);

    alter table student
    add constraint s_ck check (city in('Dhaka,comilla,gazipur));

    alter table student
    add constraint s_uq unique(std_add);


    desc student_phone;


    alter table student_phone
    add constraint std_id_fk foreign key (std_id) references student (std_id);

    alter table student_phone
    add constraint std_phone_uq unique(std_phone);


    desc course;

    alter table course
    add constraint course_id_pk primary key (course_id);

    alter table course
    add constraint course_name_uq unique(course_name);



    alter table course
    add constraint dept_id_fk_department foreign key (dept_id) references department(dept_id);


    desc registration;


    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);

    alter table registration
    modify re_take varchar2(5) not null;





    desc payment;

    alter table payment
    add constraint res_id_pk primary key (result_id);

    alter table payment
    add constraint std_id_fk foreign key(std_id) references student (std_id);

    desc teacher ;

    alter table teacher
    add constraint t_id_pk primary key (t_id);

    alter table teacher
    modify t_name varchar2(20) not null;

    alter table teacher
    add constraint dept_id_fk foreign key (dept_id) references department(dept_id);


    desc result ;

    alter table result
    add constraint sl_no_r_pk primary key (sl_no_r);

    alter table result
    add constraint t_id_fk foreign key (t_id) references teacher(t_id);
    alter table result
    add constraint std_id_fk foreign key (std_id) references student (std_id);

    alter table result
    add constraint course_id_fk foreign key (course_id) references course (course_id);

    alter table result
    modify markes number(10) not null;

    ReplyDelete
  13. create table department
    (
    dept_id varchar2(10),
    dept_name varchar2(20)
    );

    desc department;


    create table student
    (
    std_id number(12),
    std_name varchar2(30),
    std_add varchar2(40),
    dept_id varchar2(20),
    city varchar2(20)
    );

    desc student;

    create table student_phone
    (
    std_id number(12),
    std_phone varchar2(12)
    );

    desc student_phone;

    create table course
    (
    course_id varchar2(10),
    course_name varchar2(20),
    dept_id varchar2(10)
    );

    desc course;

    create table registration
    (
    std_id number(12),
    course_id varchar2(10),
    semister varchar2(10),
    year number(10),
    re_take varchar2(20),
    recite_id number(20)
    );
    desc registration;


    create table payment
    (
    result_id number(20),
    debit number(20),
    credit number(20),
    p_date number(10),
    std_id number(10)

    );

    desc payment;

    create table teacher
    (
    T_id number(12),
    T_name varchar2(20),
    dept_id varchar2(10)
    );
    desc teacher;

    create table result
    (
    sl_no_r number(10),
    T_id number(12),
    std_id number(10),
    course_id varchar2(10),
    sl_no number(10),
    markes number(10)
    );

    desc result;



    select table_name from tabs;

    desc department;

    alter table department
    add constraint dept_id pk primary key(dept_id);



    desc student;

    alter table student
    add constraint std_id_pk primary key (std_id);

    alter table student
    modify std_name varchar2(30) not null;


    alter table student
    add constraint dept_id_fk_student foreign key(dept_id) references department(dept_id);

    alter table student
    add constraint s_ck check (city in('Dhaka,comilla,gazipur));

    alter table student
    add constraint s_uq unique(std_add);


    desc student_phone;


    alter table student_phone
    add constraint std_id_fk foreign key (std_id) references student (std_id);

    alter table student_phone
    add constraint std_phone_uq unique(std_phone);


    desc course;

    alter table course
    add constraint course_id_pk primary key (course_id);

    alter table course
    add constraint course_name_uq unique(course_name);



    alter table course
    add constraint dept_id_fk_department foreign key (dept_id) references department(dept_id);


    desc registration;


    alter table registration
    add constraint r_fk foreign key(std_id)references student(std_id);
    alter table registration
    add constraint r_fk foreign key (course_id)references course(course_id);

    alter table registration
    modify re_take varchar2(5) not null;





    desc payment;

    alter table payment
    add constraint res_id_pk primary key (result_id);

    alter table payment
    add constraint std_id_fk foreign key(std_id) references student (std_id);

    desc teacher ;

    alter table teacher
    add constraint t_id_pk primary key (t_id);

    alter table teacher
    modify t_name varchar2(20) not null;

    alter table teacher
    add constraint dept_id_fk foreign key (dept_id) references department(dept_id);


    desc result ;

    alter table result
    add constraint sl_no_r_pk primary key (sl_no_r);

    alter table result
    add constraint t_id_fk foreign key (t_id) references teacher(t_id);
    alter table result
    add constraint std_id_fk foreign key (std_id) references student (std_id);

    alter table result
    add constraint course_id_fk foreign key (course_id) references course (course_id);

    alter table result
    modify markes number(10) not null;

    ReplyDelete
  14. CREATE TABLE terif(
    t_id NUMBER,
    gp NUMBER(3, 2),
    grade VARCHAR2(2),
    smarks NUMBER,
    emarks NUMBER
    );


    ALTER TABLE terif ADD CONSTRAINT terif_pk PRIMARY KEY(t_id);

    INSERT INTO terif VALUES(1, 4, 'A+', 80, 100);
    INSERT INTO terif VALUES(2, 3.75, 'A', 70, 79);



    CREATE TABLE code(
    c_id NUMBER,
    code VARCHAR2(25),
    credit NUMBER(3, 2)
    );


    ALTER TABLE code ADD CONSTRAINT code_pk PRIMARY KEY(c_id);

    INSERT INTO code VALUES(1, 'CSE 1001', 3);
    INSERT INTO code VALUES(2, 'CSE 1001S', 1.5);



    CREATE TABLE teacher(
    tech_id NUMBER,
    tech_name VARCHAR2(100)
    );


    ALTER TABLE teacher ADD CONSTRAINT tech_pk PRIMARY KEY(tech_id);

    INSERT INTO teacher VALUES(100, 'Teacher 1');
    INSERT INTO teacher VALUES(101, 'Teacher 2');

    CREATE TABLE student(
    std_id VARCHAR2(12),
    std_name VARCHAR2(150)
    );

    ALTER TABLE student ADD CONSTRAINT std_pk PRIMARY KEY(std_id);
    INSERT INTO student VALUES('100101', 'Student 1');
    INSERT INTO student VALUES('100102', 'Student 2');

    DROP TABLE result;

    CREATE TABLE result(
    r_id NUMBER,
    std_id VARCHAR2(12),
    tech_id NUMBER,
    c_id NUMBER,
    t_id NUMBER,
    marks NUMBER,
    grade VARCHAR2(2),
    rdate VARCHAR2(12),
    usern VARCHAR2(15)
    );

    ALTER TABLE result ADD CONSTRAINT r_pk PRIMARY KEY(r_id);
    ALTER TABLE result ADD CONSTRAINT r_std_fk FOREIGN KEY(std_id) REFERENCES student(std_id);
    ALTER TABLE result ADD CONSTRAINT r_tech_fk FOREIGN KEY(tech_id) REFERENCES teacher(tech_id);
    ALTER TABLE result ADD CONSTRAINT r_code_fk FOREIGN KEY(c_id) REFERENCES code(c_id);

    ReplyDelete
  15. create table department1
    (
    dept_id number(10),
    dept_name varchar2(11) not null,
    constraint pk_department_dept_name primary key(dept_id)
    );
    desc department1;


    create table studentss
    (
    std_id number(20),
    std_name varchar2(21)not null,
    std_add varchar2(22)not null,
    dept_id number(10),
    city varchar2(23),
    constraint pk_student1_std_id primary key(std_id),
    constraint fk_student1_dept_id foreign key(dept_id) references department1(dept_id),
    constraint ck_student1_city check(city in('dhaka','mymensingh'))
    );
    create table course
    (
    course_id number(30),
    dept_id number(10),
    course_name varchar2(31)not null,
    constraint pk_course_course_id primary key(course_id),
    constraint fk_course_dept_id foreign key(dept_id) references department1(dept_id)
    );
    create table teacher
    (
    teach_id number(35),
    teach_name varchar2(36)not null,
    dept_id number(10),
    constraint pk_teacher_teach_id primary key(teach_id),
    constraint fk_teacher_dept_id foreign key(dept_id) references department1(dept_id)
    );
    create table std_phone
    (
    std_id number(20),
    std_phone varchar2(25),
    constraint fk_std_phone_std_id foreign key(std_id) references studentss(std_id),
    constraint uk_std_phone_std_phone unique(std_phone)
    );
    create table payment
    (
    std_id number(20),
    recit_id number(22),
    debit varchar2(23),
    credit varchar2(24),
    p_date date not null,
    constraint fk_payment_std_id foreign key(std_id) references studentss(std_id),
    constraint ph_payment_recit_id primary key(recit_id)
    );
    create table registration
    (
    course_id number(30),
    std_id number(20),
    semisted varchar2(25),
    year number(26),
    retake varchar2(27)not null,
    recit_id number(22),
    sl_no number(28),
    constraint pk_registration_sl_no primary key(sl_no),
    constraint fk_registration_course_id foreign key(course_id) references course(course_id),
    constraint fk_registration_std_id foreign key(std_id) references studentss(std_id),
    constraint fk_registration_recit_id foreign key(recit_id) references payment(recit_id),
    constraint uk_registration unique(course_id,std_id,semisted,year)
    );
    create table result
    (
    sl_no_r number(10)primary key,
    teach_id number(35),
    std_id number(20),
    course_id number(30),
    sl_no number(28),
    marks number(21)not null,
    constraint fk_result_teach_id foreign key(teach_id) references teacher(teach_id),
    constraint fk_result_std_id foreign key(std_id) references studentss(std_id),
    constraint fk_result_course_id foreign key(course_id) references course(course_id),
    constraint fk_result_sl_no foreign key(sl_no) references registration(sl_no)
    );

    ReplyDelete
  16. RESULT MANAGEMENT SYSTEM FOR AN UNIVERSITY
    ==========================================

    create table DEPARTMENT
    (
    dept_id number(10),
    dept_name varchar2(20)not null,
    constraint pk_department_dept_id primary key(dept_id)
    );

    desc DEPARTMENT;


    ..........................


    create table STUDENT3
    (
    std_id number(10),
    std_name varchar2(20)not null,
    std_add varchar2(30)not null,
    dept_id number(10),
    city varchar2(12),
    constraint pk_student3_std_id primary key(std_id),
    constraint fk_student3_dept_id foreign key(dept_id) references department(dept_id),
    constraint ck_student3_city check(city in('dhaka','pabna'))
    );

    desc STUDENT3;

    ......................................


    create table std_phone1
    (
    std_id number(10),
    std_phone varchar2(11),
    constraint fk_std_phone1_std_id foreign key(std_id) references STUDENT3(std_id),
    constraint uk_std_phone1_std_phone unique(std_phone)
    );

    desc std_phone1;

    ...........................................


    create table course1
    (
    course_id number(10),
    course_name varchar2(20)not null,
    dept_id number(10),
    constraint pk_course1_course_id primary key(course_id),
    constraint fk_course1_dept_id foreign key(dept_id) references department(dept_id)
    );

    desc course1;

    .............................


    create table payment1
    (
    std_id number(10),
    recit_id number(10),
    debit varchar2(1000),
    credit varchar2(2000),
    p_date date,
    constraint pk_payment1_recit_id primary key(recit_id),
    constraint fk_payment1_std_id foreign key(std_id) references STUDENT3(std_id)
    );
    desc payment1;

    ...................................


    create table teacher2
    (
    teach_id number(10)primary key,
    teach_name varchar2(20)not null,
    dept_id number(10),
    constraint fk_teacher2_dept_id foreign key(dept_id) references department(dept_id)
    );
    desc teacher2;

    ...................................


    create table registration1
    (
    course_id number(10),
    std_id number(10),
    semister varchar2(12),
    year number(10),
    retake varchar2(10)not null,
    recit_id number(10),
    sl_no number(10),
    constraint pk_registration1_sl_no primary key(sl_no),
    constraint fk_registration1_course_id foreign key(course_id) references course1(course_id),
    constraint fk_registration1_std_id foreign key(std_id) references STUDENT3(std_id),
    constraint fk_registration1_recit_id foreign key(recit_id) references payment1(recit_id)
    );
    desc registration1;

    .....................................


    create table result2
    (
    sl_n_r varchar2(14)primary key,
    teach_id number(10),
    std_id number(10),
    course_id number(10),
    sl_no number(10),
    marks number(12)not null,
    constraint fk_result2 foreign key(teach_id) references teacher2(teach_id),
    constraint fk_result3 foreign key(std_id) references STUDENT3(std_id),
    constraint fk_result4 foreign key(course_id) references course1(course_id),
    constraint fk_result5 foreign key(sl_no) references registration1(sl_no)
    );
    desc result2;

    EMD.....................................RAFIQUL

    ReplyDelete

Popular Posts