数据库ch1introduction

1、database systemcourse information2course informationncourse lname: database system (数据库系统)l32 hours class room teaching, 8 hours experimentslpublic e-mail: 4password:neuneunteacherlname: wang shuang (王爽)le-mail: wangshuang_ course information3text book and referencentext bookldatabase system concept

2、s (5th edition), mcgraw-hill, 2005nreferencel数据库系统概论(第5版)，高等教育出版社，2005course information4contents and requirementsnbasic concepts ndata modellrelational modelndatabase languagelsql course information5cont.contents and requirementsndatabase designle-r modelltransform rulelnormalizendbms functionslque

3、ry optimizationlconcurrency controllrecovery systemschapter1 introduction6contents and requirementsndatabase programminglembedded sqlltriggerslstored procedurelodbc, jdbccourse information7grading policynassignments: 10%nexperiments: 10%nfinal exam: 80%chapter 1 introductionchapter1 introduction9con

4、tentsnpurpose of database systemsnview of datandatabase languagesnrelational databasesndatabase designndata storage and queryingntransaction managementndatabase architecturendatabase users and administratorsnoverall structurenhistory of database systemschapter1 introduction10数据库在计算机系统中的地位硬件操作系统 dbms

5、 编译系统 应用开发工具 应用系统 chapter1 introduction11database system applicationsnrepresentative database applicationslbanking: all transactionslairlines: reservations, schedulesluniversities: registration, gradeslsales: customers, products, purchaseslonline retailers: order tracking, customized recommendations

6、lmanufacturing: production, inventory, orders, supply chainlhuman resources: employee records, salaries, tax deductionsndatabases touch all aspects of our liveschapter1 introduction12n发展了一门计算机基础学科。l以数据模型和以数据模型和dbms核心技术为主，内容丰富、领核心技术为主，内容丰富、领域宽广。域宽广。n形成了一个巨大的软件产业。形成了一个巨大的软件产业。chapter1 introduction13or

7、acle39%microsoft17.8%ibm26%sybase13.2%其他其他4%人大金仓人大金仓1%0.7%武汉达梦武汉达梦0.5%数据来源：易观国际数据来源：易观国际 2007年年1月（权威机构报告首次出现国产数据库市场份额）月（权威机构报告首次出现国产数据库市场份额）国内数据库市场情况companyproductremarksoracle oracle 8i, 9i, etc.worlds 2nd largest software companyceo, larry ellison, worlds 2nd richestibmdb2, universal serverworlds

8、2nd largest after informix acquisitionmicrosoftaccess, sql serveraccess comes with ms officesybaseadaptive serverceo john cheninformixdynamic serveracquired by ibm in 2001commercial database systems三位图灵奖得主e.f.coddjim grayc.w.bachman1960年为通用电气制造了世界上第一个网状数据库系统ids积极推动与促成了数据库标准的制定：dbtg报告 在数据库技术的产生、发展与推广

9、应用方面都发挥了巨大的作用 由于他在数据库方面的杰出成就1973获图灵奖1983年成立自己的公司 bachman information systemcharles.w.bachman网状数据库之父edgar f.codd 博士关系数据库之父/美国工程院院士 原是英国人，1923生于英格兰中部波特兰 第二次世界大战时应征入伍，在皇家空军服役。1942-1945年间任机长，参与了许多惊心动魄的空战。英国牛津大学数学专业理学士及硕士学位，毕业后到ibm公司工作从事操作系统和自动机理论研究年近40重返密歇根大学进修计算机与通信专业，1963年获得硕士学位，1965年又获得博士学位。60年代后期开始数

10、据库研究, 1970年e.f.codd 博士提出关系模型概念(cacm,vol.13, vol.6, 1970 )1981年获图灵奖，84年从ibm公司退休还创办了一个研究所：关系研究所（the relational institute）和一个公司：codd & associations，进行关系数据库产品的研发、销售、咨询等业务james （jim） gray数据库技术和事务处理专家数据库技术和事务处理专家1944年生，美国加州大学伯克利分校 计算机科学系博士。先后在贝尔实验室、ibm、tandem、dec等公司工作，研究方向转向数据库领域。 由于他在数据库和事务处理研究方面的元创性

