教学课件·数据库应用项目化教程(第二版)

1、数据库应用项目化教程项目一校园论坛管理数据库1基本概念2数据库体系结构 数据(Data)：描述客观事物特征和特性的符号描述学生:学号- G13120026姓名- 王小丽电话- 1377456789年龄- 10照片- 数据库(DataBase)：以一定组织方式存储在一起的、统一管理的相关数据的集合学生DB描述学生:学号- G13120026姓名- 王小丽电话- 1377456789年龄- 10照片-教师辅导员教务领导问题一：数据库技术的根本目标是解决数据的- A 存储问题 B 共享问题 C 安全问题 D 保护问题 数据库管理系统( DBMS )：对数据库进行管理的软件数据库管理系统（DBMS）数

2、据库（DB） 数据库系统( DBS )：应用程序DBMS数据库DB应用程序应用程序DBA问题二：数据库，数据库系统，数据库管理系统之间的关系是 A DB包含DBS和DBMS B DBMS包含DB和DBS C DBS包含DB和DBMS2.数据库体系结构 外部体系结构 单机结构 C/S结构 B/S结构 外部体系结构： 单机结构 C/S结构 B/S结构ClientDB ServerClientClientC/S结构Server腾讯DBDBMS你有强迫症吗？思考C/S式的优缺点 外部体系结构： 单机结构 C/S结构 B/S结构BrowserDB ServerWeb ServerBrowserBrows

3、erB/S结构问题三：请说明以下各属于何种体系结构？项目二.学生成绩管理数据库项目二 学生成绩管理数据库1数据库结构设计2SQL Server 数据库结构3视图4项目实践数据库结构设计 数据库设计步骤：现实世界机器世界 E-R图数据表概念模型逻辑模型数据库结构设计 需求分析： 需求分析 概念结构设计 逻辑结构设计 物理结构设计 DB实施阶段 DB运行，维护数据库结构设计 概念模型设计：职工A 职工号B 姓名C 性别实体属性数据库结构设计 概念模型设计：学生学号姓名年龄性别学生 学号姓名年龄性别课程课程号课程名教师课程课程号课程名教师数据库结构设计 概念模型设计：学生学习课程课程学生选修课程名课

4、程号教师姓名性别年龄mn学号成绩成绩成绩 一对一 记为 1:1 一对多 记为 1:M 多对多 记为 M:N 数据库结构设计联系：分析联系,画出E-R图： 学校的班级有班主任，人数，专业。班长有学号和姓名。一个班级只有一个班长,班长只能负责本班的工作。 工厂里厂长需要姓名，年龄，工龄，职工除了这些数据还需要了解岗位，职务。一个厂长领导很多职工,职工只能在一个工厂里工作，听从于厂长。分析联系,画出E-R图： 教师一般有工号，姓名，年龄，职称，课程信息有课程号，课程名，学分和课时。一位教师可以教授多门课,一门课可以由多位教师教授。 每一个研究室包含多位科研人员,每一位科研人员只属于一个研究室.研究室

5、承当了多个科研项目，包括负责人，项目号项目名和开工时间,多个研究室可以合作完成一个项目.每个科研项目有多位科研人员参加,每位科研人员可以参加多个科研项目.科研人员参加科研项目均有相应的工作量。分析联系,画出E-R图： 校长领导学校，学校可以招收学生。校长有职工号、姓名和性别，学校有学校代码、校名和地址，学生有学号、姓名和性别。一个校长只能在一个学校任职，一个学校也只能有一位校长。一个学校可以招收多名学生，一个学生只能报考一个学校。 数据库结构设计 逻辑结构设计： 属性 主键关系中的每一个属性值都是不可分解的。关系中不允许出现相同的元组。关系中不考虑元组之间的顺序。 关系 元组 S (Sno,S

6、name,Sex,Age,Dept)关系名(主键,属性2,属性3.) E-R图 关系模式12系聘用教师1m系编号系名称姓名性别工资教工编号旅客飞机票拥有11身份证号姓名日期航班号座位号价格 系 （系编号，系名称）教师（教工编号，姓名，性别，工资，系编号）系聘用教师1m系编号系名称姓名性别工资教工编号例1旅客（身份证号， 日期，姓名，航班号，座位号）飞机票（航班号，座位号，日期，价格）旅客飞机票拥有11身份证号姓名日期航班号座位号价格旅客（身份证号，姓名）飞机票（航班号，座位号，日期，价格，身份证号）例2课程（课程号，课程名，教师）学生（学号，姓名，性别，年龄）选修（学号，课程号，成绩）选修课程

