Skip to main content
Engineering LibreTexts

7.5: Fundamental Database Concepts

  • Page ID
    59066
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    What Is a Database?

    A database is a shared collection of related data used to support the activities of a particular organization. A database can be viewed as a repository of data that is defined once and then accessed by various users as shown in Figure 2.1.

    A diagram showing a yellow cylinder with coloured rectangles within it, presumably representing data. Four rectangels are listed to the left of this each with the label "Application Program".

    Figure 2.1. A database is a repository of data.

    Database Properties

    A database has the following properties:

    • It is a representation of some aspect of the real world or a collection of data elements (facts) representing real-world information.
    • A database is logical, coherent and internally consistent.
    • A database is designed, built and populated with data for a specific purpose.
    • Each data item is stored in a field.
    • A combination of fields makes up a table. For example, each field in an employee table contains data about an individual employee.

    A database can contain many tables. For example, a membership system may contain an address table and an individual member table as shown in Figure 2.2. Members of Science World are individuals, group homes, businesses and corporations who have an active membership to Science World. Memberships can be purchased for a one- or two-year period, and then renewed for another one- or two-year period.

    Snapshot of an online membership form. Underneath it is a spreadsheet with names, barcodes, date, and teim.

    Figure 2.2. Membership system at Science World by N. Eng.

    In Figure 2.2, Minnie Mouse renewed the family membership with Science World. Everyone with membership ID#100755 lives at 8932 Rodent Lane. The individual members are Mickey Mouse, Minnie Mouse, Mighty Mouse, Door Mouse, Tom Mouse, King Rat, Man Mouse and Moose Mouse.

    Database Management System

    A database management system (DBMS) is a collection of programs that enables users to create and maintain databases and control all access to them. The primary goal of a DBMS is to provide an environment that is both convenient and efficient for users to retrieve and store information.

    With the database approach, we can have the traditional banking system as shown in Figure 2.3. In this bank example, a DBMS is used by the Personnel Department, the Account Department and the Loan Department to access the shared corporate database.

    There are three drawings on the left. The top one shows three people sitting and talking at a table; it is labelled as Personnel Dept. The drawing underneath this shows a man and woman in business attire and carrying briefcases shaking hands; it is labelled as Account Dept. The last drawing is of a woman; it is labelled Loan Dept. To the right of this is a rectangle with DBMS. to the right of this is a green cylinder that says Shared Database with employees, checking accounts, saving accounts, installment loans, mortgage loans, data files.

    Figure 2.3. A bank database management system (DBMS).

    Characteristics and Benefits of a Database

    Managing information means taking care of it so that it works for us and is useful for the tasks we perform. By using a DBMS, the information we collect and add to its database is no longer subject to accidental disorganization. It becomes more accessible and integrated with the rest of our work. Managing information using a database allows us to become strategic users of the data we have.

    We often need to access and re-sort data for various uses. These may include:

    • Creating mailing lists
    • Writing management reports
    • Generating lists of selected news stories
    • Identifying various client needs

    The processing power of a database allows it to manipulate the data it houses, so it can:

    • Sort
    • Match
    • Link
    • Aggregate
    • Skip fields
    • Calculate
    • Arrange

    Because of the versatility of databases, we find them powering all sorts of projects. A database can be linked to:

    • A website that is capturing registered users
    • A client-tracking application for social service organizations
    • A medical record system for a health care facility
    • Your personal address book in your email client
    • A collection of word-processed documents
    • A system that issues airline reservations

    Characteristics and Benefits of a Database

    There are a number of characteristics that distinguish the database approach from the file-based system or approach. This chapter describes the benefits (and features) of the database system.

    Self-describing nature of a database system

    A database system is referred to as self-describing because it not only contains the database itself, but also metadata which defines and describes the data and relationships between tables in the database. This information is used by the DBMS software or database users if needed. This separation of data and information about the data makes a database system totally different from the traditional file-based system in which the data definition is part of the application programs.

    Insulation between program and data

    In the file-based system, the structure of the data files is defined in the application programs so if a user wants to change the structure of a file, all the programs that access that file might need to be changed as well.

    On the other hand, in the database approach, the data structure is stored in the system catalogue and not in the programs. Therefore, one change is all that is needed to change the structure of a file. This insulation between the programs and data is also called program-data independence.

    Support for multiple views of data

    A database supports multiple views of data. A view is a subset of the database, which is defined and dedicated for particular users of the system. Multiple users in the system might have different views of the system. Each view might contain only the data of interest to a user or group of users.

    Sharing of data and multi-user system

    Current database systems are designed for multiple users. That is, they allow many users to access the same database at the same time. This access is achieved through features called concurrency control strategies. These strategies ensure that the data accessed are always correct and that data integrity is maintained.

    The design of modern multiuser database systems is a great improvement from those in the past which restricted usage to one person at a time.

    Control of data redundancy

    In the database approach, ideally, each data item is stored in only one place in the database. In some cases, data redundancy still exists to improve system performance, but such redundancy is controlled by application programming and kept to minimum by introducing as little redudancy as possible when designing the database.

    Data sharing

    The integration of all the data, for an organization, within a database system has many advantages. First, it allows for data sharing among employees and others who have access to the system. Second, it gives users the ability to generate more information from a given amount of data than would be possible without the integration.

    Enforcement of integrity constraints

    Database management systems must provide the ability to define and enforce certain constraints to ensure that users enter valid information and maintain data integrity. A database constraint is a restriction or rule that dictates what can be entered or edited in a table such as a postal code using a certain format or adding a valid city in the City field.

    There are many types of database constraints. Data type, for example, determines the sort of data permitted in a field, for example numbers only. Data uniqueness such as the primary key ensures that no duplicates are entered. Constraints can be simple (field based) or complex (programming).

    Restriction of unauthorized access

    Not all users of a database system will have the same accessing privileges. For example, one user might have read-only access (i.e., the ability to read a file but not make changes), while another might have read and write privileges, which is the ability to both read and modify a file. For this reason, a database management system should provide a security subsystem to create and control different types of user accounts and restrict unauthorized access.

    Data independence

    Another advantage of a database management system is how it allows for data independence. In other words, the system data descriptions or data describing data (metadata) are separated from the application programs. This is possible because changes to the data structure are handled by the database management system and are not embedded in the program itself.

    Transaction processing

    A database management system must include concurrency control subsystems. This feature ensures that data remains consistent and valid during transaction processing even if several users update the same information.

    Provision for multiple views of data

    By its very nature, a DBMS permits many users to have access to its database either individually or simultaneously. It is not important for users to be aware of how and where the data they access is stored

    Backup and recovery facilities

    Backup and recovery are methods that allow you to protect your data from loss. The database system provides a separate process, from that of a network backup, for backing up and recovering data. If a hard drive fails and the database stored on the hard drive is not accessible, the only way to recover the database is from a backup.
    If a computer system fails in the middle of a complex update process, the recovery subsystem is responsible for making sure that the database is restored to its original state. These are two more benefits of a database management system.

    Classification of Database Management Systems

    Database management systems can be classified based on several criteria, such as the data model, user numbers and database distribution, all described below.

    Classification Based on Data Model

    The most popular data model in use today is the relational data model. Well-known DBMSs like Oracle, MS SQL Server, DB2 and MySQL support this model. Other traditional models, such as hierarchical data models and network data models, are still used in industry mainly on mainframe platforms. However, they are not commonly used due to their complexity. These are all referred to as traditional models because they preceded the relational model.

    In recent years, the newer object-oriented data models were introduced. This model is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object-oriented databases are different from relational databases, which are table-oriented. Object-oriented database management systems (OODBMS) combine database capabilities with object-oriented programming language capabilities.

    The object-oriented models have not caught on as expected so are not in widespread use. Some examples of object-oriented DBMSs are O2, ObjectStore and Jasmine.

    Classification Based on User Numbers

    A DBMS can be classification based on the number of users it supports. It can be a single-user database system, which supports one user at a time, or a multiuser database system, which supports multiple users concurrently.

    Classification Based on Database Distribution

    There are four main distribution systems for database systems and these, in turn, can be used to classify the DBMS.

    Centralized systems

    With a centralized database system, the DBMS and database are stored at a single site that is used by several other systems too. This is illustrated in Figure 6.1.

    Diagram showing one large computer monitor and four small ones labelled Workstations or Terminals, with arrows between them. There is also a computer tower labelled Central Computer.

    Figure 6.1. Example of a centralized database system.

    In the early 1980s, many Canadian libraries used the GEAC 8000 to convert their manual card catalogues to machine-readable centralized catalogue systems. Each book catalogue had a barcode field similar to those on supermarket products.

    Distributed database system

    In a distributed database system, the actual database and the DBMS software are distributed from various sites that are connected by a computer network, as shown in Figure 6.2.

    Diagram showing three circles, separately labelled Site 1-3, and each containing several computer monitors and a computer tower. A line connects each of these to a central oval marked Computer Network.

    Figure 6.2. Example of a distributed database system.

    Homogeneous distributed database systems

    Homogeneous distributed database systems use the same DBMS software from multiple sites. Data exchange between these various sites can be handled easily. For example, library information systems by the same vendor, such as Geac Computer Corporation, use the same DBMS software which allows easy data exchange between the various Geac library sites.

    Heterogeneous distributed database systems

    In a heterogeneous distributed database system, different sites might use different DBMS software, but there is additional common software to support data exchange between these sites. For example, the various library database systems use the same machine-readable cataloguing (MARC) format to support library record data exchange.

    Database Users

    End Users

    End users are the people whose jobs require access to a database for querying, updating and generating reports.

    Application user

    The application user is someone who accesses an existing application program to perform daily tasks.

    Sophisticated user

    Sophisticated users are those who have their own way of accessing the database. This means they do not use the application program provided in the system. Instead, they might define their own application or describe their need directly by using query languages. These specialized users maintain their personal databases by using ready-made program packages that provide easy-to-use menu driven commands, such as MS Access.

    Application Programmers

    These users implement specific application programs to access the stored data. They must be familiar with the DBMSs to accomplish their task.

    Database Administrators (DBA)

    This may be one person or a group of people in an organization responsible for authorizing access to the database, monitoring its use and managing all of the resources to support the use of the entire database system.

    Key Terms

    application programmer: user who implements specific application programs to access the stored data

    application user: accesses an existing application program to perform daily tasks.

    centralized database system: the DBMS and database are stored at a single site that is used by several other systems too

    data elements: facts that represent real-world information

    database: a shared collection of related data used to support the activities of a particular organization

    database administrator (DBA): responsible for authorizing access to the database, monitoring its use, and managing all the resources to support the use of the entire database system

    database management system (DBMS): a collection of programs that enables users to create and maintain databases and control all access to them

    datatype: determines the sort of data permitted in a field, for example, numbers only

    distributed database system: the actual database and the DBMS software are distributed from various sites that are connected by a computer network

    end user: people whose jobs require access to a database for querying, updating, and generating reports

    heterogeneous distributed database system: different sites might use different DBMS software, but there is additional common software to support data exchange between these sites

    homogeneous distributed database systems: use the same DBMS software at multiple sites

    metadata: defines and describes the data and relationships between tables in the database

    multiuser database system: a database management system that supports multiple users concurrently

    object-oriented data model: a database management system in which information is represented in the form of objects as used in object-oriented programming

    single-user database system: a database management system that supports one user at a time

    sophisticated user: those who use other methods, other than the application program, to access the database

    Exercises

    1. What is a database management system (DBMS)?
    2. How is a DBMS distinguished from a file-based system?
    3. What is metadata?
    4. What are the properties of a DBMS?
    5. Provide three examples of a real-world database (e.g., the library contains a database of books).
    6. Provide three examples of the most popular relational databases used.
    7. What is the difference between centralized and distributed database systems?
    8. What is the difference between homogenous distributed database systems and heterogeneous distributed database systems?

    This page titled 7.5: Fundamental Database Concepts is shared under a CC BY license and was authored, remixed, and/or curated by Adrienne Watt (BCCampus) .

    • Was this article helpful?