数据库系统课件：ch1 Introduction

1、Chapter 1: Introduction数据库系统的地位数据库技术产生于六十年代末，是数据管理的最新技术，是计算机科学的重要分支数据库系统与操作系统一样是计算机系统的基础软件，也称为系统软件、平台软件。它的出现极大地促进了计算机技术在各行各业的应用数据库技术是信息系统的核心和基础，是信息化建设的基础设施数据库的建设规模、数据库信息量的大小和使用频度已成为衡量一个国家信息化程度的重要标志数据库技术发展回顾经历了三代演变层次/网状系统、关系系统、新一代数据库系统家族造就了三位图灵奖 (Turing award)得主C.W.Bachman、E.F.Codd和James Gray发展了一门计算机

2、基础学科以数据模型和DBMS核心技术为主，内容丰富、领域宽广形成了一个巨大的软件产业DBMS及其相关工具产品、应用解决方案三位图灵奖得主E.F.CoddJim GrayC.W.BachmanCharles.William.Bachman 网状数据库之父1960年为通用电气制造了世界上第一个网状数据库系统IDS积极推动与促成了数据库标准的制定：DBTG报告 在数据库技术的产生、发展与推广应用方面都发挥了巨大的作用 由于他在数据库方面的杰出成就1973获图灵奖1983年成立自己的公司：Bachman Information SystemEdgar Frank.Codd ：关系数据库之父/美国工程院

3、院士 原是英国人，1923生于英格兰中部波特兰 第二次世界大战时应征入伍，在皇家空军服役。1942-1945年间任机长，参与了许多惊心动魄的空战。英国牛津大学数学专业理学士及硕士学位，毕业后到IBM公司工作从事操作系统和自动机理论研究年近40重返密歇根大学进修计算机与通信专业，1963年获得硕士学位，1965年又获得博士学位。60年代后期开始数据库研究, 1970年E.F.Codd 博士提出关系模型概念(CACM,Vol.13, Vol.6, 1970 ) ，奠定了关系模型的理论基础。1981年获图灵奖，84年从IBM公司退休还创办了一个研究所：关系研究所（The Relational Ins

4、titute）和一个公司：Codd & Associations，进行关系数据库产品的研发、销售、咨询等业务James （Jim） Gray：数据库技术和事务处理专家1944年生，美国加州大学伯克利分校 计算机科学系博士。先后在贝尔实验室、IBM、Tandem、DEC等公司工作，研究方向转向数据库领域。 由于他在数据库和事务处理研究方面的元创性贡献以及在将研究原型转化为商业产品的系统实现方面的技术领袖地位，1998年获奖(时任微软研究员)2007年1月28日从旧金山港出发，驾驶一艘长40英尺的游艇，前往Farallon岛途中神秘失踪，至今是一个迷。数据库在计算机系统中的位置硬件平台基础软件平台

5、软件基础构架平台应用软件平台软件产品协同软件办公软件 数据库系统 操作系统中间件应用服务器四个基本概念数据(Data)数据库(Database)数据库管理系统(DBMS)数据库系统(DBS)一、数据数据(Data)是数据库中存储的基本对象数据的定义描述事物的符号记录组成数据的符号种类数字、字符串、日期、逻辑值、文本、图形、图象、声音数据的特点数据与其语义是不可分的数据举例学生档案中的学生记录（李明，男，1985，江苏，计算机系，2003）数据的形式不能完全表达其内容数据的解释语义：学生姓名、性别、出生年月、籍贯、所在系别、入学时间解释：李明是个大学生，1985年出生，江苏人，2003年考入计算

