• Security, Constraints and Views

Your task:

1. Create a table called CustomerAccounts that has the following columns (data type in the brackets): accnum (6 digit integer), customerName (string up to 30 characters), address (string up to 100 characters), and balance (number with 2 digits after decimal point and up to $1,000,000). The schema of the relation CustomerAccounts should include the following constraints:
   • accnum is the primary key;
   • customer's namd and balance can't be null;
2. Alter the table CustomerAccounts to add another column called email (string up to 80 characters).
3. Create another table called Transactions that includes the following columns (data type in the brackets): transactionID (8 digit integer), unitPrice (number with 2 decimal places), units (integer up to 99999), startTime (date), endTime (date), and the transaction is done by a customer account.
   The schema of the relation Transactions should include the following constraints:
   • transactionID is the primary key;
   • unitPrice and units should both be positive numbers;
   • endTime should be later than startTime;
   • the total amount (unitPrice*units) should be less than $100,000;
   • the customer account must be a valid one;
   • none of the attributes should allow null values.
4. Complete the following tasks using SQL update query:
   • Insert some sample data into the table CustomerAccounts and the table Transactions;
   • Delete a customer account with a given account number (accnum) - (Note that this operation may not be successful if there are already transactions references this account);
   • Delete all the transactions started on the day January 12th, 2026;
   • Add 50 to the balance of each customer account whose balance is below 500.
5. Drop the tables CustomerAcccounts and Transactions you just created.