I am having some trouble with this for an assignment in Database. I am trying to create some queries for this homework: Assignment 5: Data Definition language and Transaction in MySQL This is what I am currently working on: On June 3, 2019, customer ‘10010’ makes a payment of $100 in cash. The payment ID is 3428. The query as it is as of this moment: SELECT PAYMENT INSERT INTO PAYMENT (PMT_ID,PMT_DATE,CUS_CODE,PMT_AMT,PMT_TYPE,PMT_DETAILS) VALUES(3428,6-3-2019,
I am having some trouble with this for an assignment in
Assignment 5: Data Definition language and Transaction in MySQL
This is what I am currently working on:
-
On June 3, 2019, customer ‘10010’ makes a payment of $100 in cash. The payment ID is 3428.
The query as it is as of this moment:
SELECT PAYMENT
INSERT INTO PAYMENT (PMT_ID,PMT_DATE,CUS_CODE,PMT_AMT,PMT_TYPE,PMT_DETAILS)
VALUES(3428,6-3-2019,10010,100.00,cash,account),
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE + 100
WHERE CUS_CODE=10010,
COMMIT
2. On May 11, 2019, customer ‘10012’ makes a credit purchase of one unit of product ‘11QER/31’ with a unit price of $110.00; the invoice total is $118.80. The invoice number is 10983, and this invoice has only one product line. [note: no payment has been made here]
The Query as of now:
BEGIN TRANSACTION;
INSERT INTO VOICE VALUES (10983, 10012, ’5-11-2019’),
INSERT INTO LINE VALUES (10983, 1, ‘11QER/31’, 1, 110.00),
UPDATE PRODUCT SET P_QOH=P_QOH-1,
WHERE P_CODE= ’11QER/31’
UPDATE CUSTOMER SET CUS_BALANCE= CUS_BALANCE+110.00,
WHERE CUS_CODE=10012,
SET CUS_DATELSTPUR=’05-11-2019’,
WHERE CUS_CODE=10012,
COMMIT;
3. On September 19, 2019, customer ‘10015’ makes a purchase of 2 units of product ’14- Q1/L3’ with a unit price of $16.00, and 1 unit of ‘23109-HB’ with unit price of $9.00; the invoice total is $44.28. The invoice number is 10988, and this invoice has only two product lines. The customer paid the full price in cash. The payment id is 3520.
The Query as of now:
BEGIN TRANSACTION
INSERT INTO VOICE VALUES (10988, 10015, ’9-19-2019’),
INSERT INTO LINE VALUES (10988, 2, ‘14Q1/L3’ and ‘23109-HB’, 2, 16.00),
UPDATE PRODUCT SET P_QOH=P_QOH-1,
WHERE P_CODE= ‘14-Q1/L31’ and ‘23109-HB’,
UPDATE CUSTOMER SET CUS_BALANCE= CUS_BALANCE-16.00 and 9.00,
WHERE CUS_CODE=10012,
SET CUS_DATELSTPUR=’9-19-2019’,
WHERE CUS_CODE=10012,
INSERT INTO PAYMENT VALUES (3520, ‘9-19-2019’, 10015, 16.00 and 9.00, CASH),
COMMIT;
One last thing from earlier in the assignment was this:
The Payment Table
Image attached to this.
This was the query from earlier and I had a hard time with the validation rule:
CREATE TABLE PAYMENT(
PMT_ID INT PRIMARY KEY,
PMT_DATE DATE NOT NULL,
CUS_CODE INT,
PMT_AMT CHAR(9)
PMT_TYPE TEXT
PMT_DETAILS TEXT,
FOREIGN KEY(CUS_CODE)REFERENCES CUSTOMER(CUS_CODE));
Any help on this would be appreciated because I am not sure what I am doing wrong with these queries.
Also, I hope this is the right subject to be asking a question here.
Thanks.
![Create the PAYMENT table. Remember to save your query for this. See the screenshot
below for detailed info.
Field Name
Data Type
Description
8 PMT_ID
|РMT_DATE
CUS_CODE
PMT AMT
PMT TYPE
AutoNumber
Рayment ID
Date/Time
Payment Date
Number
Customer Code
Number
Payment Amount
Payment Type: Cc,CASH,CHECK
Payment details, bank, credit card type, acount#, etc.
Тext
PMT_DETAILS
Text
Field Properties
General Lookup
Field Size
50
Format
Note:
The PAYMENT table records any time a customer made a payment.
Use PMT_ID INT instead of autonumber for PMT_ID.
Add validation rule to the PMT TYPE so that it only allows three values [check
the script on creating Customer table ®r the CHECK statement].
The Cus code is a foreign key referencing Customer table.](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2Fb0f2aad1-c4d6-4add-ae1f-37d9ec7677ed%2Fb8c375cd-8d39-4165-92d4-da9f927ca684%2Fyrww2ej_processed.png&w=3840&q=75)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 5 images
