Transcript Slides
Database Management System • An Information system is a model about some phenomena in the world - the Universe of Discourse - (e.g. an organization) – retrieves, updates and deletes these facts – derives other facts from existing ones • Database: A database contains a collection of related items or facts arranged in a specific structure. OR A shared collection of logically related data (and a description of this data), designed to meet the organization needs. The most obvious example of a noncomputerized database is a telephone directory. Three of the most important terms to know about database are: • Fields • Records • Tables Types of Databases • Operational Databases: (Transaction, production databases) (Example Customer, inventory, HR database) • Distributed Databases (Any type of database) • External Databases (Commercial Online services – Example full text databases) • Hypermedia Databases (Hyperlinked pages of multimedia – set of interconnected multimedia pages at a website is a database of interrelated hypermedia page elements, rather then interrelated data records) DBMS: • To make large collections of data useful, people and organizations use computers and an efficient data management system. • A DBMS is a software tool that allows people to store, access, and process data or facts into useful information. Why Use a DBMS? • Data independence and efficient access. • Reduced application development time. • Data integrity and security. • Uniform data administration. • Concurrent access, recovery from crashes. Functions of a DBMS Data storage, retrieval, update A user-accessible catalog Transaction support Concurrency control Recovery services Authorization services Support for data communication Integrity services Data independence Utility services There are four essential elements of DBMS • A Modeling Language – Data Model (hierarchical, network, relational and object models ) • Data Structures (fields, records, files and objects) optimized to deal with very large amounts of data stored on a permanent data storage device (which implies relatively slow access compared to volatile main memory). • A database query language to allow users to interactively interrogate the database, analyze its data and update it according to the users privileges on data. • Transaction Mechanism: This helps to allow multiple and concurrent access to the database by multiple users, prevents the manipulation of one record by two users at the same time, and preventing the creation of duplicate records. Purpose of Database Systems Database management systems were developed to handle the following difficulties of typical fileprocessing systems supported by conventional operating systems: • • • • • • • Data redundancy and inconsistency Difficulty in accessing data Data isolation – multiple files and formats Integrity problems Atomicity of updates Concurrent access by multiple users Security problems Database Structures / Models Hierarchical Structure: • Early mainframe DBMS packages used it. • Relationships between records form a hierarchy or treelike structure. • Root Record and subordinate levels. • Relationship among records are 1:M Network Structure: • Represents more logical relationships • Allows M:N relationships among records • Access to elements by several paths • Data elements or record can be related to any number of other data elements. Relational Model: • Most widely used. • All data elements within database are viewed as being stored in the form of simple tables. RDBMS • A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd • A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables. • presented the data to the user as relations (a presentation in tabular form, i.e. as a collection of tables with each table consisting of a set of rows and columns) • provided relational operators to manipulate the data in tabular form • Relationships can exist between different tables (1:1, 1:M, M:N) • Most popular commercial and open source databases currently in use are based on the relational model. Entity-Relationship Model Example of entity-relationship model social-security customer-name customer-street customer-city customer depositor account-number balance account Relational Model Example of tabular data in the relational model: name Johnson Smith Johnson Jones Smith ssn 192-83-7465 019-28-3746 192-83-7465 321-12-3123 019-28-3746 street Alma North Alma Main North city Palo Alto Rye Palo Alto Harrison Rye account-number A-101 A-215 A-201 A-217 A-201 account-number balance A-101 500 A-201 900 A-215 700 A-217 750 Data Definition Language (DDL) • A Data Definition Language (DDL) is a computer language for defining data structures. • DDL compiler generates a set of tables stored in a data dictionary • Data dictionary contains metadata (data about data) • Create, Drop, Alter, Referential Integrity statements (Primary Key, Foreign Key) • Example: CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name CHAR(50) NULL, last_name CHAR(75) NOT NULL, dateofbirth DATE NULL ); Data Manipulation Language (DML) • is a family of computer languages used by computer programs or database users to retrieve, insert, delete and update data in a database. • the most popular data manipulation language is that of SQL • Select, Insert, Update, and Delete • Example: Select first_name from employees; Data Control Language (DCL) • A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database. • Examples of DCL commands include: • GRANT to allow specified users to perform specified tasks. • to cancel previously granted or denied permissions. 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 Database 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 Database Users • Users are differentiated by the way they expect to interact with the system. • Application programmers: interact with system through DML calls. • Specialized users: write specialized database applications that do not fit into the traditional data processing framework • Sophisticated users: form requests in a database query language. • Naive users: invoke one of the permanent application programs that have been written previously Data Sharing in a Database Environment reservation data loan data reservation program loan program overdue letters overdue loans program DATABASE MANAGEMENT SYSTEM book reserv ation loan person Advantages of Database Environment • Sharing of data • Enforcement of security • Enforcement of development and maintenance standards • Reduction of redundancy • Avoidance of inconsistency across files • Maintenance of integrity • Data independence • Firm theoretical foundation (for the relational model) Disadvantages • • • • • • size complexity cost Additional hardware costs Higher impact of failure Recovery more difficult Data Planning and Database Design • Database development involves data planning and database design activities • Data models that support business processes are used to develop databases that meet the information needs of users. Database Design: Scenario Solution A Simple scenario to Implement Residency Accommodations have recently expanded there business. They provide Hotel rooms and private rooms in Houses / apartments. Their business processes are carried manually, thereby causing problems for managers to figure out daily reports covering different aspects of the business. Initially as a prototype a simple information system is required for Residency Accommodations capable of holding records of customers, accommodation owners, accommodation bookings and details of each accommodation. The system is intended to handle day to day normal operations and to support managers specifically in identifying their customers, owners, bookings and accommodation details in form of printable system generated reports.