教案讲稿数据库1introduction to databases

1、COMP2311COMP231Database Management SystemsOverviewPrepared by Raymond WongPresented by Raymond WongCOMP2312Course DetailsInstructorDr. Zhiwen YuDr. Raymond WongCOMP2313Course DetailsWebpage COMP2314Course DetailsLectureTime: Monday, Friday (15:00-16:20)Venue: RoomLaboratoryTime: Monday (14:00-17:00)

2、Venue: Rm B3Every three weeksCOMP2315Course DetailsTextbookDatabase System Concepts, McGraw Hill (5th Edition) A. Silberschatz, H. Korth, and S. Sudarshan. ReferenceDatabase Management Systems McGraw Hill (3rd Edition)Raghu Ramakrishnan and Johannes Gehrke. COMP2316Course DetailsGrading Scheme:Assig

3、nment 20% Project 0% Mid-Term Exam 20% Final Exam 60%COMP2317Assignment2 assignmentsAssignment 1 Content before the mid-term examAssignment 2Content after the mid-term examNOTE: No late submissions are allowed.COMP2318AssignmentIf the students can answer the selected questions in class correctly,for

4、 each corrected answer, I will give him/her a couponThis coupon can be used to waive one question in an assignment which means that s/he can get full marks for this question without answering this questionCOMP2319AssignmentGuidelineFor each assignment, each student can waive at most one question onl

5、y.s/he can waive any question he wants and obtain full marks for this question (no matter whether s/he answer this question or not)s/he may also answer this question. But, we will also mark it but will give full marks to this question. When the student submits the assignment,please staple the coupon

6、 to the submitted assignmentplease write down the question no. s/he wants to waive on the couponCOMP23110ProjectPhase 1 (written)Phase 2 (program)Phase 3 (written)Phase 4 (program)Assignment 10% Project 30% Mid-Term Exam 20% Final Exam 40%COMP23111ProjectYou are required to form a group. Each group

7、contains 1 or 2 members. 3-member group is NOT allowed.Please send an email containing the following information of each member to our tutor, Grace, to form a group (regardless of the number of members in your group). student IDstudent nameemailCOMP23112ProjectProgramming Language: Java 6.0Database

8、Server: Oracle 10gCOMP23113ProjectImplement a database applicationIn Phase 4 (the last phase), we will check the correctness of your applicationWe will check your program with a number of checking stepsYou can use at most one coupon to obtain full marks for one checking stepEach group can use at mos

9、t one coupon.COMP23114Midterm and Final ExamYou are allowed to bring a calculator with you.Please remember to prepare a calculator for the examAssignment 10% Project 30% Mid-Term Exam 20% Final Exam 40%COMP23115Midterm ExamIn-class MidtermDate: 8 Oct (Thu)Time: 15:00-16:20Venue: To be arrangedCOMP23

10、116Course OutlineER Model Relational Model and Algebra SQL Functional Dependencies and Relational Database Design Storage and File Systems Tree and Hash Indexes Query Processing and Implementation of Relational Operators Query Optimization Transactions Concurrency Control Protocols Database Recovery

11、 COMP23117What is a Database?A collection of data, typically describing the activities of one or more related organizationsCOMP23118What is a Database?Databases play a critical role in almost all areasBanking: all transactionsAirline: reservation, schedulesUniversities: registration, gradesSales: cu

12、stomers, products, purchasesManufacturing: production, inventory, orders, supply chainHuman resources: employee records, salaries, tax deductionsCOMP23119What is a Database?A database can be of any size and of varying complexity.For example, the list of names and address of friendsThe book catalog o

13、f a large library may contain half a million recordsA database of much greater size and complexity is maintained by the government to keep track of the tax information filed by taxpayers.COMP23120What is a Database?A schema is the definition of a database. It defines the meaning of data.An instance

14、of a database is the collection of data in the database at a particular point of time (snap-shot).For example, in the above, the schema is “Student Name, ID, Age, Gender, Entrance Year, Grade”. The remaining rows in the table make up an instance of the database.COMP23121What is a DBMS?DBMS Database

15、Management SystemA DBMS is a collection of software programs to enable users to create, maintain and utilize a database.COMP23122What is a DBMS?DBMSInsert recordsDelete recordsUpdate recordsQuery recordsCOMP23123What is a DBMS?Commercial DBMSCompanyProductOracleOracle 8i, 9i, 10gIBMDB2, Universal Se

16、rverMicrosoftAccess, SQL ServerSybaseAdaptive ServerInformixDynamic ServerWorlds 2nd largest software companyCEO, Larry Ellison, worlds 2nd richestWorlds 2nd largest after Informix acquisitionCOMP23124What is a DBMS?Commercial DBMSCompanyProductOracleOracle 8i, 9i, 10gIBMDB2, Universal ServerMicroso

