(Solved): 2.2 FILES, RECORDS, AND DATA ITEMS Data has to be stored in some fashion in a file to be useful. Su ...

2.2 FILES, RECORDS, AND DATA ITEMS Data has to be stored in some fashion in a file to be useful. Suppose there were no computers - think back to a time when files were paper documents. A business kept track of its customers and products. A doctor's office kept track of patients. A sports team kept statistics on its players. In all of these cases, data was recorded on paper. The files with data in them could be referred to as a "database." A database is most simply a repository of data about some specific entity. A customer file might be as plain and minimal as a list of people that did business with a merchant. There are two aspects to filing: storage and retrieval. Some method of storing data that will facilitate retrieval is most desirable. Suppose we have a file of customer records. The whole file might be called the customer file, whereas the individual customer's information 11 12 - Database Design Using Entity-Relationship Diagrams is kept in a customer record. Files consist of records. More than likely, more information than a list of just customer's names would be stored. Most likely, at the very least a customer's name, address, and phone number would constitutha \( { }^{2} \) customer record. Each of these components of the record is called a data item or field. The customer file contains customer records that consist of fields of data. Here is an example of some data (you can imagine each line as a \( 3 \times 5 \) card, with the three cards making up a file): This file contains three records. There is one record for each customer. The records each consist of four fields: record number, name, address, city. As more customers are added, they also are assigned a \( 3 \times 5 \) card, and these records are placed in the file. Several interesting questions and observations arise: 1. The merchant who keeps this file may well want to add information in the future. Suppose that the merchant wanted to add a telephone number. Would the merchant add a number to all \( 3 \times 5 \) cards, or would the adding be done "as necessary"? If it were done as necessary, then some customers would have telephone numbers, and some would not. If a customer had no phone number, then the phone number for that customer would be "null" (unknown). 2. How will the file be organized? Imagine not three customers, but 300 or 3,000 . Would the \( 3 \times 5 \) cards be put in alphabetical order? Perhaps, but what happens if you get another J. Jones or S. Smith? The field on which the file is organized is called a key. Perhaps the file should be organized by telephone number, which might not have a duplicate value? 3. Suppose the file were organized by telephone number. What if the telephone number for some customer was not recorded? If there were no telephone number, the common terminology is that we say the telephone field for that record is null. It would make no sense to have the file organized by telephone number if some values were null. Clearly, the key of a file cannot be null. Also, if telephone number were the key, then the person finding a record in the file would have to know the phone number to find the appropriate record efficiently. 4. The format of the file given is CUSTOMER(record_number, name, address, city) The format of the file dictates the order of the fields in any record. In this record, record_number is first, followed by a name, and so on. The file design could have the fields in some other order, but once defined, the order of the fields stays constant. If a telephone number field were added, then the file format could be CUSTOMER (record_number, name, address, city, telephone) This shorthand format notation is referred to as the file design. If the file were set up to find data by name and name were the key, then the name would be underlined, as follows: CUSTOMER (record_number, name, address, city, telephone) 5. You might ask, "Why not use the record number to organize the file?" On one hand, it is unique (which is desirable for a key), but on the other hand, you would have to know the record number to find a customer. The example is organized by record number; however, imagine 300 or more customers. You want to find Smith's addressyou would have to know the record number. It makes more sense to organize this file of \( 3 \times 5 \) cards by name. Taking some of these points into consideration, here is an enhanced version of the customer file in which each line represents a \( 3 \times 5 \) card: 14 - Database Design Using Entity-Relationship Diagrams CHECKPOINT \( 2.1 \) 1. What does it mean to say a field has unique values? 2. Why is it desirable to have a key be unique? 3. Why does a file have to be organized by a key field? 4. What does null mean? 5. Consider this customer file: \( \begin{array}{lccccc}\text { Record 1: } 77 \text { A St Adams, A } & \text { Pensacola } 555-5847 \\ \text { Record 2: } & \text { Charles, } & 365 \text { Broad } & 555-8214 & \text { Mobile AL } \\ \text { Record 3: } & 555-1234 & \text { Harris, H } & 92 \text { Adams } & \text { Elberta, } \\ \text { Lane } & & \text { AL }\end{array} \) What is wrôg here?