DBMS TECHNOLOGY AND APPLICATION

1. DBMS TECHNOLOGY AND APPLICATION Zeng zhongping Email: zpzeng100@sohu.com Course Web page: http://www.hust.edu.cn 1

2. Why Study Databases?? Shift from computation to information at the “low end”: scramble to webspace (a mess!) at the “high end”: scientific applications Datasets increasing in diversity and volume. Digital libraries, interactive video, Human Genome project, EOS project ... need for DBMS exploding DBMS encompasses most of CS OS, languages, theory, “AI”, multimedia, logic • Science Computing • Processing Control • Data Processing(70%) ?

3. Why Study DBMS? • We often use DBMS…….. • Banking: all transactions • Airlines: reservations, schedules • Universities: registration, grades • Sales: customers, products, purchases • Online retailers: order tracking, customized recommendations • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions But DBMS just like a black box. • Try to open the black box • Database file system/pages/records/bytes/bits • Principles of DBMS and implement technologies • Learn more about DBMS

4. Benefits for… • More opportunities in the job market • Career development • Computer skills

5. Text Books and References Raghu Ramakrishnan, Database Management Systems, McGraw Hill, 3rd edition, 2003 (text book). R. Elmasri, S.B. Navathe, Fundamentals of Database Systems, 4th edition, Addison-Wesley, 2004. A. Silberschatz, H.F. Korth, S. Sudarshan, Database System Concepts, McGraw Hill, 4th edition, 2002. 萨师煊，王珊：数据库系统概论(第四版) ，高等教育出版社，2000.中国人民大学 周立柱、冯建华等《SQL Server数据库原理--设计与实现》 5

6. 采用直观的方式描述所有概念，并以结构清晰的图示和例子代替形式化的证明。采用直观的方式描述所有概念，并以结构清晰的图示和例子代替形式化的证明。 • 以银行数据库实例生动地解释重要概念，增强了本书的易读性。 • 专门用三章来介绍特定的商品化数据库系统Oracle、Microsoft SQL Server和IBM DB2的细节。 • 配有关于网状模型和层次模型的联机附录 (英文)。

7. 1950，负责全校经济和管理类公共数学课的教学工作，文科数学。 • 83年，与王珊第一版 • 数据库泰斗 • 多次奖项 • 多次规划教材 • 使用学生众多

8. Course Outline Introduction to Database Management Systems Entity-Relationship Model The Relational Data Model Relational Algebra SQL Relational Database Design: Normalization MS SQL server 8

9. Grading Assignments 10 % Experiments 40 % Final Exam 60 % 9

10. How to learn well? • 网络资源 • 精品课程 • 网络视频

11. Introduction to DBMS WhatIsa DBMS? Functions of DBMS? Why UseDBMS? What About DBMS History? DBMS Architecture?

12. WhatIsa DBMS? • Data (数据) • Database (数据库) • DBMS (数据库管理系统) • DBS(数据库系统)

13. Data • The representative forms of information, including facts, concepts, rules, or any other kind of knowledge. • numbers, characters, images or other methods of recording. • represent values that can be stored, processed, and transmitted by electronic systems，especially the computer.

14. Data instance • 学生档案中的学生记录 （李明，男，1972，江苏，计算机系，1990） • 数据的解释 • 语义：学生姓名、性别、出生年月、籍贯、所在系别、入学时间 • 解释：李明是个大学生，1972年出生，江苏人，1990年考入计算机系 • 数据的形式不能完全表达其内容

15. Database A very large, integrated collection of data stored in the computer. • Low reductdance • Share ability • Data independence • Integrity management and control

16. A very large, integrated collection of data stored in the computer Models real-world enterprise. Entities (e.g., students, courses) Relationships (e.g., Tarkan is taking CENG302) A Database Management System (DBMS)is a software package designed to store and manage databases. Database Management System (DBMS)

17. Database management system examples • Oracle • SQL server • Sybase • Mysql • DB2 • INFORMIX • VFP

18. DBMS FUNCTION • Data Definition • DDL,Data Definition Language • Data Manipulation • DML,Data Manipulation Language • Operation Management • Setup and Maintain

19. DBMS FUNCTION(continue) • Provide User Interface • Provide Data Manipulation Language • Provide Data Definition Language • Provide some tools • Manage database • Maintain database

