1 / 28

CS 370 Database Systems

CS 370 Database Systems. Lecture 3 Overview of Database Systems. DBMS Architecture. A commonly used view of data approach is the three-level architecture suggested by ANSI/SPARC (American National Standards Institute/Standards Planning and Requirements Committee). View 1. View 2. View n.

osborn
Download Presentation

CS 370 Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS 370 Database Systems Lecture 3 Overview of Database Systems CS370 Spring 2007

  2. DBMS Architecture A commonly used view of data approach is the three-level architecture suggested by ANSI/SPARC (American National Standards Institute/Standards Planning and Requirements Committee). View 1 View 2 View n External Level Conceptual Schema Conceptual Level Internal Schema Internal Level Physical data Organization Data base CS370 Spring 2007

  3. External Level User’s view of the database. Describes that part of database that is relevant to a particular user. The external level is the view that the individual user of the database has. This view is often a restricted view of the database and the same database may provide a number of different views for different classes of users. In general, the end users and even the applications programmers are only interested in a subset of the database. For example, a department head may only be interested in the departmental finances and student enrolments but not the library information. The librarian would not be expected to have any interest in the information about academic staff. The payroll office would have no interest in student enrolments CS370 Spring 2007

  4. Conceptual Level Community view of the database. Describe what data is stored in database and relationship. The conceptual view is the information model of the enterprise and contains the view of the whole enterprise without any concern for the physical implementation. This view is normally more stable than the other two views. In a database, it may be desirable to change the internal view to improve performance while there has been no change in the conceptual view of the database. The conceptual view is the overall community view of the database and it includes all the information that is going to be represented in the database. The conceptual view is defined by the conceptual schema, which includes definitions of each of the various types of data. CS370 Spring 2007

  5. Internal Level Physical representation of the database on the computer. Describes how the data is stored in the database. CS370 Spring 2007

  6. External View (User 2) External View (User 1) • EMP • EMP # • SAL • EMP • EMP # • SAL Conceptual Level Employee EMPLOYEE_NUMBER NUMBER(6) DEPARTMENT_NUMBER NUMBER( 4) SALARY NUMARIC(5) Internal Level STORED_EMP LENGTH=18 PREFIX TYPE=BYTE(6), OFFSET=0 EMP# TYPE=BYTE(6), OFFSET=6,INDEX=EMX DEPT# TYPE=BYTE(4), OFFSET=12 PAY TYPE=FULLWORD, OFFSET=16 CS370 Spring 2007

  7. SNo FName LName Age Salary Staff_No FName LName DOB Salary Branch_No External View 1 External View 2 Staff_No L_Name BNo Conceptual Level struct STAFF{ int Staff_No; int Branch_No; char Fname [15]; char Lname [15]; struct date Date_of_Birth; float Salary; struct STAFF * next; /*Pointer to next Staff Record*/ }; index Staff_No; index Branch_No; /*Define indexes for staff*/ Internal Level CS370 Spring 2007

  8. User User User User User User External View External View External View Logical Barrier Conceptual View (Table,etc) Physical Barrier Physical View CS370 Spring 2007

  9. Data Independence • One big problem in application development is the separation of applications from data • Do I have change my program when I … • replace my hard drive? • partition the data into two physical files (or merge two physical files into one)? • store salary as floating point number instead of integer? • develop other applications that use the same set of data? • add more data fields to support other applications? • … … CS370 Spring 2007

  10. Data Independence and the ANSI-SPARC Three-level Architecture External Schema External Schema External Schema External/Conceptual mapping Logical data independence Conceptual Schema Conceptual/internal mapping Physical data independence Internal Schema CS370 Spring 2007

  11. Instances and Schemas • Each level is defined by a schema, which defines the data at the corresponding level • A logical schema defines the logical structure of the database (e.g., set of customers and accounts and the relationship between them) • A physical schema defines the file formats and locations • A databaseinstance refers to the actual content of the database at a particular point in time. A database instance must conform to the corresponding schema CS370 Spring 2007

  12. Data Independence Logical Data Independence • Refers to immunity of external schemas to change in conceptual schema. • Conceptual schema changes e.g. addition /removal of entities. • Should not require changes to external schema or rewrites of application programs. CS370 Spring 2007

  13. Logical Data Independence external schema1 external schema2 external schema3 conceptual schema • For Example, by removing • record type or data item, • the external schemas that • refer only to the remaining • data should not be affected. • Only the view definitions and • mapping need to be changed • in DBMS. Logical data independence is a measure of how much the conceptual schema can change without affecting the application programs internal schema database CS370 Spring 2007

  14. Data Independence Physical Data Independence • Refers to immunity of conceptual schemas to change in internal schema. • Internal schema changes e.g. using different file organizations, storage structures/devices. • Should not require changes to Conceptual or external schema. CS370 Spring 2007

  15. Physical Data Independence external schema1 external schema2 external schema3 • some physics files are • reorganized. • For example, when create • additional access structure to • improve the performance of • retrieval or update. If the same • data as before remains same in • the database. • No need to change conceptual • schema conceptual schema Physical data independence is a measure of how much the internal schema can change without affecting the application programs internal schema database CS370 Spring 2007

  16. Queries in DBMS • Here are some questions a user might ask:- • What is the name of the student whose ID = 1234? • How many students are enrolled in CS370? • Is any student with GPA less than 3.0 enrolled in CS370? • Such questions involving the data stored in a DBMS are called queries • A DBMS provides a specialized language, called the query language, in which queries can be imposed • Relational algebra and relational calculus are the formal query languages based on mathematical logic and collection of operators. • Query language is also a part of DML (which would be discussed later) CS370 Spring 2007

  17. Transaction 1 Transaction 1 Transaction 2 Conflicting read/write Transaction Management • A transaction is a collection of operations that performs a single logical • function in database application time CS370 Spring 2007

  18. Transaction Management (cont.) • Transaction-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 failures. • Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. CS370 Spring 2007

  19. Storage Management • A storage 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. • The storage manager is responsible for the following tasks: • interaction with the file manager • efficient storing, retrieving, and updating of data. CS370 Spring 2007

  20. Overall System Architecture CS370 Spring 2007

  21. Application Architectures • 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” CS370 Spring 2007

  22. Client – Server Server hold the database and the DBMS. Clint manage the user interface and runs applications. LAN Request for data Selected data returned Database Server (With DBMS) CS370 Spring 2007

  23. Summary – Client Server functions Clint Server • Accepts and processes database requests from clients. • Checks authorization • Ensure integrity constraints not violated. • Performs query/update processing and transmit response to client. • Maintains system catalog. • Provides concurrent database access. • Provides recovery control. • Manages the user interface. • Accepts and checks syntax of user input. • Process application. • Generates database request and transmit to server. • Passes response back to user. CS370 Spring 2007

  24. Two-tier Architecture • User Interface Programs and Application Programsrun on the client side • Interface calledODBC (Open Database Connectivity)provides an Application program interface (API) allow client side programs to call the DBMS. Most DBMS vendors provide ODBC drivers. • A client program may connect to several DBMS. • Other variations of clients are possible: e.g., in some DBMS, more functionality is transferred to clients including data dictionary functions, optimization and recovery across multiple servers, etc. In such situations the server may be called the Data Server. CS370 Spring 2007

  25. Three-tier Architecture • Common forWeb applications • Intermediate Layer calledApplication ServerorWeb Server: • Stores the web connectivity software andthe rules and business logic (constraints)part of the application used to access the right amount of data from the database server • Acts like a conduit for sending partially processed data between the database server and the client. • Additional Features- Security: • Encrypt the data at the server before transmission • Decrypt data at the client CS370 Spring 2007

  26. Client - Server • Advantages include: • Wider access to existing database. • Increased performance. • Possible reduction in hardware cost. • Reduction in communication cost. • Increased consistency. CS370 Spring 2007

  27. Distributed Database Systems Distributed database system consist of collecting of sites, connected together via some kind of communication network, in which. • Each site is a database system site in its own right. • The site is have agreed to work together so that a user at any site can access data anywhere in the network exactly as if the data were all stored at the user’s own site. CS370 Spring 2007

  28. Distributed Database Systems Lahore Singapore Communication network Shanghai Islamabad CS370 Spring 2007

More Related