You are here

Logical vs. Physical Database Models

19 January, 2016 - 12:35

The concept underlying the database approach to data management is to decouple the data from the system applications (i.e., to make the data independent of the application or other users). Therefore, as reflected in part (b) of Figure 3.5, the data become the focus of attention. Several other aspects of part (b) are noteworthy:

 
  • The database is now shared by multiple system applications that support related business processes, as shown at the left of Figure 3.5, part (b).
 
  • In addition to being used by application programs, the data can also be accessed through two other user interfaces: (1) report generation, as shown in the upper-right portion of part (b), and (2) ad hoc user inquiries, i.e., queries handled through query language software, depicted in the lower-right portion of part (b). 1
media/image3.png
  • A “layer” of software called the database management system (DBMS) is needed to translate a user’s logical view of the data into instructions for retrieving the data from physical storage. Some of the more technical design issues of database management systems are described in Technology Insight 3.1.
 

Figure 3.7 depicts how a database might look to us if the data were stored using a relational data structure. The data from our three files are now stored in four relational tables: CUSTOMERS (instead of customer master data), INVENTORY_ITEMS (inventory master data), SALES_ORDERS, and SALES_LINES (i.e., the last two tables store the data from the sales order master data). These tables are logicalviews of data that are physically stored in a database. The logical database view is how the data appear to the user to be stored. This view represents the structure that the user must interface with in order to extract data from the database. The physical database storage is how the data are actually physically stored on the storage medium used in the database management system. It has little relationship to how the data appear to be stored (e.g., the logical view). The user can access the data in the tables (e.g., the logical view in a relational database) by:

  1. Formulating a query, or
  2. Preparing a report using a report writer, or
  3. Including a request for data within an application program.

These three methods are depicted in the flowchart in Figure 3.5, part (b).

Technology Insight 3.1

Database Management Systems (DBMS)

A database management system (DBMS) is a set of integrated programs designed to simplify the tasks of creating, accessing, and managing a database. The DBMS performs several functions, such as:

  • defining the data.
  • defining the relationships among data (e.g., whether the data structure is relational or object-oriented).
  • mapping each user’s view of the data (through subschemaschema).

In the language of DBMS, a schema is a complete description of the configuration of record types and data items and the relationships among them. The schema defines the logical structure of the database. The schema, therefore, defines the organizational view of the data.

A subschema is a description of a portion of a schema. The DBMS maps each user’s view of the data from subschemas to the schema. In this way the DBMS provides flexibility in identifying and selecting records. Each of the many database users may want to access records in his or her own way. For example, the accounts receivable manager may want to access customer records by invoice number, whereas a marketing manager may want to access the customer records by geographic location. The figure below portrays the schema-subschema relationship.

media/image39.JPG

A chief advantage of a DBMS is that it contains a query language, which is a language much like ordinary language. A query language is used to access a database and to produce inquiry reports. These languages allow a nontechnical user to bypass the programmer and to access the database directly. Deriving data from the database using a query does not replace applications programs, which are still required to perform routine data processing tasks. However, when information is needed quickly, or when a manager wishes to “browse” through the database, combining data in unique ways, the query facility of a DBMS is a vast improvement over the traditional method of requesting that a program be written to generate a report.

A DBMS normally contains a number of security controls to protect the data from access by unauthorized users as well as from accidental or deliberate alteration or destruction. A DBMS also contains routines for ensuring that the data can be simultaneously shared by multiple users.

media/image40.JPG
Figure 3.7 Record Layouts as Tables 
 

Now let’s see how easily data can be obtained from the relational tables in Figure 3.7 using the database query language SQL (structured query language).

media/image41.JPG

The SELECT command combines the SALES_LINES and INVENTORY_ITEMS tables over the list of items (Item_Number) and finds those items in the combined table that were sold at a price (Sales_Price) other than the price contained on the INVENTORY_ITEMS table (Unit_Price). We see that there are six instances.

Review Question

How are the applications-based file and database approaches to data management the same? How are they different?

 

Neither the user nor the application programs have any idea that these mappings are taking place. In preparing the query, a user might formulate the SELECT command (i.e., choose the selection criteria). Alternatively, rather than being visible to the users, the SQL commands might be part of an underlying application or database structure. Finally, the user might execute these commands using drop-down menus and a mouse. In these latter two alternatives, the commands would be embedded within the application programs. Relational data structures are discussed in greater detail later in this chapter.