DATABASEch1

2020-03-01 115浏览

  • 1.DATABASE principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.Chapter 1 Introduction    1.1 Fundamental Database Concepts 1.2 Database Users 1.3 Overview of Relational and Object–Relational DBMS Zhejiang Sci-Tech University Lican Huang 2
  • 3.1.1 Fundamental Database Concepts  Database management system ( Database system or DBMS) is a program product for keeping computerized records about an enterprise. wholesale business—sales data university ---student records library --- book information The collection of records kept for a common purpose ( store and retrieve , etc. ) is known as a Database. Zhejiang Sci-Tech University Lican Huang 3
  • 4.Chapter 1 Introduction  Zhejiang Sci-Tech University Lican Huang 4
  • 5.History of Database Systems  IBM’s IMS(information management S ystem), 1968, Hierarchical data Mod el, where different kinds of records re late to one another in a hierarchical f orm Zhejiang Sci-Tech University Lican Huang 5
  • 6.Hierarchical data Model 系 系编号 系名 办公地点 根节点 兄弟节点 (Sibling) 教研室 教研室编号 教研室名 学生 学号 姓名 成绩 叶节点 教员 职工号 姓名 研究方向 叶节点 Zhejiang Sci-Tech University Lican Huang 6
  • 7.Network data model   Cullinet Software’s IDMS, early 1970s , from 1971 CODASYL report of an in dustry database task group. Network data model , a generation of the hierarchical model (1) more than one node have no parents.(2) one no de has more than one parent. Zhejiang Sci-Tech University Lican Huang 7
  • 8.Legacy systems  The main drawback of products with hierarchical data model or network data model was that queries against the data were difficult to pose and execute, normally requiring a program written by an expert programmer who understand what could be a complex navigational structure of the data. Zhejiang Sci-Tech University Lican Huang 8
  • 9.The Relational and ObjectRelational Models    Relational model provides a flexible capability to allow non-programmers to pose general queries quickly and easily. A DBMS that utilizes the relational model is known as a relational DBMS, or RDBMS. A DBMS that utilizes the object-relational model is referred to as an ORDBMS product. ORDBMS is an evolutionary extension of RDBMS, so it is also written as RDBMS/ORDBMS. Zhejiang Sci-Tech University Lican Huang 9
  • 10.The commercial database systems       ORACLE DB2 Universal Database DB2 UDB INFORMAX DB2 SQL Server Mysql Zhejiang Sci-Tech University Lican Huang 10
  • 11.A Relational Database Example  Student enrollment records in a university Page 4 Figure 1.1a Zhejiang Sci-Tech University Lican Huang 11
  • 12.A Object-Relational Database Example  Student enrollment records in a university Page 5 Figure 1.1b Zhejiang Sci-Tech University Lican Huang 12
  • 13.Database Users  End users—Interactive users casual users—Users accessing the DBMS with SQL queries Naive users –Users accessing the DBMS through menus Zhejiang Sci-Tech University Lican Huang 13
  • 14.Database Users  Application programmers writes the menu applications used by naïve users  Database administrators (DBA) A DBA is a computer professional responsible for the design and maintenance of the database. Zhejiang Sci-Tech University Lican Huang 14
  • 15.1.3 Overview of Relational and Object-Relational DBMS Chapter 2 The Relational Model Relational rules RULES of Relational Model. Tables have followed rules so all commercial products are the same, like typewriter keyboards. For years these rules were as in Sect. 2.3. E.g., no column of a table should have a complex column value (a record), only a simple type (an integer). Si milarly, can't have multiple values for a column of a row. But there is coming to be a new model, known as the Object-Relational model, that does allow compl ex column values (and collection types aswell:Can have a Set, Multiset, or List of values in a single ro w column).  Zhejiang Sci-Tech University Lican Huang 15
  • 16.1.3 Overview of Relational and Object-Relational DBMS Chapter 2 The Relational Model   Relational Algebra. A non-machine que ry language that gives a good idea of p ower of querylanguages:make new ta bles out of old tables by simple operati ons. E.g., List cno, cname for courses m eeting MW2. Rel. Alg. (AGENTS where city = 'New York) [aid, aname, percent] Gives us simplest possible approach un derstanding what SQL is aiming at.  Zhejiang Sci-Tech University Lican Huang 16
  • 17.1.3 Overview of Relational and Object-Relational DBMS Chapter 3 Basic SQL Query language  SQL queries, already seen. Turns out to ha ve more power than Rel. Alg. Also covers how to Insert new row in table, Delete old r ows from table, Update column values of e xisting rows in table.  SQL is also in the midst of change, changin g from Relational model (SQL-92, Oracle R 7) to Object-Relational model (SQL-3, DB2 V2, Oracle R8). Zhejiang Sci-Tech University Lican Huang 17
  • 18.1.3 Overview of Relational and Object-Relational DBMS Chapter 4 The Object-Relational Model Object-Relational SQL (Informix, Or acle). Now we CAN perform the quer y just named, by creating a table wit h one column (abstract_word) permit ting MULTIPLE values. Zhejiang Sci-Tech University Lican Huang 18
  • 19.1.3 Overview of Relational and Object-Relational DBMS Chapter 5 Programs to Access a Database  C programs with embedded SQLstatements:Embedded SQL. Idea is to present a Menu to naive users so they never have to understand SQL. Reason:complex job to write SQL, bank tellers and airline reservation clerks don't want to have to do complex stuff like this. DANGEROUS to let people in a hurry try to update data using SQL. Zhejiang Sci-Tech University Lican Huang 19
  • 20.1.3 Overview of Relational and Object-Relational DBMS       Chapter 6. Some of what DBAs do. Design database. Lots of complex c ommands to construct a database. Begin with Logical Design. Logicaldesign:break down all the data into tables so tables have good pro perties. E.g., how handle employee with unknown number of dependents? Can get EXTREMELY complicated. E.g., relatively simple schoolapplication:Departments contain teachers an d are responsible for offering subjects, made up of multiple classes. The subjects have prerequisites. The classes are offered in given periods in specific rooms. Students have schedules (each term), drop and add classe s, get grades, have library records, tuition payments, health insurance, etc. How break all these up? One basic idea is that entities (real worldobjects:students, rooms, even cl ass-periods) each deserve their own table. They have attributes (student n ame and id, room number and floor, period length and howgrouped:3/we ek, 2/week). There are relationships betweenentities:above, enrollment is a relationshi p between student and course. But in a more complex scheme, need to rel ate teachers with periods, rooms, and subjects to form a class offering, the n the class offering and student are related in a student schedule and a dist inct class schedule. Zhejiang Sci-Tech University Lican Huang 20
  • 21.1.3 Overview of Relational and Object-Relational DBMS  Chapter 7. Commands for creating databases, creating and loading tables, (performance related issues, such as indexes, come later). Integrity. Set up rules for how data can change. Rules keep errors from occurring because of inexperienced application writers. Rules can be treated as data, so high level designers know what is happening, not buried in millions of lines of applications. Creating Views. Views are virtual tables, pretend tables created out of real base tables. DBA creates these so specific application programmers have an easier time (fewer fields to learn), easy to change DB without breaking old applications. Security. What users can look at/change salary values for employees. Tax accountants can look at, can't change. Programmers have very low data security. Zhejiang Sci-Tech University Lican Huang 21
  • 22.1.3 Overview of Relational and Object-Relational DBMS  Chapter 10. Update Transactions. When updates are performed on a database, we need some way to guarantee that a set of updates will succeed all at once or not at all—it is unacceptable, for example, that a transfer of money from one account to another should succeed in taking money out of the first account, but then, perhaps because of a system crash, fail in adding the transferred quality into the second account. Zhejiang Sci-Tech University Lican Huang 22
  • 23.Chapter 2 The Relational Model  ..\王姗\database1.pdf Zhejiang Sci-Tech University Lican Huang 23
  • 24.Chapter 2 The Relational Model    The CAP Database ( Customers, Agents. And Products) Figure 2.1 page 27 Figure 2.2 page 28 Zhejiang Sci-Tech University Lican Huang 24
  • 25.2.2 Naming the parts of a database Database is defined to be a set of named t ables, or relations. CAP = { CUSTERS, AGENTS, PRODUCTS,ORDERS}  Heading of a table– relational schema Head(CUSTOMERS) = {cid, cname,city,discnt} Head(CUSTOMERS) = cid cname city discnt Zhejiang Sci-Tech University Lican Huang 25
  • 26.2.2 Naming the Parts of a Database  The set of rows, or tuples, in a table i s referred to as the content of the tab le, and the number of such rows is so metimes referred as the cardinality o f the table. Zhejiang Sci-Tech University Lican Huang 26