As part of a series of articles on database management systems, we look at traditional versus computerized databases, the weaknesses of the former versus the benefits of the latter. We look at implementation steps from setting up data tables, populating them to manipulating and presenting the data.
What is a database management system?
A database management system (DBMS) is a software package which is used to create, manipulate and present data from electronic databases. Examples of DBMSs include Microsoft Access and Filmmaker Pro.
Traditional databases
Weaknesses
Storage of paper records was very bulky. It was also easy to mis-file a record, or records to be lost or damaged. Data was often duplicated in several records. Keeping records up-to-date was difficult and time consuming, and often resulted in data inconsistency, where duplicated values were updated in one record but not in others.
Many people were employed to maintain the records, which was costly. Searching for records was time consuming. Producing reports, such as sorted lists or data collated from several sources, was extremely time consuming, if not impossible.
Benefits of computerized databases
Searching, sorting and calculating operations can be performed much more quickly and easily. Information is more easily available to users, due to improved methods of data retrieval. Data integrity is improved resulting in more accurate information.
Types of computerized databases
- Flat file – Tabular (All data in a single table)
- Relational
Flat file databases
Limitations of flat file databases
Data is very likely to be duplicated. The duplication of data leads to the possibility of data inconsistency. It is not possible to store information about a member without entering details of a DVD. This is called an insertion anomaly. Removing a DVD from the database may remove the only record which stores details of a Member. This is called a deletion anomaly.
Relational databases
A relational database stores data in more than one table. The idea is to ensure that data is only entered and stored once, so removing the possibility of data duplication and inconsistency.
Entities, Attributes and Instances
An entity represents a person or object e.g. Member, DVD Rental. Each entity has a set of attributes which describe examples or instances of that entity.Â
The attributes of the DVD Rental entity are code, title, cost, date out, date due and member number. The attributes of the Member entity are member number, name and telephone number. Â
Data Relationships
Three types of relationships
- One-to-one
- One-to-many
- Many-to-many
Keys
A key is a field, or set of fields, whose values uniquely identify a record. In any table, there may be more than one field or set of fields, which can uniquely identify each record—these are called candidate keys. The candidate key which is chosen to be used is called the primary key.
- Member Number is a candidate key for the Member entity
- MEMBER (Member Number, Name, Telephone Number)
- DVD Code is a candidate key for the DVD Rental entity
- DVD RENTAL (DVD Code, Title, Cost, Date Out, Date Due, *Member Number)
- Member Number is called a foreign key.Â
- A foreign key is a field which is not a primary key in its own table, but is a primary key in another table.Â
- Member Number is a foreign key in the DVD table, because it is the primary key in the Member table.
- Here is the data model:
- MEMBER(Member Number, Name, Telephone Number)
- DVD RENTAL(DVD Code, Title, Cost, Date Out, Date Due, *Member Number)
Implementation
3 steps:
- Set-up the tables
- Populate the tables
- Manipulate and present the data
Setting up the tables
Which tables are required?
The tables correspond directly to the entities in the data model. In this case, there will be two tables, Member and DVD Rental.
Which fields are required?
The fields in each table are the attributes in each entity in the data model.
What are the properties of each field?
Its name – be consistent! Its data type.
- text
- numeric (integer, real, currency)
- date or time
- Boolean (yes or no)
- link
- object
Validation:
- Presence check
- Restricted Choice check
- Range check
Populating the tables
- Take care to be accurate
- Validation: make sure the data is sensible
- Verification: make sure the date is correct
- Verification methods:
- Bar codes, OCR
Manipulating the Data
- Searching records
- Sorting records
- Calculating values
- Presenting results
Searching
Which fields will be used to identify the records required?
What are the search conditions for identifying the records required?
Which fields will be displayed? E.g. Search for Test 3 = 10, “Test 3 = 10” is called the search condition.
Wildcard Searches – Search for Surname = “*son”
Complex Searches
A complex search involves more than one search condition (and usually more than one field) example:
- Search for Test 3 = 10 AND Average > 6
- Search for Test 3 = 10 OR Average > 6
- Search for Test 3 > 5 AND Test 3 < 8
- Search for Test 3 < 2 OR Test 3 >9
Sorting
Which field will be used to decide the order of records? This is called the sort key.
For the sort key, will the order of sorting be ascending or descending?
For a list of people with the tallest first:
- Sort in descending order of height
- For a list of people with youngest first
- Sort in ascending order of age
- For alphabetical order
- Sort in ascending order of surname
- “Ascending order of surname” is called the sort condition
Complex Sorting
A complex sort involves more than one sort condition involving two or more fields. The main sort key is called the primary sort key, and the second one is called the secondary sort key. Example, “Telephone book” order: Ascending order of Surname, then ascending order of Forename.
Calculating
Use formulas or expressions to calculate a value for a record based on other values in the record.
Presenting
- Use Layouts (Filmmaker Pro)
- Use forms and reports (Microsoft Access)
- Which fields are required?
- Perform a search and/or sorting operation and present the results