1.10: Single-User and Multi-user Database Applications

Database tables are structured to store data, but a database is not complete unless it also shows the relationships among the tables. To see why this is important, examine Figure 1-4 below (Kroenke, Auer, Vandenberg, Yoder, 2018) the database includes all of the basic data shown together with a GRADE table. Unfortunately, the relationships among the data are missing.

In this format, the GRADE data are useless. It would be the equivalent to a sports commentator who simply announced: “Now for tonight’s baseball scores: 2–3, 7–2, 1–0, and 4–5.” The scores are useless without knowing the teams that earned them. Thus, a database contains both data and the relationships among the data.

This demonstrates is imperative characteristic of database processing. Each row in a table is distinctively identified by a primary key, and the values of these keys are used to create the relationships between the tables. For example, in the STUDENT table StudentNumber serves as the primary key. Each value of StudentNumber is unique and identifies a particular student. Thus, StudentNumber 1 identifies Sam Cooke. For example, ClassNumber in the CLASS table identifies each class. If the numbers used in primary key columns such as StudentNumber and ClassNumber are repeatedly created and assigned in the database itself, then the key is also called a surrogate key (Kroenke, Auer, Vandenberg, Yoder, 2018).

Figure \(\PageIndex{1}\): Sample Microsoft Access Student Record. (" Figure 1-1: Sample Microsoft Access Student Record " by Dr. Sarah North, Affordable Learning Georgia, Kennesaw State University is licensed under CC BY 4.0)

Figure 1-2 shows each row in a table in specifically known by a primary key, and value of those keys that are used to create a relationship between the tables, such as student IDNumber (primary key). If the numbers used StudentNumber and ClassNumber column and generate and assigned in the database, then the key is also called a surrogate key.

Figure \(\PageIndex{2}\): The Primary Key and Surrogate Key. (" Figure 1-2: The Primary Key and Surrogate Key" by Dr. Sarah North, Affordable Learning Georgia, Kennesaw State University is licensed under CC BY 4.0)

 In the table below shows when more than one column in a table are merged to form of the primary key, is known as a composite key. In the GRADE column, StudentNumber and ClassNumber each now serve as a foreign key. A foreign key provides a relationship or link between two tables. Figure 1-3 shows a Microsoft Access 2016 point of view of the tables and their relationships.

Figure \(\PageIndex{3}\): The Grade table with foreign keys – link to Student ClassNumber Table. (" Figure 1-3: The Grade table with foreign keys – link to Student ClassNumber Table" by Dr. Sarah North, Affordable Learning Georgia, Kennesaw State University is licensed under CC BY 4.0)

Figure 1-4 shows the greater database application, part of a customer relationship management (CRM) system, which manages customers and their contacts, purchases, support requests, and so forth. The CRM system uses software to support a larger company, which may include anywhere from 500 rows to 10 million or more. An enterprise resources planning (ERP) system is an information system that affects every department in a company, including sales, inventory, planning purchasing and other business purposes. SAP (System, Applications & Products in Data Processing) is the vendor used with ERP applications for large companies.

Figure \(\PageIndex{4}\): A larger database application. (" Figure 1-4: A larger database application" by Dr. Sarah North, Affordable Learning Georgia, Kennesaw State University is licensed under CC BY 4.0)