《数据库ch1-introdu.ppt》由会员分享，可在线阅读，更多相关《数据库ch1-introdu.ppt（101页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Database SystemDatabase SystemSoftware College200920091Chapter1 IntroductionCourse InformationnCourse lName:Database System(数据库系统)l32 hours class room teaching,8 hours experiments4Password:neuneunTeacherlName:wang shuang(王爽)2Course InformationText Book and ReferencenText BooklDatabase System Concept
2、s(5th Edition),McGraw-Hill,2005nReferencel数据库系统概论(第5版)，高等教育出版社，20053Course InformationContents and RequirementsnBasic Concepts nData ModellRelational ModelnDatabase LanguagelSQL UnderstandUnderstandMaster SkillfullyMaster SkillfullyMaster SkillfullyMaster Skillfully4Course InformationCont.Contents a
3、nd RequirementsnDatabase DesignlE-R ModellTransform RulelNormalizenDBMS FunctionslQuery optimizationlConcurrency ControllRecovery SystemsMaster SkillfullyMaster SkillfullyMaster SkillfullyMaster SkillfullyMasterMasterUnderstandUnderstand5Course InformationContents and RequirementsnDatabase Programmi
4、nglEmbedded SQLlTriggerslStored ProcedurelODBC,JDBCMasterMasterUnderstandUnderstand6Chapter1 IntroductionGrading PolicynAssignments:10%nExperiments:10%nFinal Exam:80%May change if necessary May change if necessary Must finish experiments to passMust finish experiments to pass7Course InformationChapt
5、er 1 Chapter 1 IntroductionIntroduction8Chapter1 IntroductionContentsnPurpose of Database SystemsnView of DatanDatabase LanguagesnRelational DatabasesnDatabase DesignnData Storage and QueryingnTransaction ManagementnDatabase ArchitecturenDatabase Users and AdministratorsnOverall StructurenHistory of
6、 Database Systems9Chapter1 Introduction数据库在计算机系统中的地位硬件操作系统 DBMS 编译系统 应用开发工具 应用系统 10Chapter1 IntroductionDatabase System ApplicationsnRepresentative Database ApplicationslBanking:all transactionslAirlines:reservations,scheduleslUniversities:registration,gradeslSales:customers,products,purchaseslOnlin
7、e retailers:order tracking,customized recommendationslManufacturing:production,inventory,orders,supply chainlHuman resources:employee records,salaries,tax deductionsnDatabases touch all aspects of our lives11Chapter1 Introductionn发展了一门计算机基础学科。l以数据模型和以数据模型和DBMS核心技术为主，内容丰富、领核心技术为主，内容丰富、领域宽广。域宽广。n形成了一个
8、巨大的软件产业。形成了一个巨大的软件产业。12Chapter1 IntroductionOracle39%Microsoft17.8%IBM26%Sybase13.2%其他其他4%人大金仓人大金仓1%0.7%武汉达梦武汉达梦0.5%数据来源：易观国际数据来源：易观国际 2007年年1月（权威机构报告首次出现国产数据库市场份额）月（权威机构报告首次出现国产数据库市场份额）国内数据库市场情况13Chapter1 IntroductionCompanyProductRemarksOracle Oracle 8i,9i,etc.Worlds 2nd largest software companyCE
9、O,Larry Ellison,worlds 2nd richestIBMDB2,Universal ServerWorlds 2nd largest after Informix acquisitionMicrosoftAccess,SQL ServerAccess comes with MS OfficeSybaseAdaptive ServerCEO John ChenInformix Dynamic ServerAcquired by IBM in 2001Commercial Database Systems三位图灵奖得主Jim Gray1960年为通用电气制造了世界上第一个网状数据
10、库系统IDS积极推动与促成了数据库标准的制定：DBTG报告 在数据库技术的产生、发展与推广应用方面都发挥了巨大的作用 由于他在数据库方面的杰出成就1973获图灵奖1983年成立自己的公司 Bachman Information SystemCharles.W.Bachman网状数据库之父Edgar F.Codd 博士关系数据库之父/美国工程院院士 原是英国人，1923生于英格兰中部波特兰 第二次世界大战时应征入伍，在皇家空军服役。1942-1945年间任机长，参与了许多惊心动魄的空战。英国牛津大学数学专业理学士及硕士学位，毕业后到IBM公司工作从事操作系统和自动机理论研究年近40重返密歇根大学
11、进修计算机与通信专业，1963年获得硕士学位，1965年又获得博士学位。60年代后期开始数据库研究,1970年 博士提出关系模型概念(CACM,Vol.13,Vol.6,1970)1981年获图灵奖，84年从IBM公司退休还创办了一个研究所：关系研究所（The Relational Institute）和一个公司：Codd&Associations，进行关系数据库产品的研发、销售、咨询等业务James（Jim）Gray数据库技术和事务处理专家数据库技术和事务处理专家1944年生，美国加州大学伯克利分校 计算机科学系博士。先后在贝尔实验室、IBM、Tandem、DEC等公司工作，研究方向转向数据
12、库领域。由于他在数据库和事务处理研究方面的元创性贡献以及在将研究原型转化为商业产品的系统实现方面的技术领袖地位，1998年获奖(时任微软研究员)2007年1月28日从旧金山港出发，驾驶一艘长40英尺的游艇，前往Farallon岛途中神秘失踪，至今是一个迷。数据库:一个巨大的软件产业理论创立理论创立原型研制原型研制产品上市产品上市经济效益经济效益是理论成果转化为产品的成功范例是理论成果转化为产品的成功范例Database ConferencenDatabase l SIGMOD/PODS,VLDB,ICDEl DASFAA,DEXA,EDBT/ICDTl NDBCnOther area（data
13、 mining、information retrieve、knowledge management等）lACM CIKM,SIGIR,SIGKDDlPAKDD,20Chapter1 IntroductionDatabase SystemnDatalthe symbol to describe the objectlData and semantics can not be divided nDatabase(DB)nDatabase System(DBMS)nDBS21Chapter1 IntroductionDatabase SystemnDBlCollection of interrela
14、ted data4in long term4Structured4Can be sharedDatabase(DB):Database(DB):contains information about a particular enterprise22Chapter1 IntroductionDatabase SystemnDBMSlSet of programs to access the dataDatabase Management System(DBMS):Database Management System(DBMS):Provide a way to store and retriev
15、e Provide a way to store and retrieve database information database information that is both convenient and efficientLike OS,DBMS is Like OS,DBMS is a system softwarea system software23Chapter1 IntroductionFunction of DBMSnData DefinitionnData manipulationnDatabase managementlSecuritylIntegrity lCon
16、currentlRecoverynData organization,storage and managementlIndex，sequence24Chapter1 IntroductionnDBS(Database System)lDBlDBMSlApplicationslDBA应用系统应用系统应用开发工具数据库管理系统数据库管理系统操作系统数据库数据库用户用户用户数据库管理员数据库管理员数据库系统25Chapter1 IntroductionnIn the early days,applications were built directly on top of file systemsn
17、Using file systems to store and manipulate data have several drawbacksPurpose of Database Systems26Chapter1 IntroductionnDrawbacks of using file systeml Data redundancy and inconsistency4duplication of information in different files4Eg:4人事处（姓名，年龄，单位，工龄，职务，职称职务，职称，政治面貌，籍贯，住址，家庭成员，社会关系等）4财务处(姓名，单位，工龄，
18、职称职称，工龄，基本工资，岗位工资，特殊津贴等）4Maintain cost highPurpose of Database SystemsCont.27Chapter1 IntroductionnDrawbacks of using file systeml Difficulty in accessing data 4Need to write a new program to carry out each new taskProgram 1Program 1Program nProgram nProgram mProgram mPerson infoPerson infoNames of
19、all person who Names of all person who lives within particular lives within particular postal-code areapostal-code areaNames of all person who is Names of all person who is older than 50older than 50Purpose of Database SystemsExampleExample28Chapter1 IntroductionnDrawbacks of using file systemlData
20、isolation multiple files and formatsPurpose of Database SystemsBanking Banking ApplicationApplicationProgram 1Program 1Program nProgram nProgram mProgram mFortranFortranC CJAVAJAVASaving accountSaving accountChecking accountChecking accountVIP customerVIP customerProgram pProgram pNames of all VIP N
21、ames of all VIP customers who have a customers who have a checking accountchecking accountCont.Cont.ExampleExample29Chapter1 IntroductionnDrawbacks of using file systemlIntegrity problems4Integrity constraints (e.g.account balance 0)become“buried”in program code rather than being stated explicitly4H
22、ard to add new constraints or change existing onesPurpose of Database SystemsCont.Cont.30Chapter1 IntroductionCont.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 sho
23、uld either complete or not happen at all(500+100)$500$600$100$200$(200-100)$China BankCity BankA A200$B B500$100$600$Purpose of Database SystemsCont.ExampleExample31Chapter1 IntroductionnDrawbacks of using file systemlConcurrent access by multiple users4Concurrent access is needed for performance4Un
24、controlled concurrent accesses can lead to inconsistencies4Two people reading a balance and updating it at the same time200020002000-500150020002000-3001700Purpose of Database SystemsCont.Cont.ExampleExample32Chapter1 IntroductionCont.Cont.nDrawbacks of using file systemlSecurity problems4Hard to pr
25、ovide user access to some,but not all,dataDatabase systems offer solutions to all the above problemsPurpose of Database Systems33Chapter1 IntroductionData IndependencenOne big problem in application development is the separation of applications from datanDo I have change my program when I lreplace m
26、y hard drive?lstore the data 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
27、 applications?nSolution:introduce levels of abstraction.34Chapter1 IntroductionView of datandatabase system provide an abstract view of the datalAchieved through several level of abstraction 4Physical level 4Logical level4View level 35Chapter1 IntroductionThree Levels of Abstractionview 1view 2.view
28、 nLogical viewPhysical viewARRSW DeptFinancial OfficeNEU databaseFiles on disksnPhysical level:ldescribes how a record(e.g.,customer)is storede.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
29、relationships among 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 Abstraction37Chapter1 IntroductionLevel of AbstractionnView level:lDescribe part of the databaselA way to hide:(a)details of
30、 data types and(b)information(such as an employees salary)for security purposes38Chapter1 IntroductionnSimilar 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
31、and 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 Schemas39Chapter1 IntroductionviewlogicalphysicalView definitions
32、Logical schema Physical schemaApplicationPhysical Data IndependenceLogical Data Independence40Chapter1 IntroductionnSeveral Schemas of DBSlPhysical schema:database design at the physical level4(only one)4Sequential access,hash access;Index;compress,encrypt Cont.Physical schema41Chapter1 Introduction
33、heapheap42Chapter1 IntroductionSort by sidSort by sidClustering by ageClustering by age43Chapter1 IntroductionLogical 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
34、what data are stored in the database4relationships exist among those data.44Chapter1 IntroductionSub 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 45Chapter1 In
35、troductionnPhysical Data Independence application do not depend 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
36、 in the logical structure of the dataCont.Instances and Schemas46Chapter1 IntroductionData ModelsnA collection of tools for describing lData lData relationshipslData semanticslData constraints47Chapter1 IntroductionCont.Data ModelsnEntity-Relationship data modellmainly for database designnRelational
37、 modelnObject-based data models lObject-oriented lObject-relationalnSemistructured data model lXMLnOther older models:lNetwork model lHierarchical model48Chapter1 Introduction49Chapter1 IntroductionThe Entity-Relationship ModelnModels an enterprise as a collection of entities and relationshipsnEntit
38、y:a“thing”or“object”in the real world that is distinguishable from other objectslDescribed by a set of attributesEach person,bank accounts,customerEach person,bank accounts,customerAccount:described by account_number,balanceAccount:described by account_number,balanceCustomer:described by customer_na
39、me,Customer:described by customer_name,customer_street,customer_citycustomer_street,customer_city,customer_idcustomer_id50Chapter1 IntroductionnRelationship:an association among several entitiesnEntity Set:the set of all entities of the same typenRelationship Set:the set of all relationships of the
40、same typeCont.The Entity-Relationship ModelA deposit relationship associates a customer with a account that she has51Chapter1 IntroductionCont.The Entity-Relationship ModelnRepresented diagrammatically by an entity-relationship diagram:52Chapter1 IntroductionThe Entity-Relationship Modelnthe E-R mod
41、el represents certain constraints to which the contents of a database must conform.lmapping cardinalities（映射基数（映射基数）41：141：n4M:n53Chapter1 Introduction54Chapter1 Introductionn职工实体型内部具有领导与被领导的联系n某一职工（干部）“领导”若干名职工n一个职工仅被另外一个职工直接领导55Chapter1 Introduction56Chapter1 Introductionn供应商、项目、零件三个实体型n一个供应商可以供给多
42、个项目多种零件每个项目可以使用多个供应商供应的零件每种零件可由不同供应商供给57Chapter1 IntroductionExamplen实体之间的联系如下：(1)一个仓库可以存放多种零件，一种零件可以存放在多个仓库中。仓库和零件具有多对多的联系。用库存量来表示某种零件在某个仓库中的数量。(2)一个仓库有多个职工当仓库保管员，一个职工只能在一个仓库工作，仓库和职工之间是一对多的联系。职工实体型中具有一对多的联系(3)职工之间具有领导-被领导关系。即仓库主任领导若干保管员。(4)供应商、项目和零件三者之间具有多对多的联系58Chapter1 Introduction59Chapter1 Intr
43、oductionnUse a collection of tables to represent both data and relationships among those data Each table has Each table has several columns several columns Each column has Each column has a unique name a unique name Relational Model*60Chapter1 Introduction61Chapter1 IntroductionRelational ModelExamp
44、le of tabular data in the relational model:customer-name social-security customer-street customer-city account-numberJohnsonSmithJohnsonJonesSmith192-83-7465019-28-3746192-83-7465321-12-3123019-28-3746AlmaNorthAlmaMainNorthPalo AltoRyePalo AltoHarrisonRyeA-101A-215A-201A-217A-201account-numberbalanc
45、eA-101A-201A-215A-21750090070075062Chapter1 IntroductionDatabase Languages*nDatabase system provides lData Definition Language(DDL)to specify database schemalData Manipulation Language(DML)to express database queries and updates nIn practice,DDL and DML form parts of a single database languagelSQL(S
46、tructured Query Language)63Chapter1 IntroductionnSpecification notation for defining the database schema create table account(account-number char(10),balance integer)Data Definition Language(DDL)ExampleExample64Chapter1 IntroductionCont.Data Definition Language(DDL)nDDL compiler generates tables acc
47、ording 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 constraintslAuthorization65Chapter1 IntroductionData Manipulation Language(DML)nLanguage for accessing and manipulating the
48、data organized by the appropriate data modellRetrieval of informationlInsertion of new informationlDeletion of informationlModification of information nDML also known as query languageQuery 66Chapter1 IntroductionCont.Data Manipulation Language(DML)nTwo classes of languages lProcedural user specifie
49、s 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 nonprocedural query language67Chapter1 Introduction4Find the name of the customer with customer-id 192-83-7465Select
50、customer.customer_namefrom customerwhere customer.customer_id=192-83-7465SQLExampleExample68Chapter1 Introductionselect account.balancefrom depositor,accountwhere depositor.customer_id=192-83-7465 and depositor.account_number=account.account_numberCont.4Find the balances of all accounts held by the