5.5. Data Hierarchy
A relational database is composed of multiple tables, and each table holds data. Within a table, each row represents a group of related data values. A row, or record, is also known as a tuple. The columns in a table are referred to as a field or attribute. A record is one instance of a set of fields in a table. You can also think of it this way: an attribute is used to define the record, and a record contains a set of attributes. A data hierarchy is the structure and organization of data in a database, and an example can be seen below.
| Term | Description |
|---|---|
| File | A collection of related records, such as all student information including ID, name, major, and email. |
| Record | A collection of related fields, such as an individual student’s ID, major, and email. |
| Field | Holds a single fact or attribute, such as a student’s name. |
| Byte | A unit of digital information. One byte equals eight bits and typically represents a single character. |
| Bit | A unit of measurement used to quantify computer data, represented by a single value of 0 or 1. |
Data Types
When defining the fields in a database table, we must give each field a data type. For example, the field ‘StudentName’ is a text string, while ‘EnrollmentCapacity’ is a number. Most modern databases allow for several different data types to be stored. Some of the more common data types are listed here:
| Data Type | Description |
|---|---|
| Text | Used for storing non-numeric data that is brief, generally under 256 characters. The database designer can set the maximum length. |
| Number | Used for storing numbers. Different number types may be selected depending on the maximum size of the value. |
| Boolean | A data type with only two possible values, such as 0 or 1, true or false, or yes or no. |
| Date/Time | A special form of the number data type that can be interpreted as a date or a time. |
| Currency | A special form of the number data type that formats values with a currency indicator and two decimal places. |
| Paragraph Text | Used for storing longer text entries that exceed 256 characters. |
| Object | Used for storing data that cannot be entered via keyboard, such as images or audio files. |
There are two important reasons that we must properly define the data type of a field.
- First, a data type tells the database what functions can be performed with the data. For example, if we wish to perform mathematical functions with one of the fields, we must be sure to tell the database that the field is a number data type. For example, we can subtract the course capacity from the classroom capacity to find out the number of extra seats available.
- The second important reason to define a data type is so that the proper amount of storage space is allocated for our data. For example, if the ‘StudentName’ field is defined as a Text (50) data type, this means 50 characters are allocated for each name we want to store. If a student’s name is longer than 50 characters, the database will truncate it.
“Chapter 4: Data and Databases” from Information Systems for Business and Beyond (2019) by David Bourgeois is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License, except where otherwise noted.
“Chapter 7 The Relational Data Model” from Database Design – 2nd Edition by Adrienne Watt is licensed under a Creative Commons Attribution 4.0 International License except where otherwise noted.
