1 / 44

Introduction to Database Systems

Introduction to Database Systems. P. T. Chung CS 649 Database Management Systems. Database Overview. File Management vs Database Management Advantages of Database systems: storage persistence, programming interface, transaction management Three level Data Model DBMS Architecture

valiant
Download Presentation

Introduction to 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. Introduction to Database Systems P. T. Chung CS 649 Database Management Systems P. T. Chung CS 649 DBMS

  2. Database Overview • File Management vs Database Management • Advantages of Database systems: storage persistence, programming interface, transaction management • Three level Data Model • DBMS Architecture • Database System Components • Users classification P. T. Chung CS 649 DBMS

  3. File Management Systems • File is uninterpreted, unstructured collection of information • File operations: delete, catalog, create, rename, open, close, read, write, find, … • Access methods: Algorithms to implement operations along with internal file organization • Examples: File of Customers, File of Students; Access method: implementation of a set of operations on a file of students or customers. P. T. Chung CS 649 DBMS

  4. File Management System Problems • Data redundancy • Data Access: New request-new program • Data is not isolated from the access implementation • Format incompatible data • Concurrent program execution on the same file • Difficulties with security enforcement • Integrity issues P. T. Chung CS 649 DBMS

  5. Concurrent Program Execution What is the final value of the account AC? Program1 AC=AC-50 AC #103 450 Program2 AC=AC-100 P. T. Chung CS 649 DBMS

  6. Security Problems • Allow access to the file only to the authorized personnel • Ability to restrict access to parts of the record • Ability to control operation usage by different users • Protection from unauthorized use • Protection from the derivation of unauthorized information P. T. Chung CS 649 DBMS

  7. Data Integrity • A database constraint is a logical constraint about the data expressed in a logical language. • STUDENT.AGE >15 • If (STUDENT.CLASS ==cs43005) then (STUDENT.PRIOR_CLASS ==cs31001) • Database is consistent if data at each time satisfies all integrity constraints. • Input to any application is a set of consistent data. An application output is a set of consistent data. P. T. Chung CS 649 DBMS

  8. Collection of Files 60’s 70's 80's 90’s now Hierarchical Network Relational Choice for most new applications Object Bases Knowledge Bases P. T. Chung CS 649 DBMS

  9. Advantages of Databases • Persistent Storage – Database not only provides persistent storage but also efficient access to large amounts of data • Programming Interface – Database allows users to access and modify data using powerful query language. It provides flexibility in data management • Transaction Management – Database supports a concurrent access to the data P. T. Chung CS 649 DBMS

  10. Early Database Applications • Airline Reservation Systems – Data items are: single passenger reservations; Information about flights and airports; Information about ticket prices and tickets restrictions. • Banking Systems – Data items are accounts, customers, loans, mortgages, balances, etc. Failures are not tolerable. Concurrent access must be provided • Corporate Records – Data items are: sales, accounts, bill of materials records, employee and their dependents P. T. Chung CS 649 DBMS

  11. Modern Database Applications • Client – Server architecture • DBMS serves as a server and client queries are sent to servers • Where to locate servers • Multimedia Applications • Multidatabase Applications • Data Warehouses P. T. Chung CS 649 DBMS

  12. Three Aspects to Studying DBMS's 1. Modeling and design of databases. • Allows exploration of issues before committing to an implementation. 2. Programming: queries and DB operations like update. • SQL = “intergalactic dataspeak.” 3. DBMS implementation. . P. T. Chung CS 649 DBMS

  13. Definitions • A database is a collection of stored operational data used by various applications and/or users by some particular enterprise or by a set of outside authorized applications and authorized users • A DataBase Management System (DBMS) is a software system that manages execution of users applications to access and modify database data so that the data security, data integrity, and data reliability is guaranteed for each application and each application is written with an assumption that it is the only application active in the database. P. T. Chung CS 649 DBMS

  14. What Is Data ? • Different view points: • A sequence of characters stored in computer memory or storage • Interpreted sequence of characters stored in computer memory or storage • Interpreted set of objects P. T. Chung CS 649 DBMS

  15. Data Levels and their Roles • Physical – corresponds to the first view of data: How data is stored, how is it accessed, how data is modified, is data ordered, how data is allocated to computer memory and/or peripheral devices, how data items are actually represented (ASCI, EBCDIC,…) • Conceptual – corresponds to the second view of data: What we want the data to express and what relationships between data we must express, what “ story” data tells, are all data necessary for the “story’ are discussed. • View – corresponds to the third view of data:What part of the data is seen by a specific application P. T. Chung CS 649 DBMS

  16. Physical Data - Example • Physical 10 3 6 10 3 6 james J 3 000375 0000035000 . . . . . . . . . benjamin 63 P. T. Chung CS 649 DBMS

  17. Examples • Conceptual 1 TA 2 Name char(10), 2 Age char (3), 2 Salary Fixed Dec(6); 1 Student 2 Name char(10), 2 Year-of_study char(3) 2 GPA Fixed Dec(5,2); P. T. Chung CS 649 DBMS

  18. Examples 1 STUDENTS-TA 2 Name char(25), 2 Age char (3), 2 Salary Fixed Dec(8,2), 2 Year-of_study char(3) 2 GPA Fixed Dec(3,2); A view P. T. Chung CS 649 DBMS

  19. Three Level Data View –Data Abstractions . . . . . View1 View k Conceptual View Of Data Phyisal Data Storage P. T. Chung CS 649 DBMS

  20. DBMS Architecture P. T. Chung CS 649 DBMS

  21. Logical and Physical Database Components Logical • Data Definition Language (DDL) • Data Manipulation Language (DML) • Host Language Interface • Data Administrator • Users • Query Processor • Compiler • Optimizer • Management • Transaction Manager • File Manager • Buffer Manager • Authorization and Integrity Manager Physical P. T. Chung CS 649 DBMS

  22. Database Languages Department Faculty Dept Chair Name Dept SELECT Chair FROM Faculty, DepartmentWHERE Faculty.name = “Ken Noname” AND Faculty.Dept = Department.Dept Data definition language (DDL) ~ like type definitions in C or C++ Data Manipulation Language (DML) Query (SELECT) UPDATE < relation name > SET <attribute> = < new-value> WHERE <condition> SQL P. T. Chung CS 649 DBMS

  23. Data Definition Language • Specification notation for defining the database schema • E.g. create tableaccount (account-numberchar(10),balanceinteger) • DDL compiler generates a set of tables stored in a data dictionary • Data dictionary contains metadata (i.e., data about data) • database schema • Data storage and definition language • language in which the storage structure and access methods used by the database system are specified • Usually an extension of the data definition language P. T. Chung CS 649 DBMS

  24. Data Manipulation Language • Language for accessing and manipulating the data organized by the appropriate data model • Two classes of languages • Procedural – user specifies what data is required and how to get those data • Nonprocedural – user specifies what data is required without specifying how to get those data • SQL is the most widely used query language P. T. Chung CS 649 DBMS

  25. Database Host Languages C, C++, Fortran, Lisp, COBOL Application prog. DBMS Host language is completely general Query language—less general "non procedural" and optimizable Calls to DB Local Vars (Memory) (Storage) P. T. Chung CS 649 DBMS

  26. Data Administrator • Coordinates all the activities of the database system; the database administrator has a good understanding of the enterprise’s information resources and needs. • Database administrator's duties include: • Schema definition • Storage structure and access method definition • Schema and physical organization modification • Granting user authority to access the database • Specifying integrity constraints • Acting as liaison with users • Monitoring performance and responding to changes in requirements P. T. Chung CS 649 DBMS

  27. Database Users • Naïve – do not know about database too much, invoke application programs that are prepared already • Application Programmers – know how to interact with the system but may not know how DBMS is designed • Sophisticated users that know advanced use of the system and can use the system and packages on the top of the system • DBMS system users – write specialized database applications that do not fit into the traditional data processing framework P. T. Chung CS 649 DBMS

  28. Query Processor • Compiler – verifies whether a program or query is written in accordance with DDL and DML rules • Optimizer – Finds the most effective way to access the required data and supply it in a user requested form. Monitors the query execution and modifies a query evaluation plan if necessary. P. T. Chung CS 649 DBMS

  29. Transaction Management • A transaction is a collection of operations that performs a single logical function in a database application • 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. P. T. Chung CS 649 DBMS

  30. Storage Management • 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 to the following tasks: • interaction with the file manager • efficient storing, retrieving and updating of data P. T. Chung CS 649 DBMS

  31. File Manager • File Manager is responsible for mapping logical database units (objects, relations, etc.) into a set of low level files. • It is responsible for maintenance of files and indexes on them. It should be able to create and destroy index and collect unused storage space to eliminate an unneeded gaps on disks. P. T. Chung CS 649 DBMS

  32. Buffer Manager • Buffer Manager is responsible for the allocation and maintenance buffer space in a memory to facilitate processing database data by several concurrent applications. • Buffer Manager decides when to load data from a buffer to a database or discard the data and under what conditions a new data should be put into a buffer P. T. Chung CS 649 DBMS

  33. Authorization and Integrity Manager • This manager is responsible for granting an access to database or portions thereof only to authorized users and preventing the access to unauthorized users • Integrity manager must assure data integrity during normal database operations as well as during the database failures P. T. Chung CS 649 DBMS

  34. The DBMS Marketplace • Relational DBMS companies – Oracle, Sybase – are among the largest software companies in the world. • IBM offers its relational DB2 system. With IMS, a nonrelational system, IBM is by some accounts the largest DBMS vendor in the world. • Microsoft offers SQL-Server, plus Microsoft Access for the cheap DBMS on the desktop, answered by “lite” systems from other competitors. • Relational companies also challenged by “object-oriented DB” companies. • But countered with “object-relational” systems, which retain the relational core while allowing type extension as in OO systems. P. T. Chung CS 649 DBMS

  35. Logical Data Models • Acollectionoftoolsfordescribing • data • datarelationships • datasemantics • dataconstraints • Valuebasedmodels: ERModel, OOModel • Record Based Models: Relational Model P. T. Chung CS 649 DBMS

  36. Entity-Relationship Model • The enterprise data can be described as a set of entities and a set of relationships between them. • Entity – a data that pertains to, or describes some component of the enterprise • Each entity is characterized by a set of attributes • Relationship – describes an interconnection between different entities • Entity Set – a set of entities that are characterized by the same entity definition • Relationship Set – a set of relationships of the same type P. T. Chung CS 649 DBMS

  37. Entity-Relationship Model Example of schema in the entity-relationship model P. T. Chung CS 649 DBMS

  38. Object – Oriented Model • An enterprise is described as a collection of objects and a collection of algorithms that work with objects • Example: Person is an object. • Object is characterized by a set of public attributes. Applications may refer only to public attributes; private attributes . Algorithms that implement the object may refer to private attributes; a set of protected attributes and a set of methods • Attribute of an object can be another object • Objects are nested into a hierarchy and can inherit attributes of their parents P. T. Chung CS 649 DBMS

  39. Object Oriented Model OBJECT DATA MODEL 1. Complex Objects – Nested Structure (pointers or references) 2. Encapsulation, set of Methods/Access functions 3. Object Identity 4. Inheritance – Defining new classes like old classes Object model: usually find objects via explicit navigation Also query language in some systems P. T. Chung CS 649 DBMS

  40. Example • Class Person{ public: Person(); ~Person(); float GetSalary(); float PutSalary(float&); string Name; int SSN; date BirthDate; private: float salary; } P. T. Chung CS 649 DBMS

  41. Object-Oriented ModelData Encapsulation • An object contains both data and methods to work with the data • The physical data representation is visible only to the object creator. • The implementation details of methods are not visible to object users • An interface of the object consists of public attributes and methods • Each object is characterized by an object identity P. T. Chung CS 649 DBMS

  42. Relational Model • An enterprise is represented as a set of relations • Domain – is a set of atomic values. Each domain has a NULL value. • Data type – Description of a form that domain values can be represented. • Relation is a subset of a cartesian product of one or more domains • The elements of relations are called tuples. Each element in the cartesian product is called attribute. P. T. Chung CS 649 DBMS

  43. Relational model is good for: Large amounts of data —> simple operations Navigate among small number of relations Difficult Applications for relational model: • VLSI Design (CAD in general) • CASE • Graphical Data ALU ADDER CPU A FA Adder ALU ADDER Bill of Materials or transitive closure P. T. Chung CS 649 DBMS

  44. Relational Model Attributes Street City gpa Name Student-id • Example of tabular data in the relational model Johnson Smith Johnson Jones Smith 192-83-7465 019-28-3746 192-83-7465 321-12-3123 019-28-3746 Alma North Alma Main North 3.6 2.7 3.2 4.0 3.45 Palo Alto Rye Palo Alto Harrison Rye P. T. Chung CS 649 DBMS

More Related