软件开发技术基础 第4章　数据库及应用程序开发.ppt

1、第4章 数据库及应用程序开发,现实世界：事物的客观存在，由事物及其性质反映。 信息世界：是对现实世界的抽象，事物在信息世界中称为实体。实体是由属性来描述的。反映事物及其之间联系的模型称为（实体）概念模型。 数据世界：信息的数据化。用记录和数据项分别描述信息世界中的实体及属性。实体模型数据化后数据模型。,4.1 数据库技术基础,4.1.1 数据模型 1、概念模型 （1）基本概念： 实体（Entity）客观存在并可互相区别的事物称为实体。 属性(Attribute)：实体具有的特性称为属性。 属性组合起来表征了一个学生。,码（Key）：唯一标识实体的属性或属性组。 域(Domain)：属性的取值范

2、围。,实体型（Entity Type）：具有相同属性的实体所具有的共同特征和性质。用实体名及属性名集合来抽象。例如，学生（学号、姓名、性别、出生年月、系、入学时间） 实体集（Entity Set）同种实体的集合。全体学生就是一个实体集。,联系（Relationship）,实体之间存在着联系。实体之间的联系分为三类： （1）一对一联系（1:1）：实体集A中的每一个实体最多与实体集B中的一个实体，反之亦然。,（2）一对多联系(1:n),实体集A中的每一个实体与实体集B中的n个实体(n=0)联系，而实体集B中的每一个实体与实体集A最多只有一个实体联系。如班级集和学生集是一对多联系。,多对多联系(m:

3、n),实体集A中的每一个实体与实体集B中的n个实体(n=0)联系，而实体集B中的每一个实体与实体集A中的m个(m=0)实体联系。如课程和学生之间的联系。,（2） 概念模型的表示方法,实体联系模型（Entity-Relation） 实体：矩形表示 属性：用椭圆表示 联系：用菱形表示,2、数据模型,关系模型：用关系（表）来描述实体及实体之间的联系。 关系：表 元组：表中的一行 属性：表的一列 主码：唯一确定元组的属性或属性组。 关系的描述方式： 关系名（属性1，属性2， ，属性n） 学生（学号、姓名、性别、出生年月、系、入学时间）,如学生课程、学生与课程之间的多对多联系： 学生（学号、姓名、性别、

4、年龄） 课程（课程号，课程名，学分） 选课（学号，课程号，成绩） 特点： （1）建立在严格的数学基础上。 （2）简单：结构简单、清晰易用。 （3）存取路径透明。 缺点：查询效率不高。,4.1.2 规范化理论,1 问题的提出 关系： S（no,name,department,dean,course, grade） 选课关系（学号，姓名，所在系，系主任，课程，成绩）存在以下问题： （1）插入异常：如果一个系刚成立，还没有学生，无法将该系及其负责人的信息存入数据库。,（2）删除异常：如果一个系的所有学生都毕业了，在删除所有学生选课记录的时候，该系及其负责人的信息也丢失了。 （3）冗余大：多少学生选课

5、，该系及其负责人的信息就要出现多少次。造成存储空间的浪费，容易引起数据的不一致性。 原因：数据之间的依赖。,2 函数依赖,（1） 函数依赖定义 设R（U）是一个关系模式，X和Y是U的两个子集，如果R中的任意两个元组，X上属性值相同时，Y上属性值也必然相同，称Y函数依赖于X，或X函数决定Y。记为XY。,如果对X的任何真子集X1，X1Y不成立， 称Y完全函数依赖于X， 记为X1Y;否则称Y部分函数依赖于X，记为X1Y 。,f,p,（2）传递函数依赖,设R（U）是一个关系模式，X、Y和Z是U的子集，若XY，YZ，且X不函数依赖于Y，称Z传递函数依赖于X。,3、关系的规范化,关系要满足一定的要求，根据

6、满足程度的不同，分为不同的范式（Normal Form）。 （1） 第一范式1NF 满足最低要求的称为1NF。所有属性都是基本数据项。例如：,将一个元组拆开为多个元组。,1NF存在数据冗余、插入、删除异常现象。,（2）第二范式2NF,如果R是1NF，并且每个非主属性都完全函数依赖于关键字，则R是2NF。 因为：（no, course）是关键字。（no, course）部分决定dean和department。拆分为 N_C(no,course,grade)和 N_D (no,department, dean),在N_C中，（no, course）是主关键字。（no, course）完全决定gra

