The chapter begins with a description of a database as a collection of data that can be accessed by users to create meaningful information. The simplest kind of a database is just a list, but a static list has obvious limitations. For many databases, a spreadsheet program like Excel can provide all the storage and organization that is needed. Such databases are called flat files, because they are only two dimensional.
A simple flat file database has limitations as well. For example, it can store many information items redundantly. Each order placed by a given customer might feature that customer's name and ID number. This problem is multiplied by the number of customers in the database and the number of orders each makes. This is necessary information, of course, but it would take much less storage space to use a database that referenced a single copy of the customer's data instead. Each order in a flat file presents the need to enter this same information, presenting the opportunity for error, which would cause data to be left out of reports that might be generated based on matching any one variation of the customer's name. A database allows us to store most of the customer's information in one place. This information is then accessed each time it is needed, providing consistency.
Shortcomings of spreadsheets:
Advantages and Disadvantages of Database Processing
'The text presents a list of virtues of valuable information on page 467. Some of the important items:
Basic Database Terms
The text pauses here to introduce some common terms used in
describing databases. It leaves out some basic terms as well. This list
is more complete.
On page 473, there is a bulleted list of validation methods that might be used on different kinds of data. It is not possible to rely on a database to check all the data, but it is possible to program checks of these types to improve our data's accuracy and reliability. Consider the book's discussion of these types of validation:
So, now we are ready for another definition: "a database is a structure that can store information about multiple types of entities, the attributes of those entities, and the relationships among the entities".
We must consider what entities our company is tracking in order to create tables about those entities. We will create a table for each one. Each table will describe one kind of entity (e.g. Students). Rows in the table are instances of that entity (individual Students) and columns hold the attributes (e.g. LastName, FirstName, Major). If we construct tables this way, we might eventually find ourselves wanting to enter more than one item in a given cell. This would cause problems later, so this is not allowed in standard databases. So, a rule is imposed:
Database Maintenance FunctionsThere are several standard functions any database must support:
Description of Database Management Systems
Next, the text introduces the idea of a database management system (DBMS). This is any software solution that allows users to store and retrieve data, and produces reports needed by the users. Users interact with the DBMS directly or through an interface of some sort. Users typically can add, delete, and modify records through a DBMS. They should also be able to ask the DBMS to retrieve all orders for a specific part, or all commission payments to specific sales people. Making such a request is called running a query. (Query is another word for a question.)
Some examples of DBMSs are: Access, Oracle, DB2, and SQL Server. In most cases, the DBA, or Database Administrator (person who manages the database) designs the tables to be used and creates several forms that will be used in the user interface to the DBMS. Using a form, the users can add a new macguffin record to the Macguffin table without knowing anything about database design themselves (and precious little about Macguffins).
The chapter begins with a description of relational databases. A relational database can be considered as a collection of tables. For some reason, the tables are called relations, hence the name relational database. The information about each entity in this kind of database is stored in a table dedicated to that entity. The attributes of that entity are represented as columns in the table, and the rows represent unique instances (cases) of the entity. In other words, in a table about students, each column represents a characteristic common to students, and each row represents an individual student.
Rows can also be called records or tuples. (No one likes that last word.) Columns represent characteristics, also called attributes or fields.
Relationships between entities are shown by common columns in two or more tables. Our student table could be linked to a table of majors by having a column from the majors table in each table. In the majors table, each major would appear once. In the student table, each major might appear any number of times. This puts majors in a one-to-many relationship to students.
The text presents a formal definition of a relation, which has several parts. A relation is a two dimensional table that meets these rules:
This set of rules is very important. Without them, we cannot create tables that will be useful in databases.
A formal notation for tables is presented. Start with the name of the table, follow it with a list of the columns in parentheses, separated by commas. This notation helps analyze the structure of the table without being distracted by the data elements.
Student (StudentNum, LastName, FirstName, Major, Address, City, State, Zip)
Tables may have any number of columns. In most cases, the entries in one of the columns will uniquely identify each row. That column is the primary key of that table. Sometimes the primary key is not just one column, but a combination of two or more. In the notation above, primary keys would be shown by underlining them.
Student (StudentNum, LastName, FirstName, Major, Address, City, State, Zip)
In the example tables above, StudentNum is the primary key for the Student table. The Major column is the primary key for the Major table.
The Major column appears in both tables, providing the relationship between the two tables. In the Major table, the Major column is the primary key. In the Students table, the Major column is a foreign key, the primary key of another table.
The chapter presents a short discussion of SQL. SQL commands can be used to CREATE tables, DROP tables, INSERT data into tables, etc. (You can practice doing this on the SQLCourse web site. There is a link on the menu for this course.)
For example, this command will create a table called Customer:
CREATE TABLE Customer
The words shown above in capital letters need not be entered capitalized. I have capitalized them to show that they are actual SQL command words.
This example would insert a record into the Customer table:
INSERT INTO Customer
To erase a table, use the drop command:
DROP TABLE Customer;
This example would remove this table from the database.
You can use the select command to see the data in a table. To see the whole table use a command like this:
SELECT * FROM Customer;
The asterisk is a wildcard character. It appears in the
command above where I might have inserted a list of columns. Since the
asterisk is there, the command means "show me all the columns of data
in this table". To see selected fields, specify them in a comma
delimited list, instead of using an asterisk.
SQL commands can be simple or complex. Let's consider some of the basics and do a few exercises with them.
To pull specific data from a table, you have to know the table's name
and the names of the columns that hold the data. The general form of
the command is like this:
Assuming we have a table called Students, similar to the examples above, what would be a valid SQL command to report the names of all students in the table?
This is a data retrieval command, so it begins with the word SELECT. We only want data from two columns so we need their names: LastName and FirstName. This is the first phrase of the command.
SELECT LastName, FirstName
This leads to two observations about selecting all of something.