17、ftAccess, SQL ServerSybaseAdaptive ServerInformixDynamic ServerAccess comes with MS OfficeCEO, John Chen, grown up in HKAcquired by IBM in 2001COMP23125Why do we need a DBMS?To reduce application development timeSuppose we are given a collection of raw files which occupy 500GB What are the drawbacks

18、?COMP23126Drawback of File SystemData Redundancy and InconsistencyE.g., consider a bank applicationaddress of a customer in the file of “saving-accounts” andthe file of “checking-accounts”A good design of DBMS can avoid data redundancy and inconsistency.COMP23127Drawback of File SystemDifficulty in

19、accessing dataNeed to write a new program to carry out each new taskIt is easy to obtain data with DBMSCOMP23128Drawback of File SystemIntegrity problemsE.g., consider a bank applicationThe balance cannot be below $1000The day of a month cannot exceed 31DBMS can check the integrityautomaticallyCOMP2

20、3129Drawback of File SystemAtomicity of updatesE.g., consider a bank applicationWe want to transfer $100 from account A to account BSteps:Step 1: We deduct $100 from account AStep 2: Then, we increment $100 in account BIf the system crashes at Step 1, then Step 2 cannot be executedDBMS makes sure th

21、at Step 1 and Step 2 can be executed together even with acrash (We call the execution is atomic.)COMP23130Drawback of File SystemConcurrent Access by multiple usersUncontrolled concurrent accesses can lead to inconsistenciesE.g., consider a bank applicationThere is an account shared by 2 customers A

22、 and BCustomers A and B withdraw $1000 concurrentlyDBMS makes sure that the concurrentaccess cannot lead to this problemRead 5000Read 50005000 - 1000Write 40005000 - 1000Write 4000ABCOMP23131Drawback of File SystemSecurity ProblemsE.g., consider a bank applicationWe do not want system programmers to

23、 have permissions to read some data (e.g., Andy Laus saving account and Joey Yungs saving account)Need a lot of effort to re-write a program for this permission systemDBMS can enforce that different users havedifferent permissions to access differentparts of the dataCOMP23132Advantages of DBMSWith t

24、he use of DBMS, we have the following advantagesData independenceEfficient data accessData integrity and security Data administrationConcurrent access and crash recoveryOverall: Reduced application development timeCOMP23133Data ModelsA data model is a collection of tools or concepts for describing d

25、ata, the meaning of data, data relationships and data constraintsObject-based logical modelsEntity-Relationship Model (ER Model)idBorrowerCustomerLoanCustomer-nameCustomer-streetCustomer-cityLoan-numberamountCOMP23134Data ModelsRecord-based Logical ModelsRelational ModelMain concept: relation, basic

26、ally a table with rows and columns. A column is also called a field or attributeOther models such as the Network Model, Hierarchical Model, object-oriented model, objected-relational modelWe will focus on the dominant Relational model.A description of data in terms of a data model is called a schema

27、.COMP23135Data AbstractionHide certain details of how data is stored and maintainedPhysical level: how and where data are actually stored, low level data structures are specified at this levelConceptual level (logical level): describes what data should be stored in the database, and relationship and

28、 semantics of the dataView level: Relevant partial view of the database to be particular type of usersCOMP23136Data AbstractionView LevelLogical LevelPhysical LevelView 1View 2View 3View nCOMP23137Data AbstractionConceptual LevelPhysical arrangement of records by an index treeD43389C73334F89328A3445

29、5C23444E32399G29021COMP23138Data AbstractionA view when posting the grades to all studentsViews of a company with data on employees, departments, products, Payroll section: view on employees, departments, salariesSales department: view on products, prices, sales, customersPurchasing department: view

30、 on parts, with pricingCOMP23139Data IndependencePhysical Data IndependenceChange of physical database schemas without change of conceptual database schemas or application programsLogical data independenceChange of conceptual schemas without change of external viewsCOMP23140What languages does the c

31、omputer speak?Data Definition Language (DDL)A language that specifies data schemasData Manipulation Language (DML)A language to facilitate the retrieval, update of data in the databaseFor retrieval, we query the database with the query language, which is part of the DMLCOMP23141People who deal with

32、databasesDatabase Administrator (DBA): Person(s) who has central control over the database and is responsible for the following tasksSchema definition/modificationStorage structure definition/modificationAuthorization of data accessIntegrity constraint specificationMonitoring performanceResponding t

33、o changes in requirementsCOMP23142People who deal with databasesApplication ProgrammersEmbed DML calls in program written in a host language (e.g., Cobol, C, Java). (DML stands for data manipulation language)e.g., programs that generates payroll checks, transfer funds between accountsSophisticated usersForm request in database query languageNave usersInvokes one of the permanent application programs that have been wri