數據庫系統基礎教程database systems(1)

1、Department of computer science & technology NJUST,Database Systems數據庫系統,Database Systems,2.5學分上課、 0.5學分上機 ，共3學分。 學習目的：掌握數據庫系統的基本概念、原理和方法；學會有效地使用數據庫管理系統，完成數據庫的設計和對數據庫的編程。 學習方法：理論結合實踐。 考核方式及要求：作業(yè)（20％）：按時認真完成作業(yè)。

2、實驗（10％）：完成上機實驗并提交實驗報告。閉卷考試（70％）：英文試卷。,,,Database Systems,Reference booksJeffrey D.Ullman, Jennifer Widom. 數據庫系統基礎教程(原書第3版). 岳麗華等譯. 機械工業(yè)出版社. 2009.Abraham Silberschatz, Henry F.Korth, S.Sudarshan. Database System Conce

3、pts (Fourth Edition). 機械工業(yè)出版社. http://www-db.stanford.edu/~ullman/fcdb.html,,,The Worlds of Database SystemThe Relational Model of Data*Design Theory for Relational DatabasesHigh-Level Database ModelsAlgebraic and

4、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 Data

5、base Systems,,,The Evolution of Database Systems Overview of a DBMS Outline of Database-System Studies,1.1 The Evolution of Database Systems,,,What’s a database?In essence, a database is a collection of information t

6、hat 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).,1.1 The Evolution of Database Systems,,,Database

7、 Applications:Banking: all transactions(交易)Airlines: reservations(預定), schedules(航班表)Universities: registration, gradesSales: customers, products, ordersManufacturing: production, inventory(庫存), orders, supply chai

8、nHuman resources: employee records, salaries, tax deductions 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

9、 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 database completely.Data Query Language and Data Manipulation Lang

10、uage: 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

11、.1 The Evolution of Database 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（共享）: contro

12、l access to data from many users at once; without allowing the action of one user to affect other users(isolation 隔離性); without actions on the data to be performed partially but not completely(atomicity 原子性).,1.1 The Evo

13、lution of Database Systems,,,Database systems evolved from file systems.,Data redundancy(冗余) and inconsistencyMultiple file formats, duplication of information in different files.Difficulty in accessing data Need to w

14、rite 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 “

15、buried(埋入)” in program code rather than being stated explicitly(明確地).Hard to add new constraints or change existing ones.Atomicity of updatesFailures(故障) may leave database in an inconsistent state with partial update

16、s 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,,,Database systems evolved from file systems.,Concurrent(并發(fā)) ac

17、cess 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

18、all, data.,1.1 The Evolution of Database Systems,,,Late 1960s and 1970s:Hard disks allow direct access to dataNetwork and hierarchical data models in widespread useThe early DBMS didn't support high-level

19、query languages.,1.1 The Evolution of Database Systems,,,1970:Ted Codd defines the relational data modelReceived the ACM Turing Award in 1981IBM Research begins System R prototypeMain idea: organize all the data in d

20、atabase 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 relat

21、ional prototypes evolve into commercial(商用的) systemsSQL becomes industrial standardParallel and distributed(分布式) database systemsObject-oriented database systems,1990s:Large decision support and data-mining(數據挖掘) app

22、licationsLarge multi-terabyte data warehouses(數據倉庫)Emergence(出現) of Web commerce(商務),2000s:XML(可擴展模型語言) and XQuery standards,1.2 Overview of a Database Management System,,,A DBMS is a powerful tool for creating and ma

23、naging 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 System,,,Application 1,Applicat

24、ion 2,...,Application n,DBMS,,,,database,,Database Management System,,單線框 系統成分雙線框 內存中的數據結構實線 控制和數據流虛線 數據流,1.2 Overview of a Database Management System,,,Two types of user:Conventional users and applic

25、ation programs.A database administrator(DBA): a person or persons responsible for the structure or schema of the database.Three kinds of commands to the DBMS:Queries , updates : commands to affect the content of the

26、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 the schema of the database or create a new

27、 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

28、between disk and main memory, keeps track(跟蹤) of 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 m

29、emory into buffers.,1.2 Overview of a Database Management System,,,The kinds of information:Data: the contents of the database itself.Metadata(元數據): the database schema(模式) that describes the structure of, and constrai

30、nts(約束) 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 rece