7、de。 N_C,在N_D中，no是主关键字，no完全决定department 和dean。 N_D:,（3）第三范式3NF,如果R是2NF，并且每个非主属性都不传递函数依赖于关键字，则R是3NF。 N_C (no,course,grade)是3NF，但N_D (no,department, dean)不是3NF，因为department函数依赖于no, dean函数依赖于department，所以dean传递函数依赖于no。 将N_D(no,department, dean)拆分为N_D_1(no,department)和D_D(department, dean),N_D1,D_D,（4）其他

8、范式,BCNF、4NF和5NF。 关系：5NF属于4NF，4NF属于BCNF，BCNF属于3NF，3NF属于2NF，2NF属于1NF。 一般到达3NF就可以了。,4.1.3关系数据库标准语言SQL,SQL（ Structured Query Language) 1 建立表 CREATE TABLE table_name (column_name data-type consraint, ),建立表,CREATE TABLE employee (eid CHAR(6), name VARCHAR(8) NOT NULL, age NUMERIC(3,0), country VARCHAR(10)

9、 NULL),建立表,CREATE TABLE employee (eid CHAR(6) NOT NULL PRIMARY KEY, name VARCHAR(8) NOT NULL), age NUMERIC(3,0), country VARCHAR(10) NULL ),2 插入记录,INSERT INTO 表名 列名表） VALUES （值表） INSERT INTO employee (eid, name, age, country) VALUES (3018, zhang, 28, China),INSERT INTO employee VALUES (3056, wang, 3

10、5, USA) INSERT INTO employee (eid, name, country) VALUES (4025, li, Russia),二、 插入记录,3、修改记录,UPDATE 表名 SET 字段名=表达式 WHERE 条件 UPDATE employee SET name=Li Ming WHERE eid=4025,UPDATE employee SET name=Zhang Li, age=36, country=USA WHERE eid=3056 可以修改多个字段的值。如果不带WHERE修改所有的记录。,4、删除记录,DELETE 表名 WHERE 条件 DELET

11、E employee WHERE eid=3018,5、 查询记录,SELECT 表达式 FROM 表名表 WHERE 条件 GROUP BY 字段名表 HAVING 条件 ORDER BY 字段名表,（1） 举例,SELECT pub_id, pub_name, country FROM publishers SELECT * FROM publishers,（2） 删除重复的行DISTICT,SELECT city, state FROM authers SELECT DISTICT city, state FROM authers,（3） 计算表达式,SELECT title_id, t

12、ype, price, price_price*0.3 FROM title SELECT au_lname+.+au_fname, city+.+state FROM authors,（4）筛选条件,（a）条件： = = != price45.3 country=China NOT AND OR Country=China AND age35,（b）举例,SELECT title_id, type, price, price - price*0.3 FROM titles WHERE price - price*0.318 SELECT pub_id, pub_name FROM publi

13、shers WHERE country=USA AND state=CA,(c) LIKE,列名 NOT LIKE 模式 %: 任意字符串, _:任意一个字符 SELECT au_lname+.+au_fname, city+.+state FROM authors WHERE au_fname LIKE D%,SELECT au_lname+.+au_fname, city+.+state FROM authors WHERE au_id LIKE 72_-%,(5) 结果排序,ORDER BY 字段表 通常返回的结果是随机排列的。 SELECT stor_name, city FROM s

14、tores ORDER BY store_name,SELECT stor_name, city FROM stores ORDER BY store_name DESC SELECT stor_name, city FROM stores ORDER BY store_name ASC,SELECT stor_name, city FROM stores ORDER BY stor_name， city 用多个列排列。 SELECT stor_name, city FROM stores ORDER BY state 用不出现的列。,SUM（expression） AVG（ expressi

15、on ） MIN（ expression ） MAX（ expression ） COUNT（ expression ） COUNT（*）,(6) 统计函数,SELECT AVG(price) FROM titles SELECT MAX(price) FROM titles WHERE type=business,SELECT AVG(qty), SUM(qty), COUNT(DISTICT stor_id) FROM sales SELECT COUNT（*） FROM publishers WHERE state=CA,(7) 结果分组,GROUP BY 分组列名表 SELECT ty

16、pe, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (business, mod_cook, trad_cook) GROUP BY type,SELECT type, pub_id, AVG(price), SUM(price), COUNT(*) FROM titles WHERE type in (business, mod_cook, trad_cook) GROUP BY type， pub_id GO,GROUP BY 分组列名表 HAVING 条件 对GROUP的分组结果再进一步筛选。 SELECT typ

