A database is a structured collection of data in a computer system that allows arranging, modifying and extracting information easily in different logical orders. Data on its own is raw facts with no meaning; it becomes information once given a meaningful form, and knowledge once a conclusion is drawn from it.
The Structure of a Database
Within a database, data is organized into storage containers called tables, made up of columns (fields) and rows (records). Key terms:
- A table contains the basic data or information stored about an entity of the database.
- A field (or attribute) represents one column and is the smallest logical unit of storage.
- A record (or tuple) is a row of the table, a collection of related fields about one entity.
- A query retrieves information from one or more tables based on defined search conditions.
- Forms are tools that make data entry into tables easier; Reports present data using a specific layout.
- A key field or primary key is a field (or fields) whose value distinguishes one record from another. A foreign key is a column that links to the primary key of another table, cross-referencing the two tables.
- An entity is a single data item – a person, thing, place or event (a table’s name).
Advantages and Disadvantages of a Database
Advantages: less data inconsistency since each item is stored in fewer locations; enables querying and faster searches; data is independent of application; easier to maintain confidentiality.
Disadvantages: requires trained personnel to design, implement and maintain; a total system failure can severely impact the establishment.
Database Models
A database model determines the logical structure of a database – how data can be stored, organized and manipulated. The most popular is the relational model, which uses a table-based format. Other examples include the flat file, hierarchical, object-oriented, network and multi-dimensional models.
A. Entity Relationship (ER) Model
A diagrammatic representation of the relationship between entities. An entity is a “thing” or “object” distinguishable from all other objects, with a set of attributes. A relationship is an association among several entities. In an ER diagram, a rectangle represents an entity set, an ellipse represents an attribute, and a diamond represents a relationship.
Relationships between two entities are called cardinality and are of three types:
- One-to-one (1:1): one occurrence of an entity relates to only one occurrence in another, e.g. a man marries only one woman.
- One-to-many (1:M): one occurrence relates to many occurrences in another, e.g. one manager manages many employees.
- Many-to-many (M:M): one record in a table can relate to many records in a second table and vice versa, e.g. one teacher teaches many students and a student is taught by many teachers.
B. Relational Model
A lower-level model that uses a collection of tables to represent both the data and the relationships among it, physically represented as a relation (table). The software used is called a Relational Database Management System (RDBMS). Referential integrity is a property that enforces valid relationships between tables, so no foreign key can contain a value that does not match a primary key in the linked table.
To transform an ER model into a relational model: the entity name becomes the name of the relation/table, and the attributes become the column names. Example: CUSTOMER (Customer-ID, Customer-name, Customer-Address) and ORDER (Order-ID, Order-Date), where Customer-ID becomes a foreign key in the ORDER relation. Normalization is the process of breaking data down into record groups following normal forms, to eliminate redundancy and improve data integrity.
C. Other Database Models
- Hierarchical model: uses nodes to link and structure fields and records, in a one-to-many relationship.
- Object-Oriented Database (OOD): data is organized using objects, classes, attributes and methods.
- Network model: similar to hierarchical, except a child node may have more than one parent (many-to-many).
- Multidimensional model: extends two-dimensional relational tables to three or more dimensions (a hypercube).
A database’s description, including table structures and rules, is called a schema, stored in a data dictionary – a catalogue of metadata listing all tables, record counts, and field names/types.
