Here are business rules

• A customer may generate many invoices. Each invoice is generated by one customer
• An invoice contains one or more invoice lines. Each invoice line is associated with one invoice.
• Each invoice line references one product. A product may be found in many invoice lines.
• A vendor may supply many products. Some vendors do not (yet?) supply products (For example, a vendor list may include potential vendors.)
• If a product is vendor-supplied, that product is supplied by only a single vendor
• Some products are not supplied by a vendor.

Here are data dictionary for database

CUSTOMER (CUS_NUM, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE)

INVOICE (INV_NUMBER, INV_DATE)

LINE (INV_NUMBER, LINE_NUMBER, LINE_UNITS, LINE_PRICE)

PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT)

where P_INDATE means stocking data, P_ONHAND means units available, P_MIN means minimum units, P_DISCOUNT means discount rate.

VENDOR (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER)

where V_ORDER means previous order

---

Here’s what you should do

1. Draw Crow’s foot model

2. Create tables and relationships

3. Populate tables

4. Extract useful information

(1) List product description, product stocking date, product price, and vendor code where vendor code is equal to 21344

(2) List description, product stocking date, product minimum unit, and product price where product price is less than or equal to 10

(3) Show the description and the number of units of products which are stocked before 20-Jan-04?

(4) It seems that some products are not supplied by vendor. Are there any products which are not supplied by vendor? What kind of products are they?

(5) I remember that I talked to some vendors whose contact name (V_CONTACT) starts with Smith. What are their phone numbers (V_PHONE)?

(6) Update product description to “Clue hammer” when its description is “Claw hammer”.

(7) Is there any previous order in the vendor that supplies the product Claw hammer?

---

2. How to create tables and relationships

Option1: ACCESS UI

To create tables: In the left, TABLES- NEW

To create relationships: In the menu, TOOLS-RELATIONSHIPS

Option2: SQL

You do both (tables and relationships) at the same time using CREATE TABLE

e.g.

CREATE TABLE VENDOR (

V_CODE INTEGER NOT NULL UNIQUE,

V_NAME VARCHAR(35) NOT NULL,

V_CONTACT VARCHAR(15) NOT NULL,

V_AREACODE CHAR(3) NOT NULL,

V_PHONE CHAR(8) NOT NULL,

V_STATE CHAR(2) NOT NULL,

V_ORDER CHAR(1) NOT NULL,

PRIMARY KEY (V_CODE));

---

3. How to populate tables

Option1: ACCESS UI

Type in tables by opening tables

Option2: SQL

INSERT INTO VENDOR

VALUES(21225,'Bryson, Inc.' ,'Smithson','615','223-3234','TN','Y');

---

4. Extract useful information

Make sure you are familiar with the following operators

Special operators

• BETWEEN: Used to check whether attribute value is within a range

• IS NULL: Used to check whether attribute value is null

• LIKE: Used to check whether attribute value matches a given string pattern

• IN: Used to check whether attribute value matches any value within a value list

• EXISTS: Used to check if a subquery returns any rows

Option1: ACCESS UI

You can do by choosing QUERY-NEW

• Select query
• Update query
• Join query

Option2: SQL

This is the syntax for selection

SELECT columnlist from tablelist where condition

e.g. SELECT query

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE

FROM PRODUCT

WHERE V_CODE = 21344;

e.g. JOIN query

SELECT * FROM PRODUCT, VENDOR

WHERE VENDOR.V_CODE = PRODUCT.V_CODE;

e.g. UPDATE query

UPDATE PRODUCT

SET P_DESCRIPT = ‘Aluminum drill bit’

WHERE P_CODE = ‘BRT-345’;