31、nt changes to the database, these support durability(持久性) of the database.,單線框 系統成分雙線框 內存中的數據結構實線 控制和數據流虛線 數據流,1.2 Overview of a Database Management System,,,Transaction Processing:Transaction(事務): a gr

32、oup of operations that must appear to have been executed together sequentially, as a unit.Transaction manager: Concurrency-control(并發(fā)控制) manager, or scheduler(調度器): responsible for assuring atomcity(原子性) and isolation

33、(隔離性) 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 tra

34、nsaction 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 trans

35、action has completed its work, its effect should not get lost while the system fail, even if it fails immediately after the transaction completes.,1.2 Overview of a Database Management System,,,Query processor：Query com

36、piler(編譯器): Translates the query into an internal form called a query plan. Query parser(分析器): build a tree structure. Query preprocessor(預處理器): perform the initial query plan Query optimizer(優(yōu)化器):Execution engine: I

37、t 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 lo

38、cked, and with the log manager to make sure that all database changes are properly logged.,單線框 系統成分雙線框 內存中的數據結構實線 控制和數據流虛線 數據流,1.2 Overview of a Database Management System,,,Client/Server (C/S) architect

39、ure,,DB Client,,DB Client,DB Server,JavaC/C++PowerBuilderDelphiVB…,MS AccessMS SQL ServerSybase MySQLOracleIBM DB2…,Client: the process sending queries or other commands.Server: the process executing queries

40、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：

41、How to express queries and other 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 f

42、or Relational DatabasesChapter 4: High-Level Database Models,1.3.2 Database Programming,,,Chapter 5 through 10 cover database programming.Chapters 5: Algebraic and Logical Query Language Chapters 6: The Database Langu

43、age 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,,,We'll

44、 build 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 depar

45、tment”). Also manage every salesman information in those departments including exclusive(唯一的) employee number, ID card number, and some private information (such as name, gender, birthday and phone number). By the way on

46、e 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.Manage all the pro

47、duct information: manufacturers (e.g. Chunlan, Hailer ), types (e.g. motorcycle, air conditioner ), specifications (e.g. "MT125", "RE1500" ), prices, descriptions. 4.  Manage sales order whic

48、h record each deal(交易) has been done. Notes: every sales order contains an unique order No. , sign date, a corresponding customer, a salesman, and at least one kind of products. Any product in the order should have

49、its quantity and unit price which will be used to calculate the total prices.,,,為某商品銷售公司構作一個銷售業(yè)務數據庫系統，該系統至少應管理以下內容：公司分為多個下屬部門(Department)，如“江蘇銷售部”，“上海銷售部”等。每個部門有若干銷售員(Salesman)，每個銷售員有唯一的員工號(如“S0025”)和身份證號，也包含姓名、性別、出生日期、

50、電話等信息。一個銷售員僅屬于一個部門，且每個部門有一個銷售員擔任部門經理。 。 系統應管理客戶信息(Customer)，包含：名稱、省、市、單位名稱、電話等。 系統應管理所有銷售的商品(Product)：制造商(如“春蘭”、“海爾”)、種類(如“摩托車”、“空調機”)、規(guī)格(如“MT125”型摩托車、“RE1500”型空調)、價格、功能及性能描述。 該系統用銷售訂單(Sales order)表示銷售業(yè)務。每個訂單有一個訂單號和簽訂

51、日期，且對應一個客戶和一個銷售員。一個訂單至少銷售一種產品，并可銷售多種產品，且每種產品應確定其銷售數量和單價，以計算銷售金額。,1.3.3 An example of database application,Entity/Relationship Model,1.3.3 An example of database application,,,Relational Data Model：Customer (custid

52、, 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, signdate, empid, custid)Sa

53、lesitem (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=‘張平

54、' (Salesman))2 Find the names and phone numbers of all customers who have transacted(交易) with the salesman whose ID is “E0056”. Пname,phone(σempid='E0056' (Salesorder Customer)),relational opera

55、tions 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

56、(交易) with the salesman whose ID is “E0056”. SELECT name, phone FROM salesorder NATURAL JOIN customer WHERE empid = 'E0056';,Database Systems,作業(yè)：,,,閱讀第1章。通過上網或其它途徑查閱資料，了解數據庫的概念，數據庫技術的發(fā)展與現狀，并詳細了解一個庫存