 |
CSCI A114 / INFO I111
|
The following is based on Chapter 3 in Sengupta & Groth.
1. Introduction
The basic idea behind a table is simple. Conceptually, a database table is comprised of rows and columns. Columns represent a description of the contents of the table. The rows contain the actual data in the in the table.
2. Designing Tables
Database applications are usually comprosed of multiple table. Each table is designed to contain a
specific type of object. For example the Marina table contains instances of marinas, as
abstracted by our application.
2.1 Simple Design
The design of a table comprises:
A key in a database system is used to give you access to a particular record in a database. Given the key the database management system should return a unique record.
In a relational database management system, a key usually consists of a set of attributes (columns) of a table that uniquely determines a record (row) of that table. In other words, no two rows in the table can have exactly the same values for the key columns. A simple example is the social security number in an employee database.
Sometimes a combination of columns is used as a key, otherwise we use a calculated key: most database systems support this capability through some sort of auto-numbering function. In this case, a unique number is assigned to each row of data added to the table. The two tables in our database application have keys which are similar in meaning to a calculated key (although the keys are determined by the user of the database).
3. Simple Queries
Database systems are excellent tools for storing and retrieving information. in a RDBMS data is stored in tables, specifically designed for the application. In addition, database systems provide mechanisms for retrieving information: certain commands, called queries, are used to retrieve information from the database.
There are many different interface for specifying and executing queries. As a generalization the book provides the Query Construction Method (QCM) as a technique for developing database queries.
3.1 The Query Construction Method
Providing the ability to retrieve the information from a database is an essential function of a relational database system. QCM provides a simplified technique for creating database queries.
Learning to use the QCM is as easy as filling in the following table:
Answering the first two questions, DATA and LOCATION is straightforward. You just need to name the appropriate columns and tables. The third section, CONDITION, turns out to be where database queries become really interesting. We can avoid using this section in only the simplest types of queries.
DATA What pieces of information are required? LOCATION Where is the information located? CONDITION Which rows of data should be retrieved? FORMAT How should the results be presented?
The fourth section deals with the FORMAT of the results. This depends, by and large, on the intended recipient of the query results.
3.2 Simple Queries
In this section we will use QCM to create simple queries.
Use the tables below as a workbook. We'll work these queries out completely in lecture and labs.
1. List all marina numbers and marina names, as well as the technician numbers of the technicians that service that specific marina (from the Marina table).
2. List the entire information in the Marina table.
DATA LOCATION CONDITION FORMAT
3. List the marina number, name, warranty and non-warranty amounts for the marina whose number is EL25.
DATA LOCATION CONDITION FORMAT
4. List the marina number, name, warranty and non-warranty amounts for the marinas whose names start with Fe.
DATA LOCATION CONDITION FORMAT
5. List the marina number, name, address, warranty and non-warranty amounts for the marinas located in Burton.
DATA LOCATION CONDITION FORMAT
6. List the marina number, name, warranty and non-warranty amounts for those marinas whose non-warranty amounts are 0 (zero).
DATA LOCATION CONDITION FORMAT
7. List the marina number, name, warranty and non-warranty amounts for those marinas that have a warranty amount greater than 1,000 dollars.
DATA LOCATION CONDITION FORMAT
8. List the marina number, name, warranty and non-warranty amounts, as well as the technician number for those marinas whose warranty amounts are greater than 1,000 and whose technician number is 36.
DATA LOCATION CONDITION FORMAT
9. List the marina number, name, warranty and non-warranty amounts, as well as the technician number for those marinas whose warranty amounts are greater than 1,000 or whose technician number is 36.
DATA LOCATION CONDITION FORMAT
10. List all the cities that have a marina serviced by Bavant Marine Services, sorted in alphabetical (ascending) order.
DATA LOCATION CONDITION FORMAT
11. List the marina number, name, tech number, and warranty amount for all marinas, sorted by technician number (ascending). Within the collection of marinas having the same technician, the marinas should be sorted by descending warranty amount.
DATA LOCATION CONDITION FORMAT
3. Elementary SQL
DATA LOCATION CONDITION FORMAT
The SQL standard defines a formal syntax for queries. The syntax includes the structure of commands and keywords. The basic syntax of an SQL query is as follows:
This syntax is closely matched with the QCM.
SELECT field list FROM table list WHERE condition
The select query window in Access (that you will be using in lab) is offering a QBE (Query By Example) interface. You will see that for each query specified in QBE there is an SQL equivalent that Access can display for you if you want to.
Next lecture and lab will cover:
A114/I111