数据库分析与设计档.docx

《数据库分析与设计档.docx》由会员分享，可在线阅读，更多相关《数据库分析与设计档.docx（24页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、毕业设计说明书目：基于二维码技术的高校身份认证系统系统总体设计与数据库设计院:或德日新teacher_password_recoveryPK,FK1 teacher idquestionanswerVtempstudentPK,FK1 student idtemp_photo_address uploeid_tinie tagnumber gender namneidentification_number schoolcollege grade major class photo_tag change_timc name schoolPK student idnumbernamegenderi

2、dentification_number schoolcollege grade major class photo_address photo_update_time codeqr_address photo_versiontemp_teacherPK,FK1 teacher idnumberidentificationnumbcr collegegendermajor tag temp_photo address upload_timephoto tag change_timename schoolteacherPKteacher idnumbernameidentification_nu

3、mber schoolcollege2.3数据库逻辑设计我们所采用的数据库管理系统是由Oracle公司开发的MySQL,是一种关系型数据库。因此，在数据库逻辑设计阶段，我们将将E-R图转换成MySQL所支持的关系模型，形成 了数据库逻辑模式。student password recoveryPK, FK1stud6nt idquestionanswerteacher_loginPKemailPK, FK1teacher idpasswordstudent_loginPK, FK1 student idemail password图4数据库关系模式图或德日新2.4生成数据字典2.4.1 Stud

4、ent Table表1 学生表数据项值类型备注studentjdint学生唯一标识符，主键numbervarchar(15)正式学号信息namevarchar(20)正式姓名信息genderchar(2)正式性别信息identification_numberchar(18)正式身份证信息schoolvarchar(50)正式学校信息collegevarchar(50)正式学院信息gradevarchar(4)正式年级信息majorvarchar(50)正式专业信息classvarchar(4)正式班级信息photo_addressvarchar(200)正式照片存储地址photo_update

5、_timedatetime正式照片最后更新时间codechar(40)加密字符串qr_addressvarchar(200)二维码存储地址photo_versionint正式照片的版本2.4.2 Teacher Table表2教师表数据项值类型备注teacherjdint教师唯一标识符，主键numbervarchar(20)正式职工号信息namevarchar(20)正式姓名信息identification_numberchar(18)正式身份证信息schoolvarchar(50)正式学校信息collegevarchar(50)正式学院信息codechar(40)加密字符串gendercha

6、r(2)正式性别信息majorvarchar(50)正式专业信息photo_addressvarchar(200)正式照片存储地址phtoto_update_timedatetime正式照片最后更新时间authchar(l)审核权限photo_versionint真实照片版本qr_addressvarchar(200)二维码存储地址7博学笃行博学笃行或德日新2.4.3 Temp Table表3学生临时信息表数据项值类型备注studentjdint主键，参考student表的studentjdnumbervarchar(15)临时学号信息namevarchar(20)临时姓名信息genderch

7、ar(2)临时性别信息identification_numberchar(18)临时身份证信息schoolvarchar(50)临时学校信息collegevarchar(50)临时学院信息gradevarchar(4)临时年级信息majorvarchar(50)临时专业信息classvarchar(4)临时班级信息tagchar(l)个人信息审核标志change_timedatetime个人信息最后申请修改时间temp_photo_addressvarchar(200)临时照片存储地址upload_timedatetime临时照片上传时间phtoto_tagchar(l)照片审核标志2.4.4

8、 Temp.teacher Table表4教师临时信息表数据项值类型备注teacherjdint主键，参考teacher表的 teacherjdnamevarchar(20)临时姓名信息genderchar(2)临时性别信息numbervarchar(20)临时职工号信息identification_numberchar(18)临时身份证信息schoolvarchar(50)临时学校信息collegevarchar(50)临时学院信息majorvarchar(50)临时专业信息tagchar(l)个人信息审核标志change_timedatetime个人信息最后申请修改时间temp_photo

