数据库入门知识.

1、1 Zhao Hui Some slides adapted from Silberschatz, Korth and Sudarshan nIn the early days, database applications were built on top of file systems. nDrawbacks of using file systems to store data: lData redundancy, inconsistency and isolation 4Multiple file formats, duplication of information in diffe

2、rent files lDifficulty in accessing data 4Need to write a new program to carry out each new task lIntegrity problems (e.g. account balance 0) 4Integrity constraints become part of program code - hard to add new constraints or change existing ones nAtomicity of updates lFailures may leave database in

3、 an inconsistent state with partial updates carried out. lE.g. transfer of funds from one account to another should either complete or not happen at all nConcurrent access by multiple users lExample: airline ticket reservation system 4Can you imagine if only 1 person at a time could buy a ticket fro

4、m the system? 4the same seat could be sold multiple times. There are 3 seats left; Mary buys 2 seats; John buys 3 seats at the same time If Mary hits enter 1st there will be 1 seat left; if John is faster there will be 0; but 5 seats have been sold and they will fight at the airport! lNeeded for per

5、formance lUncontrolled concurrent access can lead to inconsistencies Database systems offer solutions to all the above problems A major purpose of a database system is to provide uses with an abstract view of a data. The database system hides certain details of how the data are stored and maintained

6、. An architecture for a database system physical storage nPhysical level: describes how a record (e.g., customer) is stored. nLogical level: describes data stored in database, and the relationships among the data. type customer = record customer_id : string; customer_name : string; customer_street :

7、 string; customer_city : integer; end; nView level: application programs hide details of data types. Views can also hide information (such as an employees salary) for security purposes. Be described as a block of consecutive storage location(DBA) Be described by a type definition using programming l

8、anguage (DBA) Application programs (database users) nSimilar to types and variables in programming languages nSchema the logical structure of the database le.g., the database consists of information about a set of customers and accounts and the relationship between them) lPhysical schema: database d

9、esign at the physical level lLogical schema: database design at the logical level nInstance the actual content of the database at a particular point in time lAnalogous to the value of a variable nPhysical Data Independence the ability to modify the physical schema without changing the logical schema

10、 lApplications depend on the logical schema lIn general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others. nA collection of tools for describing lData lData relationships lData semantics lData constraints

11、nRelational model nEntity-Relationship data model (mainly for database design) (E-R) nObject-based data models (Object-oriented and Object- relational) nSemi-structured data model (XML) nOther older models: lNetwork model lHierarchical model A way to describe the design of a database at the physical

12、 level, logical level, and view level. nData Definition Language (DDL): specify the database schema. nData Manipulation Language (DML): express database queries and update. nThey are not two separate languages, instead they are parts of a single database language ,such as SQL . nLanguage for accessi

13、ng and manipulating the data organized by the appropriate data model lDML also known as query language nTwo classes of languages lProcedural user specifies what data is required and how to get those data lDeclarative (nonprocedural) user specifies what data is required without specifying how to get

14、those data nSQL is the most widely used query language Retrieval Insertion Deletion Modification nSpecification notation for defining the database schema Example:create table account ( account-number char(10), balance integer) nDDL compiler generates a set of tables stored in a data dictionary, whic

15、h can only be accessed and updated by the database system itself. nData dictionary contains metadata (i.e., data about data) lDatabase schema lData storage and definition language 4Specifies the storage structure and access methods used lIntegrity constraints 4Domain constraints 4Referential integri

16、ty (references constraint in SQL) 4Assertions lAuthorization nSQL: widely used procedural language lExample: Find the name of the customer with customer-id 192- 83-7465 select customer.customer_name fromcustomer where customer.customer_id = 192-83-7465 lExample: Find the balances of all accounts hel

17、d by the customer with customer-id 192-83-7465 select account.balance from depositor, account where depositor.customer_id = 192-83-7465 and depositor.account_number = account.account_number Example of tabular data in the relational model Attributes NameGenderClass Stu_IDCollage 奉云华男05级2 班 1005251013

