Hi,what I need for this is strictly the query entry, not the result. I would greatly appreciate it if this could be answered in the next couple hours. I've included the script so you can apply it in your own DBMS software. Thanks in advanced.

SCRIPT

CREATE TABLE department ( dpt_no CHAR(2) NOT NULL PRIMARY KEY, name CHAR(20), mgr_ssn CHAR(9), mgr_start_date DATE ); CREATE TABLE project ( pro_number CHAR(2) PRIMARY KEY, pro_name CHAR(25), location CHAR(25), dpt_no CHAR(2) ); CREATE TABLE employee ( emp_ssn CHAR(9) PRIMARY KEY, last_name CHAR(25), first_name CHAR(25), middle_name CHAR(25), address CHAR(50), city CHAR(25), state CHAR(2), zip CHAR(9), date_of_birth DATE, salary decimal(7,2), parking_space CHAR(4), gender CHAR(1), dpt_no CHAR(2), super_ssn CHAR(9) ) ; CREATE TABLE assignment ( emp_ssn CHAR(9) NOT NULL, pro_number CHAR(2) NOT NULL , work_hours decimal(5,1), PRIMARY KEY (emp_ssn, pro_number ) ) ; CREATE TABLE dependent ( emp_ssn CHAR(9) NOT NULL, dep_name CHAR(50) NOT NULL , gender CHAR(1), date_of_birth DATE, relationship CHAR(10), PRIMARY KEY (emp_ssn, dep_name) ) ; INSERT INTO department VALUES ( '7', 'Production', '999666666', '2005-06-19'); INSERT INTO department VALUES ( '3', 'Admin and Records', '999555555', '2013-01-01'); INSERT INTO department VALUES ( '1', 'Headquarters', '999444444', '1998-05-22'); INSERT INTO project VALUES ( 1, 'Order Entry', 'St. Louis', 7 ); INSERT INTO project VALUES ( 2, 'Payroll', 'Collinsville', 7 ); INSERT INTO project VALUES ( 3, 'Receivables', 'Edwardsville', 7 ); INSERT INTO project VALUES ( 10, 'Inventory', 'Marina', 3 ); INSERT INTO project VALUES ( 20, 'Personnel', 'Edwardsville', 1 ); INSERT INTO project VALUES ( 30, 'Pay Benefits', 'Marina', 3 ); INSERT INTO employee VALUES( '999666666', 'Bordoloi', 'Bijoy', NULL, 'South Main #12', 'Edwardsville', 'IL', 62025, '1967-11-10', 55000, 1, 'M', 1, NULL ); INSERT INTO employee VALUES( '999555555', 'Joyner', 'Suzanne', 'A', '202 Burns Farm', 'Marina', 'CA', 93941, '1971-06-20', 43000, 3, 'F', 3, '999666666' ); INSERT INTO employee VALUES( '999444444', 'Zhu', 'Waiman', 'Z', '303 Lindbergh', 'St. Louis', 'MO', 63121, '1975-12-08', 43000, 32, 'M', 7, '999666666' ); INSERT INTO employee VALUES( '999887777', 'Markis', 'Marcia', 'M', 'High St. #14', 'Monterey', 'CA', 93940, '1978-07-19', 25000, 402, 'F', 3, '999555555' ); INSERT INTO employee VALUES( '999222222', 'Amin', 'Hyder', NULL, 'S. Seaside Apt. B', 'Marina', 'CA', 93941, '1969-03-29', 25000, 422, 'M', 3, '999555555' ); INSERT INTO employee VALUES( '999111111', 'Bock', 'Douglas', 'B', '#2 Mont Verd Dr.', 'St. Louis', 'MO', 63121, '1955-09-01', 30000, 542, 'M', 7, '999444444' ); INSERT INTO employee VALUES( '999333333', 'Joshi', 'Dinesh', NULL, '#10 Oak St.', 'Collinsville', 'IL', 66234,'1972-09-15', 38000, 332, 'M', 7, '999444444' ); INSERT INTO employee VALUES( '999888888', 'Prescott', 'Sherri', 'C', 'Overton Way #4', 'Edwardsville', 'IL', 62025, '1972-07-31', 25000, 296, 'F', 7, '999444444' ); INSERT INTO assignment VALUES ( '999111111', 1, 31.4); INSERT INTO assignment VALUES ( '999111111', 2, 8.5); INSERT INTO assignment VALUES ( '999333333', 3, 42.1); INSERT INTO assignment VALUES ( '999888888', 1, 21.0); INSERT INTO assignment VALUES ( '999888888', 2, 22.0); INSERT INTO assignment VALUES ( '999444444', 2, 12.2); INSERT INTO assignment VALUES ( '999444444', 3, 10.5); INSERT INTO assignment VALUES ( '999444444', 1, NULL); INSERT INTO assignment VALUES ( '999444444', 10, 10.1); INSERT INTO assignment VALUES ( '999444444', 20, 11.8); INSERT INTO assignment VALUES ( '999887777', 30, 30.8); INSERT INTO assignment VALUES ( '999887777', 10, 10.2); INSERT INTO assignment VALUES ( '999222222', 10, 34.5); INSERT INTO assignment VALUES ( '999222222', 30, 5.1); INSERT INTO assignment VALUES ( '999555555', 30, 19.2); INSERT INTO assignment VALUES ( '999555555', 20, 14.8); INSERT INTO assignment VALUES ( '999666666', 20, NULL); INSERT INTO dependent VALUES ( '999444444', 'Jo Ellen', 'F', '1996-04-05', 'DAUGHTER'); INSERT INTO dependent VALUES ( '999444444', 'Andrew', 'M', '1998-10-25', 'SON'); INSERT INTO dependent VALUES ( '999444444', 'Susan', 'F', '1975-05-03', 'SPOUSE'); INSERT INTO dependent VALUES ( '999555555', 'Allen', 'M', '1968-02-29', 'SPOUSE'); INSERT INTO dependent VALUES ( '999111111', 'Jeffery', 'M', '1978-01-01', 'SON'); INSERT INTO dependent VALUES ( '999111111', 'Deanna', 'F', '1978-12-31', 'DAUGHTER'); INSERT INTO dependent VALUES ( '999111111', 'Mary Ellen', 'F', '1957-05-05', 'SPOUSE');

QUESTIONS

1. Change the Salary column in the Employee table to reject nulls.

2. Change the length of the CITY column in the Employee table to 50 characters. 3

. Change the department name in the Department table to Administration for the department name entitled Admin and Records.

4. Add a department in the Department table. You choose the data for the new department.

5. Delete the new department you added in #4.

6. Add foreign key constraints to Assignment table.

7. Change the column name Date_of_Birth to DOB in Dependent table.

8. Create a new table structure named dept_Locations with dept_no and location fields. You also have to put primary and foreign key constraints so that this table is connected to department table.

9. Drop the new table you created in #8.

10.Create a new table named employee_dependent table with a SELECT statement. The new table will contain the following data: employee SSN, employee first and last name, employee address, dependent name, the relationship to employee. Note: You have to use SELECT statement.

11.Create an index named LNAMEINDEX based on the field (attribute) LNAME in employee table.

12.Create a view which contains the following information: employee SSN, employee first and last name, and project name assigned to each employee. You have to use 3 tables to create this view.

13.Create a database named CBT.