9、_addressvarchar(200)临时照片存储地址photo_tagchar(l)照片审核标志upload_timedatetime临时照片上传时间威德日新2.4.5 Studentjogin Table表5学生登录信息表数据项值类型备注emailvarchar(50)登录邮箱，uniquepasswordchar(40)登录密码studentjdint主键，参考student表的studentjd2.4.6 Teacher Jogin Table表6教师登录信息表数据项值类型备注emailvarchar(50)登录邮箱，uniquepasswordchar(40)登录密码student

10、jdint主键，参考teacher表的teacherjd2.4.7 Admin Table表7管理员信息表数据项值类型备注adminjdsmallint主键emailvarchar(50)登录邮箱，uniquepasswordchar(40)登录密码deadlinedatetime最后期限namevarchar(20)姓名博学莺行2.4.8 Teacher_password.recovery Table表8教师密码恢复表数据项值类型备注teacherjdint主键，参考teacher表的teacherjdquestionvarchar(lOO)密保问题answervarchar(100密保答案

11、9博当为行或德日新2.4.9 Student_password_recovery Table表9学生密码恢复表数据项值类型student idintquestion answervarchar(lOO) varchar(100备注主键，参考student表的 studentjd密保问血密保答案2.5数据库创建代码/*Source Server: chenkediSource Server Version : 50528Source Host: 127.0.0.1:3306Source Database: systemTarget Server Type: MYSQLTarget Server

12、Version : 50528File Encoding: 65001Date: 2014-05-26 22:27:42夫/SET FOREIGN_KEY_CHECKS=0;- Table structure for adminDROP TABLE IF EXISTS 、admin、；CREATE TABLE 、admin、(email varchar(50)NOT NULL,password char(40) NOT NULL,deadlinex datetime DEFAULT NULL,name x varchar(20) NOT NULL,admin_id、 smallint(6) N

13、OT NULL AUTO_INCREMENT,PRIMARY KEY (、admin ids),10博学莺行博学莺行威德日新UNIQUE KEY 、mail (、mail、)ENGINE=InnoDB AUT0_INCREMENT=2 DEFAULT CHARSET=utf8;-Table structure for studentDROP TABLE IF EXISTS sstudents;CREATE TABLE sstudentv (s student_idx int (11) NOT NULL AUTO_INCREMENT,x number x varchar(15) NOT NULL

14、, name varchar(20) NOT NULL, x gender x char(2) NOT NULL,s identification_number x char (18) NOT NULL,x schools varchar(50) NOT NULL, college varchar(50) NOT NULL, x grade x varchar(4) NOT NULL, major varchar(50) NOT NULL, class varchar(4) NOT NULL, xphoto_address x varchar(200) DEFAULT NULL, x phot

15、o_update_times datetime DEFAULT NULL, code、char (40) DEFAULT NULL,s qr_address x varchar(200) DEFAULT NULL, xphoto_versions int (11) NOT NULL DEFAULT 1， PRIMARY KEY (、studnt_id、)ENGINE=InnoDB AUTO INCREMENT=17 DEFAULT CHARSET=utf8;-Table structure for student_loginDROP TABLE IF EXISTS、stud njlogin、；

16、 CREATE TABLE、studenjlogin、( email varchar(50) NOT NULL, password char (40) NOT NULL,x student idx int (11) NOT NULL,11博学莺行博学莺行威德日新PRIMARY KEY (、s tudenjid、)，CONSTRAINT x student FOREIGN KEY (s student_idx) REFERENCES 、student (student_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8;-Table structure for stu

17、dent_password_recoveryDROP TABLE IF EXISTS s student_password_recoverys;CREATE TABLE s student_password_recoveryx (x student_idx int (11) NOT NULL,x question x varchar(100) NOT NULL,x answer x varchar(100) NOT NULL,PRIMARY KEY (、studnt_icT ),CONSTRAINT student_password_recovery_ibfk_l、 FOREIGN KEY (

18、s student_idx ) REFERENCES s students ( studenjid) )ENGINE=InnoDB DEFAULT CHARSET=utf8;-Table structure for teacherDROP TABLE IF EXISTS steachers;CREATE TABLE vteachers (xteacher_idx int (11) NOT NULL AUTO_工NCREMENT, number、 varchar(20) NOT NULL,name varchar(20) NOT NULL,x identification_number x ch

19、ar (18) NOT NULL, school varchar(50) NOT NULL,s college x varchar(50) NOT NULL,、cod、char (40) DEFAULT NULL,gender char(2) NOT NULL,major、 varchar(50) DEFAULT NULL,xphoto_address x varchar(200) DEFAULT NULL,photo updat tim datetime DEFAULT NULL,12博学莺行博学莺行威德日新auth、char (1) NOT NULL DEFAULT。，x photo_ve

20、rsion x int (11) DEFAULT 1，x qr_address x varchar(200) DEFAULT NULL, PRIMARY KEY (xteacher_ids)ENGINE=InnoDB AUTO INCREMENT=3 DEFAULT CHARSET=utf8;-Table structure for teacher loginDROP TABLE IF EXISTS teacher_logins;CREATE TABLE steacher_logins (x emailx varchar(50) NOT NULL, password char (40) NOT

21、 NULL, x teacher_idx int (11) NOT NULL, PRIMARY KEY (xteacher_ids, CONSTRAINT x teacher_login_ibfk_l FOREIGN KEY (tachr_id) REFERENCES tachr、( tach工_id、) )ENGINE=InnoDB DEFAULT CHARSET=utf8;-Table structure for teacher_password_recoveryDROP TABLE IF EXISTS steacher_password_recoverys;CREATE TABLE v

22、teacher_password_recoveryx (、teacher_id int (11) NOT NULL,s question x varchar(100) NOT NULL,x answer x varchar(100) NOT NULL,PRIMARY KEY (、tacher_icT ),CONSTRAINT tacher_password_rcovry_ibfk_l FOREIGN KEY (x teacher_idK) REFERENCES x teacher s x teacher_idx) )ENGINE=InnoDB DEFAULT CHARSET=utf8;13博学

23、莺行博学莺行威德日新Table structure for tempDROP TABLE IF EXISTS 、temp、；CREATE TABLE 、七mp、(s student_idx int (11) NOT NULL,s temp_photo_address x varchar(200) DEFAULT NULL,x upload_timex datetime DEFAULT NULL, 、tag、char (1) DEFAULT。，s number s varchar(15) DEFAULT NULL,sgenderx char(2) DEFAULT NULL, name varch

24、ar(20) DEFAULT NULL,s identification_number x char (18) DEFAULT NULL, x schools varchar(50) DEFAULT NULL, college varchar(50) DEFAULT NULL,x grade x varchar(4) DEFAULT NULL, major varchar(10) DEFAULT NULL, class、 varchar(4) DEFAULT NULL,photo_tag char (1) DEFAULT 0 ，x change_time x datetime DEFAULT

25、NULL, PRIMARY KEY (、s tudenjid、),CONSTRAINT 、studnt_id、 FOREIGN KEY (Sstudent_idx) REFERENCES studnt、 (、student_id、) )ENGINE=InnoDB DEFAULT CHARSET=utf8;-Table structure for temp_teacherDROP TABLE IF EXISTS tmp_teache工、；CREATE TABLE tmp_teachr、(x teacher_idx int (11) NOT NULL,number、 varchar(20) DEF

26、AULT NULL,s identification_number x char (18) DEFAULT NULL,x college x varchar(50) DEFAULT NULL,、gnd工、char (2) DEFAULT NULL,14博学莺行博学莺行威德日新major varchar(50) DEFAULT NULL,、tag、char (1) DEFAULT。，s temp_photo_address x varchar(200) DEFAULT NULL,x upload_time x datetime DEFAULT NULL,x photo_tag x char (1

27、) DEFAULT O,x change_times datetime DEFAULT NULL, 、name、 varchar(20) DEFAULT NULL, xschoolx varchar(50) DEFAULT NULL, PRIMARY KEY (、teacher_id、),CONSTRAINT x temp_teacher_ibfk_ls FOREIGN KEY (steacher_ids) REFERENCES、teacher (、teacher_id、) )ENGINE=InnoDB DEFAULT CHARSET=utf8;2.6数据库连接与操作代码?php/连接mysq

28、l,并默认选择system数据库/将数据库的连接信息设为常量DEFINEDBJJSER 一 root);DEFINEDB_PASSWORD, 133295081;/在此处使用127比localhost更快，可以避免mysql进行dns解析DEFINE(DB_HOST!f !127.0.0.1 1 );DEFINEDB_NAME 一 system，);/开始连接$dbc=mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME) OR die (f Could not connect to mysql: f .mysqli_connect_error(

29、);/设置数据库编码mysqli_set_charset($dbc,1utf8);/ 火* identify authentication夫 Copyright (c) 2014, Information System. All rights 工es工vd15系统开发任务分配说明书高校二维码身份认证系统由湘潭大学公共管理学院2010级信息管理与信息系统专业陈 科第、吴鹏、陈湘海、瞿奥斯、张中青扬、陈靖湘六人共同开发、设计与实现，指导老 师为邹凯教授，按要求在五月中旬完成系统开发与调试工作，实现系统功能和目标。项目小组的具体任务分配如下：陈科第：主要负责系统的与系统总体架构设计与数据库设计以及协

30、调组员的工作。主要 包括：系统总体架构描述，系统总体架构图，数据库ER图设计，数据库模式设计，数 据字典设计等，并参与系统各个模块的编码实现与测试，最终形成系统总体架构设计与 数据库设计文档。威德日新夫用户注册* author chenkedi* author chenkedichenkedil234 /Check if the form has been submitinclude(T./user/signup_head.html1);if (isset($_POST1 submitted1) (if($_POSTT type 1=!student!)( $type=!s1;$email=$

31、_POST!emailf;$number=$_POST1 number 1;$identification=$_POST!identification 1;$alert= 1 学号,;)if($_POST1 type=teacher 1 ) ($type=1t!;$email=$_POSTT t_email1;$number=$_POST!t_number1;$identification=$_POSTf t_identification1;$alert= !职工号,;require_once(1./important/mysqli_connect.php!);$error=array();$

32、email_pattern= T/Aw.-+w.-+. A-Za-z 2,6$/T;$identif ication_pattern= 1 / Ad 17 0-9xX $/ 1 ; /18 位身份证匹酉己 /check for an email addresif(empty($email) | !preg_match($email_pattern,$email)( $error = 1请输入正确的email地址！，;else16博学莺行博学莺行威德日新$email=mysqli_real_escape_string($dbcf trim($email); )/check for numberi

33、f(empty($number)$error = 1 请输入正确的 1 . $alert. 1 !)if(empty($identification)| !preg match($identification pattern,$identification)$error = 1请输入正确的身份证号！Ise$identification=mysqli_real_escape_string($dbc,trim($identific ation);_ ) /check for passwordif(empty($_POSTT password!)($error = T你忘记输入密码了 ！ 1 ;el

34、se if($_POST1 password1 !=$_POST!confirm_passwordT)17博学骂行W成德日新$password=mysqli_real_escape_string($db。，trim($_POSTpassword M)；)if(empty($error)( if($type=!s!)($q=HSELECT student_id FROM student WHEREnumber=$number! AND identification_number=1 $identification 1H; $r=mysqli_query($dbc,$q);if ($r & mys

35、qli_num_rows($r)=1) ( /Fetch the result$row=mysqli_fetch_array($r,MYSQLI_ASSOC);/验证邮箱是否已经被使用$q=nSELECT student_id FROM student_login WHERE email=11n;$r_mail=mysqli_query($dbc,$q);if($r_mail & mysqli_num_rows($r_mail)=0)_ $student_id=$row1student_id *; $q=nINSERT INTOstudent_login(emails password,stu

36、dent_id)VALUES(1$email1,SHA1(1 $password1,$student_id)H;$r=mysqli_query($dbc,$q);if(mysqli_affected_rows($dbc)=1) () else($q=SELECT email FROM student_login WHERE student_id=$student_idn;$r=mysqli_query($db。，$q);if ($r & mysqli num rows($r)=1)18博学莺行博学莺行威德日新$row=mysqli_fetch_array($r,MYSQLI_ASSOC);$e

37、rror = 你已经使用邮箱：L”$rowmail”,注册过了,快点右上角登录吧！，1s$error 口 =，对不起，数据库出现故障:T .mysqli error ($dbc) . 1 Qury: $q.，请稍后再试！ !;else$r mail=mysqli fetch array($r mail,MYSQL工 ASSOC);if($row1 student id/!=$r mail1 student id1)$error = ,你所输入的邮箱已经被他人使用过了，请使用其他的邮箱再试！1s$error 口小你已经使用此邮箱注册过了，快点右上角登录 _ else$error =，你所输入的身

38、份信息与数据库中的不匹配，请重新输入！，19博学莺行博学莺行威德日新/教师验证部分if ($type= T tT) ($q=nSELECT teacher_id FROM teacher WHEREnumber=1 $number AND identification_number=1 $identification 1H $r=mysqli_qury($dbc,$q);if ($r & mysqli_num_rows($r)=1)/Fetch theresult$row=mysqli_fetch_array($rz MYSQLI_ASSOC);/验证邮箱是否已经被使用$q=HSELECT t

39、eacher_id FROM teacher_login WHERE email= * $email1;$r_mail=mysqli_query($dbc,$q);if($r_mail & mysqli_num_rows($r_mail)=0) $teacher_id=$row1teacher_id *;$q=nINSERT INTOteacher_login(email,password,teacher_id)VALUES(1$email!, SHA1(1 $password,), $teacher_id)H;$r=mysqli_query($dbcA $q);if(mysqli_affec

40、ted_rows($dbc)=1) ( )else($q=SELECT email FROM teacher_login WHERE teacher_id=$teacher_idn;$r=mysqli_query($dbc,$q);if($r & mysqli_num_rows($r)=1) ( $row=mysqli_fetch_array($r,MYSQLI_ASSOC);$error = 1你已经使用邮箱:20威德日新L”$rowUemail-”.，注册过了,快点右上角登录吧！，)$r_mail=mysqli_fetch_array($ r_ma i1rMYSQLI_ASSOC);if(

41、$row1teacher_id1 !=$r_mail1teacher_id *)($error =，你所输入的邮箱已经被他人使用过了 ,请使用 其他的邮箱再试！ t)else$error /你已经使用此邮箱注册过了，快点右上角登录)else$error =，你所输入的身份信息与数据库中的不匹配，请重新输 入！，if(!empty($error)21博学莺行威德日新echobutton typ=button” class=nclosen data-dismiss=Halertn×/button;foreach ($error as $ms)echo $ms. 1 * .Hnn;ech

42、o 1 T;else(echo f×st工ong注册成功，请点击右上角登录！ 1;) /end of if submittedinclude(!./user/signup_foot.html);?22威德日新3参考文献1 John Satzinger .System Analysis and Design In a Changing World （Fourth Edition） M.北京:机械工 业出版社，2009.4Larry Ullman M.PHP and MySQL for Dynamic WebSites:Visual QuickPro Guide （Fourth Edi

43、tion） M. 北京:人民邮电出版社，2013.12 Abraham Silberschatz ,Henry F. Korth ,S. Sudarshan.数据库系统概念（第五版）M,北京:机械工业 出版社,2006.104薛华成.管理信息系统（第六版）M,北京:清华大学出版社，2011.12 张海藩.软件工程导论（第六版）M,北京:清华大学出版社，2012.5Elizabeth Castro Tom Negrino Dori Smith .HTML5 与 CSS3 基础教程（第七版）M.北京:人民邮电 出版社2013.16 Paul DuBois. MySQL技术内幕（第4版）M北京:人民邮电出版社,2011.7Mark pilgrim.HTML5 Up and Running M.美国:0, Reilly Media, 2010.823目录1系统总体设计11.1 系统逻辑架结构描述11.2 系统逻辑结构图21.3 系统软硬件架构描述21.4 系统软硬件架构图32数据库分析与设计42.1 数据库需求描述42.2 数据库概念设计52.3 数据库逻辑设计62.4 生成数据字典72.4.1 Student Table72.4.2 Teacher Table72.4.3 Temp Table82.4.4 Temp_