數(shù)據(jù)庫原理及應(yīng)用大作業(yè)文檔

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流數(shù)據(jù)庫原理及應(yīng)用大作業(yè)文檔.精品文檔.通信錄項(xiàng)目開發(fā)文檔目 錄1需求分析21.1應(yīng)用需求21.1.1用戶需求分析21.1.2系統(tǒng)存儲需求21.2功能描述21.3數(shù)據(jù)流圖和數(shù)據(jù)字典21.3.1數(shù)據(jù)流圖21.3.2數(shù)據(jù)字典22.概念結(jié)構(gòu)設(shè)計(jì)42.1局部E-R圖42.2全局E-R圖42.3關(guān)系圖53.邏輯結(jié)構(gòu)設(shè)計(jì)53.1 E-R圖向關(guān)系模型轉(zhuǎn)化53.1.1 生成關(guān)系模式53.1.2關(guān)系模式的優(yōu)化53.1.3數(shù)據(jù)庫模式和轉(zhuǎn)換原則63.1.4用戶子模式定義63.2施加約束64.數(shù)據(jù)庫建立64.1表和視圖的創(chuàng)建74.1.1建立數(shù)據(jù)表74.1.2建立視圖8

2、4.1.3存儲過程91.3.1數(shù)據(jù)流圖圖1-3-1數(shù)據(jù)流圖表1-3-2-1-1 數(shù)據(jù)項(xiàng)分E-R圖2-1-32.2全局E-R圖圖2-2-1 全局E-R圖2.3關(guān)系圖表3-1-3-2 類型信息表（TypeName）列名數(shù)據(jù)類型可否為空說明TypeNochar(3)NOT NULL學(xué)校類型號SchTypeChar(1)NOT NULL學(xué)校類型SchNatureChar(3)NOT NULL學(xué)校性質(zhì)表3-1-3-3 學(xué)校信息表（School）列名數(shù)據(jù)類型可否為空說明SchoolNameChar(20)NOT NULL學(xué)校名稱ProvinceChar(10)學(xué)校所在省份CityChar(20)學(xué)校所在市

3、CountryChar(20)學(xué)校所在縣TownChar(20)學(xué)校所在鄉(xiāng)鎮(zhèn)表3-1-3-4 教育信息表（Education）列名數(shù)據(jù)類型可否為空說明SnoChar(20)NOT NULL學(xué)生學(xué)號TypeNoChar(3)NOT NULL學(xué)校類型號SchoolNameChar(20)NOT NULL學(xué)校名稱ClassNameChar(20)NOT NULL班級名稱ClassTeacherChar(12)班主任3.1.4用戶子模式定義3.2施加約束在學(xué)生信息表中（Student）學(xué)生性別必須是男或女，學(xué)生出生日期格式為yyyy-mm-dd；在學(xué)校類型信息表中（TypeName），學(xué)校類型輸入必須

4、從以下選擇：H/M/C/U(H:高中/M：初中/C：大專/U：大學(xué))，學(xué)校性質(zhì)必須從以下選擇：PUB/PRI(PUB：公辦型/PRI：民辦型)4.數(shù)據(jù)庫建立CREATE DATABASE Information-指定數(shù)據(jù)庫名字為InformationON PRIMARY-定義在主文件組上的文件NAME=first_data,-指定主文件的邏輯名稱FILENAME='E:SQL SERVERInformationInformation.mdf',-指定主文件的物理名稱SIZE=20MB,-設(shè)置初始大小為MB,MB可省略MAXSIZE=unlimited,-設(shè)置最大限度為無限FIL

5、EGROWTH=5%-指定增長速度為%LOG ON-定義事務(wù)日志文件NAME=first_log,-定義事務(wù)日志文件的邏輯名稱FILENAME='E:SQL SERVERInformationInformation.ldf', -定義事務(wù)日志文件的物理名稱SIZE=1,-設(shè)置初始大小為MBMAXSIZE=100,-設(shè)置最大限度為MBFILEGROWTH=1-指定增長速度為MB4.1表和視圖的創(chuàng)建4.1.1建立數(shù)據(jù)表創(chuàng)建Student表CREATE TABLE StudentSno varchar(20) not null primary key,Ssex char(2) not

