Last Wednesday, we went over the syllabus for the class and then started a brief intro lecture on databases.
We said a database was a collection of related data where data is a collection of known fact with some explicit meaning.
We said databases are a kind of mini-world of information used to represent some facet of the real world and have an intended purpose.
For example, one might have a database concerning employees in a company. The database serves as an easy to look up memory of certain characteristics of employees.
We said when changes in the real world happen, these are reflected in the database. When employees are hired or fired, when employees get raises, etc., these events should be recorded in the database.
We said a database management system (DBMS) is used to create, provide access to, and maintain a database.
We then gave a concrete example of a student courses databases.
Today, we start by briefly describing some characteristics of databases before giving a short history of how database systems developed...
Characteristics of Database Systems
A database system is self-describing -- a DBMSs uses a database to keep track of what databases are stored in it, what tables they have, etc.
A database system provides insulation between programs and data. It supports data abstraction (conceptual representation of the data).
A database system supports for multiple views of the data.
Often a database system allows for sharing of data and multi-user transaction processing.
People connected with Databases
Database Administrators (DBAs)
Database Designers
End user
Casual end users (managers)
Naive or parametric users (data entry)
Sophisticated End-Users (engineers, scientists)
Stand-alone users
And more
DBMS designers
Tool developers
Operators and maintenance personnel.
Early Database Management Systems (DBMSs)
The first commercial DBMSs appeared in the 1960s.
These systems evolved from file systems, which could support the storage of large amounts of data over a long period of time. However,
File systems by themselves don't generally guarantee data won't be lost if it is not manually backed up.
File systems by themselves don't support efficient access to data items whose location in a particular file is not known.
File systems by themselves don't support a query language for data in files (ones limited to a tree-like file structure)
File systems by themselves don't support a way to flexibly define records.
File systems by themselves don't support a way to ensure the integrity of transactions.
DBMSs added these capabilities to the basic primitives of the files system.
Early DBMS Applications
The first important applications of DBMSs were ones where data was composed of many small items, and many queries or manipulations of these items were made.
For example:
Banking systems: maintaining accounts and making sure that system failures did not cause money to disappear.
Airline reservation systems: these also need to make sure data won't be lost, and they must accept very large volumes of small actions by customers.
Corporate Record Keeping: employment and tax records, inventories, sales records, and a great variety of other types of information.
Early systems required programmers to visualize data much as it was stored, either in tree-like hierarchies (hierarchal model) or in simple directed acyclic graphs (network model).
This limited the kinds of queries that could be performed on the data.
Quiz
Which of the following is true?
A random collection of data with no organizing theme is a database.
In-class exercises this semester are each worth one point directly towards your grade.
A typical DBMS will provide a query language to allow users to ask questions about what data is stored in the database.
Relational Database Systems
The foundations of the relational model appeared in a paper by Ted Codd in 1970.
Codd proposed that a DBMS should present the user with a view of data organized as tables called relations.
This notion was abstracted away from the low level details of how the records in the relations/tables are stored.
Storage might include data structures that allowed rapid response to a variety of queries.
Queries could be expressed in a high level language which greatly increased the efficiency of database programmer.
Codd's paper motivated the development of several early systems such as IBM's System R, Berkeley's Ingres, and later Oracle, such that, by the 1990s, most DBMSs were based on the relational model.
The 1990s and 2000s
Originally, DMBSs were large expensive systems running on large computers.
As time has progressed, both the memory and processing capabilities needed for DBMSs became available on cheaper and cheaper machines.
Today, all but the cheapest cell phones, all modern browsers, etc run some form of a database (usually Sqlite, a relational database developed in the 2000s).
This is used to keep track of persistent information in apps such as user preferences, etc.
During the same time period there has been a rise in the quantity of semi-structured data. Semi-structured data is data
partially split into fields and attributes or nested attributes. For example, web data in HTML, JSON, or XML.
During this time period many relational systems started to incorporate ways to handle this kind of data natively both for manipulation and for querying.
Bigger and Bigger Systems
Another trend of the last several decades is for the quantity of data stored by systems to grow.
In the 1980s and 1990s a gigabyte (`10^9` bytes) of DBMS data was considered a lot. Now systems with terabytes of data are common (`10^12` bytes), and large corporate systems might need to deal with petabytes (`10^15` bytes) or even exabytes of data (`10^18` bytes).
For example:
A billion web pages can safely be stored in less than 20 terabytes of web pages.
Google stores between 10-20 billion web pages and needs petabytes of memory to handle all of the indices used for querying of this data.
Satellites send down petabytes of information daily into specialized databases for geographical information.
Picture sites like flikr, 4chan, etc store millions of pictures each of several thousands of bytes. Even Amazons has databases of over a million pictures.
An hour of video takes around a gigabyte. YouTube holds about 1.2 billion of such videos and needs to make them available.
Peer-to-peer systems and content delivery networks use large networks of conventional computers to store and distribute data of various kinds.
Often the systems above are so large that it is more efficient to use modification to the basic relational model to handle the data they contain.
This led to the development of various NoSQL databases.
Information Integration
One standard problem in building and maintaining databases is to join information contained in many related databases into a whole.
This is called information integration.
For example, a large company has many divisions, each of which has built its own database of products or employee records independently of the other divisions.
This can easily happen especially if one division was originally a separate company that was acquired.
Over the course of the history of a company several databases might have been used and some data might still only exists in some of the earlier system.
As a result, one often needs to build a structure on top of existing databases to allow all the data to be accessed from one interface.
Another approach to the information integration problem is to periodically copy the data from these legacy system into a central system known as a data warehouse.
Overview of a Database Management System
Above is a diagram of the parts of a complete DBMS, boxes represent system components, double boxes represent in-memory data structures, and lines indicate control and data flow.
We are going to briefly consider the parts of this model over the next couple slides.
Sources of Commands
Starting at the top of the diagram, we see there are two distinct sources of commands to the DBMS:
Conventional users and application programs that ask for data or modify data.
A database administrator (DBA): a person or persons responsible for the structure or schema of the database.
Let's consider the second kind of command first as it is simpler to trace through the diagram.
Data-Definition Language Commands
Let's consider an example: A DBA for a registrar's database might decide that there should be a table or relation with columns for a student, a course the student has taken, and a grade for the student in that course.
The DBA might also decide that the only allowable grades are A,B,C,D and F.
This structure and constraint information is all part of the schema of the database.
This info could be entered by the DBA in a Data Definition Language.
To execute these commands, the DBA needs special authority over the database a typical user would not have to execute DDL commands.
These commands would then be parsed by the DDL processor and passed to the execution engine, which then goes through the index/file/record manager to alter the metadata schema information for the database.
Overview of Query Processing
The majority of interactions with the DBMS follow the path of the left hand side of the figure a few slides ago.
A user or an application program initiates some actions using the data-manipulation language (DML)
This command does not affect the schema of the database, but it may affect the content of the database.
Alternatively, it might extract information from the database.
For this reason DML statements are handled by two separate subsystems: the query subsystem and the transaction subsytem.
Answering a Query
A query is parsed and optimized by a query compiler.
This results in a query plan or a sequence of actions the DBMS should perform to answer the query.
In turn, this is passed to the execution engine to actually calculate the results of the query.
The execution engines issues a sequence of requests for small pieces of data, typically records or tuples of a relation to a resource manager.
The resource manager knows about data files for holding relations, the format and size of records in those files, as well as index files which help finds elements of data files quickly
The requests for data are passed to the buffer manager. The buffer manager's task is to bring appropriate portions of the data from secondary storage (disk) where it is kept permanently, to the main memory buffers. It does this in units of disk blocks.
Transaction Processing
Queries and other DML actions are grouped into transactions.
These are units of DB actions that must be executed atomically and in isolation from one another.
We also require that transactions must be durable, meaning that the affect of any completed transaction must be preserved even if the systems fails.
A transaction processor usually has two main parts:
A concurrency-control manager (aka scheduler) responsible for assuring atomicity and isolation of transactions.
A logging and recovery manager responsible for the durability of transactions.