7、学生mn课程号课程名 教师学号姓名性别年龄成绩例31:M1：1M：N1 方主键出现在M方成为外键任意一方主键出现在另一方中，成外键联系建为一新表，其主键由两个实体的主键复合组成 每个实体建一张表，属性转为表属性，关键属性为主键商店（商号，店名，地址）顾客（编号，姓名，电话）消费（编号，商号，日期，金额）nm消费商号店名地址日期电话编号姓名金额顾客商店商店（商店编号，商店名，地址，电话）职工（职工编号，姓名，性别，年龄，商店编号，进店日期，月薪）商店职工m1工作商店编号商店名地址电话进店日期职工编号姓名性别年龄月薪例4车间（车间号，车间名，电话，计划量,产品号 ）产品（产品号，规格）或车间（车间

8、号，车间名，电话）产品（产品号，规格，计划量,车间号 ）车间产品生产11车间号车间名电话计划量产品号规格储户存款取款存款单取款单储户（账号，身份证号 ）存款单（账号，存款单号，存款日期，金额，存款方式）取款单（账号，取款单号，取款日期，金额，取款方式）11m1存款日期金额存款方式取款日期金额取款方式存款单号取款单号帐号身份证号例5读者借阅图书出版出版社mnm1借证号姓名单位借书日期还书日期数量书名书号单价出版社名地址邮编电话供应号供应商开户行帐号供应商进货商品商品号商品名售货单价单价数量日期销售日期数量员工客户编号姓名权限客户编号姓名 邮编电话1nmnp病房科室病人医生入住从属组成诊治1mmn

9、1mm1科名工作证号病历号病房号处方课后作业床位号地址电话姓名电话姓名职称2.2 项目实践-学生成绩管理数据库列名数据类型允许空学号nchar(7)NO姓名varchar(20)NO性别char(2)YES年龄intYES系varchar(20)NO学号姓名性别年龄系4123001李庆男18计算机4123002张涛男19计算机4124001孙天昊男20电子4125001计优然女19经济管理4125002罗一恒男20经济管理列名数据类型允许空课程号intNO课程名vachar(20)YES教师vachar(10)YES课程号课程名教师1001数据库张兰1002大学英语李强1003计算机程序设计王

10、刚1004大学体育赵兰兰学生表： 课程表: 成绩表: 列名数据类型允许为空学 号nchar(7)NO课程号intNO成绩intYES 学号 课程号 成绩4123001 1001 704123001 1003 784123002 1004 854125002 1001 804125002 1004 91任务2-4 新生注册 学号姓名性别年龄系4123001李庆男18计算机4123002张涛男19计算机4124001孙天昊男20电子4125001计优然女19经济管理4125002罗一恒男20经济管理4152003 蔡青 女 21 信息管理任务2-5 课程变更 课程号课程名教师1001数据库张兰10

11、02大学英语李强1003计算机程序设计王刚1004大学体育赵兰兰 数据库应用实务任务2-6 学生退学 学号姓名性别年龄系4123001李庆男18计算机4123002张涛男19计算机4124001孙天昊男20电子4125001计优然女19经济管理4125002罗一恒男20经济管理4152003 蔡青 女 21 信息管理融会贯通项目三电子相册管理系统数据库3.1.1 关系运算 投影 选择 积 条件联接 自然联接 F例1： A B C A B Ca b c b g a d a f d a fc b d R S A Ca cd fc d 1,3(R) C Ac af dd c A B C a b c

