数据库实验1

要求:

  1. 建立如下表

    定义基本表格major_????(专业)与stud_???(学生),关系模式如下(类型长度自定义):

    major_???(MNO专业编号 Primary key, MNAME 专业名称,loc 地址 in(主校区,南校区,新校区,铁道校区,湘雅校区),mdean 专业负责人))

    stud_???(SNO primary key, SNAME, SEX (男,女,其它,其他),TEL ,E-MAIL (基本格式判断), birthday >=’19990731’ ,MNO 班长学号  fk,MajorNo 专业编号 FK ),

    其中MajorNo 专业编号为学号的第3,4位。

    注意表的命名规则

   2. 插入样本数据

    插入3个专业,如计算机科学与技术,物联网工程,数据科学与大数据技术,每个专业不小于10个人,其中包括自己的信息。

    测试相关的完整性约束,并注意保留出错的提示,分析出错的原因

  3.为每个学生建立相关用户,实现权限控制,每个学生可查询自己的信息,班长可查询本班所有学生信息; 为每个专业负责建立用户,每个专业负责可查询本专业所有学生信息。

  4. 为每个学生建立相关用户,实现权限控制,每个学生可查询自己的信息,班长可查询本班所有学生信息; 为每个专业负责建立用户,每个专业负责可查询本专业所有学生信息。

 

 

建表,注意对应的完整性约束:

create user U_J122 identified by U_J123;
grant resource,connect to U_J122;
grant create view to U_J122;
connect U_J122/U_J123;

create table T_major_J122
( 
    mno char(2) primary key,
    mname varchar(20),
    loc varchar(20) check(loc in('主校区','南校区','新校区','铁道校区','湘雅校区')),
    mdean varchar(20)
);

create table T_stud_J122
(
    sno char(10) primary key,
    sname varchar(20),
    sex varchar(10) check(sex in('','','其它','其他')),
    tel varchar(15),
    email varchar(20) check(email like '%@%.%'),
    birthday date check((TO_CHAR('yyyymmdd'))>='19990731'),
    mno char(10) references T_stud_J122(sno),
    majorno char(2) references T_major_J122(mno)
);

插入数据,测试相关完整性的约束:

insert into T_major_J122 values ('02','计算机科学与技术','主校区','胡一超');
insert into T_major_J122 values ('19','大数据','南校区','胡二超');
insert into T_major_J122 values ('21','物联网','新校区','胡三超');

insert into T_stud_J122 values ('0902160122','周锐','','18774894438','838567391@qq.com','11-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160121','胡一','','18774894439','838567392@qq.com','12-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160120','胡二','','18774894430','838567393@qq.com','13-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160119','胡三','','18774894431','838567394@qq.com','14-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160118','胡四','','18774894432','838567395@qq.com','15-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160217','胡五','','18774894433','838567396@qq.com','16-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160216','胡六','','18774894434','838567397@qq.com','17-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160215','胡七','','18774894435','838567398@qq.com','18-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160214','胡八','','18774894436','838567399@qq.com','19-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160213','胡九','','18774894437','838567390@qq.com','20-11月-2000','0902160217','02');

insert into T_stud_J122 values ('0919160122','任一','','18674894438','838564391@qq.com','21-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160121','任二','','18574894438','838567491@qq.com','22-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160120','任三','','18474894438','838567591@qq.com','23-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160119','任四','','18274894438','838567691@qq.com','24-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160118','任五','','18374894438','838567791@qq.com','25-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160217','任六','','18174794438','838567891@qq.com','26-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160216','任七','','18074694438','838567991@qq.com','20-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160215','任八','','18974594438','838567091@qq.com','27-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160214','任九','','18764894438','838567191@qq.com','28-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160213','任十','','18714894438','838567291@qq.com','29-12月-2000','0919160217','19');

