Question

Create a table Money_xxxx with the following fields and constraints.
* mid: int primary key auto_increment, code varchar(50) unique, cid: int, type: char(1), amount: float, mydatetime: datetime, note: varchar(255)
* All fields cannot have NULL values, except note.
* mid is the primary key, and the value should be auto increment by the DBMS.
*  cid is a foreign key references to the id field in CPS3740.Customers table.
* code should be unique.
* sid is a foreign key references to the id field in CPS3740.Sources table

9. (10 pts) Please insert 4 transactions into your money table with the following information.
a. Use only cid=1 for the homework 1
b. type – ‘D’: deposit, ‘W’: withdraw
c. mydatetime – the date and time when you insert the record.
d. note – “Manually inserted” for homework 1
e. Insert two deposits and two withdraws.
f. Amount - a negative number for the “W” (withdraw), and a positive number for “D” (deposit).
g. The 4 transactions should have different codes.
h. The balance (the sum of the amounts) should not be negative for any customer.

mysql> SELECT * FROM CPS3740.Customers;
+----+--------------+-------+----------+------------+--------+------------------+--------+-------+---------+
| id | name | login | password | DOB | gender | street | city | state | zipcode |
+----+--------------+-------+----------+------------+--------+------------------+--------+-------+---------+
| 1 | Austin Huang | huang | 2019 | 1980-03-13 | M | 1000 Morris Ave. | Union | NJ | 07083 |
| 2 | Mary Lee | mary | 1234 | 1990-08-13 | F | 300 Central St. | Newark | NJ | 07029 |
| 3 | Tim Smith | tim | 2018 | 1970-12-20 | M | 120 Main Ave. | Edison | NJ | 07731 |
| 4 | Sarah Wu | sarah | 3740 | 1965-02-19 | F | 319 King St. | Union | NJ | 07083 |
+----+--------------+-------+----------+------------+--------+------------------+--------+-------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM CPS3740.Sources;
+----+--------+
| id | name |
+----+--------+
| 1 | ATM |
| 2 | Online |
| 3 | Branch |
| 4 | Wired |
+----+--------+

I got a problem. I created a table but it was wrong but I cannot delete it. How do I change it?

Answer 1

Please find the correct CREATE TABLE query below. This query will drop the table if already exists and create new one.

DROP TABLE IF EXISTS Money_xxx;
CREATE TABLE `Money_xxx` (
mid int primary key auto_increment, 
code varchar(50) unique not nulll, 
cid int not null, type char(1) not null, 
amount float not null, 
mydatetime datetime not null, 
note varchar(255),
FOREIGN KEY cid REFERENCES Customers (cid),
FOREIGN KEY sid REFERENCES Sources (sid)
);