tas.sql

来自「基于数据库设计一个网页,实现基本功能：增删改、查询、统计报表的功能要有.管理信息」· SQL 代码 · 共 107 行

-- -- Teacher Assistant System tas.sql
-- -----------------------------------------------
SET ECHO OFF;
SET LIN 2000;
alter session set nls_date_format = 'yyyy-mm-dd';
-- ------------------------------------------------

drop table attend;
drop table assign;
drop table handin;
drop table students;
drop table classes;
drop table majors;
drop table lectures;
drop table homework;

create table homework
(
	homework_no	decimal(4) primary key,
	description varchar(50) not null
);

create table lectures
(
	lecture_no	decimal(4) primary key,
	lecture_date	date not null,
	topic		varchar(40) not null
);

create table majors
(
	major_no	decimal(4) primary key,
	name		varchar(40)  not null
);

create table classes
(
	major_no	decimal(4) 	not null,
	year		decimal(4)  not null,
	primary key(major_no,year),
	foreign key(major_no) references majors
);

create table students
(
	student_no	decimal(8) primary key,
	last_name	varchar(20),
	first_name	varchar(25) not null,
	pinyin		varchar(20) not null,
	email		varchar(8),
	phone_number	varchar(20),
	major_no	decimal(4) not null,
	class_major	decimal(4) not null,
	class_year	decimal(4) not null,
	admission_date	date,
	foreign key (major_no) references majors,
	foreign key (class_major,class_year) references classes
);


create table attend
(
	student_no	decimal(8) not null,
	lecture_no	decimal(4) not null,
	status		varchar(20)  not null,
	primary key(student_no,lecture_no),
	foreign key(student_no) references students,
	foreign key(lecture_no) references lectures,
	check (status in ('late','leave early','leave','absence'))
);

create table assign
(
	homework_no	decimal(4) not null,
	major_no	decimal(4) not null,
	year		decimal(4)  not null,
	assign_date	date not null,
	deadline	date,
	ratio		decimal(4)  not null,
	primary key(homework_no,major_no,year),
	foreign key(major_no,year) references classes,
	foreign key(homework_no) references homework
);

create table handin
(
	homework_no	decimal(4) not null,
	student_no	decimal(8) not null,
	handin_date	date,
	marks		decimal(4),
	comments	varchar(200),
	primary key(homework_no,student_no),
	foreign key(student_no) references students,
	foreign key(homework_no) references homework
);


INSERT INTO majors (major_no,name) VALUES ('1', 'e-government');
INSERT INTO majors (major_no,name) VALUES ('2', 'embedded software system');
INSERT INTO majors (major_no,name) VALUES ('3', 'communications software');

INSERT INTO classes (major_no,year) VALUES ('1', 2006);
INSERT INTO classes (major_no,year) VALUES ('2', 2006);
INSERT INTO classes (major_no,year) VALUES ('3', 2006);

insert into students(class_major,class_year,student_no,last_name,first_name,pinyin,major_no,admission_date) values(1,2006, 06382160,'闄?,'椋為箯','chenfeipeng',1,'2006-9-1');