DATABASE, can someone please help me out with writinf followng code using MySql

Question

DATABASE, can someone please help me out with writinf followng code using MySql bench:

TCSS445 Database Systems Design SQL Homework This homework will test your understanding of SQL that we covered in lecture. Submit a sql script that contains all the DDL and DML statements on Canvas with a .zip extension (uwnetidsqlhw.zip or uwnetid1uwnetid2sqlhw.zip). Canvas doesn't like .sql files. You will have to populate your tables with data to test the queries. Your script should run in its entirety without any modification when copied and pasted into the query editors. Place your name(s) at the top of the script within the comments. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Superssn no DEPARTMENT Dname Dnumber Mgr ssn Mgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours Schema diagram for the COMPANY relational database schema. DEPENDENT Essn Dependent name Sex Bdate Relationship

Explanation / Answer

Please Note: You have posted more than one Questions. As per Chegg Answering Guidelines, I answered the first 4 subparts of the question. Please Post Separate for other Questions.

Q1)

Create Scripts for the above tables:

CREATE TABLE EMPLOYEE(Fname varchar, Minit int, Lname varchar, Ssn int primary key, Bdate date, Address varchar, Sex varchar, Salary int, Super_ssn int, Dno int, foreign key (Dno) references Department(Dnumber));

create table Department (Dname varchar, Dnumber int primary key, Mgr_ssn int, Mgr_start_date date, foreign key (Mgr_ssn) references Employee(Ssn));

create table DEPT_LOCATIONS (Dnumber int, Dlocation varchar, primary key(Dnumber,Dlocation), foreign key (Dnumber) references Department(Dnumber));

create table Project(Pname varchar, Pnumber int primary key, Plocation varchar, Dnum int, foreign key (Dnum) references Department(Dnumber));

create table Works_On (Essn int, Pno int, Hours int, primary key(Essn,Pno), foreign key (Essn) references Employee(Ssn), foreign key (Pno) references Project(Pnumber));

create table Dependent (Essn int, Dependent_name varchar, Sex varchar, Bdate date, Relationship varchar, primary key(Essn,Dependent_name), foreign key (Essn) references Employee(Ssn));

2)

a) Employees names in dept 5 who work more than 10 hrs per week on ProductX project.

select Fname, Lname from Employee where Dno=5 and where Ssn in (

select Essn from Works_On where Pno in (

select Pnumber from Project where Pname='ProductX'

)

);

b)) Employees names who have dependent with same first name as themselves.

select Employee.Fname, Employee.Lname from Employee inner join

Dependent ON Employee.Ssn = Dependent.Essn where Employee.Fname=Dependent.Dependent_name;

c) Employees names that are directly supervised by Franklin Wong

select Fname, Lname from Employee where Super_ssn in (

select Ssn from Employee where Fname='Franklin' and Lname='Wong'

);

d) For each project, list project name and total hours per week in project

select Project.Pname, sum(Works_On.Hours) from Project inner join Works_On on Project.Pnumber=Works_On.Pno group by Works_On.Pno;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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