REM user: HXH 建立用户
create user HXH
identified by ok
default tablespace USER_DATA
temporary tablespace temp_USER_DATA
quota unlimited on USER_DATA
quota unlimited on Temp_USER_DATA
quota unlimited on Index_USER_DATA;
REM 授给用户HXH 的权限
grant connect,resource,DBA to HXH ;
REM 2.3.1 创建系统用户表格user_Info
CREATE TABLE HXH.user_Info
(
user_ID VARCHAR2 (10) NOT NULL ,
user_PWD VARCHAR2 (10) NOT NULL ,
user_Des VARCHAR2 (20),
CONSTRAINT user_ID_PK PRIMARY KEY (User_ID)
)
TABLESPACE "USER_DATA";
REM 2.3.2 创建学生基本信息表格student_Info
CREATE TABLE hxh.student_Info
(
student_ID Number(8) NOT NULL ,
student_Name VARCHAR2 (10) ,
student_Sex VARCHAR2 (2) ,
born_Date date,
class_NO Number(8),
tele_Number VARCHAR2 (10),
ru_Date date,
address varchar(50),
comments varchar(200),
CONSTRAINT student_info_PK PRIMARY KEY (student_id)
)
TABLESPACE "USER_DATA";
REM 2.3.3 创建班级信息表格class_Info
CREATE TABLE HXH.class_Info
(
class_No Number(8) NOT NULL ,
grade VARCHAR2 (10),
director VARCHAR2 (10),
classroom_No VARCHAR2 (10),
CONSTRAINT class_No_PK PRIMARY KEY (class_No)
)
TABLESPACE "USER_DATA";
REM 2.3.4 创建课程基本信息表course_Info
CREATE TABLE HXH.course_Info
(
course_No Number(8) NOT NULL ,
course_Name VARCHAR2 (10) ,
course_Type VARCHAR2 (10),
course_Des VARCHAR2 (50),
CONSTRAINT course_No_PK PRIMARY KEY (course_No)
)
TABLESPACE "USER_DATA";
REM 2.3.5 创建年级课程设置信息表gradecourse_Info
CREATE TABLE HXH.gradecourse_Info
(
grade VARCHAR2 (10),
course_No Number(8)
)
TABLESPACE "USER_DATA";
REM 2.3.6 创建学生成绩信息表result_Info
CREATE TABLE HXH.result_Info (
exam_No VARCHAR2 (10) NOT NULL ,
student_ID Number(8) NOT NULL ,
student_Name VARCHAR2 (10) ,
class_No Number(8) ,
course_Name VARCHAR2 (10) ,
result Number(3,2),
CONSTRAINT result_info_PK PRIMARY KEY (exam_no, student_id)
)
TABLESPACE "USER_DATA";