6、机系请给出另一个解释和语义二、数据库（举例）学号姓名分数性别省份来源中学政治面貌出生年月专业200530551386张三678男广东省兴宁一中共青团员1986-12-24软件工程(嵌入式技术)200530551160李四671女广东省中山市第一中学共青团员1986-7-14软件工程(嵌入式技术)200530551192王五671男广东省珠海市第二中学共青团员1986-12-8软件工程(嵌入式技术)200530551205黄麻子684男广东省深圳市红岭中学共青团员1986-5-13软件工程(嵌入式技术)200530552117夏小明638男广东省中山纪念中学共青团员1987-12-15软件工程(

7、嵌入式技术)200530552171力玮672男广东省深圳市高级中学共青团员1987-5-17软件工程(嵌入式技术)200530551241孔大力671男广东省东莞市高级中学共青团员1986-10-12软件工程(嵌入式技术)200530551449林拉拉676男广东省汕头金山中学共青团员1985-10-15软件工程(嵌入式技术)学生信息数据库二、数据库(续)人们收集并抽取出一个应用所需要的大量数据之后，应将其保存起来以供进一步加工处理，进一步抽取有用信息数据库的定义数据库(Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据集合二、数据库(续)数据库的特征数据按一定的数

8、据模型组织、描述和储存可为各种用户共享冗余度较小数据独立性较高易扩展三、数据库管理系统什么是DBMS数据库管理系统（Database Management System，简称DBMS）：位于用户与操作系统之间的一层数据管理软件。DBMS的用途科学地组织和存储数据、高效地获取和维护数据DBMS的主要功能数据定义功能提供数据定义语言(DDL)定义数据库中的数据对象数据操纵功能提供数据操纵语言(DML)操纵数据实现对数据库的基本操作(查询、插入、删除和修改)DBMS的主要功能数据库的运行管理保证数据的安全性、完整性、多用户对数据的并发使用发生故障后的系统恢复数据库的建立和维护功能(实用程序)数据库数

9、据批量装载数据库转储介质故障恢复数据库的重组织性能监视等四、数据库系统什么是数据库系统数据库系统（Database System，简称DBS）： 指在计算机系统中引入数据库后的系统构成在不引起混淆的情况下常常把数据库系统简称 为数据库数据库系统的构成数据库数据库管理系统（及其开发工具）应用系统数据库管理员Chapter 1: IntroductionPurpose of Database SystemsView of DataDatabase LanguagesRelational DatabasesDatabase DesignData Storage and QueryingTransac

10、tion ManagementDatabase ArchitectureDatabase Users and AdministratorsOverall StructureHistory of Database SystemsDatabase Management System (DBMS)DBMS contains information about a particular enterpriseCollection of interrelated dataSet of programs to access the data An environment that is both conve

11、nient and efficient to useDatabase Applications:Banking: all transactionsAirlines: reservations, schedulesUniversities: registration, gradesSales: customers, products, purchasesOnline retailers: order tracking, customized recommendationsManufacturing: production, inventory, orders, supply chainHuman

12、 resources: employee records, salaries, tax deductionsDatabases touch all aspects of our livesFile Systems and DatabasesIn the early days, database applications were built directly on top of file systemsFile Systems and Databases补贴系别姓名ID劳资科住址系别性别姓名ID房产科学位学分系别姓名ID学籍科学位出身年龄系别性别姓名ID人事科File Systems and

13、DatabasesDrawbacks of using file systems to store data:Data redundancy and inconsistencyMultiple file formats, duplication of information in different filesDifficulty in accessing data Need to write a new program to carry out each new taskData isolation multiple files and formatsIntegrity problemsIn

14、tegrity constraints (e.g. account balance 0) become “buried” in program code rather than being stated explicitlyHard to add new constraints or change existing onesFile Systems and DatabasesDrawbacks of using file systems (cont.) Atomicity of updatesFailures may leave database in an inconsistent stat

15、e with partial updates carried outExample: Transfer of funds from one account to another should either complete or not happen at allConcurrent access by multiple usersConcurrent accessed needed for performanceUncontrolled concurrent accesses can lead to inconsistenciesExample: Two people reading a b

16、alance and updating it at the same timeSecurity problemsHard to provide user access to some, but not all, dataDatabase systems offer solutions to all the above problemsPurpose of Database Systems数据库观点数据不是依赖于处理过程的附属品，而是现实世界中独立存在的对象Purpose of Database Systems学号姓名性别系别年龄住址出身学位学分补贴学籍科房产科人事科劳资科数据库系统阶段特点有了

17、数据库管理系统面向全组织，面向现实世界独立性较强由DBMS统一存取，维护数据语义及结构数据库系统的特点面向全组织的复杂的数据结构支持全企业的应用而不是某一个应用数据反映了客观事物间的本质联系，而不是着眼于面向某个应用，是有结构的数据。这是数据库系统的主要特征之一，与文件系统的根本差别。文件系统只是记录的内部有结构，一个文件的记录之间是个线性序列，记录之间无联系数据库系统的特点数据的冗余度小，易扩充数据面向整个系统，而不是面向某一应用，数据集中管理，数据共享，因此冗余度小节省存储空间，减少存取时间，且可避免数据之间的不相容性和不一致性每个应用选用数据库的一个子集，只要重新选取不同子集或者加上一小

18、部分数据，就可以满足新的应用要求，这就是易扩充性数据库系统的特点具有较高的数据和程序的独立性把数据库的定义和描述从应用程序中分离出去数据描述是分级的（全局逻辑、局部逻辑、存储）数据的存取由系统管理，用户不必考虑存取路径等细节，从而简化了应用程序数据库系统的特点统一的数据控制功能，数据共享程度高安全性控制（Security）保护数据以防止不合法的使用所造成的数据泄露和破坏措施：用户标识与鉴定，存取控制完整性控制（Integrity）数据的正确性、有效性、相容性措施：完整性约束条件定义和检查并发控制（Concurrency）对多用户的并发操作加以控制、协调，防止其互相干扰而得到错误的结果并使数据库

19、完整性遭到破坏措施：封锁恢复控制(Recovery)将数据库从故障中恢复到某个一致状态措施：冗余ExampleS(SNO, SNAME, STATUS, CITY)P(PNO, PNAME, COLOR, WEIGHT, CITY)J(JNO, JNAME,CITY)SPJ(SNO, PNO, JNO, QTY)S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市；P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市；J表示工程，各属性依次为工程号，工程名，工程所在城市；SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。供应商项目零

20、件供应Example查询“供应红色零件给北京的工程的供应商姓名”维护“不允许供应不存在的零件”ExampleFile System分别组织几个文件，存储各类对象的记录CreateFile(S, P, J, SPJ)ScanFile(P)找到红色零件的号码ScanFile(J)找到北京的工程的号码ScanFile(SPJ）找到对应以上两号码的SNOScanFile(S)找到对应以上SNO的供应商姓名查询：供应红色零件给北京的工程的供应商姓名ExampleFile System如果InsertFile(SPJ)ScanFile(P)判断欲插入的零件号是否在P中如果DeleteFile(P)Scan

21、File(SPJ)判断欲删除的零件号是否在SPJ中维护：不允许供应不存在的零件ExampleDatabase数据统一按表结构存放，设为S，P，J，SPJ查询：只需提查询要求，由系统完成查询过程SELECT SNAMEFROM S, P, J, SPJWHERE SPJ.SNO = S.SNO AND SPJ.PNO = P.PNO AND SPJ.JNO = J.PNO AND J.CITY = “BEIJING” AND P.COLOR = “RED” 维护：应用提出完整性约束，系统自动检查CREATE TABLE SPJ(, FOREIGN KEY (PNO) REFERENCES P(P

22、NO), )Database vs. File System文件系统数据管理应用逻辑文件系统应用逻辑数据管理应用软件系统软件Data Abstraction现实世界信息世界计算机世界概念化形式化用户计算机Data AbstractionLevels of AbstractionView level: application programs hide details of data types. Views can also hide information (such as an employees salary) for security purposes. Logical level:

23、describes data stored in database, and the relationships among the data.type customer = recordcustomer_id : string; customer_name : string;customer_street : string;customer_city : integer;end;Physical level: describes how a record (e.g., customer) is stored.View of DataAn architecture for a database

24、 systemInstances and SchemasSimilar to types and variables in programming languagesSchema the logical structure of the database Example: The database consists of information about a set of customers and accounts and the relationship between them)Analogous to type information of a variable in a progr

25、amPhysical schema: database design at the physical levelLogical schema: database design at the logical levelInstances and SchemasInstance the actual content of the database at a particular point in time Analogous to the value of a variablePhysical Data Independence the ability to modify the physical

26、 schema without changing the logical schemaApplications depend on the logical schemaIn general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others.Data ModelsA collection of tools for describing Data Data re

27、lationshipsData semanticsData constraintsRelational modelEntity-Relationship data model (mainly for database design) Other older models:Network model Hierarchical modelThe Entity-Relationship ModelModels an enterprise as a collection of entities and relationshipsEntity: a “thing” or “object” in the

28、enterprise that is distinguishable from other objectsDescribed by a set of attributesRelationship: an association among several entitiesRepresented diagrammatically by an entity-relationship diagram:Relational ModelExample of tabular data in the relational modelAttributesTuplesA Sample Relational Da

29、tabaseData Definition Language (DDL)Specification notation for defining the database schemaExample:create table account ( account-number char(10), balance integer)DDL compiler generates a set of tables stored in a data dictionaryData dictionary contains metadata (i.e., data about data)Database schem

30、a Data storage and definition language Specifies the storage structure and access methods usedIntegrity constraintsDomain constraintsReferential integrity (references constraint in SQL)AssertionsAuthorizationData Manipulation Language (DML)Language for accessing and manipulating the data organized b

31、y the appropriate data modelDML also known as query languageTwo classes of languages Procedural user specifies what data is required and how to get those data Declarative (nonprocedural) user specifies what data is required without specifying how to get those dataSQL is the most widely used query la

32、nguageSQLSQL: widely used non-procedural languageExample: Find the name of the customer with customer-id 192-83-7465selectcustomer.customer_namefromcustomerwherecustomer.customer_id = 192-83-7465Example: Find the balances of all accounts held by the customer with customer-id 192-83-7465selectaccount

33、.balancefrom depositor, accountwhere depositor.customer_id = 192-83-7465 anddepositor.account_number = account.account_numberApplication programs generally access databases through one ofLanguage extensions to allow embedded SQLApplication program interface (e.g., ODBC/JDBC) which allow SQL queries

34、to be sent to a databaseDatabase DesignThe process of designing the general structure of the database:Logical Design Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas.Business decision What attributes should we record in the database?Compu

35、ter Science decision What relation schemas should we have and how should the attributes be distributed among the various relation schemas?Physical Design Deciding on the physical layout of the database Storage ManagementStorage manager is a program module that provides the interface between the low-

36、level data stored in the database and the application programs and queries submitted to the system.The storage manager is responsible to the following tasks: Interaction with the file manager Efficient storing, retrieving and updating of dataIssues:Storage accessFile organizationIndexing and hashing

37、Storage HierarchyQuery Processing1.Parsing and translation2.Optimization3.EvaluationQuery Processing (Cont.)Alternative ways of evaluating a given queryEquivalent expressionsDifferent algorithms for each operationCost difference between a good and a bad way of evaluating a query can be enormousNeed

38、to estimate the cost of operationsDepends critically on statistical information about relations which the database must maintainNeed to estimate statistics for intermediate results to compute cost of complex expressionsTransaction ManagementA transaction is a collection of operations that performs a

39、 single logical function in a database applicationTransaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.Concurrency-control manager controls the interacti

40、on among the concurrent transactions, to ensure the consistency of the database. Database ArchitectureThe architecture of a database systems is greatly influenced by the underlying computer system on which the database is running:CentralizedClient-serverParallel (multi-processor)Distributed Figure 1

41、.7Database UsersUsers are differentiated by the way they expect to interact with the systemApplication programmers interact with system through DML callsSophisticated users form requests in a database query languageSpecialized users write specialized database applications that do not fit into the tr

42、aditional data processing frameworkNave users invoke one of the permanent application programs that have been written previouslyExamples, people accessing database over the web, bank tellers, clerical staffDatabase AdministratorCoordinates all the activities of the database system; the database admi

43、nistrator has a good understanding of the enterprises information resources and needs.Database administrators duties include:Schema definitionStorage structure and access method definitionSchema and physical organization modificationGranting user authority to access the databaseSpecifying integrity constraintsActing as liaison with usersMonitoring performance and responding to changes in requirementsOverall System Structure 索引数据文件统计数据数据字典事务管理器应用程序目标代码嵌入式DML预编译器DML编译器DDL解释器查询计算引擎应用界面应用程序查询数