《数据库系统基础教程DatabaseSystems.ppt》由会员分享，可在线阅读，更多相关《数据库系统基础教程DatabaseSystems.ppt（40页珍藏版）》请在三一办公上搜索。
1、Department of computer science&technology NJUST,Database Systems数据库系统,Database Systems,2.5学分上课、0.5学分上机，共3学分。学习目的：掌握数据库系统的基本概念、原理和方法；学会有效地使用数据库管理系统，完成数据库的设计和对数据库的编程。学习方法：理论结合实践。考核方式及要求：作业（20）：按时认真完成作业。实验（10）：完成上机实验并提交实验报告。闭卷考试（70）：英文试卷。,Database Systems,Reference booksJeffrey D.Ullman,Jennifer Widom.
2、数据库系统基础教程(原书第3版).岳丽华等译.机械工业出版社.2009.Abraham Silberschatz,Henry F.Korth,S.Sudarshan.Database System Concepts(Fourth Edition).机械工业出版社.http:/www-db.stanford.edu/ullman/fcdb.html,The Worlds of Database SystemThe Relational Model of Data*Design Theory for Relational DatabasesHigh-Level Database ModelsAlg
3、ebraic and Logical Query Language*The Database Language SQLConstraints and TriggersViews and IndexesSQL in a Server EnvironmentAdvanced topics in Relational Databases,A First Course in Database Systems,1 The Worlds of Database Systems,The Evolution of Database Systems Overview of a DBMS Outline of D
4、atabase-System Studies,1.1 The Evolution of Database Systems,Whats a database?In essence,a database is a collection of information that exists over a long period of time,often many years.In common parlance(说法),a database is a collection of data that is managed by a database management system(DBMS).,
5、1.1 The Evolution of Database Systems,Database Applications:Banking:all transactions(交易)Airlines:reservations(预定),schedules(航班表)Universities:registration,gradesSales:customers,products,ordersManufacturing:production,inventory(库存),orders,supply chainHuman resources:employee records,salaries,tax deduc
6、tions Databases touch all aspects of our lives.,1.1 The Evolution of Database Systems,A DBMS is expected to:Data Definition Language(DDL):create databases and specify their schema(模式).A schema is a group of collections of sentences that are expressed by DDL to describe the logical structure of the d
7、atabase completely.Data Query Language and Data Manipulation Language:query（查询）and modify（更新）the data based on the schema of the database.Support the storage of very large amounts of data:many terabytes(1012 bytes)or more,over a long period of time,allowing efficient access.,1.1 The Evolution of Dat
8、abase Systems,A DBMS is expected to:Enable durability(持久):the recovery of the database in the face of failures(故障),errors of many kinds,or intentional misuse.Support Data sharing（共享）:control access to data from many users at once;without allowing the action of one user to affect other users(isolatio
9、n 隔离性);without actions on the data to be performed partially but not completely(atomicity 原子性).,1.1 The Evolution of Database Systems,Database systems evolved from file systems.,Data redundancy(冗余)and inconsistencyMultiple file formats,duplication of information in different files.Difficulty in acce
10、ssing data Need to write a new program to carry out each new task.,1.1 The Evolution of Database Systems,Database systems evolved from file systems.,Integrity(完整性)problemsIntegrity constraints(约束)(e.g.account balance 0)become“buried(埋入)”in program code rather than being stated explicitly(明确地).Hard t
11、o add new constraints or change existing ones.Atomicity of updatesFailures(故障)may leave database in an inconsistent state with partial updates carried out.Example:Transfer of funds(资金)from one account to another should either complete or not happen at all.,1.1 The Evolution of Database Systems,Datab
12、ase systems evolved from file systems.,Concurrent(并发)access by multiple usersUncontrolled concurrent accesses can lead to inconsistencies.Example:Two people reading a balance and updating it at the same time.Security problemsHard to provide user access to some,but not all,data.,1.1 The Evolution of
13、Database Systems,Late 1960s and 1970s:Hard disks allow direct access to dataNetwork and hierarchical data models in widespread useThe early DBMS didnt support high-level query languages.,1.1 The Evolution of Database Systems,1970:Ted Codd defines the relational data modelReceived the ACM Turing Awar
14、d in 1981IBM Research begins System R prototypeMain idea:organize all the data in database as tables called relations.Queries could be expressed in a very high-level language to increase the efficiency of database programmers.,1.1 The Evolution of Database Systems,1980s:Research relational prototype
15、s evolve into commercial(商用的)systemsSQL becomes industrial standardParallel and distributed(分布式)database systemsObject-oriented database systems,1990s:Large decision support and data-mining(数据挖掘)applicationsLarge multi-terabyte data warehouses(数据仓库)Emergence(出现)of Web commerce(商务),2000s:XML(可扩展模型语言)
16、and XQuery standards,1.2 Overview of a Database Management System,A DBMS is a powerful tool for creating and managing large amounts of data efficiently and allowing it to persist over long periods of time,safely.,1.2 Overview of a Database Management System,1.2 Overview of a Database Management Syst
17、em,Application 1,Application 2,.,Application n,DBMS,database,Database Management System,单线框 系统成分双线框 内存中的数据结构实线 控制和数据流虚线 数据流,1.2 Overview of a Database Management System,Two types of user:Conventional users and application programs.A database administrator(DBA):a person or persons responsible for the
18、 structure or schema of the database.Three kinds of commands to the DBMS:Queries,updates:commands to affect the content of the database or extract(提取)data from the database.Transaction commands:commands to tell the transaction manager when transactions begin and end.DDL commands:commands to change t
19、he schema of the database or create a new database by DBA.,1.2 Overview of a Database Management System,Storage and buffer manager:includes the storage manager and the buffer manager:storage manager:controls the placement of data on disk and its movement between disk and main memory,keeps track(跟踪)o
20、f the location of files on the disk and obtains the block or blocks containing a file on request from the buffer manager.buffer manager:partition(分割)the available main memory into buffers.,1.2 Overview of a Database Management System,The kinds of information:Data:the contents of the database itself.
21、Metadata(元数据):the database schema(模式)that describes the structure of,and constraints(约束)on,the database.Indexes(索引):data structures that support efficient access to the data.Statistics(统计):information gathered and stored by the DBMS about data properties.Log(日志)records:information about recent chang
22、es to the database,these support durability(持久性)of the database.,单线框 系统成分双线框 内存中的数据结构实线 控制和数据流虚线 数据流,1.2 Overview of a Database Management System,Transaction Processing:Transaction(事务):a group of operations that must appear to have been executed together sequentially,as a unit.Transaction manager:Co
23、ncurrency-control(并发控制)manager,or scheduler(调度器):responsible for assuring atomcity(原子性)and isolation(隔离性)of transactions.Lock.Logging and recovery manager:responsible for the durability(持久性)of transactions.Logging.,1.2 Overview of a Database Management System,Properties:ACIDAtomicity:either all of a
24、 transaction be executed or none of it is.Consistency(一致性):keep consistent state.Isolation(隔离性):When two or more transactions run concurrently,their effects must be isolated from one another.Durability(持久性):If a transaction has completed its work,its effect should not get lost while the system fail,
25、even if it fails immediately after the transaction completes.,1.2 Overview of a Database Management System,Query processor：Query compiler(编译器):Translates the query into an internal form called a query plan.Query parser(分析器):build a tree structure.Query preprocessor(预处理器):perform the initial query pl
26、an Query optimizer(优化器):Execution engine:It executes each of the steps in the chosen query plan.It gets the data from the database into buffers in order to manipulate that data.It needs to interact(交流)with the scheduler(调度器)to avoid accessing data that is locked,and with the log manager to make sure
27、 that all database changes are properly logged.,单线框 系统成分双线框 内存中的数据结构实线 控制和数据流虚线 数据流,1.2 Overview of a Database Management System,Client/Server(C/S)architecture,DB Client,DB Client,DB Server,JavaC/C+PowerBuilderDelphiVB,MS AccessMS SQL ServerSybase MySQLOracleIBM DB2,Client:the process sending querie
28、s or other commands.Server:the process executing queries or other commands,1.3 Outline of Database-System Studies,Three types of ideas related to database systems:Design：How to build a useful database?Requirement analysis,data modeling,relational modeling.Programming：How to express queries and other
29、 operations on database?Iimplementation：How to build a DBMS?,1.3.1 Database Design,Chapter 2,3 and 4 cover design.Chapter 2:The Relational Model of DataChapter 3:Design Theory for Relational DatabasesChapter 4:High-Level Database Models,1.3.2 Database Programming,Chapter 5 through 10 cover database
30、programming.Chapters 5:Algebraic and Logical Query Language Chapters 6:The Database Language SQLChapters 7:Constraints and TriggersChapters 8:Views and IndexesChapters 9:SQL in a Server EnvironmentChapters 10:Advanced topics in Relational Databases,1.3.3 An example of database application,Well build
31、 a marketing database system for a sale company.It will manage all the following information:1.Manage all department information in the company(such as“Shanghai sale department”,“JiangSu sale department”).Also manage every salesman information in those departments including exclusive(唯一的)employee nu
32、mber,ID card number,and some private information(such as name,gender,birthday and phone number).By the way one of salesmen will act as the department manager in his department.,1.3.3 An example of database application,2.Manage a group of customers:name,province(省),city,company name,phone number.3.Ma
33、nage all the product information:manufacturers(e.g.Chunlan,Hailer),types(e.g.motorcycle,air conditioner),specifications(e.g.MT125,RE1500),prices,descriptions.4.Manage sales order which record each deal(交易)has been done.Notes:every sales order contains an unique order No.,sign date,a corresponding cu
34、stomer,a salesman,and at least one kind of products.Any product in the order should have its quantity and unit price which will be used to calculate the total prices.,为某商品销售公司构作一个销售业务数据库系统，该系统至少应管理以下内容：公司分为多个下属部门(Department)，如“江苏销售部”，“上海销售部”等。每个部门有若干销售员(Salesman)，每个销售员有唯一的员工号(如“S0025”)和身份证号，也包含姓名、性别
35、、出生日期、电话等信息。一个销售员仅属于一个部门，且每个部门有一个销售员担任部门经理。系统应管理客户信息(Customer)，包含：名称、省、市、单位名称、电话等。系统应管理所有销售的商品(Product)：制造商(如“春兰”、“海尔”)、种类(如“摩托车”、“空调机”)、规格(如“MT125”型摩托车、“RE1500”型空调)、价格、功能及性能描述。该系统用销售订单(Sales order)表示销售业务。每个订单有一个订单号和签订日期，且对应一个客户和一个销售员。一个订单至少销售一种产品，并可销售多种产品，且每种产品应确定其销售数量和单价，以计算销售金额。,1.3.3 An example
36、of database application,Entity/Relationship Model,1.3.3 An example of database application,Relational Data Model：Customer(custid,name,prov,city,phone,unit)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)Salesorder(orderno,signd
37、ate,empid,custid)Salesitem(orderno,lineno,prodid,singlecost,quantity),1.3.3 An example of database application,relational algebra expressions:1 Find the IDs and phone numbers of all salesmen named“张平”.empid,phone(name=张平(Salesman)2 Find the names and phone numbers of all customers who have transacte
38、d(交易)with the salesman whose ID is“E0056”.name,phone(empid=E0056(Salesorder Customer),relational operations by SQL:1 Find the IDs and phone numbers of all salesmen named“张平”.SELECT empid,phone FROM salesman WHERE name=张平;2 Find the names and phone numbers of all customers who have transacted(交易)with the salesman whose ID is“E0056”.SELECT name,phone FROM salesorder NATURAL JOIN customer WHERE empid=E0056;,Database Systems,作业：,阅读第1章。通过上网或其它途径查阅资料，了解数据库的概念，数据库技术的发展与现状，并详细了解一个库存管理系统的信息需求和功能需求：需要管理哪些数据？数据的结构及关系是怎样的？对这些数据通常有哪些操作？,
