Experimental Report on Database Operations

Table Creation

• Student Table: Student(Sno, Sname, Ssex, Sage, Sdept)

create table Student(
    Sno char(9) primary key,
    Sname char(20),
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
);

• Course Table: Course(Cno, Cname, Cpno, Ccredit)

create table Course(
    Cno char(4) primary key,
    Cname char(40),
    Cpno char(4),
    Ccredit smallint,
    foreign key (Cpno) references Course(Cno)
);

• Student Enrollment Table: SC(Sno, Cno, Grade)

create table SC(
    Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key (Sno, Cno),
    foreign key (Sno) references Student(Sno),
    foreign key (Cno) references Course(Cno)
);

Table Modification

• Add Blood Type to Student Table

alter table Student add BloodType char(2);

• Modify Department Column Type

alter table Student modify (Sdept varchar2(40));

• Add Custom Constraint to Age Column

alter table Student add check (Sage > 15 and Sage < 30);

• Remove Constraint

alter table Student drop constraint CK_STUDENT;

• Remove Blood Type Column

alter table Student drop BloodType;

Table Deletion

• Delete Student Table with Constraints

drop table Student cascade CONSTRAINTS;

• Delete Student Enrollment Table and Its Constraints

drop table SC cascade CONSTRAINTS;

Index Creation

• Create Unique Index on Student Name in Student Table

create unique index stusnam on Student(Sname);

• Create Composite Index on Student Enrollment Table

create unique index i_sc on SC(Sno asc, Cno desc);

Index Deletion

• Remove Indexes from Student and Course Tables

drop index stusnam;

drop index i_sc;

Data Insertion

• Insert Data into Student Table

insert into Student values('200215121', '李勇', '男', '20', 'CS');
insert into Student values('200215122', '刘晨', '女', '19', 'CS');
insert into Student values('200215123', '王敏', '女', '18', 'MA');
insert into Student values('200215125', '张立', '男', '19', 'IS');

• Insert Course Data

insert into Course values('2', '数字', null, '2');
insert into Course values('6', '数据处理', null, '2');
insert into Course values('7', 'PASCAL语言', '6', '4');
insert into Course values('4', '操作系统', '6', '3');
insert into Course values('5', '数据结构', '7', '4');
insert into Course values('1', '数据库', '5', '4');
insert into Course values('3', '信息系统', '1', '4');

• Insert Student Enrollment Data

insert into SC values('200215121', '1', '92');
insert into SC values('200215121', '2', '85');
insert into SC values('200215121', '3', '88');
insert into SC values('200215122', '4', '90');
insert into SC values('200215122', '3', '80');

Data Update

• Update Age of Wang Mei

update Student set Sage = 20 where Sname = '王敏';

• Increment Age of All Students

update Student set Sage = Sage + 1;

• Reset Grades for CS Department Students

update SC set Grade = '0' where Sno in(
    select Sno from Student where Sdept = 'CS'
);

Data Deletion

• Delete Information for Liu Chen and Related Students

delete from Student where Sdept in(
    select Sdept from Student where Sname = '刘晨'
);

• Delete CS Department Enrollment Data

delete from SC where Sno in(
    select Sno from Student where Sdept = 'CS'
);

Implementing Foreign Key Constraints

alter table table_name add constraint FK foreign key (column_name) references referenced_column;

Deleting a Table with Foreign Keys

• Enable cascading deletion before dropping the table.

Modifying Column Data Types

• Columns must be empty or have NULL values to modify or drop constraints.