11、贡献以及在将研究原型转化为商业产品的系统实现方面的技术领袖地位，1998年获奖(时任微软研究员)2007年1月28日从旧金山港出发，驾驶一艘长40英尺的游艇，前往farallon岛途中神秘失踪，至今是一个迷。数据库:一个巨大的软件产业理论创立理论创立原型研制原型研制产品上市产品上市经济效益经济效益是理论成果转化为产品的成功范例是理论成果转化为产品的成功范例chapter1 introduction20database conferencendatabase l sigmod/pods, vldb, icdel dasfaa, dexa, edbt/icdtl ndbcnother area（d

12、ata mining、information retrieve、knowledge management等）lacm cikm, sigir, sigkddlpakdd,chapter1 introduction21database systemndatalthe symbol to describe the objectldata and semantics can not be divided ndatabase(db)ndatabase system(dbms)ndbschapter1 introduction22database systemndblcollection of inte

13、rrelated data4in long term4structured4can be sharedcontains information about a particular enterprisechapter1 introduction23database systemndbmslset of programs to access the datathat is both convenient and efficientchapter1 introduction24function of dbmsndata definitionndata manipulationndatabase m

14、anagementlsecuritylintegrity lconcurrentlrecoveryndata organization, storage and managementlindex，sequencechapter1 introduction25ndbs(database system)ldbldbmslapplicationsldba应用系统应用系统应用开发工具数据库管理系统数据库管理系统操作系统数据库数据库用户用户用户数据库管理员数据库管理员数据库系统chapter1 introduction26nin the early days, applications were bui

15、lt directly on top of file systemsnusing file systems to store and manipulate data have several drawbackspurpose of database systemschapter1 introduction27ndrawbacks of using file systeml data redundancy and inconsistency4duplication of information in different files4eg:4人事处 （姓名，年龄，单位，工龄，职务，职称职务，职称，

