Database

Flat File Database / Spreadsheet

Know that the term "flat file database" describes a very simple database model, where all the information is stored in plain text files, one database record per line. Each record is divided into fields using delimiters or at fixed column positions. The data is "flat", as in a sheet of paper, as compared to amore complex model such as a relational database.

Flat file databases are very handy when every record contains the same attributes with simple values. This is true of simple tables such as a list of names and phone numbers or tables of scientific data with one key attribute and one or more simple descriptive attributes such as the example of chromosome data from UW-FHCRC (http://pga.gs.washington.edu).

The main problem with flat file databases is that they don't handle redundancy very well. If one or more of the entities in the database have the same value for an attribute, and that value is a complex value like "publisher information" or "course information", then the complex value must be repeated for each entity in the database. When the same information is repeated, it is very likely that it will contain small (or large) differences each time it is entered, and so the database will be inconsistent. Inconsistent data is a bad thing!

Relational Database

One solution to the redundancy issue is the relational database. In this design, the data is split apart into tables of basic information. Tables store information about entities. Entities have characteristics called attributes. Each row in a table represents a single entity: each row is a set of attribute values and every row must be unique, identified by a primary key (an attribute or set of attributes whose values are unique for every row in the table). Relationships among the attributes in the tables are stored and can be used to reconstitute the original data. Tables have names, attributes, and rows. Each table represents a set of entities. The schema for a table describes the name and type of each attribute in the table.

In actual practice, we use a database management system (DBMS) to manipulate our database. The DBMS defines additional operations based on basic algebra. An important operator is the join operator that combines rows from two tables if the value of a common attribute matches.

Generally, each table will have an attribute or set of attributes that is unique for every possible row in the table. This attribute set is identified as the primary key. If the table has a relationship with another table, then the attribute (or set of attributes) that identifies the associated record in the other table is called the foreign key. The possible values of the foreign key in one table are chosen from the values of the primary key in the other table. The join operator matches the value of the foreign key given in one table with the value of the primary key in the other table in order to find the rows that should be combined.

An entity-relationship diagram is often used to describe the logical architecture of a database. Each entity name is shown, representing the table that contains one or more instances of that entity type. The attributes of each entity are shown. In the tables, these attributes will be columns of the tables. Relationships are shown as connecting lines between entities, with a description of the relationship shown in a diamond. In the tables, these relationships will be implemented as attributes (columns) that contain common values in the two related tables.

Relationships can express several different types of connections. In a one-to-one relationship, an entity in one table is associated with one and only one entity in another table. For example, this sort of relationship might be used to associate a student with a transcript: each student has one transcript, and a particular transcript is associated with only one student.

In a one-to-many relationship, an entity in one table can be associated with one