數(shù)據(jù)庫(kù)入門知識(shí).

1、1Zhao HuiSome slides adapted from Silberschatz, Korth and SudarshannIn the early days, database applications were built on top of file systems.nDrawbacks of using file systems to store data:lData redundancy, inconsistency and isolation4Multiple file formats, duplication of information in different f

2、ileslDifficulty in accessing data4Need to write a new program to carry out each new tasklIntegrity problems (e.g. account balance 0) 4Integrity constraints become part of program code - hard to add new constraints or change existing onesnAtomicity of updateslFailures may leave database in an inconsi

3、stent state with partial updates carried out.lE.g. transfer of funds from one account to another should either complete or not happen at allnConcurrent access by multiple userslExample: airline ticket reservation system4Can you imagine if only 1 person at a time could buy a ticket from the system?4t

4、he 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 performance lUncontr

5、olled concurrent access can lead to inconsistenciesDatabase 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.An architecture f

6、or a database system physical storagenPhysical 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 = recordcustomer_id : string; customer_name : string;customer_street : string;customer_city :

7、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 language (DBA)Application pro

8、grams(database users)nSimilar to types and variables in programming languagesnSchema 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 design at the physical levellLogic

9、al schema: database design at the logical levelnInstance the actual content of the database at a particular point in time lAnalogous to the value of a variablenPhysical Data Independence the ability to modify the physical schema without changing the logical schemalApplications depend on the logical

10、schemalIn 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 relationshipslData semanticslData constraintsnRelational modelnEntity-Relationship dat

11、a 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 modelA way to describe the design of a database at the physical level, logical level, and view level. nData Def

12、inition 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 accessing and manipulating the data organized by the app

13、ropriate data modellDML also known as query languagenTwo 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 those datanSQL is the most widely used query langua

14、geRetrievalInsertionDeletionModificationnSpecification notation for defining the database schemaExample:create table account ( account-number char(10), balance integer)nDDL compiler generates a set of tables stored in a data dictionary, which can only be accessed and updated by the database system i

15、tself. nData dictionary contains metadata (i.e., data about data)lDatabase schema lData storage and definition language 4Specifies the storage structure and access methods usedlIntegrity constraints4Domain constraints4Referential integrity (references constraint in SQL)4AssertionslAuthorization nSQL

16、: widely used procedural languagelExample: Find the name of the customer with customer-id 192-83-7465select customer.customer_namefromcustomerwhere customer.customer_id = 192-83-7465lExample: Find the balances of all accounts held by the customer with customer-id 192-83-7465select account.balancefro

17、m depositor, accountwhere depositor.customer_id = 192-83-7465 anddepositor.account_number = account.account_numberExample of tabular data in the relational modelAttributes NameGenderClass Stu_IDCollage奉云華男05級(jí)2班 10052510134 軟件學(xué)院壽辰男05級(jí)2班10052510135軟件學(xué)院黃俊錫男05級(jí)2班10052510136軟件學(xué)院黃蔚菁男05級(jí)2班10052510137軟件學(xué)院馬贊

18、男05級(jí)2班10052510138軟件學(xué)院高鵬男05級(jí)2班10052510139軟件學(xué)院田佳君男05級(jí)2班10052510140軟件學(xué)院劉思男05級(jí)2班10052510141軟件學(xué)院李江男05級(jí)2班10052510142軟件學(xué)院金明暉男05級(jí)2班10052510143軟件學(xué)院student nModels an enterprise as a collection of entities and relationshipslEntity: a “thing” or “object” in the enterprise that is distinguishable from other obj

19、ects4Described by a set of attributeslRelationship: an association among several entitiesnRepresented diagrammatically by an entity-relationship diagram:學(xué) 生課 程編號(hào)姓名性別班級(jí)學(xué)生證號(hào)學(xué)院編號(hào)名稱學(xué)時(shí)學(xué)期性質(zhì)選修n nmm成績(jī) 教 師課 程編號(hào)姓名性別職稱系別研究方向編號(hào)名稱學(xué)時(shí)學(xué)期性質(zhì)講授n nmm 教 師課 程編號(hào)姓名性別職稱系別基本情況編號(hào)名稱學(xué)時(shí)學(xué)期性質(zhì)講授n nmm學(xué) 生編號(hào)姓名性別班級(jí)學(xué)生證號(hào) 基本情況選修n nmm成績(jī) nS

20、torage 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 to the system.nThe storage manager is responsible to the following tasks: lInteraction with the file manager lEfficient storing, retriev

21、ing and updating of datanIssues:lStorage accesslFile organizationlIndexing and hashing 1. Parsing and translation2. Optimization3. Evaluation nAlternative ways of evaluating a given querylEquivalent expressionslDifferent algorithms for each operationnCost difference between a good and a bad way of e

22、valuating a query can be enormousnNeed to estimate the cost of operationslDepends critically on statistical information about relations which the database must maintainlNeed to estimate statistics for intermediate results to compute cost of complex expressions nA transaction is a collection of opera

23、tions that performs a single logical function in a database applicationlAn atomic sequence of database actions (reads/writes)lEach transaction, executed completely, must leave the DB in a consistent state if DB is consistent when the transaction beginsnTransaction-management component ensures that t

24、he database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.nConcurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. nLanguage exten

25、sions to allow embedded SQLnApplication program interface (e.g., ODBC/JDBC) which allow SQL queries to be sent to a databasenThe process of designing the general structure of the database:lLogical Design Deciding on the database schema. (Database design requires that we find a “good” collection of r

26、elation schemas.)4Business decision What attributes should we record in the database?4Computer Science decision What relation schemas should 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 a

27、rchitecture of a database systems is greatly influenced by the underlying computer system on which the database is running:lCentralizedlClient-serverlParallel (multi-processor)lDistributed nUsers are differentiated by the way they expect to interact with the systemlApplication programmers interact w

28、ith system through DML callslSophisticated users form requests in a database query languagelSpecialized users write specialized database applications that do not fit into the traditional data processing frameworklNave users invoke one of the permanent application programs that have been written prev

29、iously4Examples: people accessing database over the web, bank tellers, clerical staff nCoordinates all the activities of the database system; the database administrator has a good understanding of the enterprises information resources and needs.nDatabase administrators dutieslSchema definitionlStora

30、ge structure and access method definitionlSchema and physical organization modificationlGranting user authority to access the databaselRoutine maintenance4Back up the database onto tapes, or remote server to prevent loss of data4Ensure that enough free disk space is available for normal operations,

31、upgrade disk space as required 4Monitor jobs running on the database and ensuring that performance is not degraded by very expensive tasks submitted by some users.2005級(jí)本科生 Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a database Three-tier architecture: E.g. web-bas

32、ed applications, and applications built using “middleware” n1950s and early 1960s:lData processing using magnetic tapes for storage4Tapes provide only sequential accesslPunched cards for inputnLate 1960s and 1970s:lHard disks allow direct access to datalNetwork and hierarchical data models in widespread uselTed Codd defines the relational data model4Would win the ACM Turing Award for thi