17、e, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type,使用HAVING,SELECT type, AVG(price), SUM(price), COUNT(*) FROM titles WHERE price$10 GROUP BY type HAVING AVG(price)$20,使用HAVING,(8) 多表查询,（a）笛卡尔乘积 SELECT title_id, au_fname FROM titles, authors SELECT titles.title_id, titles.t

18、itle, titleauthor.au_id FROM titles, titleauthor,（b ）别名,SELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta SELECT t.title_id, t.title, ta.au_id FROM titles t, titleauthor ta WHERE t.title_id=ta.title_id,（c）筛选,SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t

19、.title_id=ta.title_id and ta.au_id=a.au_id,6 视图,(1）视图的定义 SQL建立视图的语句格式为： CREATE VIEW (，) AS ,CREATE VIEW au_view AS SELECT au_id, au_fname, au_lname FROM authors CREATE VIEW author_title AS SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id and ta.au_id=a

20、.au_id,(2)视图查询,SELECT * FROM au_view SELECT t.title_id, a.au_id FROM titles t, titleauthor ta, authors a WHERE t.title_id=ta.title_id and ta.au_id=a.au_id,（3）删除视图,DROP VIEW 视图名 DROP VIEW a_authors,7、索引,（1）索引类型 按照存储位置：聚集索引和非聚集索引。 按照关键字值是否唯一：唯一索引和非唯一所以。,（2）建立索引,CREATE UNIQUE CLUSTERED | NOCLUSTERED IN

21、DEX 索引名 ON 表名 （列名表） CREATE UNIQUE INDEX id_index ON authors (au_id),CREATE INDEX id_index ON authors ( au_fname DESC,au_id) CREATE CLUSTERED INDEX id_index ON authors ( au_fname DESC,au_id),（3）删除索引,DROP INDEX 表名.索引名 DROP INDEX authors.id_index,8、数据库安全性,（1）添加数据库用户 SP_GRANTDBACCESS 有登录权的帐户名 SP_GRANTDB

22、ACCESS lixin （2）删除数据库用户 SP_REVOKEDBACCESS 帐户名 SP_REVOKEDBACCESS lili,4.2 数据库设计,需求分析 概念设计 逻辑结构设计 物理结构设计 数据库实施阶段,4.2.1需求分析与概念设计,1需求分析 详细调查要处理的对象 了解原系统工作概况 确定新系统的功能 考虑今后可能的扩充和改变,信息要求 要从数据库中获得信息的内容与性质 处理要求 要完成什么处理功能，对处理的响应时间有什么要求。 安全性与完整性要求,2概念设计,将现实世界抽象为具有属性的实体及及联系。画出一张ER图，就得到了一个对系统信息的初步描述，进而形成数据库的概念模型

23、。,4.2.2逻辑结构设计,将概念结构转化为一般的关系模型。 将转化来的关系模型向特定DBMS支持下的数据模型转换。 对数据模型进行优化,1数据库逻辑模型的产生,（1）一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。,（2）一个1:l联系 一个独立的关系模式：各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码。 与一端的关系模式合并：在该关系模式的属性中加入另一个关系模式的码和联系本身的属性 。,方案1 职工（职工号，姓名，年龄） 产品（产品号，产品名，价格） 负责（职工号，产品号）,方案2 职工（职工号，姓名，年龄，产品号） 产品（产

24、品号，产品名，价格） 方案3 职工（职工号，姓名，年龄） 产品（产品号，产品名，价格，职工号） 方案3比较合理,方案1: 1:n联系形成的关系独立存在。 仓库（仓库号，地点，面积） 产品（产品号，产品名，价格） 仓储（仓库号，产品号，数量）,方案2: 联系形成的关系与n端对象合并 仓库（仓库号，地点，面积） 产品（产品号，产品名，价格，仓库号，数量） 方案2较优,(3)一个1：n联系,一个独立的关系模式： 与该联系相连的各实体的码以及联系本身的属性均换为关系的属性，而关系的码为n端实体的码 与n端对应的关系模式合并： 联系本身的属性均换为关系的属性，再加1端实体的码,学生（学号，姓名，年龄，性

25、别）； 课程（课程号，课程名，学时数）； 选修（学号，课程号，成绩）。,（4）m：n联系,一个m：n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性。而关系的码为各实体码的组合。,供应商（供应商号，供应商名，地址） 零件（零件号，零件名，单价） 产品（产品号，产品名，型号） 供应（供应商号，零件号，产品号，数量）,(5)三个及以上实体间联系,三个或三个以上实体间的一个多元联系转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性。而关系的码为各实体码的组合。,方案1：转换为两个关系模式。 职工（职工号，姓名，年龄） 领导（领导工

26、号，职工号） 方案2:转换为一个关系模式。 职工（职工号，姓名，年龄，领导工号）,(6)同一实体集联系，,按上述1：1，1:n和m：n三种情况分别处理。,零件（零件号，名称，价格） 组装（组装件号，零件号，数量）,2.数据模型的优化,以规范化理论为指导 （1）确定数据依赖。按需求分析，写出各属性之间的数据依赖。 （2）考查是否存在部分函数依赖、传递函数依赖等，确定各关系模式分别属于第几范式。 （3）按照需求分析对数据处理的要求，确定是否需要对它们进行合并或分解。,3设计用户模式,利用视图功能设计更符合局部用户需要的用户外模式。 （1）使用更符合用户习惯的别名 （2）针对不同级别的用户定义不同的

27、外模式，以满足系统对安全性的要求。,4.2.3数据库物理设计及实施,物理设备上的存储结构与存取方法称为数据库的物理结构 1确定数据的存储结构 综合考虑存取时间、存储空间利用率和维护代价3方面的因素。 2设计数据的存取路径 在关系数据库中，选择存取路径主要是指确定如何建立索引。,（1）聚簇索引,聚簇键相同的元组自然地被放在同一个物理页中，如果元组过多，一个物理页放不下，则被链接到多个物理页中 。,（2）非聚簇索引,索引页上的顺序与物理数据页上的顺序一般不一致。,建立索引原则,一个（组）属性经常在操作条件中出现。 一个（组）属性经常作为聚集函数的参数。 一个（组）属性经常在连接操作的连接条件中出现

28、。,建立聚簇索引原则,检索数据时，常以某个（组）属性作为排序、分组条件。 检索数据时，常以某个（组）属性作为检索限制条件，并返回大量数据。 表中某个（组）的值重复性较大。,3确定数据的存放位置,数据库数据备份、日志文件备份等，可以考虑存放在磁带上。 可以考虑将表和索引分别放在不同的磁盘上。在查询时，由于两个磁盘驱动器分别在工作，因而可以保证物理读写速度比较快。,4确定系统配置,同时使用数据库的用户数 同时打开的数据库对象数 使用的缓冲区长度、个数 时间片大小 装填因子,5数据库的实施,（1）定义数据库结构 （2）数据装载,例子建立图书馆,需求分析 查询图书：通过书名和类别查询库中的图书，其中书

29、名为模糊查询。 借书处理：在查询的基础上完成借书登记处理。借书时需要输人书号和读者编号，修改图书表记录和增加借阅表的记录。 还书处理：实现还书处理操作。还书时需要先修改图书记录，改变其借出否标志，再删除相关的借阅记录。,数据库设计,概念模型 读者,图书,借书,数据库逻辑模型,读者（编号，姓名，单位，性别，电话）,图书（书号，类别，出版社，作者，书名，定价，借出否）,借阅（书号，读者编号，借阅日期）,规范化,图书：书号类别，书号出版社，书号作者，书号书名，书号定价，书号借出否； 读者：编号姓名，编号单位，编号性别，编号电话； 借阅：（书号，读者编号）借阅日期； 所有非主属性对码完全并直接依赖。各

30、表均为第三范式。,完整性约束,主码约束： 在“图书”表中，“书号”为主码； 在“读者”表中，“编号”为主码； 在“借阅”表中，“书号”和“读者编号”为主码。 这些主码的属性值具有惟一性和非空性。,借阅表和图书表间的外码约束：,借阅中书号为外码，参照表为图书，参照属性为书号。 在借阅关系中插入元组（借阅图书）时，仅当图书表中有相应书号时，系统才执行插人操作，否则拒绝此操作。,借阅表和读者表间的外码约束,借阅中读者编号为外码，参照表为读者，参照属性为编号。 在借阅关系中插入元组（借阅图书）时，仅当读者表中有相应读者编号时，系统才执行插人操作，否则拒绝此操作。,关系的属性设计,关系属性的设计包括属性

31、名、数据类型、数据长度、该属性是否允许空值、是否为主码、是否为索引项及约束条件。,读者表,图书表,借阅表,4.3 数据库编程,4.3.1 数据库连接技术 1ODBC 微软公司ODBC(Open Database Connectivity，开放数据库互连),，建立了一组规范，并提供了一组对数据库访问的标准API（应用程序编程接口）。基于ODBC的应用程序对数据库的操作不依赖任何DBMS，由对应的DBMS的ODBC驱动程序完成。能以统一的方式处理所有的数据库。,Microsoft推出的一致数据访问技术，为关系型或非关系型数据访问提供了一致的访问接口。ADO提供了高层软件接口，不仅可在高级语言开发环

32、境中使用，还可以在一些脚本语言中使用，这对于WEB程序访问数据库提供了捷径。OLE DB 提供了底层软件接口,2ADO和OLE DB,3JDBC,Java 数据库连接JDBC (Java Database Connectivity)由一组用 Java 编程语言编写的类和接口组成。JDBC 为数据库开发人员提供了一个标准的API。程序可在任何平台上运行 。,4.3.2 利用V C开发应用系统,1MFC ODBC类简介 （1）Cdatabase：建立与数据源的连接 virtual BOOL Open( LPCTSTR lpszDSN, BOOL bExclusive = FALSE, BOOL b

33、ReadOnly = FALSE, LPCTSTR lpszConnect = “ODBC;”, BOOL bUseCursorLib = TRUE ); throw( CDBException, CMemoryException ); CDatabase m_db; m_db.Open(MyDS);,m_db.Open(NULL,FALSE,FALSE,ODBC;DSN=MyDS; UID=ABC;PWD=1234); virtual void Close( );,（2）CRecordset类,从数据源选择的一组记录（记录集）。 virtual BOOL Open( UINT nOpenTy

34、pe = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none );throw( CDBException, CMemoryException );,（3）CRecordView,该类提供了一个表单视图与某个记录集直接相连。 在记录集与表单视图的控件之间传输数据。,（4）CDBException,该类代表ODBC类产生的异常。 对于数据库操作错误，出现CDBException类型的异常，其它类型的错误，出现CMemoryException异常。,2执行SQL语句,void ExecuteSQL(LPCTS

35、TR lpszSQL),3 数据库操作的基本过程,（1）连接数据库 建立CDataBase类对象，通过CDataBase类的Open函数连接数据库。 （2）执行SQL语句 对于要返回结果集的查询操作，可以通过CRecordSet类的Open函数实现，对于不返回结果集的其它SQL语句可以通过CdataBase类的ExecuteSQL函数实现。 （3）断开与数据库的连接 通过CDataBase类的Close函数断开与数据库的连接。,.3.3 编程实例,连接SQL Server 2000数据库company。 #include stdafx.h #include #include #ifdef _D

36、EBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE = _FILE_; #endif,CDatabase database ; CWinApp theApp; using namespace std; int _tmain(int argc, TCHAR* argv, TCHAR* envp) int nRetCode = 0; / 初始化MFC并进行判断 if (!AfxWinInit(:GetModuleHandle(NULL), NULL, :GetCommandLine(), 0) / TODO: chan

37、ge error code to suit your needs cerr _T(Fatal Error: MFC initialization failed) endl; nRetCode = 1; ,else BOOL bStatus = false; /设置等待建立连接的时间 database.SetLoginTimeout(10); try /与数据源LibraryDB建立连接 bStatus=database.Open(NULL,FALSE,FALSE,ODBC;DSN=CompanyDS; ); if(bStatus) cout连接成功！endl; else cout连接失败！en

38、dl; ,catch(CMemoryException *pEx) /异常处理 pEx-ReportError(); catch(CDBException * pDBEx) pDBEx-ReportError(); /输出错误信息 coutm_nRetCode m_strError m_strStateNativeOriginn; /关闭数据库 database.Close(); return nRetCode; ,查询库company中表employee的记录,CDatabase database ; CWinApp theApp; BOOL bStatus = false; /设置等待建立

39、连接的时间 database.SetLoginTimeout(10); try /与数据源LibraryDB建立连接 bStatus=database.Open(NULL,FALSE,FALSE,ODBC;DSN=LibraryDB; ); ,/异常处理 catch(CMemoryException *pEx) pEx-ReportError(); catch(CDBException * pDBEx) pDBEx-ReportError(); /输出错误信息 coutm_nRetCode m_strError m_strStateNativeOriginn; ,CRecordset recs

40、et( else,/输出表中的列名 cout编号 姓名 性别 工资 endl; CString temp; while(!recset.IsEOF() /每循环一次，输出结果集中的一条记录 recset.GetFieldValue(no,temp);cout(LPCTSTR)temp; recset.GetFieldValue(name,temp);cout(LPCTSTR)temp; recset.GetFieldValue(sex,temp);cout(LPCTSTR)temp; recset.GetFieldValue(salary,temp);cout(LPCTSTR)temp; recset.MoveNext(); coutendl; recset.Close(); database.Close(); /关闭数据库,为表employee插入记录。,CDatabase database