Data Storage Devices
Required Resources:
Outline:
Database Basics
Definition (from Wikipedia): A database is a collection of data elements (facts) stored in a computer in a systematic way, such that a computer program can consult it to answer questions. The answers to those questions become information that can be used to make decisions that may not be made with the data elements alone. The computer program used to manage and query a database is known as a database management system (DBMS). The properties of database systems are studied in information science.
Two popular database programs are:
|
|
In this course we will be using MSAccess.
Databases allow users to search and navigate through vast quantities of information with relative ease. They allow users to view this data in different ways (e.g. to browse a list of books by title, author or date), and to search by these same criteria. Complex combinations of conditions and criteria can be used to uncover important patterns in the data. Also, databases often allow users to add, delete and modify information. When connected with software that controls the content and appearance of Web pages, databases can power dynamic Websites (amazon.com, www.cbc.ca, etc.)
(For related, optional reading, see: Database Journal; Introduction to Databases for the Web [pages 1-5, inclusive] - http://www.databasejournal.com/sqletc/print.php/1428721)
Database Components & Terms
Table: collections of related information divided into records. Typically, a table will be used to organize and store data related to a particular entity or event, such as "users," "purchases," or "products." Databases that are used to support complex activities (e.g. library circulation, book orders, purchases, blogs and their users) will have more than one table.
Record: tables are divided into separate records. In a catalogue describing a library collection, merchandise or course listings, each book, product or course is represented by a separate record. By convention, in a spreadsheet or database table, each (horizontal) row represents a separate record.
Field: The contents of each record, in turn, is divided into separate fields. A book or course title, or a product name will form one field; a call number, course number or price can form a second field, and so on. These fields are represented by the columns in a database table.
Value: The particular contents assigned to any one field is known as its value. In some cases, the values of a field can vary widely and unsystematically (e.g. when the field contains textual descriptions). In other cases, a field will have a fixed range or type of values, as determined by the associated "datatype."
Click on image for larger version.
Datatypes: the kinds of data that a field may contain, including values such as integer (number), date/time, automatic numbering (e.g. 1,2,3...), binary (yes/no).
Primary Key: a field that uniquely identifies each record stored in a table. This field is often automatically numbered; it cannot contain any empty, blank or null values.
Click on image to enlargeForeign Key: a field that refers to the primary key field in another table; it indicates how corresponding records in the two tables are related.
Relation: A connection between two tables, each describing an entity that interacts with the other. In the example above, users (described in the first table) compose and send messages (described in the second table). The values for the primary key for one of these entities is stored in two places: in its own table, and as a foreign key in the related table.
Database normalization: relates to the level of redundancy in a relational database's structure. The key idea is to reduce the chance of having multiple different versions of the same data, like an address, by storing all potentially duplicated data in different tables and linking to them instead of using a copy. Then updating the address in one place will instantly change all the places where the address is used. (Wikipedia - Database normalization)
Query: In information retrieval, a query is a statement of information needs, typically keywords combined with boolean operators and other modifiers.
In computing, a database query is a specification for viewing specific data from one or more tables in a database; it is often specified using the structured query language SQL. (adapted from Wikipedia: Query)Report: formatted output of select data stored in a database.
Exploring Tables in an "Enterprise" Database Management System
After reading Geekgirls: Databases from scratch, explore the image provided below of the "Northwind" database. It is a sample database that Microsoft has created for its MSAccess (Clicking on any of the tables allows you to see that table and the data stored in it).
Note that the primary and foreign keys are joined with lines. Note also the symbol at each end of these lines, appearing as either a 1 ("one") or ∞ ("infinity" or "many"). These symbols indicate the type of relationship between the 2 tables and keys. For example, one supplier can have many products that they supply; similarly, many orders will be received for a single product. As a result, the relationship between suppliers and products is one to many, and the relationship between products and orders is also one to many. It is also possible to have one to one, and many to many relationships.
Here is another way of characterizing these relationships:
1------------1
One to One: Each field in table A has exactly one matching field in table B.
1------------ ∞
One to Many: Any one record in table A can have many matching entries in table B.
∞ --- 1 [ ] 1--- ∞
Many to Many: This is generally accomplished through two One to Many relationships going through an intermediate table (called a junction table). This table simply contains the primary key fields and values from the other two tables. The combinations of the two keys together are used as the single primary key value for the junction table's primary key. An example of a junction table is provided, below.
Exploring a Bibliographic Database
Register to try out this commercial database . It was constructed by yours truly (Norm Friesen): http://www.victoriandatabase.com. (To search the database itself, you must first register; you can then log on anytime during a free, one-month trial.) Try searching for some of the authors and titles listed in the images of the tables provided, above. Think of the information you provided in registering to use the database, and note how it is stored in the users table.
NOTE: The information in this table is not entirely optimized; not nearly all of the redundant information has been removed. You may want to consider ways in which this redundancy could be reduced or eliminated.
Below is the database design.
(select to view larger image)Below are the individual tables (with sample content)
(select to view larger image)
(main)
(mainold)
(users)
Activity: Create a two-table database. Create your own table with one-to-one or one-to- many relationships. You can use whatever examples you like. Anything you can describe systematically that relates to something else that can be similarly described can form the basis for this example. Be sure to choose something that is related one-to-one or one-to-many, not many-to-many! (Click on the image to the right to download a MSWord example for this activity.)
Submit your MSWord document to: scott.paquette@utoronto.ca
