清华大学数据库原理课件第一章.

1、2022-3-81王 建 民清 华 大 学 软 件 学 院2003年/秋2l授课时间第1周第16周l学生教材Database System Concepts(4th Edition)，高等教育出版社l参考教材数据库系统概论(第3版)，萨师煊、王珊高等教育出版社3l作业平时作业(h)、项目作业(pr)l考试期中考试(mt，第9周)期末考试(ft，第17/18周)l成绩h*15%+pr*25%+ mt*20%+ft*40%4l学习方式学思统一团队学习(3-5人)l课程辅导骆科东、潘莉莉l其它信息清华大学网络学堂5l深入理解数据库系统的基本概念、原理和方法l重点掌握关系数据模型及关系数据语言，能熟练

2、应用SQL语言表达各种数据操作l掌握E-R模型的概念和方法，关系数据库规范化理论和数据库设计方法l了解并掌握数据库管理系统的基本实现与应用技术6You Should be familiar with lBasic Data StructurelComputer Organization lA High-level Programming LanguageJAVA, PASCAL or C7lDatabase Systems and File SystemslData Abstraction/Data ModellDatabase LanguageslDatabase Users and Adm

3、inistratorlTransaction Management lOverall System StructurelApplication ArchitectureslHistory and the Future8lData数据lDatabase 数据库lDatabase Management System数据库管理系统lDatabase System数据库系统lDatabase Application数据库应用9lCollection of interrelated data and Set of programs to access the data lDBMS contains in

4、formation about a particular enterpriselDBMS provides an environment that is both convenient and efficient to use.lDatabases touch all aspects of our lives10lBanking: all transactionslAirlines: reservations, scheduleslUniversities: registration, gradeslSales: customers, products, purchaseslManufactu

5、ring: production, inventory, orders, supply chainlHuman resources: employee records, salaries, tax deductionslIP Phone/Dial-Up Systems:AAA11lIn the early days, database applications were built on top of file systemslDrawbacks of using file systems to store data:Data redundancy and inconsistencylMult

6、iple file formats, duplication of information in different fileslE.g. address and telephone number saved insavings-account recordchecking-account record12lDrawbacks of using file systemsDifficulty in accessing data lNeed to write a new program to carry out each new taskData isolation multiple files

7、and formatslAutoCAD, 2D drawing toolsdwg file binary file formatdifferent version, different formatlMS Word, doc file13lDrawbacks of using file systemsIntegrity problemslIntegrity constraints (e.g. account balance 0) become part of program codelHard to add new constraints or change existing onesAtom

8、icity of updateslFailures may leave database in an inconsistent state with partial updates carried outlE.g. transfer of funds from one account to another should either complete or not happen at all14lDrawbacks of using file systemsConcurrent access by multiple userslConcurrent accessed needed for pe

9、rformancelUncontrolled concurrent accesses can lead to inconsistencies E.g. two people reading a balance and updating it at the same time Account A $500,one withdraws $50, the other withdraws $100Security problemslPasswordslAccount balance15lCapturelOrganize & StorelRetrievelAnalyzel16lA primary

10、 method of computer scienceModel: A small object, usually built to scale, that represents in detail another, often larger object.lDBMS provides users with an abstract view of the dataHides the details of storage and maintenanceSimplify the users interaction with the system17Data architecture for a d

11、atabase system AdministratorProgrammerEnd UserMemoryVariableDisplay18lPhysical level describes how a record (e.g., customer) is stored.lLogical level: describes data stored in database, and the relationships among the data.type customer = recordname : string;street : string;city : integer;end;19lVie

12、w level: application programs hide details of data types. Views can also hide information (e.g., salary) for security purposes. Prinf(“%d”,int-variable)20lSimilar to types and variables in programming languageslSchema模式模式 the logical structure of the database e.g., the database consists of informati

13、on about a set of customers and accounts and the relationship between themAnalogous to type information of a variable in a programPhysical schema: database design at the physical levelLogical schema: database design at the logical level21lInstance 实例 the actual content of the database at a particula

