studentdb.sql

实例4学院信息管理系统 - RAR 档案文件

/*
    THIS FILE IS FOR USE WITH THE TUTORIAL IN "GETTING STARTED",
    IT IS NOT MEANT TO BE USED WITHOUT REFERRING TO THE MANUAL.

*   You must change the parameters below to match your
*   server name, database name , username, and password.
*   This file defines tables for the STUDENT database.
*/

CONNECT "D:\InterBaseDB\STUDENT.GDB"
USER "SYSDBA" PASSWORD "masterkey";

SET TERM ^ ;

/*
** 基本信息表BaseTB
*/
create table BaseTB
(
  student_id  varchar(8)  NOT NULL, /*学号*/
  name        varchar(12) NOT NULL,/*学员姓名*/
  sex         char(1),/*性别*/
  grade       varchar(4),/*入学年度*/
  native      varchar(20) NOT NULL,/*籍贯*/
  identity    varchar(18),/*身份证编号*/
  age         int, /*年龄*/
  PRIMARY KEY (student_id)
) ^
create unique index idx_BaseTB on BaseTB(student_id) ^

/*
** 学员课时CourseTB
*/
create table CourseTB
(
  student_id varchar(8)  NOT NULL, /*学号*/
  course     varchar(10) NOT NULL, /*学科*/
  course_t   varchar(1)  NOT NULL, /*类别*/
  period     int, /*学时*/
  credit     int, /*学分*/
  begin_date varchar(8), /*起始日期*/
  end_date   varchar(8) /*结束日期*/
) ^


/*
** 学员成绩表CreditTB
*/
create table CreditTB
(
  student_id varchar(8)  NOT NULL, /*学号*/
  course     varchar(10) NOT NULL, /*学科*/
  grades     integer /*成绩*/
) ^

/*
** 系统参数表SysPramTB
*/
create table SysPramTB
(
  pmky varchar(8)  NOT NULL, /*参数类别*/
  cdky varchar(8)  NOT NULL, /*参数名*/
  pama varchar(80), /*参数值*/
  PRIMARY KEY (pmky,cdky)
) ^

INSERT INTO SysPramTB(pmky,cdky,pama) VALUES ('student','index','0000') ^
INSERT INTO SysPramTB(pmky,cdky,pama) VALUES ('student','grade','2002') ^

Create view Sys_YearTB as
select pama from SysPramTB where pmky='student' and cdky='grade' ^


SET TERM ; ^

COMMIT ;