Databases

Having gained some idea of the history of Normalisation, let's just clarify what a database actually is. Databases can take almost any form from card indexes to data stored on computer systems.

         

Whilst computerised data stores are more effective and efficient in managing data than manual data stores, it is worth noting that any store of data can be considered a database e.g. a word document or a spreadsheet. However, it is invariably far more appropriate to use a "Relational Database" than a Word document or a Spreadsheet when handling large volumes. This is because Word documents are unwieldy beyond a few dozen pages and a Spreadsheet is difficult to manage when it contains hundreds of rows of information.

A Relational Database can provide control over how data is used and how the data is stored and shared with other users. The database stores information in a series of linked tables, designed in a way that minimises the number of repeating data items. Such systems provide facilities that make it easy to index and manage large amounts of data in many tables thereby permitting efficient data storage.

 

Some Definitions of frequently used Database Terms:

 

Entity

A person, place, thing or event; about which we want to store data, in the database. (a car or a customer are both entities)

 

Attribute

A characteristic of an entity that we want to record or later retrieve; a fact about an entity. (the colour of a car or a customer’s name are attributes)

 

Master table and Detail table

The relationship between tables is like a ‘master and slave’ relationship. The master controls the detail.

 

Primary Key

Unique Identifier for the primary COLUMN in the table - and the data item that uniquely identifies each record.

•     Use integers to save memory & increase speed

•     Can be “composite” or “compound” Key (means made of multiple parts [or multiple keys])

 

Foreign Key

A key from a detail table that is used to create the linking relationship between the primary key in the master table or another record in another table. It has the same name and properties as the primary key from which it was copied.

 

Data Integrity

Refers to the validity, accuracy & consistency of the data in a database; the problem of data integrity involves potential inconsistencies, arising during updates or simultaneous users accessing a shared file.

 

DBMS – Data Base Management System

A collection of programs that enables you to store, modify, and extract information from a database.

Technically DBMSs can differ widely. The terms relational, network, flat, and hierarchical refer to the way a DBMS organizes data internally. The internal organization affects how quickly and flexibly data can be extracted. Requests for information from a database to retrieve data are made in the form of a query. Most DBMSs include a report writer program that enable data output in the form of a report and a graphics component that enables graphs and charts output.

 

Referential Integrity

Referential Integrity is a feature provided by Relational Database Management System (RDBMS) that prevents users or applications from entering inconsistent data. Most RDBMS have various referential integrity rules that are automatically applied when you create a relationship between two tables.

For example, suppose Table.Car has a foreign key that links to a field in Table.Rental. Referential integrity would prevent you from adding a record to Table.Car that cannot be linked to Table.Rental.

Also closely related to the referential integrity rules; are the Cascade Update and Cascade Delete rules.

 

Relationships

	One to Many relationships – are abbreviated to 1:M - e.g. one customer makes many rentals.
	Many to One relationships - are abbreviated to M:1 - e.g. one car has many customers who drive it.
	Many to Many relationships - are abbreviated to M:M.

Cascade Update

Whenever you modify or update the value of a linked field in Table.Rental, all records in Table.Car that are linked to it, will also be updated. This is a cascading update - the updates 'cascade' down from the master table to all the detail tables.

 

Cascade Delete

Whenever you delete a record from Table.Rental, any records in Table.Car that are linked to the deleted Rental record, will also be deleted. This is a cascading delete - the deletions 'cascade' down from the master table to all the detail tables.

 

Functional Dependency

An attribute (customer address) of an entity (customer) is FUNCTIONALLY DEPENDANT on another attribute (customer name) ONLY if each occurrence of the first attribute (customer name) has associated with it only ONE occurrence of the second attribute (customer address). A customer will only supply one billing address to the Car Hire Company.

 

Commit & Rollback

A standard mechanism for ensuring completion of a set of transactions involves; commit (like a Save) and rollback (rolls the data back to the previous, ‘good’ save).

Very often during data processing; transactions are grouped together. Transfer of funds using a debit card from the Car Hire Customer’s bank account to The Car Hire Company bank account is an example; where the transaction process must ensure that BOTH steps are satisfactorily completed.

In using commit and rollback, a set of transactions must be identified as belonging to a single group. Then, when the entire group of transactions is completed satisfactorily, a commit statement is issued to make sure that all changes are stored on the database server. If an error prevents completion, a rollback command is issued that undoes all the transactions in the set so far completed, returning the database to its original, pre-change state.