排课系统的数据库设计

发布网友发布时间：2022-05-06 12:36

共2个回答

热心网友时间：2022-06-30 13:42

刚用powerdesigner做了一个不是很完整,如有帮助最好
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2005 */
/* Created on: 2008-5-16 21:05:41 */
/*==============================================================*/

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Class_Time_Map') and o.name = 'FK_CLASS_TI_REFERENCE_上课时段')
alter table Class_Time_Map
drop constraint FK_CLASS_TI_REFERENCE_上课时段
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Class_Time_Map') and o.name = 'FK_CLASS_TI_REFERENCE_班级')
alter table Class_Time_Map
drop constraint FK_CLASS_TI_REFERENCE_班级
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Room_Time_Map') and o.name = 'FK_ROOM_TIM_REFERENCE_上课时段')
alter table Room_Time_Map
drop constraint FK_ROOM_TIM_REFERENCE_上课时段
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Room_Time_Map') and o.name = 'FK_ROOM_TIM_REFERENCE_教室表')
alter table Room_Time_Map
drop constraint FK_ROOM_TIM_REFERENCE_教室表
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Teacher_Time_Map') and o.name = 'FK_TEACHER__REFERENCE_教师')
alter table Teacher_Time_Map
drop constraint FK_TEACHER__REFERENCE_教师
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Teacher_Time_Map') and o.name = 'FK_TEACHER__REFERENCE_上课时段')
alter table Teacher_Time_Map
drop constraint FK_TEACHER__REFERENCE_上课时段
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('教师课程对应表') and o.name = 'FK_教师课程对应表_REFERENCE_教师')
alter table 教师课程对应表
drop constraint FK_教师课程对应表_REFERENCE_教师
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('教师课程对应表') and o.name = 'FK_教师课程对应表_REFERENCE_课程')
alter table 教师课程对应表
drop constraint FK_教师课程对应表_REFERENCE_课程
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('班级课程对应表') and o.name = 'FK_班级课程对应表_REFERENCE_课程')
alter table 班级课程对应表
drop constraint FK_班级课程对应表_REFERENCE_课程
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('班级课程对应表') and o.name = 'FK_班级课程对应表_REFERENCE_班级')
alter table 班级课程对应表
drop constraint FK_班级课程对应表_REFERENCE_班级
go

if exists (select 1
from sysobjects
where id = object_id('Class_Time_Map')
and type = 'U')
drop table Class_Time_Map
go

if exists (select 1
from sysobjects
where id = object_id('Room_Time_Map')
and type = 'U')
drop table Room_Time_Map
go

if exists (select 1
from sysobjects
where id = object_id('Teacher_Time_Map')
and type = 'U')
drop table Teacher_Time_Map
go

if exists (select 1
from sysobjects
where id = object_id('上课时段')
and type = 'U')
drop table 上课时段
go

if exists (select 1
from sysobjects
where id = object_id('教室表')
and type = 'U')
drop table 教室表
go

if exists (select 1
from sysobjects
where id = object_id('教师')
and type = 'U')
drop table 教师
go

if exists (select 1
from sysobjects
where id = object_id('教师课程对应表')
and type = 'U')
drop table 教师课程对应表
go

if exists (select 1
from sysobjects
where id = object_id('班级')
and type = 'U')
drop table 班级
go

if exists (select 1
from sysobjects
where id = object_id('班级课程对应表')
and type = 'U')
drop table 班级课程对应表
go

if exists (select 1
from sysobjects
where id = object_id('课程')
and type = 'U')
drop table 课程
go

/*==============================================================*/
/* Table: Class_Time_Map */
/*==============================================================*/
create table Class_Time_Map (
TimeID int not null,
班级ID int not null,
constraint PK_CLASS_TIME_MAP primary key (TimeID, 班级ID)
)
go

/*==============================================================*/
/* Table: Room_Time_Map */
/*==============================================================*/
create table Room_Time_Map (
教室ID int not null,
TimeID int not null,
constraint PK_ROOM_TIME_MAP primary key (教室ID, TimeID)
)
go

/*==============================================================*/
/* Table: Teacher_Time_Map */
/*==============================================================*/
create table Teacher_Time_Map (
教师ID int not null,
TimeID int not null,
constraint PK_TEACHER_TIME_MAP primary key (教师ID, TimeID)
)
go

