桂电数据库实验.

1、Teacher: 李优Email: Q/A: room 408,Wednesday 14:00-15:30n专业必修课（电子信息科学与技术专业）n参考教材lAbraham Silberschatz etc.数据库系统概念（第五版，影印版）高等教育出版社l王珊等 数据库系统概论（第四版） 高等教育出版社l施伯乐等 数据库系统教程（第二版） 高等教育出版社nThis chapter:lInteresting Stuff About DatabaseslDatabase Management System (DBMS)lOverview of a DBMS- Database Architectur

2、elDatabase-system Studies nRelational KnowledgelDatabase LanguageslRelational DatabaseslDatabase DesignlData Storage and QueryinglTransaction ManagementnIt used to be about boring stuff: employee records, bank records, etc.nToday, Database Applications:lBanking: all transactionslAirlines: reservatio

3、ns, scheduleslUniversities: registration, gradeslSales: customers, products, purchases; lManufacturing: production, inventory, orders, supply chainlWeb searchnDatabases touch all aspects of our livesnYou may not notice it, but databases are behind almost everything you do on the Web.lGoogle searches

4、. Baidu searches.lQueries at Amazon, Taobao, etc.nDBMS contains information about a particular enterprise, It expect to:lCreate new database (DDL)lManipulate the data (eg:Modify, Query)lStorage of very large amounts of datalDurability (For Recovery)lIsolation,AtomicitylAn environment that is both co

5、nvenient and efficient to usenIn the early days, database applications were built directly on top of file systemsnDrawbacks of using file systems to store data:lData redundancy and inconsistency4Multiple file formats, duplication of information in different fileslDifficulty in accessing data 4Need t

6、o write a new program to carry out each new tasklData isolation multiple files and formatslIntegrity problems4Integrity constraints (e.g. account balance 0) become “buried” in program code rather than being stated explicitly4Hard to add new constraints or change existing onesnDrawbacks of using file

7、 systems (cont.) lAtomicity of updates4Failures may leave database in an inconsistent state with partial updates carried out4Example: Transfer of funds from one account to another should either complete or not happen at alllConcurrent access by multiple users4Concurrent accessed needed for performan

8、ce4Uncontrolled concurrent accesses can lead to inconsistencies Example: Two people reading a balance and updating it at the same timelSecurity problems4Hard to provide user access to some, but not all, datanDatabase systems offer solutions to all the above problemsn1970s , Ted CoddlRelations (use t

9、able)lWhy Relations4Very simple model.4Often matches how we think about data.4Abstract model that underlies SQL, the most important database language today.lSQL (Structured Query Language)n1990, normal usednA collection of tools for describing lData lData relationshipslData semanticslData constraint

10、snRelational modelnEntity-Relationship data model (mainly for database design) nObject-based data models (Object-oriented and Object-relational)nSemistructured data model (XML)nOther older models:lNetwork model lHierarchical modelnExample of tabular data in the relational modelAttributesnSmallerlDBM

11、S can run on personal computerlDBMS appear as a common toolnLargerlDBMS store gigabytes and terabytes data, even petabytes；4Google holds petabytes of data;4Satellites send petabytes of informaitions4Picture and movies consume much more storage;4Peer-to-peer file-sharing systems.nThe DBA decide the s

12、chema of the table/relationnThe DBA decide the constraintsnAnswering the querynTransaction ProcessinglConcurrency-control managerlLogging and recovery managernStorage Manager-data movement between disk and main memorynBuffer manager nA transaction include one or more database operations.lLogginglCon

13、currency controllDeadlock resolutionnACID properties of transactionsnQuery compilernExecution enginenPart 1- Relational Database modelinglFunctional DependencieslNormalizationlE/R (Entity Relational) modelnPart 2-Relational Database programminglLanguage based on algebra and logiclSQLnPart 3-Database

14、 system implementationlStorage managementlQuery processinglTransaction processingnModels an enterprise as a collection of entities and relationshipslEntity: a “thing” or “object” in the enterprise that is distinguishable from other objects4Described by a set of attributeslRelationship: an associatio

15、n among several entitiesnRepresented diagrammatically by an entity-relationship diagram:BACKnSpecification 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 dictionarynData dictio

16、nary 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)4AssertionslAuthorizationnLanguage for accessing

17、and manipulating the data organized by the appropriate 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

18、 datanSQL is the most widely used query languagenSQL: widely used non-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 custom

19、er with customer-id 192-83-7465select account.balancefrom depositor, accountwhere depositor.customer_id = 192-83-7465 anddepositor.account_number = account.account_numbernApplication programs generally access databases through one oflLanguage extensions to allow embedded SQLlApplication program inte

20、rface (e.g., ODBC/JDBC) which allow SQL queries to be sent to a databaseThe process of designing the general structure of the database:nLogical Design Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas.lBusiness decision What attributes sho

21、uld we record in the database?lComputer Science decision What relation schemas should we have and how should the attributes be distributed among the various relation schemas?nPhysical Design Deciding on the physical layout of the database nPhysical level: describes how a record (e.g., customer) is s

22、tored.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 : integer;end;nView level: application programs hide details of data types. Views can also hide info

23、rmation (such as an employees salary) for security purposes. An architecture for a database system 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 to the system.nThe storage manager

24、is responsible to the following tasks: lInteraction with the file manager lEfficient storing, retrieving and updating of datanIssues:lStorage accesslFile organizationlIndexing and hashing1. Parsing and translation2. Optimization3. EvaluationnAlternative ways of evaluating a given querylEquivalent ex

25、pressionslDifferent algorithms for each operationnCost difference between a good and a bad way of evaluating 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

26、intermediate results to compute cost of complex expressionsnA transaction is a collection of operations that performs a single logical function in a database applicationnTransaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g.,

27、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. The architecture of a database systems is greatly influenced by the underlying computer system on

28、which the database is running:nCentralizednClient-servernParallel (multi-processor)nDistributed Users are differentiated by the way they expect to interact with the systemnApplication programmers interact with system through DML callsnSophisticated users form requests in a database query languagenSp

29、ecialized users write specialized database applications that do not fit into the traditional data processing frameworknNave users invoke one of the permanent application programs that have been written previouslylExamples, people accessing database over the web, bank tellers, clerical staffnCoordina

30、tes all the activities of the database system; the database administrator has a good understanding of the enterprises information resources and needs.nDatabase administrators duties include:lSchema definitionlStorage structure and access method definitionlSchema and physical organization modificationlGra