20. Development tool kits/language • C,VC,C++ • VB • POWERBUILDER • DELPHI • JAVA • ASP/JSP • ODBC/JDBC • Database language (DDL,DML)

21. Open Database Connect • 开放数据库互连（ODBC）是MICROSOFT提出的数据库访问接口标准。 • 开放数据库互连定义了访问数据库的API一个规范，这些API独立于不同厂商的DBMS，也独立于具体的编程语言。 PB JAVA C++ VB ODBC API ODBC 驱动 Sql server Oracle sybase access

22. Java Database Connect(JDBC) Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。

23. 应用系统 应用开发工具 操作系统 DBMS 硬件

24. Database Management System (DBMS) • DBMS contains information about a particular enterprise • Collection of interrelated data -- Database • Set of programs to access the data • An environment that is both convenient and efficient to use • Database applications • Banking: all transactions • Airlines: reservations, schedules • Universities: registration, grades • Sales: customers, products, purchases • Online retailers: order tracking, customized recommendations • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions • Databases touch all aspects of our lives

25. Database System (DBS) • Database • Operation system • DBMS • Application system • DBA • Users 数据库系统一般由数据库、操作系统、数据库管理系统（及其开发工具）、应用系统、数据库管理员（DBA）和用户构成。

26. Database system 用户 用户 用户 USER USER USER Development Tools kits Application 应用系统 开发工具 DBMS DBA OS 数据库管理员 DB

27. Database system Architecture The architecture of a database systems is greatly influenced by the underlying computer system on which the database is running: • Centralized • Client-server • Parallel (multi-processor) • Distributed • Browser/Server

28. Centralized（集中式系统） DBMS和应用程序以及与用户终端进行通信的软件等都运行在一台宿主计算机上，所有的数据处理都是在宿主计算机中进行。宿主计算机一般是大型机、中型机或小型机。应用程序和DBMS之间通过操作系统管理的共享内存或应用任务区来进行通信，DBMS利用操作系统提供的服务来访问数据库。终端通常是非智能的，本身没有处理能力。 集中系统的主要优点是：具有集中的安全控制，以及处理大量数据和支持大量并发用户的能力。集中系统的主要缺点是：购买和维持这样的系统一次性投资太大,并且不适合分布处理。 • PC(个人计算机系统) 与大型系统不同，通常个人计算机（微机）上的DBMS功能和数据库应用功能是结合在一个应用程序中的，这类DBMS（如FoxPro、Acssce）的功能灵活，系统结构简洁，运行速度快，但这类DBMS的数据共享性、安全性、完整性等控制功能比较薄弱。