18、4 软件学院 寿辰男05级2 班 10052510135软件学院 黄俊锡男05级2 班 10052510136软件学院 黄蔚菁男05级2 班 10052510137软件学院 马赞男05级2 班 10052510138软件学院 高鹏男05级2 班 10052510139软件学院 田佳君男05级2 班 10052510140软件学院 刘思男05级2 班 10052510141软件学院 李江男05级2 班 10052510142软件学院 金明晖男05级2 班 10052510143软件学院 student nModels an enterprise as a collection of entiti

19、es and relationships lEntity: a “thing” or “object” in the enterprise that is distinguishable from other objects 4Described by a set of attributes lRelationship: an association among several entities nRepresented diagrammatically by an entity-relationship diagram: 学 生 课 程 编号姓名性别班级学生证号学院 编号名称学时学期性质 选

20、修 n n mm 成绩 教 师 课 程 编号姓名性别职称系别研究方向 编号名称学时学期性质 讲授 n n mm 教 师 课 程 编号姓名性别职称系别基本情况 编号名称学时学期性质 讲授 n n mm 学 生 编号姓名性别班级学生证号 基本情况 选修 n n mm 成绩 nStorage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted

21、 to the system. nThe storage manager is responsible to the following tasks: lInteraction with the file manager lEfficient storing, retrieving and updating of data nIssues: lStorage access lFile organization lIndexing and hashing 1. Parsing and translation 2. Optimization 3. Evaluation nAlternative w

22、ays of evaluating a given query lEquivalent expressions lDifferent algorithms for each operation nCost difference between a good and a bad way of evaluating a query can be enormous nNeed to estimate the cost of operations lDepends critically on statistical information about relations which the datab

23、ase must maintain lNeed to estimate statistics for intermediate results to compute cost of complex expressions nA transaction is a collection of operations that performs a single logical function in a database application lAn atomic sequence of database actions (reads/writes) lEach transaction, exec

24、uted completely, must leave the DB in a consistent state if DB is consistent when the transaction begins nTransaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction fai

25、lures. nConcurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. nLanguage extensions to allow embedded SQL nApplication program interface (e.g., ODBC/JDBC) which allow SQL queries to be sent to a database nThe process of desi

26、gning the general structure of the database: lLogical Design Deciding on the database schema. (Database design requires that we find a “good” collection of relation schemas.) 4Business decision What attributes should we record in the database? 4Computer Science decision What relation schemas should

27、we have and how should the attributes be distributed among the various relation schemas? lPhysical Design Deciding on the physical layout of the database nThe architecture of a database systems is greatly influenced by the underlying computer system on which the database is running: lCentralized lCl

28、ient-server lParallel (multi-processor) lDistributed nUsers are differentiated by the way they expect to interact with the system lApplication programmers interact with system through DML calls lSophisticated users form requests in a database query language lSpecialized users write specialized datab

29、ase applications that do not fit into the traditional data processing framework lNave users invoke one of the permanent application programs that have been written previously 4Examples: people accessing database over the web, bank tellers, clerical staff nCoordinates all the activities of the databa

30、se system; the database administrator has a good understanding of the enterprises information resources and needs. nDatabase administrators duties lSchema definition lStorage structure and access method definition lSchema and physical organization modification lGranting user authority to access the

31、database lRoutine maintenance 4Back up the database onto tapes, or remote server to prevent loss of data 4Ensure that enough free disk space is available for normal operations, upgrade disk space as required 4Monitor jobs running on the database and ensuring that performance is not degraded by very

32、expensive tasks submitted by some users. 2005级本科生 Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a database Three-tier architecture: E.g. web-based applications, and applications built using “middleware” n1950s and early 1960s: lData processing using magnetic tapes for storage 4Tapes provide only sequential access lPunched cards for input nLate 1960s and 1970s: lHard disks allow direct access to data lNetwork and hierarchical data models in widespread use lTed Codd defines the relational data model 4Would win the ACM Turing Award for this work 4I