2 Chapter 2 Database Fundamental Concepts

Database Properties

A database has the following properties:

• It is a representation of an aspect of the real world or a collection of data or data elements (facts) representing the real-world.
• 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. One table could contain data about departments in an organization. Another table could contain data about product inventory. And still another table could contain data about employees.

In fact, a database may have a table that provides insights into the nature of a database table. This is known as metadata.

Data mining looks at the actual data in order to find patterns.

For example, a membership system may contain an address table and an individual member table as shown in Figure 2.2. Individuals, group homes, businesses, and corporations are examples of possible members in the Science World subscription service. Memberships can be purchased for a one- or a two-year period. And then renewed for another one- or two-year period.

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

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

Database Models

A database model defines a database’s logical structure. The database model defines how the data will be stored, will be organized, and will be manipulated. In Chapter 1, we mentioned two data models without any explanations. We will address the features of those two data models plus mention other database models.

Hierarchical Database Model

This was one of IBM’s first database model. The data is organized in a tree-like structure. It has nodes for records and branches for the data.

Advantages:

• Easy to add data and to delete of data.
• Data at the top of hierarchical database can be accessed quickly.
• Work nicely with the 1970s mainframe storage systems and other linear data storage media.
• Works nicely with one-to-many relationships.

Disadvantages:

• Data may need to be stored in several locations.
• Linear storage systems are not used today.
• Executing queries requires going from the top down to the lower levels and this takes time.
• Does not support many-to-many relationships.

Figure 2.3. An example of a hierarchical database model. Source of image: https://d2myx53yhj7u4b.cloudfront.net/sites/default/files/ic-database-model-hierarchaical.jpg

Network Database Model

A database task force group created the network database model in the 1960s. The network database model expanded upon the hierarchical database model by adding multiple parent segments, which are grouped into levels. A logical relationship can exist between the segments on a level. This changed supported a many-to-many logical relationship.

In a sense, the network database model was a forerunner to an object-oriented database model, because it represented data as objects and supported more than one parent record. In the 1970s, the Conference on Data systems Languages (CODASYL) formally defined the network databases model.

Advantages:

• Easy to implement.
• The network model can represent data redundancy better than the hierarchical database model.
• Can handle one-to-many and many-to-many relationships.
• The network model is better at isolating the program from the complex physical storage details.

Disadvantages:

• Pointers are used for maintained and this makes the structure extremely complicated.
• Inserting, deleting, or updating actions involve manipulating numerous pointers.
• Changing the database structure is extremely difficult.

Figure 2.4. An example of a network database model. Source of image: https://static.studytonight.com/dbms/images/network-dbms-model.png

Object-Oriented Database Model

Data is represented as objects. The object-oriented database model is based on object-oriented programming languages. This is well-suited for handling spatial data.

Advantages:

• Can handle pictures, voice, video, text, and numbers.
• Code reuse is possible.
• Object-oriented databases have lower maintenance costs.

Disadvantages:

• There is no universally defined object-oriented database model.
• Has not been widely adopted.
• Does not have security “out-of-the-box.”
• Can be more complex than other database management systems.

Figure 2.5. An example of an object-oriented database model. Source of image: https://media.geeksforgeeks.org/wp-content/uploads/20201224205618/oo.png

Database Models that are Variations of Other Database Models

Object-Relational Database Model attempts to combine the features of the object-oriented database model with the best features of the relational database model. (The relational database model will be addressed shortly.)

Entity Relationship Database Model is more of a concept than an actual implementation. (The development of a relational database will draw on the entity relationship diagram approach.)

Distributed Database Model is using two or more servers. The data could be either duplicated on each server or parts of the database could split.

Database Models that are Different from Other Database Models

The Inverted File Structure Database is designed to support fast full-text searches. The data is indexed as a series of keys in a lookup table. This approach is used with Big Data systems.

The Flat Database Model (not the same thing as a flat file system) puts everything into one large table. The computer reads the entire file into memory. This works well for very small datasets.

Relational Database Model Database

Edgar Codd was not happy with the lack of a search engine in the CODASYL approach and in the IMS model. In 1970, he began to write a series of papers about a different way of constructing a database. In June 1970, Codd best paper, “A Relational Model of Data for Large Shared Data Banks,” described a new method for storing data and processing large databases.

IBM had invested heavily in the IMS model and was not interested in Codd’s ideas. However. Michael Stonebraker and Eugene Wong (both then at UC Berkeley) saw Codd’s paper and made the decision in 1973 to research relational database systems. The project came to be known as INGRES (Interactive Graphics and Retrieval System) and it successfully demonstrated that a relational model could be efficient and practical. INGRES worked with a query language known as QUEL. These efforts pressured IBM to develop SQL in 1974. This replaced QUEL. So SQL became the standard way of interacting with a relational database (ANSI and OSI standards in 1986 and 1987).

Advantages:

• Changes in the database structure would have no impact on data access.
• The arrangement of data in rows in a table made it much easier to comprehend.
• This supports both data independence and structure independence. Thus making creating a database design, performing maintenance, performing administration, and using the data much easier.
• Complex queries can be used to access or modify the data.
• It is easier to maintain security.

