Database Model: KimTay The management of KimTay Pet Supplies (a supplier of pet supplies, food, and accessories located in Cody, Wyoming) has determined that the company’s recent growth no longer makes it feasible to maintain customer, invoice, and inventory data using its manual systems. In addition, KimTay Pet Supplies wants to build an Internet presence. With the data stored in a database, management will be able to ensure that the data is up-to-date and more accurate than in the current manual systems. In addition, managers will be able to obtain answers to their questions concerning the data in the database easily and quickly, with the option of producing a variety of useful reports. Task 1: List the item ID as ITEM_ID and description as ITEM_DESC for all items. The descriptions should appear in uppercase letters. Task 2: List the customer ID and first and last names for all customers located in the city of Cody. Your query should ignore case. For example, a customer with the city Cody should be included, as should customers whose city is CODY, cody, cOdY, and so on. Task 3: List the customer ID, first and last names, and balance for all customers. The balance should be rounded to the nearest dollar.
The management of KimTay Pet Supplies (a supplier of pet supplies, food, and accessories located in Cody, Wyoming) has determined that the company’s recent growth no longer makes it feasible to maintain customer, invoice, and inventory data using its manual systems. In addition, KimTay Pet Supplies wants to build an Internet presence. With the data stored in a database, management will be able to ensure that the data is up-to-date and more accurate than in the current manual systems. In addition, managers will be able to obtain answers to their questions concerning the data in the database easily and quickly, with the option of producing a variety of useful reports.
Task 1: List the item ID as ITEM_ID and description as ITEM_DESC for all items. The descriptions should appear in uppercase letters.
Task 2: List the customer ID and first and last names for all customers located in the city of Cody. Your query should ignore case. For example, a customer with the city Cody should be included, as should customers whose city is CODY, cody, cOdY, and so on.
Task 3: List the customer ID, first and last names, and balance for all customers. The balance should be rounded to the nearest dollar.
Task 4: KimTay Pet Supplies is running a promotion that is valid for up to 20 days after an order is placed. List the INVOICE_NUM, CUST_ID, FIRST_NAME, LAST_NAME, INVOICE_DATE, and the promotion date for each invoice as PROMOTION_DATE. The promotion date is 20 days after the INVOICE_DATE was placed.
Task 5: Create the GET_CREDIT_LIMIT procedure to obtain the full name and credit limit of the customer whose ID currently is stored in I_CUST_ID. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. When the procedure is called it should output the contents of I_CUSTOMER_NAME and I_CREDIT_LIMIT.
Task 6: Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the customer, and the invoice date for the invoice whose number currently is stored in I_INVOICE_NUM. Place these values in the variables I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE respectively. When the procedure is called it should output the contents of I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE.
Task 7: Create the ADD_INVOICE procedure to add rows to the INVOICE table.
Task 8: Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in I_INVOICE_NUM to the date currently found in I_INVOICE_DATE.
Task 9: Create the DELETE_INVOICE procedure to delete the invoice whose number is stored in I_INVOICE_NUM.
Task 10: Create the ADD_COMMISSION_TRIG trigger to add the customer’s balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep when adding a new customer.
Task 11: Create the UPD_COMMISSION_TRIG trigger to add the difference between the new balance and the old balance multiplied by the sales rep’s commission rate to the commission for the corresponding sales rep when updating a customer.
Task 12: Create the DEL_COMMISSION_TRIG trigger to subtract the balance multiplied by the sales rep’s commission rate from the commission for the corresponding sales rep when deleting a customer.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
