Internet Programming I
Assignment 5
Access Databases and SQL
Due:
For this assignment, you are
to create an Access database with two tables.
The database might be for a library with tables for books and borrowers
or maybe for a club with tables for members and events. The following example is for a store with
products and customers.
1.
Open Access and
select New from the File menu. Then
select Blank Database, find a folder to store the database in and give it a
name (not db1). The database below is
simply titled Store.
2.
Now create a
table in design view similar to the one below.
3.
Save the table
with some appropriate name, such as Products.
Then open it and add data to it as in the example below.
4.
Now create a
second table. The example here keeps
track of some customers.
5.
Next choose
Queries from the list on the side and go into Design View. Select one of the tables, here the Product
table, and then add all the fields to the design.
6.
Now go to the
View menu and select SQL View. This will
show you the SQL statement that your query generated. The one here is show next.
This statement is used to SELECT all the fields from the table
called Products. It can also be written
as SELECT * FROM Products; where the asterisk indicates all fields of the
table. Save your query giving it a name,
such as ListProducts.
Note that SQL is not case sensitive, but the commands are often written
in upper case. Also listing the name of
the table as in Products.ID is not required when only
one table is queried. But it is
necessary with more than one table.
7.
Next create a
query that selects only products that satisfy a specific condition. The one in the example below lists products
that have a price of $30 or more. Note
the condition in the Criteria field below price.
8.
Next check the
SQL View from the View menu.
This
one can also be simplified to SELECT * FROM Products WHERE Price >= 30.
Open
the query to see the resulting table.
The result of a query is called a dataset or a resultset.
9.
Next add a field
to one of the tables that corresponds to a field in the other. In this example, a product ID field is added
to the Customers table to indicate the product that the customer has on order.
10.
Next choose
Relationships from the Tools menu.
Select both tables and use your mouse to connect the fields that are
duplicated in the two tables. The result
for the store example is shown below.
11.
When this is
done, we can create a query that gets data from both tables. A design view that has the customer’s name
together with all the data for the product ordered is shown below.
12.
The SQL for this
is more complicated. An INNER JOIN means
that the two tables are to be joined together using the common field (defined
in the Relationships table) to determine which fields go together. Note that since both tables have a field
called Name, Access renamed these fields to Customers_Name and Products_Name.
13.
Finally the
dataset returned by this query is shown below.
Note that the quantity field here is not defined properly. The store manager needs to know how much of
the product she has on hand and also how much the customer has ordered. We really should have separate fields for these
and only show the amount ordered in the Orders query.
14.
When you have
finished your database with tables, queries, and relationships, zip it up and
send it to me.