12、c b d2=b (R) 、A,C(R) ：、C,A(R) :1、B=b (R) :例2： A B C A B C a b c b g a d a f d a f c b d R S RS:R.A R.B R.C S.A S.B S.C a b c b g a a b c d a f d a f b g a d a f d a f c b d b g a c b d d a f A B C A B C 3 6 7 3 4 52 5 7 7 3 37 3 34 4 3 R S 1、 3,2(S) 、B5(R) 、RS思考：23(R)和23(R)?4、s.A(R.C=S.B (R S)例A B C

13、 D E1 2 3 3 14 5 6 6 27 8 9 R S RS A B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 3 1 4 5 6 6 2 7 8 9 3 1 7 8 9 6 224( RS) A B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2R S 即 RS 21 BDA B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2条件连接求：24( RS) R SA B C D E1 2 3 3 14 5 6 6 27 8 9 R S21 1 2 A B C D E 1 2 3 3 1 4 5 6 6 2 A B C

14、B C Da b c b c dd b c b c eb b f a d bc a d R S 例4R SA B C Da b c da b c ed b c dd b c ec a d b（）记为R S 自然连接 RSR S21RSR S23(R) A B C1 5 72 8 97 0 27 1 2例3-2 计算RS A B C1 5 72 8 97 0 2A B4 16 5RS例3-3 (a) R S (b) R S 122=2A B C1 5 72 8 97 0 2D E4 16 5RS例3-4 计算RS A B C1 5 72 8 97 0 2RC D8 17 52 47 0S单元测验

15、1、关系数据库中，将具有共同属性的两个关系中的元组连接到一起，构成新表的操作称为A、选择 B、投影 C、连接 D、扫描 2、一般情况下，当对关系R和S使用自然连接时，要求R和S至少含有一个共同的 .A 元组 B 行 C 关系 D 属性 3、如下两个关系R1和R2，它们进行 运算后得到R3A 选择 B 选择连接 C 笛卡儿积 D 自然连接 R1 R2A B CA 1 XC 2 YD 1 YB D E1 M I2 N J5 M K R3A B C D EA 1 X M ID 1 Y M IC 2 Y N J4、设有如图所示的关系R和S，计算RSABCabcbabcfd关系 RABCbdaafc 关

16、系 S5、设有关系R和S如下图所示，计算C，D（RS） R ABCa5Ce8Fa4GSBDE4ac4eg8bAABC359230368BCD302686305R A,C(R) D3课堂作业：User表:项目三电子相册管理系统数据库class表:photo表:cp表: 任务1. T-SQL创建数据库Create Database 数据库名ON ( Name=数据文件名, Filename=“文件路径名”, Size=大小, Maxsize=大小, Filegrowth=大小, )Log ON( 同上) 要求是数据库名为mypic，指定数据文件位于D:pmDb文件夹中。初始容量为5MB，最大容量为

17、10MB，文件增量为10%。T-SQL“文件路径名”格式：盘符文件夹.数据库名.mdfcreate database mypicon( name=mypic, filename=”d:pmDbmypic.mdf”, size=5, maxsize=10, filegrowth=10% )任务2.T-SQL创建 cp表结构?主键?数据?主键、外键任务2.T-SQL创建Create Table 表名 ( 列名1 数据类型 列级约束， 列名2 数据类型 列级约束， primary key(主键)， Foreign key(外键) references 表(主键)） USE mypicCREATE T

18、ABLE cp(CID int,PID int,ptime date not null,PRIMARY KEY(CID,PID),FOREIGN KEY(CID) references class(CID),FOREIGN KEY(PID) references photo(PID) )练习时间 创建保存网站用户的表：user表列名数据类型允许为空账号VarChar(10)NO密码Varchar(10)NO任务3.T-SQL完成cp表数据的添加Insert into 表名(属性列表)Values (元组值)INSERT INTO cp(CID,PID,ptime)VALUES (1,1001,

19、2017-6-1)CIDPIDptime 1 10012017-6-1 2 10012017-6-1 110022017-68-21210042017-810-11410062015-10-11为user表完成两个用户注册帐号密码aaa123bbb456列名数据类型允许为空账号VarChar(10)NO密码Varchar(10)NO练习时间SQL生活中应用之微博：微博账号注册个人资料修改账号登陆T-SQL查询语句Select 属性From 表Where 条件T-SQL简单查询(1) 查询男性用户的UID，姓名，性别和qq号。命 令:selec

20、t UID,username,sex,qqfrom userswhere sex=男T-SQL简单查询(2) 查询101用户所建相册的相册名和创建时间。命 令select cname,ctimefrom classwhere UID=101任务7 T-SQL模糊查询(1)查询使用qq邮箱注册的用户信息。命 令:select *from userswhere email like %任务7 T-SQL模糊查询(2)查询用户名为s或f开头的用户的信息。命 令:select *from userswhere username like sf%任务7 T-SQL模糊查询(3)查询创建日期在5月份的相册

21、信息。命 令:select *from classwhere ctime like _05%任务8 T-SQL多表查询连接查询(1)查询用户simon所创建的相册ID，相册名称和相册描述。命 令:select CID,cname,c_descfrom users,classwhere username=simonand users.UID=class.UID任务8 T-SQL多表查询连接查询(2) 查询2号相册的所有照片的ID，标题和存放地址。要求显示结果按照照片的ID从大到小排序。命 令:select photo.PID,title,picfrom cp,photowhere CID=2an

22、d cp.PID=photo.PIDorder by PID desc任务8 T-SQL多表查询连接查询(3) 查询相册名为“我的学校”，它的所有照片的ID,标题，描述和照片时间。按照照片时间排序，如果同一时间，按PID降序排列。命 令:select cp.PID,title,p_desc,ptimefrom photo,class,cpwhere cname=我的学校and photo.PID=cp.PIDand class.CID=cp.CIDorder by ptime,PID desc任务9 T-SQL多表查询子查询(1) 查询用户mike所创建的相册名，相册描述。命 令:select

23、 cname,c_descfrom classwhere UID in ( select UID from users where username=mike )任务9 T-SQL多表查询子查询(2) 查询还没有创建相册的用户ID,用户名。命 令:select UID,usernamefrom userswhere UID not in( select distinct UID from class)任务10 T-SQL统计查询(1)统计103号用户所创建的相册数量。命 令:select count(*) from class where UID=101任务10 T-SQL统计查询(2)统计每

24、个用户的创建相册数量，要求显示用户的ID和对应的房屋数目。命 令:select UID as 用户ID,count(*) as 相册数from classgroup by UID任务10 T-SQL统计查询(3) 统计照片数大于等于3张的相册，要求显示照片ID和标题。命 令:select cp.CID,count(*)from cpgroup by CIDhaving count(*)=3项目四.商品进销存管理数据库数据完整性数据完整性主要分为实体完整性域完整性参照完整性用户定义完整性约束SQL Server中提供了6中类型的约束：NOT NULL（非空）约束PRIMARY KEY （主键）约

25、束FOREIGN KEY（外键）约束UNIQUE（唯一）约束CHECK（检查）约束DEFAULT（默认值）约束存储过程1 存储过程的创建CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 存储过程2 存储过程的修改ALTER PROC EDURE procedure_name ; numbe

26、r parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 4.2.4触发器在SQL Server 2012中触发器分为DML和DDL触发器两种，分别在数据库中发生数据操作（INSERT、UPDATE和DLETE）和数据定义（CREATE、ALTER、DROP）事件时执行。触发器的创建CREATE TRIGGER trigger_nameONtable|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFDELETE,INSERT,UPDATE ASSQL语句n2触发器的修改ALT