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

以学号建立索引

生日表以姓名建立索引

巴特西

DataBase project physical design

DataBase physical design

最新文章

热门文章