The following data elements comprise the conceptual-level schema for a database:
billing address
cost
credit limit
customer name
customer number
description
invoice number
item number
price
quantity on hand
quantity sold
shipping address
terms
a. Identify 3 users of the database in the given scenario and create a subschema for each.
The Accounts Receivable Clerk will need to create invoices. She will need access to the following data elements:
1. Customer name
2. Customer number
3. Billing address
4. Item number
5. Description
6. Quantity sold
7. Price
8. Terms
The Purchasing Clerk will need to replenish the inventory sold. He will need access to the following data elements:
1. Item number
2. Description
3. Quantity on hand
4. Cost
The Sales Clerk will need to receive the customer order and create a sales order. She will need access to the following data elements:
1. Customer name
2. Shipping address
3. Item number
4. Description
5. Quantity on hand
6. Price
7. Credit limit
8. Account balance
b. Create the schema tables using the data elements in the given scenario.
Table Name | Primary Key | Foreign Key | Other Attributes |
Customer | Customer number | Customer name Billing address Shipping address Account balance Credit Limit | |
Sales | Invoice number | Customer number | Terms Date |
Inventory | Item number | Description Cost Quantity on hand Price | |
Sales-Inventory | Item number Invoice number | Quantity sold Price |