Since this article is a summary written after the completion of the system, many operation results show the final results after the completion of the system, which will be different from the corresponding SQL execution results.

1, Introduction

requirement:

Complete a simple application system design and development.

Background:

In this practice, the database structure of student information management system is created.

Requirements:

Realize the database design of teaching module in student information management system.

Function Description:

The teaching module includes student basic information management, course information management, class information management, course selection information management, teacher basic information management and teacher teaching information management.

Details are described as follows:

The basic information of students includes: student number, name, age, gender, enrollment date and home address.

The basic information of teachers includes: teacher number, name, age and course number.

The basic information of the course includes: course number, course name and course credits.

Course selection (teacher selection) information includes: student number, teacher number and grade. (since there may be multiple instructors for each course, the instructor of this course needs to be specified at the same time when selecting a course, so the teacher's teacher number is stored here)

Class information includes: class number, Department, grade and class.

The relationship between the entities is as follows:

1. A student can only belong to one class, and there are multiple students in one class;
2. A student can choose multiple courses, and a course can be selected by multiple students;
3. A teacher can only teach one course, and a course can be taught by multiple teachers.

2, Conceptual model design stage

1. Analyze business entities:

   Four entities: teachers, classes, students and courses.

2. Analyze the relationship between business entities and clear the data redundancy of business entities:

   From the topic analysis, there are entity relationships between students and courses, teachers and courses, classes and students: students curriculum (m:n), courses teachers (1:n), classes students (1:n). Students can use the teacher number to replace the course number in course selection, and the teacher number can uniquely determine the course information.

3, Create school user

grant dba to hr;
conn hr/hr;
--establish school user
create user school identified by school;
--by school User authorization dba;
grant dba to school;
--connect school user
conn school/school;
--Create table space llf
create tablespace llf datafile 'd:llf.dbf'size 20m uniform size 128k;

4, Build table

--1,class surface
create table class(
clno varchar(5) primary key,
dept varchar2(40) not null,
grade varchar(8) not null,
branch varchar2(20) not null
)tablespace llf;
--2,student surface
create table student(
sno varchar2(5) primary key,
name varchar2(12) not null,
age number(2) not null,
sex char(1) not null,
entrance date,
address varchar2(100),
clno varchar2(5),
constraint fk_clno foreign key(clno) references class(clno)
)tablespace llf;
--3,course surface
create table course(
cno varchar2(5) primary key,
name varchar2(60) not null,
score number(2) not null
)tablespace llf;
--4,teacher surface
create table teacher(
tno varchar2(5) primary key,
name varchar2(12) not null,
age number(2),
cno varchar2(5) not null
)tablespace llf;
--5,Student course selection table st
create table st(
sno varchar2(5) not null,
tno varchar2(5) not null,
grade number(2) default 0
)tablespace llf;

5, Append comments to table

comment on table class is 'Class information table';
comment on table student is 'Student information sheet';
comment on table course is 'Course information sheet';
comment on table teacher is 'Teacher information form';
comment on table st is 'Student course selection table';

6, Add constraint

1. class.grade constraint check

   alter table class
   add CONSTRAINT ck_class_grade check(grade in('Freshman','Sophomore','Junior','Senior'));
   

2. student.age/student.sex constraint

   alter table student
   add constraint ck_student_age check(age>=10 and age<41);
   alter table student
   add constraint ck_student_sex check(sex in ('1','0'));
   comment on column student.sex is'Gender,1:male,0:female';
   

3. check constraint for course.score

   alter table course
   add constraint ck_course_score check(score>0 and score<10);
   

4. teacher table constraint

   alter table teacher
   add constraint fk_teacher_cno foreign key(cno) references course(cno);
   alter table teacher
   add constraint ck_teacher_age check(age>20 and age<66);
   

5. Constraints of st table

   --st Foreign key constraints for
   alter table st
   add constraint fk_st_sno foreign key(sno) references student(sno);
   alter table st
   add constraint fk_st_tno foreign key(tno) references teacher(tno);
   --st of check constraint
   alter table st
   add constraint ck_st_grade check(grade >= 0 and grade <= 9);
   

7, Enter starting data

1. Add data to class table

   insert into class values ('CC101','computer','Freshman','Class one');
   insert into class values ('CC102','computer','Freshman','Class two');
   

   

2. Add data to student table

   insert into student values('95001','Fei Zhang',23，1，to_date('2006-9-1','yyyy-mm-dd'),'Qingdao','CC101');
   insert into student values('95002','Zhao Yun',21，1，to_date('2006-9-1','yyyy-mm-dd'),'Qingdao','CC101');
   insert into student values('95003','Guan Yu',22，1，to_date('2006-9-1','yyyy-mm-dd'),'Tianjin','CC101');
   insert into student values('95004','army officer's hat ornaments',20，0，to_date('2006-9-1','yyyy-mm-dd'),'Beijing','CC102');
   insert into student values('95005','Little Joe',19，0，to_date('2006-9-1','yyyy-mm-dd'),'Shanghai','CC102');
   

   

3. Add data to the course information table

   insert into course values('CN001','Database principle',6);
   insert into course values('CN002','data structure',2);
   insert into course values('CN003','Compilation principle',2);
   insert into course values('CN004','Programming',2);
   insert into course values('CN005','Advanced mathematics',3);
   

   

4. Add data to the teacher information table

   insert into teacher values('T8101','Liu Hua',34,'CN001');
   insert into teacher values('T8103','Wang Gang',28,'CN002');
   insert into teacher values('T8104','Xue Zhang',33,'CN003');
   insert into teacher values('T8105','Zhao Shun',32,'CN004');
   insert into teacher values('T8106','Zhou Gang',32,'CN005');
   

   

5. Add data to the course selection information table

   insert into st values('95001','T8101',0);
   insert into st values('95001','T8102',0);
   insert into st values('95001','T8104',0);
   insert into st values('95002','T8101',0);
   insert into st values('95002','T8103',0);
   insert into st values('95002','T8104',0);
   insert into st values('95002','T8106',0);
   insert into st values('95003','T8102',0);
   insert into st values('95003','T8104',0);
   insert into st values('95003','T8105',0);
   

   

8, Create view

During the development of student information management system - teaching module, the following pages need to be realized (create view):

1. Student information query page: Department, grade, class, student number, student name, age, gender, enrollment date

   create or replace view vw_class_student
   as
   select c.dept, c.branch,s.sno,s.age,s.sex,s.entrance
   from class c join student s on c.clno=s.clno
   with read only;
   

   

2. Query of teaching information page: provide teacher number, teacher name, age, number and name of courses

   create or replace view vw_teacher_course 
   as 
   select t.tno Teacher number,t.name Teacher name,t.age Teacher age,c.cno Course number,c.name Course name
   from teacher t join course c on t.cno=c.cno with read only;
   

   

3. Course selection information query page: provide student number, student name, selected course number and course name

   create or replace view vw_student_course
   as
   select s.sno Student number,s.name Student name,c.cno Course number,c.name Course name
   from student s join st st on s.sno=st.sno
   join teacher t on st.tno=t.tno
   join course c on t.cno=c.cno
   with read only;
   

   

4. Teaching information query page: provide the names of departments, grades, classes and teachers who are teaching in the current class

   create or replace view vw_class_teacher
   as
   select c.dept system,c.grade grade,c.branch class,t.name Teacher name
   from class c join student s on c.clno=s.clno
   join st st on s.sno=st.sno
   join teacher t on st.tno=t.tno
   with read only;
   

   

5. Class course query page: provides the name of the course covered by the Department, grade, class and current class

   create or replace view vw_class_course
   as
   select cl.dept system,cl.grade grade,cl.branch class,c.name Course name
   from class cl join student s on cl.clno=s.clno
   join st st on s.sno=st.sno
   join teacher t on st.tno=t.tno
   join course c on t.cno=c.cno
   with read only;
   

   

9, Create data statistics page

1. Class size statistics page

   create or replace view vw_class_count
   as
   select cl.dept system,cl.grade grade,cl.branch class,count(*) Number of students
   from class cl join student s on cl.clno=s.clno
   group by(cl.dept,cl.grade,cl.branch);
   

   

2. Student achievement statistics page: it is divided into classes and students to count the total achievement information of each student and the number of elective courses

   create or replace view vw_student_count
   as
   select cl.dept system,cl.grade grade,cl.branch class,s.name Student name,count(*) Number of courses,sum(st.grade) Total score
   from class cl join student s on cl.clno=s.clno
   join st st on s.sno=st.sno
   group by(cl.dept,cl.grade,cl.branch,s.name);
   

   

3. Student grade list page

   The decode function will be used here

   Decode (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n, default value)

   The meaning of this function is as follows:

   IF condition=Value 1 THEN
   　　　　RETURN(Translation value 1)
   ELSIF condition=Value 2 THEN
   　　　　RETURN(Translation value 2)
   　　　　......
   ELSIF condition=value n THEN
   　　　　RETURN(Translation value n)
   ELSE
   　　　　RETURN(Default value)
   END IF
   

   Create page:

   create or replace view vw_student_grade
   as
   select s.name full name,
        sum(decode(c.name,'Database principle',grade,null))Database principle,
        sum(decode(c.name,'data structure',grade,null))data structure,
        sum(decode(c.name,'Compilation principle',grade,null))Compilation principle,
        sum(decode(c.name,'Programming',grade,null))Programming,
        sum(decode(c.name,'Advanced mathematics',grade,null))Advanced mathematics
   from student s join st st on s.sno=st.sno
   join teacher t  on st.tno=t.tno
   join course c on t.cno=c.cno
   where c.name in ('Database principle','data structure','Compilation principle','Programming','Advanced mathematics')
   group by s.name;
   

   

10, Define record type by using% rowtype

declare
--use%rowtype statement class Record type variable for
class_record class%rowtype;
row_id rowid;
info varchar2(60);
begin
  class_record.clno:='CC103';
  class_record.dept:='automation';
  class_record.grade:='Junior';
  class_record.branch:='Class two';
--Use the record type variable to complete the data insertion operation
  insert into class values class_record
  returning rowid,clno||','||dept||','||grade||','||branch
  into row_id,info;
  dbms_output.put_line('insert:'||row_id||':'||info);
--Complete the whole line modification of data based on the record type variable
  class_record.clno:='CC104';
  update class set row=class_record where clno='CC103'
  returning rowid,clno||','||dept||','||grade||','||branch into   row_id,info;
  dbms_output.put_line('modify:'||row_id||':'||info);
--Data deletion based on record type variables
  class_record.clno:='CC104';
  delete from class where clno=class_record.clno
  returning rowid,clno||','||dept||','||grade||','||branch into   row_id,info;
  dbms_output.put_line('Delete:'||row_id||':'||info);
  exception
    when others then
      dbms_output.put_line('Something unusual happened');
end;

11, Use of cursors

Cursor:

Oracle sets up a data buffer for users to store the execution results of SQL statements. Each SQL statement executed in Oracle database has a corresponding cursor.

Cursors fall into two categories:

1. Implicit cursors: handle single line select into and DML statements;
2. Display cursor: handles multiple rows of data returned by the select statement

Use (processing) steps of explicit cursor:

1. Define cursor

   cursor cursor_name[...]select_statement;

2. Open cursor

   open cursor_name[...];

3. Extract cursor data

   fetch cursor_name into {};

4. Process the data pointed by the cursor pointer

5. Continue processing until there are no records to process

6. Close cursor

   close cursor_name;

Case:

declare
--declare cursor 
  cursor class_cursor is select clno,dept from class;
  v_clno class.clno%type;
  v_dept class.dept%type;
begin
--Open cursor
  open class_cursor;
--Cyclic value
  loop
    --Extract the current row data into a variable
    fetch class_cursor into v_clno,v_dept;
    exit when class_cursor%notfound;
    dbms_output.put_line('Class number:'||v_clno||'Department:'||v_dept);
  end loop;
--Close cursor
  close class_cursor;
end;

12, Nested table

1. When creating a nested table column, you need to first create a nested table type

   create or replace type family_type is table of varchar2(20);
   

2. Readjust the student table and add a column of family_ New attribute column of type

   alter table student add(
     family family_type
   )nested table family store as family_table;
   

3. Add data to nested tables

   insert into student 
   values('95006','Zhaojun',19,0,to_date('2006-09-01','yyyy-mm-dd'),'Shanghai','CC102',family_type('father','mother','sister'));
   

4. When you need to retrieve the data of nested table columns, you also need to define nested table variables to receive data

   declare
   --statement family_type Type is used to accept search results
   family_table family_type;
   v_name student.name%type;
   begin
     select name,family into v_name,family_table
     from student where sno=&sno;
     dbms_output.put_line('student'||v_name||'Relatives are:');
     for i in 1..family_table.count
     loop
       dbms_output.put_line(family_table(i)||' ');
     end loop;
       dbms_output.new_line();
     exception
       when no_data_found then
         dbms_output.put_line('The student number you specified does not exist!');
   end;
   

5. Update nested table column data

   declare
     family_table1 family_type:=family_type('father','mother','brother','sister','younger brother');
     family_table2 family_type;
     v_sno student.sno%type:=&sno;
     v_name student.name%type;
   begin
   --Updating nested table columns with nested table variables
     update student
     set family=family_table1 where sno=v_sno;
   --Get updated data
     select name,family into v_name,family_table2
     from student where sno=v_sno;
     dbms_output.put_line('student'||v_name||'Relatives are:');
     for i in 1..family_table2.count
     loop
       dbms_output.put_line(family_table2(i)||' ');
     end loop;
       dbms_output.new_line();
     exception
       when no_data_found then
         dbms_output.put_line('The student number you specified does not exist!');
   end;
   

13, Examples of exceptions

declare
  v_n1 int:=&n1;
  v_n2 int:=&n2;
  v_div int;
begin
  v_div:=v_n1/v_n2;
  dbms_output.put_line(v_n1||'/'||v_n2||'='||v_div);
  exception
    when zero_divide then
      dbms_output.put_line('Divisor cannot be zero!');
end;

14, Trigger

trigger:

When the database is operated (DML operation), some events will occur to automatically execute the corresponding program.
Trigger classification:

1. Statement trigger: this type of trigger is activated when DML operations (insert, update, delete) are executed.

2. Row trigger.

Statement trigger:

create or replace trigger trg_class
before insert or update or delete on class
declare
  v_now varchar2(30);
begin
  v_now:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  case
    when INSERTING THEN
          DBMS_OUTPUT.PUT_LINE(v_now||'yes class Table insert operation');
    when updating then
          dbms_output.put_line(v_now||'yes class Table update operation');
    when deleting then
          dbms_output.put_line(v_now||'yes class Table delete operation');
  end case;
end;

Test:

insert into class values('CC106','automation','Sophomore','Class two');
delete from class where clno='CC106';

Row trigger:

create or replace trigger trg_class_before
before insert or update or delete on class
for each row
declare
  v_now varchar2(30);
begin
  v_now:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
  case
    when INSERTING THEN
          DBMS_OUTPUT.PUT_LINE('Add data:'||:new.clno||','||:new.dept||','||:new.grade||','||:new.branch);
    when updating then
      dbms_output.put_line('Data before modification:'||:old.clno||','||:old.dept);
      dbms_output.put_line('Modified data:'||:new.clno||','||:new.dept);
    end case;
end;

Test:

insert into class values('CC106','automation','Freshman','Class one');
update class
set clno='CC109',dept='chemical industry'
where clno='CC106';

15, Use package

In view of the system requirements, it is often necessary to print report information. In order to facilitate maintenance, we uniformly encapsulate the process of printing reports and put them into the package for unified maintenance.

analysis:

1. The functions of the package structure are generally defined according to the module functions. The operation on the student table can be defined as a package.
2. For report printing, it generally involves the processing of multi line statements and the introduction of cursors.
3. Since report printing generally does not require return value, it can be encapsulated by process.

Case:

--1,Define package specification
  create or replace package report_pack
  is
    --Print the current class information and the student information of the current class according to the entered class number
    procedure student_of_class(p_clno class.clno&type);
  end report_pack;
  
--2,about report_pack The package body code is as follows:
  create or replace package body report_pack
  is
  --Implementation process student_of_class
  procedure student_of_class(p_clno class.clno&type)  --process
  is
    cursor student_cursor is 
    select * from student where clno=p_clno;  --cursor
    student_record student%rowtype;  --Variables defining student record types
    class_record class%rowtype;   --Define class record type
    v_count number(2);
    v_sex char(3);
  begin
    select * into class_record from class where clno = p_clno;  --Get class information
    select count(*) into v_count 
    from student 
    where clno=p_clno
    group by clno;   --Get class size
    dbms_output.put_line(class_record.dept||'system'||
    class_record.grade||class_record.branch||'In total:'||v_count||'people');
    dbms_output.putline('--------------------------------------------');
    --Get the student information of the current class
    open student_cursor(p_clno);
    loop
      fetch student_cursor into student_record;
      exit when student_cursor&notfound;
      if student_record.sex='1' then v_sex:='male';
                                else v_sex:='female';
     end if;
     dbms_output.put_line('Student number:'||student_record.sno||',full name:'||
     student_record.name||',Age:'||student_record.age||',Gender:'||v_sex
     ||',Date of admission:'||tochar(student_record.entrance,'yyyy-mm-dd'));
   end loop;
   close student_cursor;
   exception
      when no_data_found then 
            dbms_output.put_line('The specified class number does not exist!');
end student_of_class;
end report_pack;

Test:

begin
  report_pack.student_of_class('cc101');
end;