14、r point in time Analogous to the value of a variablelPhysical Data Independence the ability to modify the physical 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 ch

15、anges in some parts do not seriously influence others.22lA collection of conceptual tools for describing data data relationshipsdata semanticsdata constraintslEntity-Relationship modelInformation Architecturel/course-VI/course-VI-map.htmllRelational model23lOther models:

16、 Object-oriented modelSemi-structured data modelsOlder models: network model and hierarchical model24Example of schema in the entity-relationship model25lE-R model of real worldEntities (objects) lE.g. customers, accounts, bank branchRelationships between entitieslE.g. Account A-101 is held by custo

17、mer JohnsonlRelationship set depositor associates customers with accounts26lE-R DiagramRectangleslRepresent entity setsEllipseslRepresent attributesDiamondslRepresent relationships among entity setsLineslLink attributes to entity setslLink entity sets to relationships27lWidely used for database desi

18、gnDatabase design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing28lRelation关系a subset of a Cartesian product of a list of domainslTable表Represent both the data and their relationshipHas multiple columns, and each column has

19、a unique name 29lExample of tabular data in the relational modelcustomer-nameCustomer-idcustomer-streetcustomer-cityaccount-numberJohnsonSmithJohnsonJonesSmith192-83-7465019-28-3746192-83-7465321-12-3123019-28-3746AlmaNorthAlmaMainNorthPalo AltoRyePalo AltoHarrisonRyeA-101A-215A-201A-217A-201Attribu

20、tes3031lData definition languageSpecify the database schemalData manipulation languageExpress database queries and updates32lSpecification notation for defining the database schemaE.g. create table account ( account-number char(10), balance integer)lDDL compiler generates a set of tables stored in a

21、 data dictionarylMetadata A33lData dictionary contains metadata (i.e., data about data) database schema Data storage and definition language l language in which the storage structure and access methods used by the database system are specifiedlUsually an extension of the data

22、definition language34lLanguage for accessing and manipulating the data organized by the appropriate data modelDML also known as query languagelTwo classes of languages Procedural user specifies what data is required and how to get those data Nonprocedural user specifies what data is required without

23、 specifying how to get those datalSQL is the most widely used query language35lStructured Query LanguagelDeveloped in IBM at early 1970slSQL StandardSQL-86SQL-89SQL-92SQL:199936lSQL: widely used non-procedural languageE.g. find the name of the customer with customer-id 192-83-7465select customer.cus

24、tomer-namefrom customerwhere customer.customer-id = 192-83-7465E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465select account.balancefrom depositor, accountwhere depositor.customer-id = 192-83-7465 anddepositor.account-number = account.account-number37lApplica

25、tion programs generally access databases through one ofLanguage extensions to allow embedded SQLApplication program interface (e.g. ODBC/JDBC) which allow SQL queries to be sent to a database38lUsers are differentiated by the way they expect to interact with the systemlApplication programmers intera

26、ct with system through DML callslSophisticated users form requests in a database query language39lSpecialized users write specialized database applications that do not fit into the traditional data processing frameworklNave users invoke one of the permanent application programs that have been writte

27、n previouslyE.g. people accessing database over the web, bank tellers, clerical staff40lCentral control of both the data and the programslCoordinates all the activities of the database systemlthe database administrator has a good understanding of the enterprises information resources and needs.41lDa

28、tabase administrators duties include:Schema definitionStorage structure and access method definitionSchema and physical organization modificationGranting user authority to access the databaseRoutine maintenancelBack up the databaselEnsure enough free disk spacelEnsure the database performance42lA tr

29、ansaction is a collection of operations that performs a single logical function in a database applicationlTransaction-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 fai

30、lures.43lConcurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the databaselACID featuresAtomicity原子性Consistency 一致性Isolation隔离性Durability持久性4445lStorage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.lThe storage manager is responsible to the following tasks: interaction with the file manager efficient storing, retrieving and updating of data46l