Use the ERD to respond to the following questions. Supply your code below. 1. De

Question

Use the ERD to respond to the following questions. Supply your code below.

1. Describe the cardinality of the tables within the ERD.

2. Write the DDL for each of the 6 tables above, including all constraints.

3. Write the DQL for the following:

All Employees, their titles, salaries, and the department they work for making over fifty thousand dollars.

All department manager’s information, his or her department information, and start date.

Total salary paid to all employees in each department.

Average number of managers per department.

Total number of department managers.

4. Write the DML for the following:

Updating a department manager.

Inserting a new employee, their salary, title, and department association.

Insert a new department.

Delete an employee.

salaries v emp no INT(10) salary INT (11) from date DATE to date DATE Indexes titles emp no INT(10) title VARCHAR(50) from date DATE to date DATE Indexes employees v emp no INT(10) birth date DATE first name VARCHAR(14) last name VARCHAR(16) t gender ENUM(M', F) hire date DATE Indexes dept manager dept no CHAR(4) emp no INT(10) from date DATE to date DATE Indexes dept emp emp no INT(10) dept no CHAR(4) from date DATE to date DATE Indexes LU departments dept no CHAR(4) dept name VARCHAR(40) Indexes

Explanation / Answer

1. Cardinality of the tables means relationships between tables. It can be following

A. one-to-one,
B. one-to-many (whose reversal is many-to-one)
C. many-to-many

Now here we need check that each table has some realtionship with other table so here we have Cardinality of the tables as per ERD given

1. Employee - titles [ one-to-many relatioship ]
   Employee can have multiple tiltles for a given time duration.
  
2. Employee - salaries [ one-to-many relatioship ]
   Employee can have multiple salaries for a given time duration like month wise salaries detail
  
3. Employee - Dept-emp [ one-to-many relationship ]
   employee can be associated with multiple department for a specific time duration. employee can switch from one depatment to another.
  
4. Employee - Dept-manager [ one-to-many relationship ]
   employee can be associated with multiple manger deaprtment for a specific time duration. employee can switch from one depatment to another.
  
5. Employee - Department [ many -to-many relationship ]

   Employee can be associated with many department through his/her service period and one department can be associated with multiple employee or multiple
   employee can be associated with single department.

2. Write the DDL for each of the 6 tables above, including all constraints.

CREATE TYPE gender_enum AS ENUM ('M', 'F');

CREATE TABLE public.chegg_employee
(
emp_no BIGINT CONSTRAINT TenDigits CHECK (emp_no BETWEEN 1000000000 and 9999999999),
birth_date date,
first_name character varying(14),
last_name character varying(16),
gender gender_enum,
hire_date date,
CONSTRAINT chegg_employee_pkey PRIMARY KEY (emp_no)
)

CREATE TABLE public.chegg_department
(
dept_no character varying(4) NOT NULL,
dept_name character varying(40),
CONSTRAINT dept_name_pkey PRIMARY KEY (dept_no)
)

CREATE TABLE public.dept_emp
(
emp_no BIGINT CONSTRAINT TenDigits CHECK (emp_no BETWEEN 1000000000 and 9999999999),
dept_no character varying(4) REFERENCES chegg_department (dept_no) ,
from_date date,
to_date date,
CONSTRAINT dept_emp_pkey PRIMARY KEY (emp_no,dept_no)
)

CREATE TABLE public.dept_manager
(
dept_no character varying(4) REFERENCES chegg_department (dept_no),
emp_no BIGINT CONSTRAINT TenDigits CHECK (emp_no BETWEEN 1000000000 and 9999999999),
from_date date,
to_date date,
CONSTRAINT dept_manager_pkey PRIMARY KEY (dept_no,emp_no)
)

CREATE TABLE public.salaries
(
emp_no BIGINT CONSTRAINT TenDigits CHECK (emp_no BETWEEN 1000000000 and 9999999999),
salary integer
from_date date,
to_date date,
)

CREATE TABLE public.titles
(
emp_no BIGINT CONSTRAINT TenDigits CHECK (emp_no BETWEEN 1000000000 and 9999999999),
title character varying(50) NOT NULL,
from_date date,
to_date date,
)

Note : - here i have not added index. beacuse you have not mentioned on which column we need to apply index.
if we decleare primary key in table then database automatically create one index on primary key column.
   if we want to explicitly declear the index on some other column then we have to mention it

3.   Write the DQL for the following:

A. All Employees, their titles, salaries, and the department they work for making over fifty thousand dollars.

   SELECT E.EMP_NO,S.SALARY,T.TITLE,D.DEPT_NAME FROM chegg_employee E,salaries S,titles T, dept_emp DE,chegg_department d where E.EMP_NO = S.EMP_NO and S.EMP_NO = T.EMP_NO and
   DE.EMP_NO = E.EMP_NO and d.dept_no = de.dept_no and S.salary >= 50000

B. Total salary paid to all employees in each department.

   SELECT FOO.DEPT_NAME,SUM(FOO.SALARY) FROM ( SELECT E.EMP_NO,S.SALARY,T.TITLE,D.DEPT_NAME FROM chegg_employee E,salaries S,titles T, dept_emp DE,chegg_department d where E.EMP_NO = S.EMP_NO and S.EMP_NO = T.EMP_NO and
   DE.EMP_NO = E.EMP_NO and d.dept_no = de.dept_no and S.salary >= 50000) AS FOO GROUP BY DEPT_NAME

C. All department manager’s information, his or her department information, and start date.

SELECT DISTINCT E.EMP_NO,E.FIRST_NAME,D.DEPT_NAME,M.FROM_DATE FROM CHEGG_EMPLOYEE E, DEPT_MANAGER M, CHEGG_DEPARTMENT D WHERE E.EMP_NO = M.EMP_NO AND M.DEPT_NO = D.DEPT_NO

D. Average number of managers per department.

SELECT M.DEPT_NO,COUNT(M.EMP_NO) FROM DEPT_MANAGER M GROUP BY M.DEPT_NO

E. Total number of department managers.

   select count(emp_no) from dept_manager

4. Write the DML for the following:

Inserting a new employee, their salary, title, and department association.
Insert a new department.
  
   Insert into chegg_employee vALUES( 1234567890,'1994-11-29','deepak','rajdev','M','1994-11-29')
   INSERT INTO salaries values( 1234567890, 50000, '1994-11-29','1994-11-29')
   INSERT INTO titles values( 1234567890, 'title_1', '1994-11-29','1994-11-29')
   INSERT INTO chegg_department values('d101','IT DEPARTMENT')
   INSERT INTO dept_emp values( 1234567890,'d101', '1994-11-29','1994-11-29')

Delete an employee.
  

DELETE FROM dept_emp where dept_no = 'd101'
DELETE FROM chegg_department where dept_no = 'd101'

Updating a department manager.

   Update dept_manager set emp_no = 1234567891 where emp_no = 1234567890

Description : Apart from answer of this question, you can use the following quesries to create dummy records in database for the above given tables

SELECT STATEMENT

select * from chegg_employee
select * from chegg_department
select * from salaries
select * from titles
select * from dept_emp
select * from dept_emp
select * from dept_manager

INSERT STATEMENT

Insert into chegg_employee vALUES( 1234567890,'1994-11-29','deepak','rajdev','M','1994-11-29')
Insert into chegg_employee vALUES( 1234567891,'2014-11-29','john','sinha','M','2024-11-29')
INSERT INTO salaries values( 1234567890, 50000, '1994-11-29','1994-11-29')
INSERT INTO salaries values( 1234567891, 550000, '2021-11-29','2024-11-29')
INSERT INTO titles values( 1234567890, 'title_1', '1994-11-29','1994-11-29')
INSERT INTO titles values( 1234567891, 'title_2', '2021-11-29','2021-11-29')
INSERT INTO chegg_department values('d101','IT DEPARTMENT')
INSERT INTO chegg_department values('d102','SALES DEPARTMENT')
INSERT INTO dept_emp values( 1234567890,'d101', '1994-11-29','1994-11-29')
INSERT INTO dept_emp values( 1234567891,'d102', '2021-11-29','2021-11-29')
INSERT INTO dept_manager values( 'd101',1234567890,'1994-11-29','1994-11-29')
INSERT INTO dept_manager values( 'd102',1234567891,'2021-11-29','2021-11-29')

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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