1 use the E-R Diagram to create the following tables: Employees, Departments,Job

Question

1 use the E-R Diagram to create the following tables: Employees, Departments,Jobs, Job_History, Locations,Countries. 2. after create these table above answer the question from 3 to 12.

board/execute/content/file?cmdsview&content ids 3270734,1kcourse id 62879.1 RA332 LOCATIONS LOCATION ID STREET ADDRESS VARCHAR2 (40 BYTE) POSTAL CODE CITY STATE PROVINCE VARCHAR2 (25 BYTE) COUNTRY ID NUMBER (4) 2 BYTE) VARCHAR2 (12 BYTE) VARCHAR2 (30 BYTE) RA332 DEPART MENT5 DEPARTMENT-ID enaR(26Y1 NUMBER (4) DEPARTMENT NAME VARCHAR2 (30 BYTE) MANAGER ID LOCATIONID CHAR ( BYTE) NUMBER (8) NUMBER (4) ORA332.EMPL - P EMPLOYEED NUMBER () VARCHAR2 O BYTE) VARCHAR2 25 BYTE) VARCHAR2 (25 gYTE VARCHKAR2 QD BYTE DATE VARCHAR2 (10 BYTE NUMBER (8.2) FIRST NAME LAST NAME EMAIL PHONE_NUMBER HIRE DATE JOB ID COMMISSION PCT NUMBER C.2) MANAGER ID DEPARTMENT ID NUMBER (4 NUMBER 0) ORA332 COUNTRIES ORA332.JOB HISTORY COUNTRY ID COUNTRY NAME REGION ID CHAR (2 BYTE) VARCHAR2 (40 BYTE) NUMBER EMPLOYEE START DATE END DATE NUMBER (0) DATE - JOB ID VARCHAR2 (10 BYTE) DEPARTMENT D NUMBER (4) 08 10 JOBTITLE MIN-SALARY MAX SALARY VARCHAR2 (10 BYTE VARCHAR2 CE - NUMBER (8) NUMBER p) ORA332.REGIONS 27 PM NUMBER REGION ID to search

Explanation / Answer

3. ALTER table Employee ADD SSN INT(9), ADD DateOfBirth DATE;

4. SELECT CONCAT(FirstName, " ", LastName) AS Employee_Name, DateOfBirth FROM Employee;

5. SELECT CONCAT(FirstName, " ", LastName) AS Employee_Name, Salary AS CurrentSalary, Salary * 1.03 AS NewSalary FROM Employee;

6. ALTER TABLE Jobs ADD PRIMARY KEY (JOB_ID);

7. ALTER TABLE Employee ADD CONSTRAINT FK_EJ FOREIGN KEY (JOB_ID) REFERENCES Jobs(JOB_ID);

8.  ALTER TABLE Regions ADD PRIMARY KEY (REGION_ID);

9a. ALTER TABLE JobHistory ADD CONSTRAINT FK_JHJ FOREIGN KEY (JOB_ID) REFERENCES Jobs(JOB_ID);

9b. ALTER TABLE Countries ADD CONSTRAINT FK_CR FOREIGN KEY (REGION_ID) REFERENCES Regions(REGION_ID);

10.  

Option 1 : DESC Locations;

Option 2: SELECT * FROM IINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Locations';

11.

(1) First remove the foreign keys which any table is referencing the employee table.

ALTER TABLE Job_History DROP CONSTRAINT FK_JHE;

OR

ALTER TABLE Job_History DROP FOREIGN KEY FK_JHE;

(Assuming the Foreign key name given is FK_JHE)

(2) Then remove the self referencing foreign key which the Employee table is referencing to itself for Manager_ID

ALTER TABLE Employee DROP CONSTRAINT FK_SM;

OR

ALTER TABLE Employee DROP FOREIGN KEY FK_SM;

(Assuming the Foreign key name given is FK_SM)

(3) Drop the foreign key of Departments which is referencing the Employee table.

ALTER TABLE Departments DROP CONSTRAINT FK_DE;

OR

ALTER TABLE Departments DROP FOREIGN KEY FK_DE;

(Assuming the Foreign key name given is FK_DE)

(4) Finally drop the Employee table.

DROP TABLE Employee

Note: There is no need of dropping other tables as long as they are not referencing Employee table. Here we first dropped those foreign keys anyway. So dropping other tables is not necessary.

12. If we can understand the dependencies correctly,

Countries table references Regions

Locations table references Counties

Department table references Location

JobHistory table references Departments and Jobs

So, we need to follow the same order while deleting to prevent any integrity violation errors. Below would be right sequence

DROP TABLE JOB_HISTORY

DROP TABLE JOBS

DROP TABLE DEPARTMENTS

DROP TABLE LOCATIONS

DROP TABLE COUNTIES

DROP TABLE REGIONS

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.