The scenario is that you are working for a company and they are creating a website that allows users to download manuals and software updates for products they have purchased. They want to track all of the downloads from the site and you need to create a database for that purpose.

info iconHow to do it$?$

$1.$ Write a script that implements the following design in a database named downloads

Assignment $10$ Database Schema

Include statement$($s$)$ to create the database if it does not exists.

Include statement$($s$)$ to open the database.

Include statement$($s$)$ to create all of the tables and fields in this schema.

In the downloads table, the user$\_$id and product$\_$id columns are the foreign keys.

$2.$ Write a script that adds the following data to the database that you created above.

Users:

Last Name First Name Email Address

Witty Max mwitty$6$@yahoo.com

Sherwin Lauren lsherwinm@stumbleupon.com

Dyers Wendy wdyers@sphinn.com

Sansbury Lois lsansbury$1$n@cafepress.com

Products:

Product Name Model

Virtual Path Computer VPX

AppliedMicro Server XC$-1$ Server

Redragon Keyboard K$585$

Cyborg Gaming Keypad XZ$29$

$3.$ Write an ALTER TABLE statement that adds two new columns to the Products table created above.

Add one column for product price that provides for six digits to the left of the decimal point and two to the right. This column should have a default value of $99\mathrm{.}99.$

Add one column for the date and time that the product was added to the database. This column should have a default value of the current date and time.

$4.$ Write an ALTER TABLE statement that modifies the Users table so the first$\_$name column cannot store NULL values and can store a maximum of $20$ characters.

$5.$ Create SQL statements that tests the the alterations to the table:

Create an UPDATE statement that attempts to insert a NULL value into the first$\_$name column. It should fail due to the NOT NULL constraint. What is the Error Code number?

Create another UPDATE statement that attempts to insert a first name that$$s longer than $20$ characters. It should fail due to the length of the column. What is the Error Code number?