6、 null,Sname char(12) not null,Sbirth date,QQ char(20)創(chuàng)建School表CREATE TABLE SchoolSchoolName char(20) not null primary key,Province char(10),City char(20),Country char(20),Town char(20)創(chuàng)建TypeName表CREATE TABLE TypeNameTypeNo char(3) not null primary key,SchType char(1) not null,SchNature char(3) not n

7、ull創(chuàng)建Education表CREATE TABLE EducationSno varchar(20) not null foreign key references Student(Sno),TypeNo char(3) not null foreign key references TypeName(TypeNo),SchoolName char(20) not null foreign key references School(SchoolName),ClassName char(20) not null,ClassTeacher char(12) not null,Constrai

8、nt PK_EDU PRIMARY KEY (Sno,TypeNo,SchoolName)創(chuàng)建信息查詢視圖create view Informationview(學(xué)號,姓名,性別,出生日期,QQ號碼,學(xué)校名稱,學(xué)校所在省份,學(xué)校所在市區(qū),學(xué)校所在縣,學(xué)校所在鄉(xiāng)鎮(zhèn),學(xué)校類型,學(xué)校性質(zhì),班級名稱,班主任)asselect Student.Sno,Sname,Ssex,Sbirth,QQ,School.SchoolName,Province,City,Country,Town,SchType,SchNature,ClassName,ClassTeacherfrom Student,School,Ty

9、peName,Educationwhere Student.Sno=Education.Sno and School.SchoolName=Education.SchoolName and TypeName.TypeNo=Education.TypeNo 4.1.2建立視圖create view Studentview(學(xué)號,姓名,性別,出生日期,QQ號碼)asselect Sno,Sname,Ssex,Sbirth,QQfrom Studentcreate view Schoolview(學(xué)校名稱,學(xué)校所在省份,學(xué)校所在市區(qū),學(xué)校所在縣,學(xué)校所在鄉(xiāng)鎮(zhèn))asselect SchoolName,

10、Province,City,Country,Townfrom Schoolcreate view TypeNameview(學(xué)校類型號,學(xué)校類型,學(xué)校性質(zhì))asselect TypeNo,SchType,SchNaturefrom TypeNamecreate view Educationview(學(xué)號,類型號,學(xué)校名稱,班級名稱,班主任)asselect Sno,TypeNo,SchoolName,ClassName,ClassTeacherfrom Education4.1.3存儲過程根據(jù)數(shù)據(jù)庫的設(shè)計(jì)需要，共需要6個存儲過程存儲過程名字作用Student _Insert向Student信息

11、表中插入一元組School_Insert向School信息表插入一元組Education_Insert向Education信息表插入一元組Student_Update更新Student信息表Stu_Delete刪除學(xué)生信息一元組Edu_Delete刪除教育信息- 向?qū)W生信息表插入一元組create procedure Student_InsertSno varchar(20),Sname char(12),Ssex char(2),Sbirth date,QQ char(20)ASinsert into Studentvalues(Sno,Sname,Ssex,Sbirth,QQ);-向?qū)W校信

12、息表插入一元組create procedure School_InsertSchoolName char(20),Province char(10),City char(20),Country char(20),Town char(20)ASinsert into Schoolvalues(SchoolName,Province,City,Country,Town);-向教育信息表插入一元組create procedure Education_InsertSno varchar(20),TypeNo char(3),SchoolName char(20),ClassName char(20),

13、ClassTeacher char(12)ASinsert into Educationvalues(Sno,TypeNo,SchoolName,ClassName,ClassTeacher)-在學(xué)生信息表中更新一元組CREATE PROCEDURE Student_UpdateSno varchar(20),Sname char(12),QQ char(20)ASupdate Studentset QQ=QQwhere Sno=Sno and Sname=Sname-刪除Student表一元組CREATE procedure Stu_DeleteSno varchar(20),Sname char(12)ASdelete from Studentwhere Studen