DataBase project physical design

DataBase physical design

//Table:
/*student*/
create table student(
	id int not null primary key,	/*学号*/
	name varchar(20) not null,		/*姓名*/
	college varchar(20) not null,	/*学院*/
	grade int not null,				/*年级*/
	gender varchar(6) not null,		/*性别*/
	age int not	null				/*年龄*/
);

Table student(学生信息)

id	name	college	grade	gender	age
14001	pual	CS	junior	male	20
14002	alice	AD	junjor	femele	21

可按上述表格形式插入

/*study*/
create table curriculum(
	s_id int not null, 	/*学号*/
    c_id int not null	/*课程号*/
    	constraint c_id_fk foreign key (c_id) references course(c_id)
);

Table curriculum(课表)

s_id	c_id
14001	9001
14001	9002
14002	9001
14002	9003
14003	9004

/*study*/
create table course(
	c_id int not null constraint primary key,	/*课程号*/
	teacher varchar(20) not null,	/*老师*/
    credit int not null,			/*学分*/
	signed int not null,			/*签到率*/
	c_difficulty int not null		/*课程难度*/
);

Table course

c_id	c_name	teacher	signed(10)	c_difficulty(10)
9001	DataBase	Dr.Guan JiHong	10	8
9002	Computer Graphic	Dr.Zhao Junqiao	0	9
9003	Computer Architecture	Dr.Qin Guofeng	9	8
9004	Operate System	Dr.Fang Yu	0	8.5

/*do*/
create table task(
	t_id varchar(7) not null constraint primary key,/*作业号(任务创立日期+编号)*/
    												/* 格式: Year+M + D  + number
                                                       e.g  7 + 12 + 16 + 02 */
	t_course int constraint t_c_fk 
		foreign key references course(c_id),	/*作业所属课程*/
	t_deadline datetime, 				/*20161210 最后期限*/
    t_detail varchar(20),				/*详情*/
	t_amount int not null,				/*1.2.3.4.5 作业数量*/
    t_difficulty int not null			/*1.2.3.4.5 作业难度*/
);

Table task

t_id	t_course	t_deadline	t_detail	t_amount(10)	t_difficulty(10)
6121601	9001	2016-12-20 23:59:59	P232,2/3/5	3	7
2	9002	2017-01-09 23:59:59	Assignment3	2	9

create table message(
	m_id int not null constraint primary key,/*信息号(任务创立日期+编号)
    										   格式: Year+M + D  + number
                                       		   e.g  7 + 12 + 16 + 02   */
	type varchar(10) not null,		/*
									信息类型:
                                    娱乐(entertainment)
                                    兴趣(interest)
                                    工作(job)
                                    学习(study)
                                    信仰(faith)
                                    */
	details varchar(30),			/*详情*/
	m_deadline datetime,			/*最后期限*/
	duration datetime,				/*持续时间*/
	Emergency_index	int not null	/*1.2.3.4.5	紧急程度*/
);

Table message

m_id	type	details	Start	End	Emergency_index(10)
6120201	interest	play piano	2016-12-12 17:30:00	2016-12-12-17:59:59	9
6120201	faith	go to the YangPu church	2016-12-12 17:30:00	2016-12-12-17:59:59	9

/*live*/
create table birthday(
	f_name varchar(20) not null constraint primary key,	/*姓名*/
	relation varchar(10) not null,	/*关系*/
	current_age int not null,		/*当前年龄*/
	f_gender varchar(6) not null,	/*性别*/
	f_birthdat datetime not null,	/*生日*/
	gift varchar(1) not null		/*是否需要礼物*/
);

f_name	relation	current_age	f_gender	f_birthday	gift(Y/N)
Bob	Faother	34	male	2017-09-17 00:00:00	Y
Alice	sister	23	female	2017-02-13 00:00:00	Y
Judy	friend	20	female	2017-02-22 00:00:00	Y

以学号建立索引
生日表以姓名建立索引