Design a Database for Student's course wise marks entry

Assume that you are responsible to design a database, where the following conditions are applicable:

1. Students can not register a subject without payment.
2. Subjects and Students are belongs to Department
3. Teachers are belongs to Department
5. A teacher can submit result only for the registered students
6. Teacher will submit only subject/course wise total numbers
7.Consider the retake/improvement case
8. Published result as a Grade System
9. Automatic Grade Sheet creation by some fixed rules provided by authority. Which will be stored in a table. For example: If a student gets more then or equal to 80 marks then he/she will get a+
10. Teacher will not entry any Grade to system.

Submit necessary DDL and DML to satisfy the above system.

49 comments:

  1. Taiful Hasan
    id-201520377
    49th batch(A)
    Though it's no completed but i have tried.thanks


    create table student_info
    (
    student_id number(15) unique,
    student_name varchar2(20) not null,
    student_address varchar2(50) not null,
    student_cell_number number(12) unique
    );
    desc student_info

    insert into student_info values(201520377,'Taiful Hasan','Sydney,Australia','01922795523')
    insert into student_info values(201520378,'Kamrul Hasna Hasan','London,England','01922795524')
    insert into student_info values(201520379,'Anik Hasan','Barcelona,Spain','01922595524')
    insert into student_info values(201520380,'Pavel Hasan','New York USA','01922795507');

    select * from student_info where student_id='201520378';
    select * from student_info;

    update student_info set student_name='Kamrul Hasan' where student_id='201520378';

    create table payment
    (
    student_id number(15) unique,
    debit number(8) not null,
    credit number(8) not null,
    batch varchar2(20) not null,
    depatment varchar2(50) not null,
    recit_no varchar2(20) unique,
    constraint fk foreign key (student_id)references student_info(student_id)
    );
    alter table payment modify student_id number(15);
    insert into payment values(201520377,'5200','2500','49th','Cse-dip','001')
    insert into payment values(201520378,'5200','2500','49th','Cse-dip','002')
    insert into payment values(201520379,'5200','2500','49th','Cse-dip','003')
    select * from payment
    //
    create table course
    (
    code varchar2(12) unique,
    Tite varchar2(35) unique,
    dept varchar2(12) not null
    );


    select * from course

    alter table course add credit number(20);
    update course set credit='3' where code='1203';
    insert into course values(1204,'Math-1','3')


    insert into course values(1202,'Micro-processor','Cse')
    insert into course values(1203,'Database Management','Cse')

    select * from course
    //

    create table registation
    (
    student_id number(10),
    code varchar2(15),
    recit_no number(15),
    recit_date date(15) not null,
    semester varchar2(15) not null,
    constraint fks foreign key (student_id)references student_info(student_id),
    constraint fkr foreign key (recit_no) references payment(recit_no),
    constraint fkc foreign key (code) references course(code),
    constraint ruq unique(student_id,code,semester)
    );
    alter table registation add constraint reuq unique (student_id,code,semester,recit_no);
    desc registation
    insert into registation values(201520377,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1201','001','06-14-2016','sum-16')


    select * from registation
    //
    select * from student_info;
    select * from payment;
    select * from course;
    select * from registation;
    create table department
    (
    dept_id number(15) unique,
    dept_name varchar2(20)
    );
    insert into department values(100,'cse-dip')
    insert into department values(101,'cse')
    insert into department values(102,'bba')
    select * from department;
    //
    create table teacher
    (
    tech_id number(15) unique,
    dept_id number(15) not null,
    tech_name varchar2(20) not null
    );
    alter table teacher add constraint defk foreign key (dept_id)references department(dept_id);
    alter table teacher modify dept_id number(15) not null;

    insert into teacher values(500,'100','Ahsan Arif');
    select * from teacher;
    alter table student_info add dept_id number(15);
    alter table student_info add constraint dfk foreign key (dept_id)references department(dept_id);
    update student_info set student_address='dhaka' where student_id='2015220377';

    select * from student_info where student_id='201520377';

    //

    ReplyDelete
  2. Iftekher Bin Jahan
    ID-201520586
    49th batch(A)

    create table student_info
    (
    student_id number(15) unique,
    student_name varchar2(20) not null,
    student_address varchar2(50) not null,
    student_cell_number number(12) unique
    );
    desc student_info

    insert into student_info values(201520586,'Iftekher Anik','Melbourne,Australia','01815049388')
    insert into student_info values(201520585,'Dipayan Basak','Kolkata,India','01683183367')
    insert into student_info values(201520378,'Kamrul Hasna Hasan','London,England','01922795524')
    insert into student_info values(201520379,'Anik Hasan','Barcelona,Spain','01922595524')
    insert into student_info values(201520380,'Pavel Hasan','New York USA','01922795507');

    select * from student_info where student_id='201520378';
    select * from student_info;

    update student_info set student_name='Kamrul Hasan' where student_id='201520378';

    create table payment
    (
    student_id number(15) unique,
    debit number(8) not null,
    credit number(8) not null,
    batch varchar2(20) not null,
    depatment varchar2(50) not null,
    recit_no varchar2(20) unique,
    constraint fk foreign key (student_id)references student_info(student_id)
    );
    alter table payment modify student_id number(15);
    insert into payment values(201520377,'5200','2500','49th','Cse-dip','001')
    insert into payment values(201520378,'5200','2500','49th','Cse-dip','002')
    insert into payment values(201520379,'5200','2500','49th','Cse-dip','003')
    select * from payment
    //
    create table course
    (
    code varchar2(12) unique,
    Tite varchar2(35) unique,
    dept varchar2(12) not null
    );


    select * from course

    alter table course add credit number(20);
    update course set credit='3' where code='1203';
    insert into course values(1204,'Math-1','3')


    insert into course values(1202,'Micro-processor','Cse')
    insert into course values(1203,'Database Management','Cse')

    select * from course
    //

    create table registation
    (
    student_id number(10),
    code varchar2(15),
    recit_no number(15),
    recit_date date(15) not null,
    semester varchar2(15) not null,
    constraint fks foreign key (student_id)references student_info(student_id),
    constraint fkr foreign key (recit_no) references payment(recit_no),
    constraint fkc foreign key (code) references course(code),
    constraint ruq unique(student_id,code,semester)
    );
    alter table registation add constraint reuq unique (student_id,code,semester,recit_no);
    desc registation
    insert into registation values(201520377,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1201','001','06-14-2016','sum-16')


    select * from registation
    //
    select * from student_info;
    select * from payment;
    select * from course;
    select * from registation;
    create table department
    (
    dept_id number(15) unique,
    dept_name varchar2(20)
    );
    insert into department values(100,'cse-dip')
    insert into department values(101,'cse')
    insert into department values(102,'bba')
    select * from department;
    //
    create table teacher
    (
    tech_id number(15) unique,
    dept_id number(15) not null,
    tech_name varchar2(20) not null
    );
    alter table teacher add constraint defk foreign key (dept_id)references department(dept_id);
    alter table teacher modify dept_id number(15) not null;

    insert into teacher values(500,'100','Ahsan Arif');
    select * from teacher;
    alter table student_info add dept_id number(15);
    alter table student_info add constraint dfk foreign key (dept_id)references department(dept_id);
    update student_info set student_address='dhaka' where student_id='2015220377';

    select * from student_info where student_id='201520377';

    ReplyDelete
  3. Dipayan Basak
    ID-201520585
    49th batch(A)

    create table student_info
    (
    student_id number(15) unique,
    student_name varchar2(20) not null,
    student_address varchar2(50) not null,
    student_cell_number number(12) unique
    );
    desc student_info

    insert into student_info values(201520586,'Dipayan Basak','Melbourne,Australia','01812534565')
    insert into student_info values(201520585,'Iftekher Anik','Kolkata,India','01815049388')
    insert into student_info values(201520378,'Ajgor Hasna','London,England','01822796624')
    insert into student_info values(201520379,'Anik opu','Barcelona,Spain','01943498524')
    insert into student_info values(201520380,'Pavel Khan','New York USA','01922795678');

    select * from student_info where student_id='201520378';
    select * from student_info;

    update student_info set student_name='Kamrul Hasan' where student_id='201520378';

    create table paymentS
    (
    student_id number(15) unique,
    debit number(8) not null,
    credit number(8) not null,
    batch varchar2(20) not null,
    depatment varchar2(50) not null,
    recit_no varchar2(20) unique,
    constraint fk foreign key (student_id)references student_info(student_id)
    );
    alter table payment modify student_id number(15);
    insert into payment values(201520377,'5200','2500','49th','Cse-dip','001')
    insert into payment values(201520378,'5200','2500','49th','Cse-dip','002')
    insert into payment values(201520379,'5200','2500','49th','Cse-dip','003')
    select * from payment
    //
    create table course
    (
    code varchar2(12) unique,
    Tite varchar2(35) unique,
    dept varchar2(12) not null
    );


    select * from course

    alter table course add credit number(20);
    update course set credit='3' where code='1203';
    insert into course values(1204,'Math-1','3')


    insert into course values(1202,'Micro-processor','Cse')
    insert into course values(1203,'Database Management','Cse')

    select * from course
    //

    create table registation
    (
    student_id number(10),
    code varchar2(15),
    recit_no number(15),
    recit_date date(15) not null,
    semester varchar2(15) not null,
    constraint fks foreign key (student_id)references student_info(student_id),
    constraint fkr foreign key (recit_no) references payment(recit_no),
    constraint fkc foreign key (code) references course(code),
    constraint ruq unique(student_id,code,semester)
    );
    alter table registation add constraint reuq unique (student_id,code,semester,recit_no);
    desc registation
    insert into registation values(201520377,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1201','001','06-14-2016','sum-16')


    select * from registation
    //
    select * from student_info;
    select * from payment;
    select * from course;
    select * from registation;
    create table department
    (
    dept_id number(15) unique,
    dept_name varchar2(20)
    );
    insert into department values(100,'cse-dip')
    insert into department values(101,'cse')
    insert into department values(102,'bba')
    select * from department;
    //
    create table teacher
    (
    tech_id number(15) unique,
    dept_id number(15) not null,
    tech_name varchar2(20) not null
    );
    alter table teacher add constraint defk foreign key (dept_id)references department(dept_id);
    alter table teacher modify dept_id number(15) not null;

    insert into teacher values(500,'100','Ahsan Arif');
    select * from teacher;
    alter table student_info add dept_id number(15);
    alter table student_info add constraint dfk foreign key (dept_id)references department(dept_id);
    update student_info set student_address='dhaka' where student_id='2015220377';

    select * from student_info where student_id='201520377';

    ReplyDelete
  4. Design a Database for Student's course wise marks entry with grade sheet.
    submit by
    Md Nasir Fardoush
    ID:201520612
    Batch:49,Section-A
    https://drive.google.com/file/d/0ByXbJpfiiD8kamo0SVpHb28wVnc/view?usp=sharing

    ReplyDelete
  5. Name: Suraia Akter
    ID:201521136
    Department: CSE (DIP)
    Section: B 49 Batch
    Semester: Summar 2016
    Subject: Database Management Systems
    Asian University of Bangladesh
    -------------------------------------------------------------------------------------------------------
    Assignment:
    Design a Database for Student's course wise marks entry
    Assume that you are responsible to design a database, where the following
    -------------------------------------------------------------------------------------------------------
    create table departments(
    dept_id number(5),
    dept_name varchar2(100),
    constraint pk_deptid primary key(dept_id));
    create table students(
    std_id number(5),
    std_name varchar2(150),
    std_address varchar2(150),
    std_mobile varchar2(12),
    std_email varchar2(30),
    std_dept_id number(3),
    constraint pk_stdid primary key(std_id),
    constraint fk_deptid foreign key(std_dept_id) references departments(dept_id));
    create table payment(
    pay_id number(10),
    pay_std_id number(3),
    pay_amount number(5,2),
    constraint pk_payid primary key(pay_id),
    constraint fk_paystdid foreign key(pay_std_id) references students(std_id));
    create table subjects(
    sub_id number(5),
    sub_name varchar2(50),
    sub_dept_id number(3),
    sub_pay_id number(10),
    constraint pk_subid primary key(sub_id),
    constraint fk_subdeptid foreign key(sub_dept_id) references
    departments(dept_id),
    constraint fk_payid foreign key(sub_pay_id) references
    payment(pay_id)
    );
    create table teachers(
    teac_id number(5),
    teac_name varchar2(150),
    teac_address varchar2(150),
    teac_mobile varchar2(12),
    teac_email varchar2(30),
    teac_dept_id number(3),
    constraint pk_teacid primary key(teac_id),
    constraint fk_teacdeptid foreign key(teac_dept_id) references
    departments(dept_id));
    create table result_entry(
    dept_id number(5),
    teac_id number(5),
    sub_id number(5),
    std_id number(5),
    exam_type varchar2(10),
    result_value number(5,2),
    constraint fk_stdid foreign key(std_id) references
    students(std_id));
    create table grade_initialization(
    grd_mark_frm number(3),
    grd_mark_to number(3),
    grd_letter varchar(2));
    create or replace function grade_calculation(p_mark in number)
    return varchar2 is
    v_grade_letter varchar2(2);
    begin
    select grd_letter
    into v_grade_letter
    from grade_initialization
    where (grd_mark_frm<=p_mark and grd_mark_to>=p_mark);
    return v_grade_letter;
    end;
    select std_id,result_value,grade_calculation(result_value)
    from result_entry;
    Data Analysis and Result
    -------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------
    SQL> select * from departments;
    DEPT_ID DEPT_NAME
    -------------------------------------------------------------------------------------------------------
    10 CSE
    20 ETE
    SQL> select * from students;
    STD_ID STD_NAME STD_ADDRESS
    -------------------------------------------------------------------------------------------------------
    501 Mohammad Mamunur Rahman Dhaka
    502 Mohammad Abdul Rahman Dhaka
    SQL> select * from grade_initialization;
    GRD_MARK_FRM GRD_MARK_TO GR
    -------------------------------------------------------------------------------------------------------
    80 100 A+
    75 79 A
    70 74 A-
    65 69 B+
    60 64 B
    55 59 B-
    SQL> select * from result_entry;
    DEPT_ID TEAC_ID SUB_ID STD_ID EXAM_TYPE RESULT_VALUE
    -------------------------------------------------------------------------------------------------------
    10 1005 101 501 Regular 65
    10 1005 102 501 Regular 71
    10 1005 103 501 Regular 85
    10 1005 104 501 Regular 55
    SQL> select std_id,result_value,grade_calculation(result_value) grade
    2 from result_entry;
    STD_ID RESULT_VALUE GRADE
    -------------------------------------------------------------------------------------------------------
    501 65 B+
    501 71 A-
    501 85 A+
    501 55 B-

    ReplyDelete
  6. Abir Hossain
    ID:201520770
    Batch:49,Section-B
    https://drive.google.com/drive/my-drive

    ReplyDelete
  7. roll-201520401 section A
    drop table student;
    create table student(
    std_id number(10) not null,
    std_name varchar2(30) not null,
    department_name varchar2(40) not null,
    constraint pk_st_id primary key(std_id)

    );
    insert into student values (2016,'md rohul amin','cse');
    insert into student values (201620102,'md rohul ','cse');
    insert into student values (201620103,'md amin','cse');
    insert into student values (201620104,'amin','eee');
    insert into student values (201620105,'rohul','eee');
    desc student;
    create table subject(
    s_code number(4) not null,
    s_title varchar2(100) unique,
    department varchar2(100) not null,
    constraint pk_s_code primary key(s_code)
    );
    insert into subject values(101,'c++','cse');
    insert into subject values(102,'c','cse');
    insert into subject values(103,'java','cse');
    insert into subject values(201,'elctric-1','eee');
    insert into subject values(202,'elctric-1','eee');
    desc subject;
    drop table payment;
    create table payment(
    receipt_no varchar2(15) not null,
    std_id number(10),
    s_code number(4),
    amount number(5),
    constraint pk_r_no primary key(receipt_no),
    constraint fk_std_id foreign key(std_id) references student(std_id),
    constraint fk_s_code foreign key(s_code) references subject(s_code)
    );
    insert into subject values('std10002',201620102,103,2000);
    desc payment;
    drop table registration;
    create table registration(
    reg_no number(10) not null,
    std_id number(10) not null,
    receipt_no varchar2(15) not null,
    s_code number(4)not null,
    constraint pk_reg primary key(reg_no),
    constraint fk_std_reg foreign key(std_id) references student(std_id),
    constraint fk_s_code_reg foreign key(s_code) references subject(s_code),
    constraint fk_receipt_no_reg foreign key(receipt_no) references payment(receipt_no)

    );
    desc registration;
    create table marks(
    std_id number(10),
    reg_no number(10) not null,
    s_code number(4) not null,
    mark number(3) not null,
    constraint fk_id foreign key(std_id) references student(std_id),
    constraint fk_code foreign key(s_code) references subject(s_code),
    constraint fk_cod foreign key(reg_no) references subject(reg_no)

    );

    ReplyDelete
  8. Badiuzzaman
    Id-201220981
    Batch:40,Section-B

    create table student
    (
    sid number(25),
    sname varchar2(25) not null,
    sdept varchar2(15)not null,
    sbatch varchar2(15)not null,
    sphone varchar2(25),
    constraint sidpk primary key(sid),
    constraint sphoneuq unique(sphone)
    );

    insert into student
    values(20120981,'badiuzzaman','cse','40th','01922139453');
    insert into student
    values(20130098,'zaman','cse','41th','0192213443');

    create table course
    (
    ccode varchar2(20),
    ctitle varchar2(15),
    ccredit varchar2(15) not null,
    cdept varchar2(15) not null,
    constraint ccodepk primary key(ccode),
    constraint ctitleuq unique(ctitle)
    );

    insert into course
    values('cs102','p language','3','cse');
    insert into course
    values('cs104','net','1.5','cse');

    create table account
    (
    sid number(12),
    Debitbalance number(14) not null,
    Creditbalance number(10) not null,
    Recietno number(21),
    paymentdate varchar2(10) not null,
    constraint accSidFK foreign key(SID) references student(SID),
    constraint accRenoPq primary key(Recietno)
    );

    insert into account
    values(20120981,'5050.00','00.00',1,'01/02/16');
    insert into account
    values(20130098,'9050.00','550.00',2,'01/02/16');

    create table registration
    (
    SID number(20),
    coursecode varchar2(20),
    courseTitle Varchar2(30) not null,
    Recietno number(20),
    Regno varchar2(20),
    constraint RegRnomPK primary key(Regno),
    constraint regSidFK foreign key(SID) references student(SID),
    constraint regRenoFK foreign key(Recietno) references account(Recietno)
    );

    insert into registration
    values(20120981,'CSE101','programming language-1','1','3015201');
    insert into registration
    values(20130098,'CSE101','programming language-2','1','2015202');


    create table teacher
    (
    tid number(12),
    tname varchar2(35) not null,
    tdept varchar2(30) not null,
    constraint tidPK primary key(tid)
    );

    insert into teacher
    values(251232,'Abdul Salam','CSE');
    insert into teacher
    values(121425,'Kamal','CSE');


    create table MarksEntry
    (
    StdID number(25),
    Regno varchar2(20),
    coursecode varchar2(10),
    Mark number(3) not null,
    TeaID number(10),
    constraint MaEeSidFK foreign key(StdID) references student(StdID),
    constraint MaEeRegnoFK foreign key(Regno) references registration(Regno),
    constraint MaEeCcodFK foreign key(coursecode) references CourseInfo(coursecode),
    constraint MaEeTeIDFK foreign key(TeaID) references teacher(TeaID)
    );

    insert into MarksEntry
    values(201220981,3015202,'CSE101',95,111111);
    insert into MarksEntry
    values(2015201,3015202,'CSE102',95,111111);



    create table resultentry
    (
    SID number(20),
    course_code varchar2(10),
    semester varchar2(15),
    Mark number(5,2) not null,
    Gradpoint number(5,2) not null,
    LaterGrade varchar2(3) not null
    );

    ReplyDelete
  9. Name:Shah Riaz Hasan
    Id:201520768
    Batch:49, S(B)
    https://drive.google.com/open?id=0B63igbekTsPuRkJiR2RzWUR1Vmc

    ReplyDelete
  10. Name:Shahadat Hossen Raju
    Id:201520858
    Batch:49th(sB)
    https://drive.google.com/file/d/0B1nXD4H5j8zeM2tWU3Q0dlBWNGs/view?usp=sharing

    ReplyDelete
  11. Md.milon Hossain
    Id:201520477
    Batch:49th(DIP)
    section:A
    create table student
    (
    Std_ID number(10),
    Std_name varchar2(30) not null,
    Std_Dept varchar2(30) not null,
    Std_Batch varchar2(15) not null,
    Std_Cell_no varchar2(15),
    constraint SidPK primary key(Std_ID),
    constraint ScellUK unique(Std_Cell_no)
    );

    insert into student
    values(2015200,'Nasir Fardoush','CSE','49th','01918649999');
    insert into student
    values(2015201,'Bashir Fardoush','CSE','49th','01918649988');
    insert into student
    values(2015202,'Abdur Rhaman','CSE','49th','01918649977');




    create table courseInfo
    (
    course_code varchar2(10),
    course_Title Varchar2(30),
    course_credit varchar2(5) not null,
    course_Dept varchar2(30) not null,
    constraint CcodePK primary key(course_code),
    constraint CtitleUK unique(course_Title)
    );

    insert into courseInfo
    values('CSE101','programming language-1','3','CSE');
    insert into courseInfo
    values('CSE102','programming language-2','3','CSE');
    insert into courseInfo
    values('BBA101','Business organigation','1.5','BBA');




    create table account
    (
    Std_ID number(10),
    Debit_balance number(13) not null,
    Credit_balance number(10) not null,
    Reciet_no number(20),
    payment_date varchar2(10) not null,
    constraint accSidFK foreign key(Std_ID) references student(Std_ID),
    constraint accRenoPK primary key(Reciet_no)
    );

    insert into account
    values(2015201,'5050.00','00.00',1,'01/02/16');
    insert into account
    values(2015202,'9050.00','550.00',2,'01/02/16');
    insert into account
    values(2015200,'7050.00','00.00',3,'03/02/16');




    create table registration
    (
    Std_ID number(10),
    course_code varchar2(10),
    course_Title Varchar2(30) not null,
    Reciet_no number(20),
    Reg_no varchar2(20),
    constraint RegRnomPK primary key(Reg_no),
    constraint regSidFK foreign key(Std_ID) references student(Std_ID),
    constraint regCcodeFK foreign key(course_code) references courseInfo(course_code),
    constraint regRenoFK foreign key(Reciet_no) references account(Reciet_no)
    );

    insert into registration
    values(2015200,'CSE101','programming language-1','1','3015201');
    insert into registration
    values(2015201,'CSE101','programming language-1','1','3015202');
    insert into registration
    values(2015202,'CSE101','programming language-1','2','3015203');




    create table teacher
    (
    Tea_ID number(10),
    Tea_name varchar(30) not null,
    Tea_Dept varchar(30) not null,
    constraint TeaIDPK primary key(Tea_ID)
    );

    insert into teacher
    values(111111,'Atiqul Islam','CSE');
    insert into teacher
    values(222222,'Ariful Islam','CSE');
    insert into teacher
    values(111100,'Mynul Islam','BBA');




    create table MarksEntry
    (Std_ID number(10),
    Reg_no varchar2(20),
    course_code varchar2(10),
    Mark number(3) not null,
    Tea_ID number(10),
    constraint MaEeSidFK foreign key(Std_ID) references student(Std_ID),
    constraint MaEeRegnoFK foreign key(Reg_no) references registration(Reg_no),
    constraint MaEeCcodFK foreign key(course_code) references CourseInfo(course_code),
    constraint MaEeTeIDFK foreign key(Tea_ID) references teacher(Tea_ID)
    );

    insert into MarksEntry
    values(2015201,3015202,'CSE101',95,111111);
    insert into MarksEntry
    values(2015201,3015202,'CSE102',95,111111);
    insert into MarksEntry
    values(2015202,3015203,'CSE101',90,111111);





    create table result_entry
    (
    Std_ID number(10),
    course_code varchar2(10),
    semester varchar2(15),
    Mark number(5,2) not null,
    Gradpoint number(5,2) not null,
    LaterGrade varchar2(3) not null
    );

    ReplyDelete
  12. Md.Shahidul Islam
    Id:201520513
    Batch:49th(DIP)
    section:A
    create table student
    (
    Std_ID number(10),
    Std_name varchar2(30) not null,
    Std_Dept varchar2(30) not null,
    Std_Batch varchar2(15) not null,
    Std_Cell_no varchar2(15),
    constraint SidPK primary key(Std_ID),
    constraint ScellUK unique(Std_Cell_no)
    );

    insert into student
    values(2015200,'Nasir Fardoush','CSE','49th','01918649999');
    insert into student
    values(2015201,'Bashir Fardoush','CSE','49th','01918649988');
    insert into student
    values(2015202,'Abdur Rhaman','CSE','49th','01918649977');




    create table courseInfo
    (
    course_code varchar2(10),
    course_Title Varchar2(30),
    course_credit varchar2(5) not null,
    course_Dept varchar2(30) not null,
    constraint CcodePK primary key(course_code),
    constraint CtitleUK unique(course_Title)
    );

    insert into courseInfo
    values('CSE101','programming language-1','3','CSE');
    insert into courseInfo
    values('CSE102','programming language-2','3','CSE');
    insert into courseInfo
    values('BBA101','Business organigation','1.5','BBA');




    create table account
    (
    Std_ID number(10),
    Debit_balance number(13) not null,
    Credit_balance number(10) not null,
    Reciet_no number(20),
    payment_date varchar2(10) not null,
    constraint accSidFK foreign key(Std_ID) references student(Std_ID),
    constraint accRenoPK primary key(Reciet_no)
    );

    insert into account
    values(2015201,'5050.00','00.00',1,'01/02/16');
    insert into account
    values(2015202,'9050.00','550.00',2,'01/02/16');
    insert into account
    values(2015200,'7050.00','00.00',3,'03/02/16');




    create table registration
    (
    Std_ID number(10),
    course_code varchar2(10),
    course_Title Varchar2(30) not null,
    Reciet_no number(20),
    Reg_no varchar2(20),
    constraint RegRnomPK primary key(Reg_no),
    constraint regSidFK foreign key(Std_ID) references student(Std_ID),
    constraint regCcodeFK foreign key(course_code) references courseInfo(course_code),
    constraint regRenoFK foreign key(Reciet_no) references account(Reciet_no)
    );

    insert into registration
    values(2015200,'CSE101','programming language-1','1','3015201');
    insert into registration
    values(2015201,'CSE101','programming language-1','1','3015202');
    insert into registration
    values(2015202,'CSE101','programming language-1','2','3015203');




    create table teacher
    (
    Tea_ID number(10),
    Tea_name varchar(30) not null,
    Tea_Dept varchar(30) not null,
    constraint TeaIDPK primary key(Tea_ID)
    );

    insert into teacher
    values(111111,'Atiqul Islam','CSE');
    insert into teacher
    values(222222,'Ariful Islam','CSE');
    insert into teacher
    values(111100,'Mynul Islam','BBA');




    create table MarksEntry
    (Std_ID number(10),
    Reg_no varchar2(20),
    course_code varchar2(10),
    Mark number(3) not null,
    Tea_ID number(10),
    constraint MaEeSidFK foreign key(Std_ID) references student(Std_ID),
    constraint MaEeRegnoFK foreign key(Reg_no) references registration(Reg_no),
    constraint MaEeCcodFK foreign key(course_code) references CourseInfo(course_code),
    constraint MaEeTeIDFK foreign key(Tea_ID) references teacher(Tea_ID)
    );

    insert into MarksEntry
    values(2015201,3015202,'CSE101',95,111111);
    insert into MarksEntry
    values(2015201,3015202,'CSE102',95,111111);
    insert into MarksEntry
    values(2015202,3015203,'CSE101',90,111111);





    create table result_entry
    (
    Std_ID number(10),
    course_code varchar2(10),
    semester varchar2(15),
    Mark number(5,2) not null,
    Gradpoint number(5,2) not null,
    LaterGrade varchar2(3) not null
    );

    ReplyDelete
  13. Kamrul Hasan
    Id:201520463
    Batch:49th(DIP)
    section:A
    create table student
    (
    Std_ID number(10),
    Std_name varchar2(30) not null,
    Std_Dept varchar2(30) not null,
    Std_Batch varchar2(15) not null,
    Std_Cell_no varchar2(15),
    constraint SidPK primary key(Std_ID),
    constraint ScellUK unique(Std_Cell_no)
    );

    insert into student
    values(2015200,'Nasir Fardoush','CSE','49th','01918649999');
    insert into student
    values(2015201,'Bashir Fardoush','CSE','49th','01918649988');
    insert into student
    values(2015202,'Abdur Rhaman','CSE','49th','01918649977');




    create table courseInfo
    (
    course_code varchar2(10),
    course_Title Varchar2(30),
    course_credit varchar2(5) not null,
    course_Dept varchar2(30) not null,
    constraint CcodePK primary key(course_code),
    constraint CtitleUK unique(course_Title)
    );

    insert into courseInfo
    values('CSE101','programming language-1','3','CSE');
    insert into courseInfo
    values('CSE102','programming language-2','3','CSE');
    insert into courseInfo
    values('BBA101','Business organigation','1.5','BBA');




    create table account
    (
    Std_ID number(10),
    Debit_balance number(13) not null,
    Credit_balance number(10) not null,
    Reciet_no number(20),
    payment_date varchar2(10) not null,
    constraint accSidFK foreign key(Std_ID) references student(Std_ID),
    constraint accRenoPK primary key(Reciet_no)
    );

    insert into account
    values(2015201,'5050.00','00.00',1,'01/02/16');
    insert into account
    values(2015202,'9050.00','550.00',2,'01/02/16');
    insert into account
    values(2015200,'7050.00','00.00',3,'03/02/16');




    create table registration
    (
    Std_ID number(10),
    course_code varchar2(10),
    course_Title Varchar2(30) not null,
    Reciet_no number(20),
    Reg_no varchar2(20),
    constraint RegRnomPK primary key(Reg_no),
    constraint regSidFK foreign key(Std_ID) references student(Std_ID),
    constraint regCcodeFK foreign key(course_code) references courseInfo(course_code),
    constraint regRenoFK foreign key(Reciet_no) references account(Reciet_no)
    );

    insert into registration
    values(2015200,'CSE101','programming language-1','1','3015201');
    insert into registration
    values(2015201,'CSE101','programming language-1','1','3015202');
    insert into registration
    values(2015202,'CSE101','programming language-1','2','3015203');




    create table teacher
    (
    Tea_ID number(10),
    Tea_name varchar(30) not null,
    Tea_Dept varchar(30) not null,
    constraint TeaIDPK primary key(Tea_ID)
    );

    insert into teacher
    values(111111,'Atiqul Islam','CSE');
    insert into teacher
    values(222222,'Ariful Islam','CSE');
    insert into teacher
    values(111100,'Mynul Islam','BBA');




    create table MarksEntry
    (Std_ID number(10),
    Reg_no varchar2(20),
    course_code varchar2(10),
    Mark number(3) not null,
    Tea_ID number(10),
    constraint MaEeSidFK foreign key(Std_ID) references student(Std_ID),
    constraint MaEeRegnoFK foreign key(Reg_no) references registration(Reg_no),
    constraint MaEeCcodFK foreign key(course_code) references CourseInfo(course_code),
    constraint MaEeTeIDFK foreign key(Tea_ID) references teacher(Tea_ID)
    );

    insert into MarksEntry
    values(2015201,3015202,'CSE101',95,111111);
    insert into MarksEntry
    values(2015201,3015202,'CSE102',95,111111);
    insert into MarksEntry
    values(2015202,3015203,'CSE101',90,111111);





    create table result_entry
    (
    Std_ID number(10),
    course_code varchar2(10),
    semester varchar2(15),
    Mark number(5,2) not null,
    Gradpoint number(5,2) not null,
    LaterGrade varchar2(3) not null
    );

    ReplyDelete
  14. mainul islam mamun
    id-201520448
    49th batch(A)
    Though it's no completed but i have tried.thanks


    create table student_info
    (
    student_id number(15) unique,
    student_name varchar2(20) not null,
    student_address varchar2(50) not null,
    student_cell_number number(12) unique
    );
    desc student_info

    insert into student_info values(201520377,'Taiful Hasan','Sydney,Australia','01922795523')
    insert into student_info values(201520378,'Kamrul Hasna Hasan','London,England','01922795524')
    insert into student_info values(201520379,'Anik Hasan','Barcelona,Spain','01922595524')
    insert into student_info values(201520380,'Pavel Hasan','New York USA','01922795507');

    select * from student_info where student_id='201520378';
    select * from student_info;

    update student_info set student_name='Kamrul Hasan' where student_id='201520378';

    create table payment
    (
    student_id number(15) unique,
    debit number(8) not null,
    credit number(8) not null,
    batch varchar2(20) not null,
    depatment varchar2(50) not null,
    recit_no varchar2(20) unique,
    constraint fk foreign key (student_id)references student_info(student_id)
    );
    alter table payment modify student_id number(15);
    insert into payment values(201520377,'5200','2500','49th','Cse-dip','001')
    insert into payment values(201520378,'5200','2500','49th','Cse-dip','002')
    insert into payment values(201520379,'5200','2500','49th','Cse-dip','003')
    select * from payment
    //
    create table course
    (
    code varchar2(12) unique,
    Tite varchar2(35) unique,
    dept varchar2(12) not null
    );


    select * from course

    alter table course add credit number(20);
    update course set credit='3' where code='1203';
    insert into course values(1204,'Math-1','3')


    insert into course values(1202,'Micro-processor','Cse')
    insert into course values(1203,'Database Management','Cse')

    select * from course
    //

    create table registation
    (
    student_id number(10),
    code varchar2(15),
    recit_no number(15),
    recit_date date(15) not null,
    semester varchar2(15) not null,
    constraint fks foreign key (student_id)references student_info(student_id),
    constraint fkr foreign key (recit_no) references payment(recit_no),
    constraint fkc foreign key (code) references course(code),
    constraint ruq unique(student_id,code,semester)
    );
    alter table registation add constraint reuq unique (student_id,code,semester,recit_no);
    desc registation
    insert into registation values(201520377,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1202','001','06-14-2016','sum-16')
    insert into registation values(201520378,'1201','001','06-14-2016','sum-16')


    select * from registation
    //
    select * from student_info;
    select * from payment;
    select * from course;
    select * from registation;
    create table department
    (
    dept_id number(15) unique,
    dept_name varchar2(20)
    );
    insert into department values(100,'cse-dip')
    insert into department values(101,'cse')
    insert into department values(102,'bba')
    select * from department;
    //
    create table teacher
    (
    tech_id number(15) unique,
    dept_id number(15) not null,
    tech_name varchar2(20) not null
    );
    alter table teacher add constraint defk foreign key (dept_id)references department(dept_id);
    alter table teacher modify dept_id number(15) not null;

    insert into teacher values(500,'100','Ahsan Arif');
    select * from teacher;
    alter table student_info add dept_id number(15);
    alter table student_info add constraint dfk foreign key (dept_id)references department(dept_id);
    update student_info set student_address='dhaka' where student_id='2015220377';

    select * from student_info where student_id='201520377';

    ReplyDelete
  15. Kamrul Hasan
    Id:201520463
    Batch:49th(DIP)
    section:A

    CREATE TABLE ORDERS
    (
    ORDER_ID NUMBER(5),
    QUANTITY NUMBER(4),
    COST_PER_ITEM NUMBER(6,2),
    TOTAL_COST NUMBER(8,2)
    );
    /* DONE */
    SELECT SYSDATE FROM DUAL;
    SELECT USER FROM DUAL;
    /* DONE */
    CREATE TABLE ORDER_AUDIT
    (
    ORDER_ID NUMBER(5),
    QUANTITY NUMBER(5),
    COST_PER_ITEM NUMBER(5),
    TOTAL_COST NUMBER(5),
    USERNAME VARCHAR2(15),
    ODATE DATE
    );
    /* DONE */
    DESC ORDER_AUDIT;
    /* Done */
    CREATE OR REPLACE TRIGGER ORDERS_AFTER_INSERT AFTER INSERT
    ON ORDERS FOR EACH ROW
    DECLARE
    V_USERNAME VARCHAR2(10);
    O_DATE DATE;
    BEGIN
    SELECT USER INTO V_USERNAME FROM DUAL;
    SELECT SYSDATE INTO O_DATE FROM DUAL;
    INSERT INTO ORDER_AUDIT
    (
    ORDER_ID,
    QUANTITY,
    COST_PER_ITEM,
    TOTAL_COST,
    USERNAME,
    ODATE
    )
    VALUES
    (
    :NEW.ORDER_ID,
    :NEW.QUANTITY,
    :NEW.COST_PER_ITEM,
    :NEW.TOTAL_COST,
    V_USERNAME,
    O_DATE
    );
    END;
    INSERT INTO ORDERS(ORDER_ID,QUANTITY,COST_PER_ITEM,TOTAL_COST)VALUES(2,10,8,10);

    ReplyDelete
  16. declare
    v_counter number :=0;
    v_result number;
    begin
    dbms_output.put_line('alamin id 201521223');
    loop
    v_counter := v_counter+1;
    v_result := 5 * v_counter;
    dbms_output.put_line('5' || 'X' || v_counter || '=' || v_result);
    IF v_counter >=20 THEN
    Exit;
    END IF;
    end loop;
    END;

    ReplyDelete
  17. Schema and Cardinality
    https://drive.google.com/open?id=1iBFyjdSXr8zrwLmohlRsNgcskbeXqvlM

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. https://drive.google.com/open?id=1Dgsf181cxlNifSV-HiWngJoiw2lF-JxD


    https://drive.google.com/open?id=1Dgsf181cxlNifSV-HiWngJoiw2lF-JxD

    ReplyDelete
  20. ID:201610254
    https://drive.google.com/open?id=1o0gnc_2IQ9TIIZme5f440sp8

    ReplyDelete
  21. Id: 201710494
    https://www.dropbox.com/s/ogm9scv4bgxxogz/Schema%20and%20cardinality.docx?dl=0

    ReplyDelete
  22. sagar ali
    id:201520552
    https://drive.google.com/open?id=1DNgbOEbm2jCMHNc3jAFLbBFwMeSuZIL8

    ReplyDelete
  23. ID:-201610058
    https://drive.google.com/open?id=1meyzUPNQDrIl77lJtb-4vswC8RjmYs9y

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. ID:201610456
    https://drive.google.com/open?id=1nt3NAKiggmXrsCNFNcfXYVTRZjd928wW

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. This comment has been removed by the author.

    ReplyDelete
  28. Name: Monir Hossain
    ID: 201710443
    Batch: 54th

    https://drive.google.com/file/d/1aUyy3_D8ds3Eq9PZ3l-JSRrmMkvd2e5G/view?usp=sharing

    ReplyDelete
    Replies







    1. CREATE or REPLACE TRIGGER student_result
      AFTER INSERT ON marks
      FOR EACH ROW
      DECLARE
      num number;
      grd varchar(2);
      c_creditt number;
      gpt number;
      gpat number;
      BEGIN
      Select credit into c_credit from c_credit where c_code=:new.c_code;
      num := :new.mark;


      akhane ami bojtechi na

      Delete
  29. Name:MD.Humayun Kobir
    ID: 201710444
    Batch:54th

    https://drive.google.com/file/d/1aUyy3_D8ds3Eq9PZ3l-JSRrmMkvd2e5G/view?usp=sharing

    ReplyDelete
  30. Name: Maniruzzaman
    ID:201710943
    Batch:54th
    Section:B
    https://drive.google.com/drive/recent

    ReplyDelete
  31. Name: Imtiaz Mahmud
    ID:201710390
    Batch:54th
    Section:B

    https://drive.google.com/open?id=1wDnOIyiOXxQfE7Du55y_8DNZOt4i0b3K

    ReplyDelete
  32. This comment has been removed by the author.

    ReplyDelete
  33. Anamul haque rubel
    201710169
    54th
    sir copy to shah riaz hasan ridoy
    https://drive.google.com/open?id=1awiNx5ugU3nD3TQ8rxn_R_gtx-ES3Tjc

    ReplyDelete
  34. This comment has been removed by the author.

    ReplyDelete
  35. This comment has been removed by the author.

    ReplyDelete
  36. Md.Mahbub Hasan Noman
    Id: 201710445
    batch: 54th

    https://drive.google.com/file/d/1UC4lPdT_KNTOUYwc3iJ7ryCqU1R1akch/view?usp=drivesdk

    ReplyDelete
  37. Name: Probal Chandra Das
    ID: 201710196
    Batch: 54th
    Semester: Spring 2018
    https://drive.google.com/drive/folders/1l3fHE9pI4jFY2vi-C-eE1CaOHntkIvHK

    ReplyDelete
  38. id:201710117(54th)

    https://drive.google.com/open?id=1D7XGyYrB-zFYcAJEmbQ7iVBuob9DbiM9

    ReplyDelete
  39. This comment has been removed by the author.

    ReplyDelete
  40. Name : Asha Rahman
    ID: 201710412
    Batch : 54th

    https://drive.google.com/file/d/1wDnOIyiOXxQfE7Du55y_8DNZOt4i0b3K/view?usp=sharing

    ReplyDelete
  41. https://drive.google.com/file/d/1sRB36S3dv7-DIlzOqYfZUnMnQTXH4t7b/view?usp=drivesdk

    ReplyDelete
  42. Name:Subrata Halder
    ID:201710572
    Batch:54th
    Table Design: 5.2

    https://drive.google.com/open?id=1MUWuvXBTdUCZHxlhgamfe3MCH5Z0meSC

    ReplyDelete
  43. This comment has been removed by the author.

    ReplyDelete

  44. Name: Ujjal Halder
    ID:201710571
    Batch:54th
    Table Design: 5.2



    https://drive.google.com/open?id=1qyJChDTiVoF0MFptqBCWq1-rnFEi-oLh

    ReplyDelete
  45. Name: Amit Mridha
    ID No:201710666
    Batch:54th
    Table Design 5.2

    https://drive.google.com/drive/my-drive

    ReplyDelete

Popular Posts