Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / There are 6 questions listed below
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.
· 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.
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
Answer:
Step-by-step explanation
Question 1:
M09_LastNameFirstName_CreateTables :
Table :Department
CREATE TABLE Department(
Department_ID NUMBER NOT NULL,
Department_Name VARCHAR2(20) NOT NULL,
Office_Location VARCHAR2(20) NOT NULL,
Phone_Number VARCHAR2(20) NOT NULL
);
Table :Project
CREATE TABLE Project(
Project_ID int not null,
Project_Name varchar2(20) NOT NULL,
Department_ID NUMBER NOT NULL,
MaxHours DECIMAL(4,2) NOT NULL,
Start_Date DATE,
End_Date DATE
)
Table : Employee
CREATE TABLE Employee(
Employee_ID NUMBER NOT NULL, First_Name VARCHAR2(20) NOT NULL, Last_Name varchar2(20) NOT NULL, Department_ID NUMBER NOT NULL, Phone Number VARCHAR2(20)
);
Table : Project_Task
CREATE TABLE Project_Task(
Project_ID NUMBER NOT NULL,
Employee_ID NUMBER NOT NULL,
Task_Details VARCHAR2(100),
Hours_Worked DECIMAL(4,2)
);
Question 2:
M09_LastNameFirstName_Constraints :
Alter Table Department ADD constraints dept_PK Primary Key(Department_ID);
Alter Table Project ADD CONSTRAINTS Project_PK PRIMARY KEY(Project_ID);
ALTER TABLE Employee ADD CONSTRAINTS emp_PK PRIMARY KEY(Employee_ID);
ALTER TABLE ProjectTask ADD CONSTRAINTS ProjectTask_PK PRIMARY KEY(Project_ID,Employee_ID);
ALTER TABLE Project ADD CONSTRAINTS Project_FK
FOREIGN KEY(Department_ID) REFERENCES Department(Department_ID);
ALTER TABLE Employee ADD CONSTRAINTS Employee_FK
FOREIGN KEY(Department_ID) REFERENCES Department(Department_ID);
ALTER TABLE ProjectTask ADD CONSTRAINTS ProjectTask_FK
FOREIGN KEY(Project_ID) REFERENCES Project(Project_ID);
ALTER TABLE ProjectTask ADD CONSTRAINTS ProjectTask_empFK
Foreign Key(Employee_ID) REFERENCES Employee(Employee_ID);
Question 3:
M09_LastNameFirstName_Alter :
ALTER TABLE employee ADD email varchar2(20) NOT NULL UNIQUE