/*==============================================================*/
/* Table: 上课时段 */
/*==============================================================*/
create table 上课时段 (
TimeID int not null,
Day int null,
Time int null,
constraint PK_上课时段 primary key (TimeID)
)
go

/*==============================================================*/
/* Table: 教室表 */
/*==============================================================*/
create table 教室表 (
教室ID int not null,
constraint PK_教室表 primary key (教室ID)
)
go

/*==============================================================*/
/* Table: 教师 */
/*==============================================================*/
create table 教师 (
教师ID int not null,
教师姓名 char (10) null,
constraint PK_教师 primary key (教师ID)
)
go

/*==============================================================*/
/* Table: 教师课程对应表 */
/*==============================================================*/
create table 教师课程对应表 (
教师ID int not null,
课程ID int not null,
constraint PK_教师课程对应表 primary key (教师ID, 课程ID)
)
go

/*==============================================================*/
/* Table: 班级 */
/*==============================================================*/
create table 班级 (
班级ID int not null,
专业 varchar(20) null,
constraint PK_班级 primary key (班级ID)
)
go

/*==============================================================*/
/* Table: 班级课程对应表 */
/*==============================================================*/
create table 班级课程对应表 (
课程ID int not null,
班级ID int not null,
constraint PK_班级课程对应表 primary key (课程ID, 班级ID)
)
go

/*==============================================================*/
/* Table: 课程 */
/*==============================================================*/
create table 课程 (
课程ID int not null,
课程名 varchar(Max) null,
Column_3 varchar(20) null,
constraint PK_课程 primary key (课程ID)
)
go

alter table Class_Time_Map
add constraint FK_CLASS_TI_REFERENCE_上课时段 foreign key (TimeID)
references 上课时段 (TimeID)
go

alter table Class_Time_Map
add constraint FK_CLASS_TI_REFERENCE_班级 foreign key (班级ID)
references 班级 (班级ID)
go

alter table Room_Time_Map
add constraint FK_ROOM_TIM_REFERENCE_上课时段 foreign key (TimeID)
references 上课时段 (TimeID)
go

alter table Room_Time_Map
add constraint FK_ROOM_TIM_REFERENCE_教室表 foreign key (教室ID)
references 教室表 (教室ID)
go

alter table Teacher_Time_Map
add constraint FK_TEACHER__REFERENCE_教师 foreign key (教师ID)
references 教师 (教师ID)
go

alter table Teacher_Time_Map
add constraint FK_TEACHER__REFERENCE_上课时段 foreign key (TimeID)
references 上课时段 (TimeID)
go

alter table 教师课程对应表
add constraint FK_教师课程对应表_REFERENCE_教师 foreign key (教师ID)
references 教师 (教师ID)
go

alter table 教师课程对应表
add constraint FK_教师课程对应表_REFERENCE_课程 foreign key (课程ID)
references 课程 (课程ID)
go

alter table 班级课程对应表
add constraint FK_班级课程对应表_REFERENCE_课程 foreign key (课程ID)
references 课程 (课程ID)
go

alter table 班级课程对应表
add constraint FK_班级课程对应表_REFERENCE_班级 foreign key (班级ID)
references 班级 (班级ID)
go

热心网友时间：2022-06-30 13:43

.....光排课系统四个字很难想象哎，要实现什么功能

按照大学的上课方式,罗列了主要的几个属性

教师表教师编号 PK,课程编号 FK
课程表课程编号 PK,课时
教室表教室编号 PK
班级表班级编号 PK
班级组表班级组编号 PK,班级编号 FK
课程表 (教室编号,时间段号) 复合主键,课程编号 FK,教室编号 FK,班级组编号 FK

表是不怎么难设计，并没有唯一答案，
只是会影响代码的简洁度而已

-------------------------------------------------------------
上面没有考虑时间变化，如果不是所有课都是2节一上的话，还要再加个
时间组表时间组编号 PK,时间段编号(区分星期几上下午),时间块编号(区分12节 123节 34节 345节等)根据各人学校情况了
如果教室不是等大的，教室表里还要加个教室类型，根据班级组情况排大班进大教室，中班进大教室或者中教室，小班可以进任何教室。当然，这些都是在代码里实现的