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 1-1: Sample Microsoft Access Student Record

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 1-2 The Primary key and Surrogate key

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.

StudentNumber Table

ClassNumber Table

Figure 1-3: The Grade table with foreign keys – link to Student ClassNumber Table

Single-User and Multi-user Database Applications

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 1-4 shows a larger database application

What is Microsoft Access?

Microsoft Access is not just a database management system (DBMS) but is also a personal database system. Microsoft Access is a combination of the relational Microsoft Jet Database engine with a graphical user interface (GUI) and software-development tools.

Microsoft Access is one of the office suites that is intended for individuals and small works groups such as interact with application through data entry process forms, generate reports, run the queries.

Attribution

By Sarah North and Xiaohua Xu, Introduction to Database Systems, textbook was developed as part of a Round 16 Textbook Transformation Grant, and licensed under  CC BY-NC-SA 4.0.