DBMS vs. Traditional Methods: Benefits of Database Management Systems, Slides of Introduction to Database Management Systems

Download DBMS vs. Traditional Methods: Benefits of Database Management Systems and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems Design

Why do we need Databases?

• Suppose that you own a bank.
• You need to keep track of information about your costumers and their accounts. - Customer personal information - Account information - Receipts of the transactions performed
• There are several possibilities to accomplish this
  • Do nothing, throw papers in a box.
  • Keep records sorted alphabetically
  • Buy a custom program to manage your information.
  • Get a database system and get a database application to manage your information.

Option 2: Keep records sorted

• Rather than throwing your paper in a box, you

buy a cabinet and keep all records sorted, perhaps alphabetically on the costumer name.

• Advantages
  • Cheap
  • Easier to use
• Disadvantages
  • Too slow to find aggregated information about the bank (e.g. Which are the 10 most active accounts?).
  • Still slow, since human has to find the records.
  • Cabinets take too much space

Option 3: Buy a custom program

• Buy a program that uses the file system in a

computer to store all the data associated with your bank.

• Advantages:
  • Fast
  • Takes up little space
• Disadvantages:
  • Expensive
  • Difficult to add new features
  • Reliance on expertise of programmers
    • Re-inventing the wheel

Database and DBMS

• A database is a collection of data that describes the

inner structure or inner workings of an enterprise.

• A Database Management System (DBMS) is a software

system used to maintain the data stored in one or more databases.

• Databases is also meant to convey the area of

Computer Science devoted to the study of hardware, software, algorithms, data structures and other techniques required to design and implement a DBMS or an application that use a DBMS.

Top Database Groups and Products

• Academia:
  • 1. Stanford University, 2) University of Wisconsin, Madison, 3) University of California, Berkeley, 4) University of Maryland, College Park, 5) University of Washington.
• Research Labs:
  • 1. IBM Almaden Research Lab, 2) AT&T Research Labs,
    2. Microsoft Research Lab, 4) Lucent Technologies.
• Database Products:
  • 1. Oracle, 2) IBM DB2, 3) MS Access, 4) Sybase, 5) MS SQL Server, 6) Informix.

Modeling data by levels

• Conceptual Schema
  • Logical description of the data
  • Relationships between data items
  • Includes:
    • Table names
    • Column names and types
    • Integrity constrains
• Physical Schema
  • Deals with the organization and storage of the data within the database
  • Details on how to access the data from disk
  • Includes:
    • Files
    • Indices
    • Data partitioning

Modeling data by levels (cont.)

• External Schema

• Customized version of the global conceptual

schema

• Allow for tailoring access of data for a particular

user(s)

• Can also be used to prevent unauthorized user

from accessing sensitive data.

• Usually defined by means of views on the

conceptual schema

DBMS: Client API

• The Client API contains the infrastructure necessary to:
  • Accept connection from client applications
  • Submit queries to the DBMS
  • Extract the data from the database
  • Send commands to create/delete records or tables in the database.
  • Start/end operations such as transactions, recovery or backups.
• Examples APIs:
  • JDBC, ODBC (standards)
  • Informix MI API (proprietary)

DBMS: Query Parser

• The parser takes care of analyzing the syntax of the commands send to the DBMS via the client API.
• Many parsers, also check some the semantics in the statements, so they are more than just parsers.
• This layer also generates an initial representation of the query that has been posed to the system
• Often the parser fetches metadata from catalog in order to give the next layer some valuable information to perform their task.
• Example: SQL parser found in most DBMS
  • Also, OQL parser found in many Object-Oriented DBMS.

DBMS: Relational Operators

• At this layer, the set of relational operator supported by the DBMS are implemented.
• The most common of the operators are:
  • Selections
  • Simple projections
  • Generalized projections
  • Aggregates
  • Sorting
  • Joins
  • Unions
• An execution plan produced by the optimizer is used to represent the set of operators to be executed.

DBMS: File and Access Methods

• At this layer, we find the implementation of the various mechanism available to access the data in each table.
• The basic service in the unordered file, which is also called the heap. This provides a service to access record sequentially and in no particular order.
• Also, we find mechanisms to index the records in a table. Using these indices, we can speed up the execution of query by only reading the necessary records from the database.
• Example indices: B+-tree, Hash-index, ISAM, R-tree.
• Lots of research papers and Ph.D. thesis have been written on this subject.

DBMS: Disk Space Management

• This layer provides the abstraction of a page of data

from disk..

• This layer provides the infrastructure necessary to

create, delete, read and write data pages associated with a database.

• This is the lowest layer in the system, often dealing

with issues such as:

• Fixed-sized vs. variable length records
• Striping of data pages (partitioning across multiple disk).
• Memory alignment of data when moved between disk and memory.

Advantages of a DBMS

• Data Independence
  • External schema shields from changes in conceptual schema - Logical data independence - Remove columns, add columns in base tables
  • Conceptual schema shields from changes in physical schema - Drop an index - Re-partition data over new disks
• Efficient data access
  • Proven, sophisticated data techniques to quickly read/write data
  • No amateur work…