There are 6 questions listed below. Based on the content in the M05 Readings, answer each question. After completing, save and execute your scripts in Oracle Application Express.

https://apex.oracle.com/pls/apex/f?p=4550:1:95346704569:::::

Upload a copy of each of your scripts to IvyLearn.

  • M05_LastNameFirstName_CreateTables
  • M05_LastNameFirstName_Constraints
  • M05_LastNameFirstName_Alter
  • M05_LastNameFirstName_InsertInto
  • M05_LastNameFirstName_Update

*Note: For fields that exist in multiple tables, data type and size should be the same (i.e. ProjDeptID exists in ProjDept, Employee, and Task tables; the datatype for the ProjDeptID in each of these tables should be the same).  When creating tables, consider data that is dependent on each other (i.e. primary key fields must always be created before foreign keys can reference them).  For the data entry is time consuming; be sure to allot enough time to complete the data entry portion of this assignment.  To successfully insert your statements, use the same table names that used in when creating the tables for this assignment. When entering data, consider data that is dependent on each other (i.e. primary key data must always be added before foreign keys). Required fields must have data.

(40 points) Based on the Entity Relationship Diagram (shown on page 3) for the Employee Projects Database, complete the following:

  1. 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.
  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. In each script created above, include the following comments:

 

sql uml diagram assignment help

  • 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.

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.

 

ProjDept Data

ProjDeptID ProjDeptName OfficeLocation PhoneNumber
1001 Accounting ITCC01-400 888-285-8100
2001 Human Resources ITCC01-200 888-285-8200
3001 Marketing ITCC02-300 888-285-8300
4001 Information Technology ITCC02-100 888-285-8400
5001 Legal ITCC01-100 888-285-8500

 

Employee Data

EmployeeID FirstName LastName ProjDeptID PhoneNumber Email
10 Mark Columbus 1001 888-285-8101 [email protected]
29 Elvin Wahl 2001 888-285-8201 [email protected]
38 Taylor Noel 3001 888-285-8303 [email protected]
47 Ariel Colby 4001 888-285-8401 [email protected]
56 Riley Peterson 4001 888-285-8402 [email protected]
65 Terence Ferdinand 1001 888-285-8102 [email protected].com
74 Bryce Daley 5001 888-285-8501 [email protected]
83 Eva Myers 2001 888-285-8203 [email protected]
92 Lyn Lorenzo 3001 888-285-8305 [email protected]
11 Jamaal Holt 3001 888-285-8307 [email protected]

 

Project Data

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.

INSERT INTO Project VALUES (ProjectID, ProjectName, ProjDeptID, MaxHours, TO_DATE (StartDate,’DD-Mon-YYYY’), TO_DATE (EndDate,’DD-Mon-YYYY’));

Project Data

ProjectID ProjectName ProjDeptID MaxHours StartDate EndDate
901 Product Plan 3001 135 10-May-12 15-Sep-12
902 Tax Preparation 1001 120 05-Jul-12 15-Oct-12
903 Portfolio Analysis 5001 145 10-Aug-12  

 

Project Task Data

ProjectID EmployeeID HoursWorked
901 47 30
901 56 75
901 38 55
902 65 40
902 10 45
902 74 25
903 74 20
903 83 45
903 29 40
901 74 35
902 56 80

 

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 EmployeeID TaskDetails
901 47 Payments
901 56 Error checking
901 38 User management
902 65 Annual Accounts
902 10 Research requirements
903 83 Overview
903 29 Error checking

 

 

 

WeCreativez WhatsApp Support
Stuck with your assignment? When is it due? Chat with us.
👋 Hi, how can I help?