1. CAN CASHAPP U 5$ TO DO THIS Write the SQL statements for creating the tables for the Employee Projects Database. Save these statements in a script called M05_LastNameFirstName_CreateTables (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Upload a copy of your script to IvyLearn. ID fields use NUMBER(x) datatype Date fields use DATE datatype Any field that will be a string use the VARCHAR2(x) datatype, where x is the appropriate size of the field To assist with choosing sizes for the datatypes, some examples of the data for each table created can be found on the last page of this assignment

. 2. Write the SQL statements for altering the tables in the Employee Projects Database to define the primary key and foreign key constraints. Save these statements in a script called M05_LastNameFirstName_Constraints (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Upload a copy of your script to IvyLearn. If the primary key and foreign key constraints have been created in the previous script, ignore this step.

3. Write the SQL statements for the altering the following tables. Save these statements in a script called M05_LastNameFirstName_Alter (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Upload a copy of your script to IvyLearn. Employee add a unique column that holds an email address

4. Create the SQL DML statements for inserting rows of data into the tables for the Employee Projects Database. Save these statements in a script called M05_LastNameFirstName_InsertInto (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Upload a copy of your script to IvyLearn.

5. Create the SQL DML statements for updating the task details in the Project Task table in the Employee Projects Database. Save these statements in a script called M05_LastNameFirstName_Update (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Upload a copy of your script to IvyLearn.

6. In each script created above, include the following comments: At the beginning of the script include your name, the date, and the name of the script and a summary of what the script will do. Throughout the script, include comments to describe briefly what each section of script will do.

Employeeld 10 EMPLOYEE 29 ProjDeptid 1001 2001 3001 4001 4001 1001 PK FirstName | LastName Mark Columbus Elvin Wahl Taylor Noel Ariel Colby Riley Peterson Terence Ferdinand Bryce Daley Eva Myers Lyn Lorenzo Jamaal Holt *Employee Phone Number 888-285-8101 888-285-8201 888-285-8303 888-285-8401 888-285-8402 888-285-8102 888-285-8501 888-285-8203 888-285-8305 88-285-8307 Email mark@itcc.com elvin@itcc.com taylor@itcc.com ariel@itcc.com riley@itcc.com terence@itcc.com bryce@itcc.com eva@itcc.com lyn@itcc.com jamaal@itcc.com * FirstName PROJECTTASK * Last Name PK FK Projects FK ProDept PK FK + Employed 92 11 | 2001 3001 3001 8 PROJDEPT Phone Number TaskDetails PK + ProjDepid Project Data Hours Worked * ProDepthame PROJECT When inserting into this table, use the following format to help with the start dates and end dates. Start date/End date will be replaced with the data values exactly as they are shown in the Start Date/End date column. + Once Location PK Project * PhoneNumber * ProjectName INSERT INTO Project VALUES (ProjectID, ProjectName, ProjDeptid, MaxHours, TO_DATE (StartDate, 'DD-Mon-YYYY), TO_DATE (EndDate,'DD-Mon-YYYY")); FK * ProjDepD Project Data + Maxthours Start Date 1 ProjectID 901 902 903 Project Name Product Plan Tax Preparation Portfolio Analysis ProjDeptID 3001 1001 5001 MaxHours 35 120 45 StartDate 10-May-12 05-Jul-12 10-Aug-12 EndDate 15-Sep-12 1 5-Oct-12 | EndDate 1 Project Task Data For fields that are marked as, these are required/mandatory fields. For fields that are marked as, these are optional fields. Primary key and foreign key fields have been labelled accordingly. Hours Worked 30 Employeeld 47 56 | 38 ProjDept Data O 65 10 ProjDeptid 1001 2001 3001 4001 | 5001 ProjDeptName Accounting Human Resources Marketing Information Technology fficeLocation ITCC01-400 ITCC01-200 ITCCO2-300 ITCC02-100 ITCC01-100 Phone Number 888-285-8100 8 88-285-8200 888-285-8300 888-285-8400 888-285-8500 ProjectID 901 901 901 902 902 902 903 903 903 901 902 74 83 Legal 74 56 80 Employee Data UPDATE Project Task Data To update the Task Details table, you will have to use the AND keyword in your WHERE clause. Look at the format below. UPDATE tablename SET columname = value WHERE condition1 = value AND condition2 = value ProjectID Employeels 90147 90156 38 65 90210 83 903 29 TaskDetails Payments Error checking User management Annual Accounts Research requirements Overview Error checking