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

1、通信錄項(xiàng)目開發(fā)文檔目 錄1需求分析21.1應(yīng)用需求2用戶需求分析2系統(tǒng)存儲(chǔ)需求21.2功能描述21.3數(shù)據(jù)流圖和數(shù)據(jù)字典2數(shù)據(jù)流圖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)系模式5關(guān)系模式的優(yōu)化5數(shù)據(jù)庫模式和轉(zhuǎn)換原則6用戶子模式定義63.2施加約束64.數(shù)據(jù)庫建立64.1表和視圖的創(chuàng)建7建立數(shù)據(jù)表7建立視圖8存儲(chǔ)過程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 類型信息

2、表（TypeName）列名數(shù)據(jù)類型可否為空說明TypeNochar(3)NOT NULL學(xué)校類型號(hào)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é)校所在市CountryChar(20)學(xué)校所在縣TownChar(20)學(xué)校所在鄉(xiāng)鎮(zhèn)表3-1-3-4 教育信息表（Education）列名數(shù)據(jù)類型可否為空說明SnoChar(20)NOT NULL

3、學(xué)生學(xué)號(hào)TypeNoChar(3)NOT NULL學(xué)校類型號(hào)SchoolNameChar(20)NOT NULL學(xué)校名稱ClassNameChar(20)NOT NULL班級(jí)名稱ClassTeacherChar(12)班主任用戶子模式定義3.2施加約束在學(xué)生信息表中（Student）學(xué)生性別必須是男或女，學(xué)生出生日期格式為yyyy-mm-dd；在學(xué)校類型信息表中（TypeName），學(xué)校類型輸入必須從以下選擇：H/M/C/U(H:高中/M：初中/C：大專/U：大學(xué))，學(xué)校性質(zhì)必須從以下選擇：PUB/PRI(PUB：公辦型/PRI：民辦型)4.數(shù)據(jù)庫建立CREATE DATABASE Infor

4、mation-指定數(shù)據(jù)庫名字為InformationON PRIMARY-定義在主文件組上的文件(NAME=first_data,-指定主文件的邏輯名稱FILENAME='E:SQL SERVERInformationInformation.mdf',-指定主文件的物理名稱SIZE=20MB,-設(shè)置初始大小為MB,MB可省略MAXSIZE=unlimited,-設(shè)置最大限度為無限FILEGROWTH=5%-指定增長速度為%)LOG ON-定義事務(wù)日志文件(NAME=first_log,-定義事務(wù)日志文件的邏輯名稱FILENAME='E:SQL SERVERInforma

5、tionInformation.ldf', -定義事務(wù)日志文件的物理名稱SIZE=1,-設(shè)置初始大小為MBMAXSIZE=100,-設(shè)置最大限度為MBFILEGROWTH=1-指定增長速度為MB)4.1表和視圖的創(chuàng)建建立數(shù)據(jù)表創(chuàng)建Student表CREATE TABLE Student(Sno varchar(20) not null primary key,Ssex char(2) not null,Sname char(12) not null,Sbirth date,QQ char(20)創(chuàng)建School表CREATE TABLE School(SchoolName char(2

6、0) not null primary key,Province char(10),City char(20),Country char(20),Town char(20)創(chuàng)建TypeName表CREATE TABLE TypeName(TypeNo char(3) not null primary key,SchType char(1) not null,SchNature char(3) not null)創(chuàng)建Education表CREATE TABLE Education(Sno varchar(20) not null foreign key references Student(Sn

7、o),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,Constraint PK_EDU PRIMARY KEY (Sno,TypeNo,SchoolName)創(chuàng)建信息查詢視圖create view Informationview(學(xué)號(hào),姓名,性別,出生日期,

8、QQ號(hào)碼,學(xué)校名稱,學(xué)校所在省份,學(xué)校所在市區(qū),學(xué)校所在縣,學(xué)校所在鄉(xiāng)鎮(zhèn),學(xué)校類型,學(xué)校性質(zhì),班級(jí)名稱,班主任)asselect Student.Sno,Sname,Ssex,Sbirth,QQ,School.SchoolName,Province,City,Country,Town,SchType,SchNature,ClassName,ClassTeacherfrom Student,School,TypeName,Educationwhere Student.Sno=Education.Sno and School.SchoolName=Education.SchoolName and

9、TypeName.TypeNo=Education.TypeNo 建立視圖create view Studentview(學(xué)號(hào),姓名,性別,出生日期,QQ號(hào)碼)asselect Sno,Sname,Ssex,Sbirth,QQfrom Studentcreate view Schoolview(學(xué)校名稱,學(xué)校所在省份,學(xué)校所在市區(qū),學(xué)校所在縣,學(xué)校所在鄉(xiāng)鎮(zhèn))asselect SchoolName,Province,City,Country,Townfrom Schoolcreate view TypeNameview(學(xué)校類型號(hào),學(xué)校類型,學(xué)校性質(zhì))asselect TypeNo,SchTyp

10、e,SchNaturefrom TypeNamecreate view Educationview(學(xué)號(hào),類型號(hào),學(xué)校名稱,班級(jí)名稱,班主任)asselect Sno,TypeNo,SchoolName,ClassName,ClassTeacherfrom Education存儲(chǔ)過程根據(jù)數(shù)據(jù)庫的設(shè)計(jì)需要，共需要6個(gè)存儲(chǔ)過程存儲(chǔ)過程名字作用Student _Insert向Student信息表中插入一元組School_Insert向School信息表插入一元組Education_Insert向Education信息表插入一元組Student_Update更新Student信息表Stu_Delete

11、刪除學(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校信息表插入一元組create procedure School_InsertSchoolName char(20),Province char(10),City char(20),Country char(20)

12、,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),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 Student.Sno