The Arlington Gallery (TAG) sells quality antique furniture for home and office use. Its sales representatives (SalesReps) use the attached form to take orders. Customer Account Numbers on the form are unique. Customers have only one Billing Address. The Billing Address is filled out when a new customer account is created or when there is a change of address for an existing customer. The Delivery Address is the place where the furniture is to be delivered. The Delivery Address is filled in the Order Form when the customer decides to use TAG’s delivery service. All furniture in one order is delivered to the same Delivery Address. TAG offers free delivery service for orders with a total value exceeding $1,000. For smaller orders, TAG charges a nominal fee of $50. The customer is not charged the nominal delivery fee if s/he declines use of TAG’s delivery service for orders less than $1,000.

TAG owns several Trucks for delivering Customer Orders. Trucks have a Vehicle Number, License Plate Number, License Expiration Date, and Inspection Expiration Date. Each Truck is assigned to a Driver, who is an Employee of TAG. Every evening the TAG Operations Manager plans the next day’s Delivery. Orders are broken down into delivery units called Shipments so that each Shipment may fit into one Truck. A large Order that cannot fit into a single Truck may be broken into multiple Shipments, whereas a small order may be delivered as a single Shipment. Shipments are assigned to Trucks that are available for the next day’s delivery service. A Truck may carry more than one Shipment. All items included in a Shipment are delivered by one Truck. Each type of Furniture, such as computer desk, executive chair, etc., is identified by an Item Code. The Price of each Furniture Type and the quantity in stock are also tracked.

TAG employs several Employees. Each Employee has a Social Security Number, Name, Address, and Phone. A Driver, a type of Employee, has a Driver’s License Number and License Expiration Date, in addition to other Employee information. SalesReps are also a type of Employee. All Employees are Salaried. SalesReps earn Commission in addition to Salaries.

a.) Create tables in Oracle to implement the TAG database. Be sure to enter at least five (5) rows in each
table. To document this part in your report, use the DESCRIBE command to list the schema for each
table, followed by the SELECT command to list its content.

b.) Execute the following queries using SQL:
1) List all customer names, addresses, and phone numbers.
2) Pick an order and get all information about that order included in the order form. You do not need to compute the totals and the delivery fee. This may be split into two queries: one for the header and the other for the order lines.
3) What is the phone number of the SalesRep who took the order in the query (#2) above?
4) Pick a driver and list all customers whose orders have been delivered by her/him.
5) What is the total value (quantity times unit price) of all items in stock that have unit prices exceeding $45?

Answer rating: 100% (QA)

aCreate tables in Oracle to implement the TAG database Table 1 Customer DESCRIBE Customer NameNullType ACCOUNTNUMBERNOT NULLVARCHAR220 CUSTOMERNAMENOT NULLVARCHAR250 BILLINGADDRESSNOT NULLVARCHAR250 D... View the full answer