insert into T_stud_J122 values ('0921160122','刘一','','11774844438','828567391@qq.com','22-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160121','刘二','','12774874438','838667391@qq.com','23-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160120','刘三','','17774884438','838767391@qq.com','24-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160119','刘四','','14774834438','838867391@qq.com','25-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160118','刘五','','19774821438','838967391@qq.com','26-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160217','刘六','','18774899438','838067391@qq.com','27-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160216','刘七','','18774898438','838167391@qq.com','28-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160215','刘八','','18774896438','838267391@qq.com','29-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160214','刘九','','18774895438','838367391@qq.com','30-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160213','刘十','','18774893438','838467391@qq.com','21-11月-2000','0921160217','21');

给每个学生建立相关用户:

select 'create user U'||sno||' identified by P'||sno||';'from T_stud_J122;

connect system/zr13795;
create user U0902160118 identified by P0902160118;
create user U0902160119 identified by P0902160119;
create user U0902160120 identified by P0902160120;
create user U0902160121 identified by P0902160121;
create user U0902160122 identified by P0902160122;
create user U0902160213 identified by P0902160213;
create user U0902160214 identified by P0902160214;
create user U0902160215 identified by P0902160215;
create user U0902160216 identified by P0902160216;
create user U0902160217 identified by P0902160217;
create user U0919160118 identified by P0919160118;
create user U0919160119 identified by P0919160119;
create user U0919160120 identified by P0919160120;
create user U0919160121 identified by P0919160121;
create user U0919160122 identified by P0919160122;
create user U0919160213 identified by P0919160213;
create user U0919160214 identified by P0919160214;
create user U0919160215 identified by P0919160215;
create user U0919160216 identified by P0919160216;
create user U0919160217 identified by P0919160217;
create user U0921160118 identified by P0921160118;
create user U0921160119 identified by P0921160119;
create user U0921160120 identified by P0921160120;
create user U0921160121 identified by P0921160121;
create user U0921160122 identified by P0921160122;
create user U0921160213 identified by P0921160213;
create user U0921160214 identified by P0921160214;
create user U0921160215 identified by P0921160215;
create user U0921160216 identified by P0921160216;
create user U0921160217 identified by P0921160217;

select 'grant connect to U'||sno||';'from T_stud_J122;

grant connect to U0902160118;
grant connect to U0902160119;
grant connect to U0902160120;
grant connect to U0902160121;
grant connect to U0902160122;
grant connect to U0902160213;
grant connect to U0902160214;
grant connect to U0902160215;
grant connect to U0902160216;
grant connect to U0902160217;
grant connect to U0919160118;
grant connect to U0919160119;
grant connect to U0919160120;
grant connect to U0919160121;
grant connect to U0919160122;
grant connect to U0919160213;
grant connect to U0919160214;
grant connect to U0919160215;
grant connect to U0919160216;
grant connect to U0919160217;
grant connect to U0921160118;
grant connect to U0921160119;
grant connect to U0921160120;
grant connect to U0921160121;
grant connect to U0921160122;
grant connect to U0921160213;
grant connect to U0921160214;
grant connect to U0921160215;
grant connect to U0921160216;
grant connect to U0921160217;

select 'create user U'||mno||' identified by P'||mno||';'from T_major_J122;

create user U02 identified by P02;
create user U19 identified by P19;
create user U21 identified by P21;

select 'grant connect to U'||mno||';'from T_major_J122;

grant connect to U02;
grant connect to U19;
grant connect to U21;

建立视图,实现权限控制:

create view V_view1_J122
as
select * from T_stud_J122
where 'U'||sno=user or 'U'||mno=user or 'U'||majorno=user;

grant select on V_view1_J122 to public;

set linesize 200;
select * from U_J122.V_view1_J122;

conn U02/P02;
select * from U_J122.V_view1_J122;

 

posted @ 2018-12-18 15:21  从让帝到the_rang  阅读(1082)  评论(0编辑  收藏  举报