Introduction to Database Systems and the Relational Model

Hierarchical Structure of Data Organization

Data within a system is organized through a specific hierarchy of complexity and size. At the most fundamental level is the bit, which stands for binary digit and represents the smallest unit of data, existing as either a "$1$" or a "$0$". Moving up the hierarchy, a byte consists of storage for a single character and is usually comprised of $8$ bits. A field is a collection of characters that represents one single data item. A set of related fields is grouped together to form a record. A collection of related records is then organized into a table. Finally, a database is the software designed to store and manage a related set of these tables.

Definition and Purpose of a Database Management System

A database, or Database Management System (DBMS), is defined as software specifically designed to store and manage large amounts of data. The primary purpose of utilizing a database is to keep track of these vast quantities of information through several key functions: organization, storage, retrieval, searching, and the enforcement of security. By centralizing these tasks, the system ensures that data remains accessible and protected.

Core Components of Data Representation

A database stores information regarding real-world entities, their attributes, and the relationships that exist between those entities. These three concepts form the foundation of database design and management.

An entity is defined as a real or abstract object in the real world. Much like a noun, it can be thought of as a "person, place, or thing." Examples of entities include a person, an automobile, a book, or a student enrollment.

A relationship describes an association or connection between entities. These are often represented by verbs. Examples include scenarios where a professor "teaches" students, a customer "places" orders, or one or more authors "write" books.

An attribute is a property or characteristic of an entity. These are properties that are deemed "of interest" to a particular application. For instance, attributes of a person might include their height, weight, name, address, salary, and age.

Case Study: Henry’s Bookstores

To illustrate these concepts, consider the business operations of Henry, who owns several branches of a bookstore. To manage his business, Henry needs to organize data regarding books, authors, and publishers. In this scenario, the primary entities are Author, Branch, Book, and Publisher.

The relationships between these entities are defined as follows: A branch of the bookstore sells many books. Books are written by authors. Books are published by publishers. In an Entity-Relationship Diagram (ERD), these connections are visualized to show how data points intersect. For Henry's bookstore, the ERD captures specific attributes for each entity:

Branch: includes attributes such as BranchName, Street, City, State, and Zip. Author: includes attributes such as FirstName and LastName. Book: includes attributes such as ISBN and Title. Publisher: includes attributes such as Pname, City, and Country.

Comparison of Data Processing Approaches

The traditional flat-file approach to data processing involves using separate file systems for different departments or programs. For example, a business might maintain $3$ separate file systems for Personnel Programs, Reading Circles, and Payroll. This approach leads to significant problems, most notably data redundancy. If a person’s name and address are stored in $3$ different files, it results in wasted storage space and the high possibility of inconsistent data across the different files.

The DBMS approach provides a solution through a centralized storage system where each data item is stored just once. In this model, relationships are accurately represented, and programs for personnel, payroll, and reading circles all access the same centralized database through the DBMS, ensuring that data is synchronized and efficient.

Advantages and Disadvantages of a DBMS

The DBMS approach offers several advantages. It allows organizations to get more information from the same amount of data, increasing productivity by making more knowledge available quickly and easily. Data can be shared among different users within the same organization. Management is improved through a Database Administrator (DBA) who makes key decisions about the database. Redundancy is minimized because there is only one copy of everything. Data integrity, which refers to the correctness of the data, is enhanced; for example, the system can enforce a rule that a social security number must be exactly $9$ digits. Furthermore, security is increased, as only authorized users can view specific data, such as restricting payroll data to only certain personnel.

However, there are also disadvantages to consider. The size of a DBMS is significant; it is a large program that requires substantial storage space and main memory ($RAM$), which may necessitate more powerful hardware. The complexity of a DBMS requires that the DBA and other database personnel undergo special training. Cost is also a factor, as large-scale applications can be expensive and involve many recurring costs. Finally, there is a higher impact of a failure in a centralized system, and recovery from such a failure is more difficult due to the inherent complexity of the DBMS.

Key Facts about the Relational Model

The relational model is the most prevalent type of DBMS in use today. It was originally developed in $1970$ by E. F. Codd. Popular examples of relational database systems include Oracle, SQL Server, MySQL, DB2, and Postgres. Microsoft Access is another example, though it is categorized as a "personal database" designed for use by only one person at a time.

Questions & Discussion

There are five essential takeaways for understanding the introduction to database systems:

1. Understanding how data is organized from bits to databases.

2. Defining what a database and a DBMS are.

3. Identifying and differentiating between Entities, Attributes, and Relationships.

4. Evaluating the various Advantages and Disadvantages of the DBMS approach.

5. Recognizing historical and practical facts regarding the Relational Model.