Databases: a brief overview

Desired Features of a Database Management System (DBMS)  

 

• Easy to store, retrieve, modify data
• Security
• Handle Concurrent Access
• Data Recovery
• Consistent Data
• Metadata should be available.

DBMS  Models  

Flat-File Not really a database, simply store data in files.

Network Stores data in a network structure.

Hierarchical Stores in a hierarchical structure. Old, not used anymore

Relational Stores data in tables. Makes use of the Math concepts of relations. Since 1970s. Example Oracle.

Object-oriented Expands upon Relational model to handle more complicated kinds of data in non-transactional manners.

---

 

Relational Databases

Most of today's modern databases are what are called relational databases.  Basically, this means that data is organized in tables.  Each table consists of a set of columns, each column representing some item of information.  For example, below is a table representing information about software distributors.
 

A relation represents an operation on a database described by Mathematical Set Operations such as unions and joins.

A Union B  = all the elements of set A and B.
A Joint B = only the comment elements of set A and B.

 

Name	Street Address	City	State	Distribute
Butch Grewe	100 Campus Dr.	Seaside	CA	Microsoft,  Adobe
Doug MacIntire	100 6th Street.	NY	NY	Enterprise Suite

They keys here are "Name, Street Address, City, State, Distribute"

We can search a database by searching against certain key values which represent values for the entries we are interested in retrieving.  For example, we may want to find all the entries in the Database that have the key State = CA.  In this case, we would retrieve the Butch Grewe row.   Similarly if we searched with the key State=FL we would retrieve no items.  To search a database you must use the language the Database understands.  Most databases understand a version of a scripting language called SQL.

More about Relational Databases:

• All data stored in a column is of the same data type.
• Each row is uniquely identifiable.
• Columns do not have an order.
• Rows do not have a order.
• Columns have unique names,  these are called keys.
• Data integrity maintained across tables.
• Primary Key = A column or set of columns whose values are used to uniquely identify rows (records) in a relational table.  A primary key consisting of multiple columns are referred to as composit or concatenated keys.  Can not be null or empty, nor duplicates..
• Foreign Key = A foreign key is a column or set of columns whose values serve as primary keys in some other relational table.  It should be NULL or should match exactly the primary key value of some other table it references.
• Any data item can be accessed given: table name, primary key value, and column name.
• NULL should be used consistently to represent no data.   You can assign a Default value for a column.

 

---

Tablespace and Tables
 

Tablespace

Is a set of segments and consist of one or more data files.  May contain many kinds of multiple data objects such as tables, indexes, etc.

EXAMPLES in Oracle

System tablespace = Contains the data dictionary, stored procedures, triggers, and system rollback segment.  Used for system maintenance and operation.

Data tablespace= Stores user data in tables and indexes.  User data tablespaces don't need to be online all of th time unlike system tablespace.  Need to be online in order to acces data in them. Note: There is often a defined default tablespace for each user. A user does not have to specify the tablespace when creating new objects, like tables, that they want to be in the default tablespace. The DBA sets up what the default tablespace is when creating a user.

Temporary tablespace= Used by operations, such as sorting, unions, joins, that take up temporary space.

Table

Sits inside of a particular tablespace.  Contains data in a table (row, column) format. When creating a table you can specify the tablespace it is to be inside of. If not the default tablespace for the user is assumed.

TIP: Think of a database as a filing cabinent: the drawers withing the cabinet are tablespaces and the folders in those drawers are datafiles, and the pieces of paper in each folder are tables or other database objects, information written on a piece of paper is the data stored in the table.