《数据库课件.ppt》由会员分享，可在线阅读，更多相关《数据库课件.ppt（57页珍藏版）》请在三一办公上搜索。
1、Database SystemsPrinciples,College of Computer ScienceZhejiang UniversitySpring 2011,Instructor:陈根才 Class Time:Tue 15:55-17:30,Thu 8:00-9:35Practice Hours:Thu 9:50-11:25Office Add:曹光标楼（主楼-306，616B）Office Hours:Mon 2:00-4:00pmTel:87953052,13606500423Email:,TA:王礼文Practice Assistance:Thu 9:50-11:25(com
2、puter centre)AQ:Thu 9:50:55-12:00 Email:Tel:15258851254,Textbook:Database Systems Concepts5th edition By Abraham Silberschatz,Henry F.Korth and S.SudarshanReference books:Database Management Systems-3rd edition by Ramakrishnan and Gehrke Database Systems:The Complete Book-by Garcia-Molina,Ullman and
3、 Widom数据库系统概论（第四版），萨师选 王珊，高等教育出版社，2006数据库课程设计，陈根才 孙建伶 林怀中 周波，浙江大学出版社，2007(实验参考书),Grading Policy:平时（作业、课堂测验、原理实验）20%Project:20%（Due date:Apr 18）Final exam:60%【注1：Close book test,allow take one A4 page note.】【注2：若期末试卷成绩低于40分，则该课成绩fail】,实验：数据库系统原理实验地点：计算中心机房，专用服务器和用户IP Addr：10.214.6.98User：库 db20：user2
4、00 user209 库 db21：user210 user219 库 db29：user290 user299（缺省密码同用户名）Projects:图书管理系统实现see appendix:数据库系统原理实验-2011.docSQL实验补充.doc图书管理系统实验指导书-2011.doc也可以(并鼓励)选择其他数据库管理项目，如在线书店管理系统、超市销售管理系统、学生成绩管理系统、人事管理系统、设备管理系统等进行设计和开发，基本要求与图书管理系统类似。,LecturesLecture slides in PPT format will be posted shortly before or
5、after the lectureMany issues discussed in the lectures will be covered in the exams and assignments Try to attend lectures regularly!Assignments Paper-based+some programmingWill be collected at the end of class on the due dateHomework handed in by the due time will be graded for full credit,then a 3
6、0%late charge will be applied.No late homework is accepted after on-time papers are returned,or the sample solution is made public,whichever occurs first.,课件下载和实验文档上载：FTP Server：10.214.36.16 User:dbms PWD:dbms port:21,Chapter 1:Introduction,Purpose of Database SystemsView of DataData Models Database
7、 Language Database AdministratorDatabase Users Transaction Management Storage ManagementOverall System Structure,Data processing and management are the most important fields of computer applications.Knowledge of database concepts is essential for computer scientistsDatabases touch all aspects of our
8、 lives in information society，even when you dont see themBanking:all transactionsAirlines:reservations,schedulesUniversities:registration,gradesSales:customers,products,purchasesManufacturing:production,inventory,orders,supply chainE-Government,E-Business,1.0 Why should we learn Database?,-Of all co
9、urses you have taken,this may be the one that gets you a job.,1.1 What should we learn?-Three Aspects to Study Database,1)Modeling and design of databases.Get(abstract)data models from real world,then translate them into the forms suitable for the target DBMS(Database Management System)-tables,views
10、.2)Programming:use database-queries and update of data.SQL=“intergalactic data-speak.”3)DBMS implementation-how does DBMS work,and how to design a DBMS-another course:Database System Design,A Bad design,Another Design,Students,Courses,Enrolled,方法1：利用数据库管理系统提供的交互工具访问数据库如：SQL Server的查询分析器，ORACLE的Sql*P
11、lus，Work Sheet,方法2：利用开发工具设计界面、处理数据，调用ODBC访问数据库,如ASP,JSP,VC+,PHP,PowerBuilder,Delphi,1.2 What is a database-Definition,Database:A collection of interrelated data,relevant to an enterprise.A large collection of integrated and persistent data(DB)R.Ramakrishnan,J.GehrheA collection of information that e
12、xists over a long period of time,often manay years.Ullman长期存储在计算机内、有组织的、可共享的数据集合。萨师煊，王珊Database Management System(DBMS):(Database)+A set of programs used to access,update and manage the data in database,Efficiency and scalability(可扩展性)in data accessReduced application development time;Data independe
13、nce(physical data independence/logical data independence);Data integrity(完整性)and security;Concurrent access and robustness(recovery).,Major properties of DBMS,1.3 DBMSA Historical Perspective,File processing system(1950s-1960s)Network and hierarchical DBMS(1960s-1970s)-网状数据模型、层次数据模型-网状数据库、层次数据库(结构复杂
14、、使用很困难)Relational database systems(RDBMS)Relational model(1970,E.F.Codd)Relational database system developed(late 1970s)Relational database systems on the market(1980s)Matured relational DBMS technology(1990s),DBMSA Historical Perspective,Object-oriented database system(OODBMS)Object-relational data
15、base systems(ORDBMS)Application-oriented database systemsSpatial,temporal,multimedia,Web databasesData warehousing and data mining systems(for data analysis)（数据仓库、数据挖掘系统）,Database system versus File Processing Systems,File processing system supported by a conventional OS:New application programs mus
16、t be written when needed,and new data files are created as required.But over a long period of time,data files may be in different formats.Data files are independent of each other.Drawbacks of using file systems to store data:1、Data redundancy and inconsistency Multiple file formats,duplication of in
17、formation in different files,Drawbacks of using file systems(cont.)2、Difficulty in accessing data Need to write a new program to carry out each new task3、Data isolation multiple files and multiple formats.(Difficult to retrieve,difficult to share)4、Integrity problemsIntegrity constraints(e.g.account
18、 balance 0)become part of program codeHard to add new constraints or change existing ones,Database system versus File Processing Systems(Cont.),Drawbacks of using file systems(cont.)5、No atomicity of updates（consistent）Failures may leave database in an inconsistent state with partial updates carried
19、 outE.g.transfer of funds from one account to another should either complete or not happen at all-atomicity6、Difficult to concurrent access by multiple usersConcurrent accessed needed for performanceUncontrolled concurrent accesses can lead to inconsistenciesE.g.two people reading a balance and upda
20、ting it at the same time7、Security problems(Right person use right data)Database systems offer solutions to all the above problems!,The DBMS Marketplace,Relational DBMS companies:IBM DB2-is by some accounts the largest DBMS vendor in the world.Oracle,Sybase are among the largest database software co
21、mpanies in the world.Microsoft SQL-Server,plus Microsoft Access for the cheap DBMS on the desktop,answered by“lite”systems from other competitors.Relational companies also challenged by“object-oriented DB”companies.But countered with“object-relational”systems,which retain the relational core while a
22、llowing type extension as in OO systems.Other database products:Ingres,Paradox,Foxbase,FoxPro,dBase,The DBMS Marketplace(cont.),MySQL:is the most popular open source database for small system on web sites.MySQL is a key part of LAMP(Linux,Apache,MySQL,PHP/Perl/Python),a fast growing open source ente
23、rprise software stack.http:/PostgreSQL:is a highly scalable,open source object-relational database management system.http:/www.postgresql.orgOriginally developed by CS Dept of UC Berkeley called Postgres,Open source database:,1.4 Levels of Data Abstraction,Different usage needs different level of ab
24、straction.1)Physical level describes how a record is stored.(e.g.employee)2)Logical level:describes data stored in database,and the relationships among the data on upper level-logical level.3)View level:application programs hide details of data types.Views can also hide information(e.g.,employees sa
25、lary)for security purposes.,View of Data,An architecture for a database system,Schemas and Instances（模式与实例）,Schema the structure of the database on different level Analogous to type information of a variable in a programPhysical schema:database design of structure at the physical levelLogical schema
26、:database design of structure at the logical level Subschema:schema at view levelInstance the actual content of the database at a particular point in time Analogous to the value of a variableSimilar to types and variables in programming languages(type schema,variable instance)schema:a representation
27、 of an arrangement or plan.,（对各层抽象数据的总体描述）,View level,Physical level,(subschema),(logical schema),(physical schema),Ability to modify a schema definition at one level w.o.affecting a schema definition at a higher level.Physical Data Independence the ability to modify the physical schema without chan
28、ging the logical schemaApplications depend on the logical schemaApplications insulated from how data is structured and storedOne of the most important benefits of using a DBMS!Logical data independence:Protect application programs from changes in logical structure of data.Logical data independence i
29、s harder to achieve as the application programs are heavily dependent on the logical structure of data,Physical Independence vs.Logical independence,1.5 Data Models,Data model is a collection of conceptual tools for describing:data structuredata relationshipsdata semanticsdata constraintsDifferent l
30、evel of data abstraction needs different data model to describe：Entity-Relationship modelRelational modelOther models:object-oriented modelsemi-structured data models(XML)Older models:network model and hierarchical model,（怎样具体描述抽象数据对象）,Steps of Database Design,Requirement analysisWhat data,applicati
31、ons,and operations neededConceptual database design:A high-level description of data,constraints using E-R model or a similar high level data modelLogical database design:Convert the conceptual design into a DB schemaSchema refinementNormalization of relations:Check relational schema for redundancie
32、s and related anomalies.Physical database design:Indexing,clustering and database tuningCreate and initialize the database&Security designLoad initial data,testingIdentify different user groups and their roles,现实世界,概念模型,（数据模型）逻辑模型,物理模型,信息世界E-R 图(E-R模型),关系模型（表结构）,物理存储结构,数据库的设计步骤,半结构化数据模型,(1)Entity Re
33、lationship Model(实体-联系模型),E-R model of real worldEntities(objects)E.g.customers,accounts,bank branchEntities are described by attributesRelationships between entitiesE.g.Account A-101 is held by customer JohnsonRelationship set depositor associates customers with accountsE-R Model is widely used for
34、 database designDatabase design in E-R model usually converted to design in the relational model.Was first proposed by Pete Chen.,Entity-Relationship Model(Cont.),Example of schema in the entity-relationship model,(2)Relational Model,Example of tabular data in the relational model,customer-name,Cust
35、omer-id,customer-street,customer-city,JohnsonSmithJohnsonJonesSmith,192-83-7465019-28-3746192-83-7465321-12-3123019-28-3746,AlmaNorthAlmaMainNorth,Palo AltoRyePalo AltoHarrisonRye,Attributes,Tuple 元组,Column(field),Relation Customer,Transfer E-R diagrams into relational schema,A Sample Relational Dat
36、abase,Another example:University Database,Students,Courses,Enrolled,成绩登记表,1.6 Database Language,Database Language:Data Definition Language(DDL,数据定义语言)Data Manipulation Language(DML，数据操纵语言)Data Control Language(DCL，数据控制语言)(1)Data Definition Language Specifies a database scheme as a set of definitions
37、 of relational schemaAlso specifies storage structure,access methods and consistency constraints.DDL statements are compiled,resulting in a set of tables stored in a special file:data dictionary(metadata,元数据)E.g.CREATE TABLE account(account_number char(10),balance integer);,该SQL语句创建了表account,(1)Data
38、 Definition Language(cont.),Data dictionary(数据字典)contains metadata(i.e.,the data about data)about:Database schema Data storage structure Access methods and constraints Statistical information Authorization,(2)Data Manipulation Language(DML),Data Manipulation Language(DML)Retrieve data from the datab
39、aseInsert/delete/update data in the databaseDML also known as query languageTwo classes of languages Procedural user specifies what data is required and how to get those data(C,Pascal,Java,)Nonprocedural user specifies what data is required without specifying how to get those data(SQL,Prolog),(3)SQL
40、,SQL=DDL+DML+DCLSQL has been widely used.SQL(Structured query Language,结构化查询语言)，1975.IBM.System R.Called“SEQUEL”(Structured English QUEry Language).E.g.find the name of the customer with customer-id 192-83-7465SELECT customer-nameFROM customerWHERE customer-id=192-83-7465E.g.find the balances of all
41、 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-number,A Sample Relational Database,(3)SQL(cont.),SQL is the most widely used query language.3 kinds of usage:1)U
42、se it directly in the interactive environmentSQL Server的查询分析器Oracle的Sql*Plus,Work Sheet MySQL的mysql command line client2)Use it by host language through ODBC(open database connectivity),JDBC3)Use it by host language with embed-SQL,1.7 Database Users,Users are differentiated by the way they expect to
43、 interact with the systemNave users invoke one of the permanent application programs that have been written previously by a high level language.(普通用户)E.g.people accessing database over the web,bank tellers,clerical staffApplication programmers interact with system through SQL callsSophisticated user
44、s form requests in a database query language.E.g online analytical processing(OLAP),Data mining。（富有经验的用户）Specialized users write specialized database applications that do not fit into the traditional data processing framework.E.g CAD,KDB,ES.(专门用户，特殊用户),专业用户,How to use a database,Developed by C,C+,Fo
45、rtran,Cobol,JavaDelphi,VB,PowerBuilder,Nave users,1.8 Database Administrator,Database administrator(DBA):A special user having central control over database and programs accessing that data.DBA has the highest privilege for the database.DBA coordinates all the activities of the database system.DBA c
46、ontrols all users authority to the database.DBA has a good understanding of the enterprises information resources and needs.,1.8 Database Administrator(cont.),Database administrators duties include:Schema definitionStorage structure and access method definitionSchema and physical organization modifi
47、cationGranting of authorization for data access Routing maintenance Monitoring performance and responding to changes in requirementsSecurity for the database(e.g.periodically backup database,recovery when failure),1.9 Transaction Management(事务管理),Concurrent use is important.But cause problems.A tran
48、saction is a collection of operations that performs a single logical function in a database application-(Jim Gray)Transaction requirement:atomicity(原子性),consistence(一致性),isolation(隔离性),durability(持久性)Transaction-management component ensures that the database remains in a consistent(correct)state des
49、pite system failures(e.g.,power failures and operating system crashes)and transaction failures.-by backup and recovery subsystemConcurrency-control manager(并发控制管理器)controls the interaction among the concurrent transactions.,1.10 Database management system structure,1)Storage managerProvides interfac
50、e between the low-level data stored in the database(file system)and the application programs and queries submitted to the system.Responsible for efficient storing,retrieving,updating data in the database.Includes:Transaction managerAuthorization and integrity mangerFile manager(interaction with the