29. Client-server(客户/服务器系统) • 在客户/服务器(简记为C/S）结构的数据库系统中，数据处理任务被划分为两部分：一部分运行在客户端，另一部分运行在服务器端。客户端负责应用处理，数据库服务器完成DBMS的核心功能。 • 这种模型中，客户机上都必须安装应用程序和工具，客户端比较庞大、负担比较重，而且系统安装、维护、升级和发布困难，从而影响效率。

30. Distributed（分布式系统） 一个分布式数据系统由一个逻辑数据库组成，整个逻辑数据库的数据，存储在分布于网络中的多个结点上的物理数据库中。在当今的客户/服务器结构的数据库系统中，服务器的数目可以是一个或多个。当系统中存在多个数据库服务器时就形成了分布系统。

31. Distributed system

32. Browser/Server（浏览器/服务器系统） 随着Internet的迅速普及，出现了三层客户机/服务器模型：客户机→应用服务器→数据库服务器。这种系统称为浏览器/服务器（Browser/Server，简记为B/S）系统。

33. the increasing of the great number of information resources KB,MB,GB,TB,PB,EB,ZB,YB..... Traditional data management technology can not meet the increasing needs Massive benefits through data analysis and processing Weather forecast airspace Bank and stocks market DBMS is the best way to solve the above questions Why We Need a DBMS?

34. Why Use a DBMS? Data independence and efficient access. (数据独立性和高效访问) Reduced application development time. （减少应用程序开发时间） Data integrity and security. （数据完整性和安全性） Uniform data administration. （统一的数据管理） Concurrent access, recovery from crashes. （并发控制和灾难恢复）

35. Data management history • Data management • the control of data handling operations--such as acquisition, analysis, translation, coding, storage, retrieval, and distribution of data • Development • Manual management (1940s—mid 1950s) • File system management (1950s—mid 1960s) • Database management (1960s --)

36. Manual management • 1940s -1950s • Focus on • scientific computing • Hardware • no direct access storage device • Software system • no operation system • Data processing • batch processing

37. File management • 1950s and early 1960s: • Data processing using magnetic tapes for storage • Tapes provide only sequential access • Punched cards for input • 1960s File system（In the early days, database applications were built directly on top of file systems） • Demand • scientific management • Hardware • disk、disk drum • Software • file system • Processing • OLAP、batch processing

38. Drawbacks of using file systems • Drawbacks of using file systems to store data: • Data redundancy and inconsistency • Multiple file formats, duplication of information in different files • Difficulty in accessing data • Need to write a new program to carry out each new task • Data isolation — multiple files and formats • Integrity problems • Integrity constraints (e.g. account balance > 0) become “buried” in program code rather than being stated explicitly • Hard to add new constraints or change existing ones

39. Drawbacks of using file systems(Cont.) • Atomicity of updates • Failures 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 • Concurrent access by multiple users • Concurrent accessed needed for performance • Uncontrolled concurrent accesses can lead to inconsistencies • Example: Two people reading a balance and updating it at the same time • Security problems • Hard to provide user access to some, but not all, data • Database systems offer solutions to all the above problems

40. History of Database Systems • Late 1960s and 1970s: • Hard disks allow direct access to data • Network and hierarchical data models in widespread use • Ted Codd defines the relational data model • Would win the ACM Turing Award for this work • IBM Research begins System R prototype • UC Berkeley begins Ingres prototype • High-performance (for the era) transaction processing

41. History (cont.) • 1980s: • Research relational prototypes evolve into commercial systems • SQL becomes industrial standard • Parallel and distributed database systems • Object-oriented database systems • 1990s: • Large decision support and data-mining applications • Large multi-terabyte data warehouses • Emergence of Web commerce • 2000s: • XML and XQuery standards • Automated database administration

42. Database - Advantages • Reduced data redundancy（冗余） • Reduced updating errors and increased consistency（一致性） • Greater data integrity and independence from applications programs （完整性和独立性） • Improved data access to users through use of host and query languages （访问能力） • Improved data security（数据安全） • Reduced data entry, storage, and retrieval costs（减少数据存取开销） • Facilitated development of new applications program（为开发新程序提供便利

43. Summary • DBMS - how come today • Demand driven by application requirements • Software development • Hardware development

44. Assignment • 1、试述数据、数据库、数据库管理系统、数据库系统的概念； • 2、使用数据库有什么好处？ • 3、试述文件系统和数据库系统的区别与联系。 • 4、名词解释：数据定义语言；数据操纵语言。 • 5、试述数据库系统的功能。 • 6、以图书馆管理系统为例，试述开发一个数据库应用程序的大致步骤。

45. 概念模型和三级模式结构

46. shift from the real world to Concept schema 数据库中用数据模型来抽象、表示和处理现实世界中的数据。 • 客观对象的抽象过程---两步抽象 • 现实世界中的客观对象抽象为概念模型； • 把概念模型转换为某一DBMS支持的数据模型。 概念模型是现实世界到机器世界的一个中间层次。

47. User view DBMS Architecture data Computer view DBMS- schema and data model shift from the real world to digital world

48. Concept schema • 概念模型的用途 • 概念模型用于信息世界的建模 • 是现实世界到机器世界的一个中间层次 • 是数据库设计的有力工具 • 数据库设计人员和用户之间进行交流的语言 • 对概念模型的基本要求 • 较强的语义表达能力，能够方便、直接地表达应用中的各种语义知识 • 简单、清晰、易于用户理解。

49. flexibility redundant and inconsistent multiple databases

50. Schemas • External schema for user views • defines the view of the data presented to the application programs • Conceptual schema integrates external schemata • the logical structure of the database • The primary objective of this conceptual schema is to provide a consistent definition of the meanings and interrelationship of data which can be used to integrate, share, and manage the integrity of data. • Internal schema that defines physical storage structures • describes the internal formats of the data stored in the database