Disadvantages:

• It does not support an object-oriented database model.
• There are two expenses. One expense is for the database storage. The other cost is for the software that interacts with the data.
• If a database has many tables and if a query needs to work with several tables, then the performance will be slow. A poorly designed database will be slower.
• There is a need for a large amount of physical memory.

Figure 2.6. An example of a relational database model. Source of image: https://i2.wp.com/theintactone.com/wp-content/uploads/2019/03/5.1-relational-dbms-model.png?resize=591%2C391&ssl=1

Database Management System

A database management system (DBMS) is a collection of programs that enables users to create and maintain databases. In a large operation, the data might be on one server, the DBMS might be on a second server, and user-friendly interfacing software could be on a third server. And if the data is made available to the public, then a fourth server would be hosting the web pages. This is known as a multi-tier system. This divides the workload and adds security.

Figure 2.7. An example of a database management system. Source of image: https://www.researchgate.net/profile/Yahya-Al-Samawi/publication/308181535/figure/fig4/AS:407820987060229@1474243508852/Multi-tier-architecture-with-dedicated-Web-server-and-application-server.png

A DBMS can control how individuals are able to access the data and limits on the amount. The primary goal of a DBMS is to provide an environment that is both convenient and efficient for users to retrieve and store data.

Users will store data into a database. They will run queries in order to gain information and insights. Then actions can be taken.

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

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

The rest of this book will focus on the relational database management systems (RDBMS).

Key Terms

data or data elements: Facts that represent something in the real-world.

data mining: Looking for patterns.

database: A shared collection of related data that is used to support the activities of a particular organization.

database management system (DBMS): A collection of programs that enables users to create and to maintain databases plus provide the ability to control who and how the access is done.

information: Insights gained from pieces of data.

metadata: Data about the database.

multi-tier system: An arrangement that uses several servers. It divides the workload.

table: A combination of fields

Exercises

1. What is a database?
2. What are the properties of a database?
3. Provide three examples of a real-world database (e.g., the library contains a database of books). (Note: You will need to do some thinking here.)
4. Explain the goals and functions of a database. (CS2013 IM/DS 3.)
5. Explain the following database models and where each one might be used (CS2013 IM/DS 3, NSA DMS 1, and NSA DAT 2.):
   1. Hierarchical database Model
   2. Network Database Model
   3. Object-oriented database model
   4. Relational database model
   5. Distributed database model
6. Describe the role of data, information, and databases in an organization. (IT2017 ITE-IMA-02 a)
7. Define or explain the following terms (IT2017 ITE-IMA-02 b)
   1. Information
   2. Data
   3. Database
   4. Database management system
   5. Metadata
   6. Data mining
8. What is a database management system (DBMS)?
9. Describe the role of a database, a database management system (DBMS), and a database server within a complex system supporting one or more applications. (NSA DMS 2 and NSA DAT 1)

A Running Project

In order to fully understand databases and database management systems, you will need to work on a project. This will be an iterative effort. You will be coming back to pieces again and again. You will be adding additional details each time.

This will be an academic exercise, but it could be turned into a service project or into an internship.

For the first effort, work on the following questions:

1. Come up with a company or an organization.
2. What kind of data do you wish to track?
3. What are the relationships between the data?
4. What are the constraints on the data?

Imagine that you are helping out a French cultural museum. Here are some possible things to be explored:

• Books: Title, author, date, edition, language, location, condition, number of copies.
• Hard goods: English label, French label, location, ownership, on-loan.
• Property details
• Funds, donations
• Calendar
• Volunteer information
• Media: Facebook, normal website.
• Locations:
  • The museum could have more than one location.
  • The museum may wish to track items held by other museums.

Attribution

This chapter of Database Design (including its images, unless otherwise noted) is a derivative copy of Database System Concepts by Nguyen Kim Anh licensed under Creative Commons Attribution License 3.0 license

The following material was written by Nelson Eng for the second edition:

• Example under Database Properties
• Key Terms

The following material was written by Adrienne Watt for the second edition:

• Exercises

The whole chapter was completely revised by Fred Strickland for the third edition.

References

Harshitha Balasankula. “Types of databases Models: A Comprehensive Guide 101,” Hevo, May 9, 2022. https://hevodata.com/learn/types-of-database-models/

E. F. Codd. “A Relational Model of Data for Large shared data Banks,” Communications of the ACM, Volume 13, Number 6, June 1970, 377. https://dl.acm.org/doi/pdf/10.1145/362384.362685

Keith D. Foote. “A Brief History of database Management,” Dataversity.net, October 25, 2021. https://www.dataversity.net/brief-history-database-management/

“Purpose of Database Systems in DBMS,” Geeks for Geeks, July 14, 2023. https://www.geeksforgeeks.org/purpose-of-database-system-in-dbms/

Mishal Roor. “6 Advantages and Disadvantages of Relational database | Limitations & Benefits of Relational database,” Hitechwhizz.com, April 14, 2021. https://www.hitechwhizz.com/2021/04/6-advantages-and-disadvantages-limitations-benefits-of-relational-database.html