16、政治面貌，籍贯，住址，家庭成员，社会关系等）4财务处(姓名，单位，工龄，职称职称，工龄，基本工资，岗位工资，特殊津贴等） 4maintain cost highpurpose of database systemscont.chapter1 introduction28ndrawbacks of using file systeml difficulty in accessing data 4need to write a new program to carry out each new taskpurpose of database systemschapter1 introduction

17、29ndrawbacks of using file systemldata isolation multiple files and formatspurpose of database systemscont.cont.chapter1 introduction30ndrawbacks of using file systemlintegrity problems4integrity constraints (e.g. account balance 0) become “buried” in program code rather than being stated explicitly

18、4hard to add new constraints or change existing onespurpose of database systemscont.cont.chapter1 introduction31cont.ndrawbacks of using file system latomicity of updates4failures may leave data in an inconsistent state with partial updates carried out4transfer of funds from one account to another s

19、hould either complete or not happen at all(500+100)$500$600$100$200$(200-100)$ china bankcity bank200$500$100$600$purpose of database systemscont.chapter1 introduction32ndrawbacks of using file systemlconcurrent access by multiple users4concurrent access is needed for performance4uncontrolled concur

20、rent accesses can lead to inconsistencies4two people reading a balance and updating it at the same time200020002000-500150020002000-3001700purpose of database systemscont.cont.chapter1 introduction33cont.cont.ndrawbacks of using file systemlsecurity problems4hard to provide user access to some, but

21、not all, datadatabase systems offer solutions to all the above problemspurpose of database systemschapter1 introduction34data independencenone big problem in application development is the separation of applications from datando i have change my program when i lreplace my hard drive?lstore the data

22、in a b-tree instead of a hash file?lpartition the data into two physical files (or merge two physical files into one)?lstore salary as floating point number instead of integer?ldevelop other applications that use the same set of data?ladd more data fields to support other applications?nsolution: int

23、roduce levels of abstraction.chapter1 introduction35view of datandatabase system provide an abstract view of the datalachieved through several level of abstraction 4physical level 4logical level4view level three levels of abstractionview 1view 2.view nlogical viewphysical viewarrsw deptfinancial off

24、iceneu databasefiles on diskschapter1 introduction37nphysical level: ldescribes how a record (e.g., customer) is stored e.g., “divide the customer records into 3 partitions and store them on disks 1, 2 and 3.”nlogical level: ldescribes what data are stored in the database, and the relationships amon

25、g the data lsimilar to defining a record type in pascal or c:type customer = recordname: string;street: string;city: integer; end;ldescribe entire databaselevel of abstractionchapter1 introduction38level of abstractionnview level: ldescribe part of the databasela way to hide: (a) details of data typ

26、es and (b) information (such as an employees salary) for security purposeschapter1 introduction39nsimilar to types and variables in programming languagesnschema the overall design of the database la logical schema defines the logical structure of the database(e.g., set of customers and accounts and

27、the relationship between them)lanalogous to type information of a variable in a programninstance the collection of information stored in the database at a particular point in time lanalogous to the value of a variableinstances and schemaschapter1 introduction40viewlogicalphysicalview definitionslogi

28、cal schema physical schemaapplicationphysical data independencelogical data independencechapter1 introduction41nseveral schemas of dbslphysical schema: database design at the physical level4(only one)4sequential access ,hash access; index; compress, encrypt cont.physical schemachapter1 introduction4

29、2chapter1 introduction43chapter1 introduction44logical schemallogical schema: database design at the logical level4(only one)4describes the entire database，considering the needs of all users4the next-higher level of abstraction describes what data are stored in the database4relationships exist among

30、 those data.chapter1 introduction45sub schemaslsub schemas: describe different views at the view level 4(many)4the highest level of abstraction describes only part of the entire database.4subset of the logical schema4security chapter1 introduction46nphysical data independence application do not depe

31、nd on physical schema, and thus need not be rewritten if the physical schema changeslapplications depend on the logical schemalphysical schema is hidden beneath the logical schema n logical data independence lusers are shielded from changes in the logical structure of the datacont.instances and sche

32、maschapter1 introduction47data modelsna collection of tools for describing ldata ldata relationshipsldata semanticsldata constraintschapter1 introduction48cont.data modelsnentity-relationship data modellmainly for database designnrelational modelnobject-based data models lobject-oriented lobject-rel

33、ationalnsemistructured data model lxmlnother older models:lnetwork model lhierarchical modelchapter1 introduction49chapter1 introduction50the entity-relationship modelnmodels an enterprise as a collection of entities and relationshipsnentity: a “thing” or “object” in the real world that is distingui

34、shable from other objectsldescribed by a set of attributeschapter1 introduction51nrelationship: an association among several entitiesnentity set: the set of all entities of the same typenrelationship set: the set of all relationships of the same typecont.the entity-relationship modela deposit relati

35、onship associates a customer with a account that she haschapter1 introduction52cont.the entity-relationship modelnrepresented diagrammatically by an entity-relationship diagram:chapter1 introduction53the entity-relationship modelnthe e-r model represents certain constraints to which the contents of

36、a database must conform.lmapping cardinalities（映射基数（映射基数 ）41：141：n4m:nchapter1 introduction54chapter1 introduction55n职工实体型内部具有领导与被领导的联系n某一职工（干部）“领导”若干名职工n一个职工仅被另外一个职工直接领导chapter1 introduction56chapter1 introduction57n供应商、项目、零件三个实体型n一个供应商可以供给多个项目多种零件每个项目可以使用多个供应商供应的零件每种零件可由不同供应商供给chapter1 introductio

37、n58examplen实体之间的联系如下：(1)一个仓库可以存放多种零件，一种零件可以存放在多个仓库中。仓库和零件具有多对多的联系。用库存量来表示某种零件在某个仓库中的数量。(2)一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作，仓库和职工之间是一对多的联系。职工实体型中具有一对多的联系(3)职工之间具有领导-被领导关系。即仓库主任领导若干保管员。(4)供应商、项目和零件三者之间具有多对多的联系chapter1 introduction59chapter1 introduction60nuse a collection of tables to represent both data

38、 and relationships among those data chapter1 introduction61chapter1 introduction62relational modelexample of tabular data in the relational model: customer- name social- security customer- street customer- city account-numberjohnsonsmithjohnsonjonessmith192-83-7465019-28-3746192-83-7465321-12-312301

39、9-28-3746almanorthalmamainnorthpalo altoryepalo altoharrisonryea-101a-215a-201a-217a-201account-numberbalancea-101a-201a-215a-217500900700750chapter1 introduction63database languages*ndatabase system provides ldata definition language (ddl) to specify database schemaldata manipulation language (dml)

40、 to express database queries and updates nin practice, ddl and dml form parts of a single database languagelsql (structured query language) chapter1 introduction64nspecification notation for defining the database schema create table account ( account-number char(10), balance integer)data definition

41、language (ddl)chapter1 introduction65cont.data definition language (ddl)nddl compiler generates tables according to ddl statements and store the schema definitions of these tables in data dictionaryndata dictionary contains metadata (i.e., data about data)ldatabase schema lintegrity constraintslauth

42、orizationchapter1 introduction66data manipulation language (dml)nlanguage for accessing and manipulating the data organized by the appropriate data modellretrieval of informationlinsertion of new informationldeletion of informationlmodification of information ndml also known as query languagequery c

43、hapter1 introduction67cont.data manipulation language (dml)ntwo classes of languages lprocedural user specifies what data is required and how to get those data ldeclarative (nonprocedural) user specifies what data is required without specifying how to get those datansql is the most widely used nonpr

44、ocedural query languagechapter1 introduction684find the name of the customer with customer-id 192-83-7465select customer.customer_namefrom customerwhere customer.customer_id = 192-83-7465chapter1 introduction69select account.balancefrom depositor, accountwhere depositor.customer_id = 192-83-7465 and

45、 depositor.account_number = account.account_numbercont.4find the balances of all accounts held by the customer with customer-id 192-83-7465chapter1 introduction70napplication programs generally access databases through one ofllanguage extensions to allow embedded sqllapplication program interface (e

46、.g., odbc/jdbc) which allow sql queries to be sent to a databaseaccess from application programsopen database connectivityjavadatabase connectivitychapter1 introduction71relational databasesna relational database is based on the relational data modelndata and relationships among the data is represen

47、ted by a collection of tables nincludes both a dml and a ddlnmost commercial relational database systems employ the sql query languagechapter1 introduction72chapter1 introduction73database designnthe process of designing the general structure of the database:llogical designlphysical designchapter1 i

48、ntroduction74cont.database designnlogical design deciding on the database schema. database design requires that we find a “good” collection of relation schemas.lbusiness decision what attributes should we record in the database?lcomputer science decision what relation schemas should we have and how

49、should the attributes be distributed among the various relation schemas?nphysical design deciding on the physical layout of the databasechapter1 introduction75tools:database designsql (queries)programmingdesignsqlprogramdesignsqlprogrambest:spend your timeon design and sql.worst:compensate for poor

50、designand limited sql with programming.chapter1 introduction76database usersnnaive users invoke one of the permanent application programs that have been written previouslylexamples, people accessing database over the web, bank tellers, clerical staffusers are differentiated by the way they expect to

51、 interact with the systemchapter1 introduction77database usersnapplication programmers lare computer professionals who write application programs.lfourth-generation languages，often include special features to facilitate the generation of forms and the display of data on the screenl interact with sys

52、tem through dml callschapter1 introduction78cont.database usersnsophisticated users linteract with the system without writing programs.l form requests in a database query languagechapter1 introduction79database usersnspecialized users lwrite specialized database applications that do not fit into the

53、 traditional data processing frameworklamong these applications are computer-aided design systems, knowledge base and expert systemsl systems that store data with complex data types (for example, graphics data and audio data), and environment-modeling systems.chapter1 introduction80database administ

54、ratorndatabase administrator dbana person who have central control of the database system, coordinates all the activities of the database systemnhas a good understanding of the enterprises information resources and needs chapter1 introduction81database administratorndatabase administrators duties in

55、clude:lschema definitionlspecifying integrity constraintslstorage structure and access method definitionlschema and physical organization modificationlgranting user authority to access the databaselacting as liaison with userslmonitoring performance and responding to changes in requirementscont.prim

56、ary job of a databasedesignermore systemorientedchapter1 introduction82database system structurenfunction components of dbmslstorage management4important because databases typically require a large amount of storage space. g terabytelquery processingchapter1 introduction83storage managementnstorage

57、manager is a program module responsible tolinteract with the file system 4translates the various dml statements into low-level file-system commands.lefficient storing, retrieving and updating of datachapter1 introduction84cont.storage managementnstorage manager include following componentslauthoriza

58、tion and integrity managerlfile managerlbuffer managerltransaction managerchapter1 introduction85file managerndatabase data is typically stored on secondary memory (hard disk), using file systemndata are moved between disk storage and main memory as needed.lthe movement of data to and from disk is s

59、lowlminimize the need to move data between disk and main memory.chapter1 introduction86file managementnmanages the allocation of space on disk storagendata structures used to represent information stored on disk.lheap file organizationlsequential file organizationlhashing file organizationchapter1 i

60、ntroduction87nbuffer managerlfetch data from disk storage to main memoryldecide what data to cache in main memorycont.buffer managerchapter1 introduction88transaction managementna transaction is a collection of operations that performs a single logical function in a database applicationntransaction-mana