SUBJECT: DESIGN OF DATABASE SYSTEM Note: Please i need the SQL COMMAND AND THE O

Question

SUBJECT: DESIGN OF DATABASE SYSTEM

Note: Please i need the SQL COMMAND AND THE ORACLE OUTPUT . PLEASE SHOW DETAILED WORK PLEASE. THANKS

Description of Homework For each question below, provide solution for DBMS of your choice (Oracle or Microsoft SQL Server) as shown in class. So, your answer must contain the following: .SQL command Output from DBMS (Oracle or Microsoft SQL Server) 1. Create the database for following relational schema with Employee, Department, and Works tables: Employeeleid.integer ename: string, age: integer, salary: real) Employee(eid: integer, ename: string, age: integer, salary: real) Works(eid:integer, did: integer, hours: integer) Department(did: integer, dname: string, budget: real, managerid: integer)

Explanation / Answer

1. To create tables use below queries:

// creates employee table and in the output it shows:You have made changes to the database.

CREATE TABLE Employee (
eid int NOT NULL PRIMARY KEY,
ename VARCHAR2 NOT NULL,
age int,
salary REAL
);

// creates department table and in the output it shows:You have made changes to the database.

CREATE TABLE Department (
did int NOT NULL PRIMARY KEY,
dname VARCHAR2 NOT NULL,
budget REAL,
managerid int NOT NULL
);

// creates work table and in the output it shows:You have made changes to the database.

CREATE TABLE Works (
eid int,
did int,
hours int,
CONSTRAINT eid FOREIGN KEY (eid)
REFERENCES Employee(eid),
CONSTRAINT did FOREIGN KEY (did)
REFERENCES Department(did)
);

2. for the second condition, while creating department you must execute the below query:

// the output for the below query is: You have made changes to the database.

create table Department(
did int PRIMARY KEY,
dname VARCHAR2(40) NOT NULL,
budget REAL,
managerid int check (managerid >= 0) CHECK (managerid < 1000)
);

3.

// to insert data to employee table:

INSERT INTO Employee (eid, ename, age, salary)
SELECT 101, 'Jones', 23, 60000
UNION ALL SELECT 105, 'Adams', 44, 55000
UNION ALL SELECT 110, 'Rivera','',43000
UNION ALL SELECT 120, 'Tanaka','',60000
UNION ALL SELECT 122, 'Lee',57,45000

// output is: You have made changes to the database. Rows affected: 5

// to insert data to department table:

INSERT INTO Department (did, dname, budget, managerid)
SELECT 10, 'Accounting', 250000, 234
UNION ALL SELECT 12, 'Research', 100000, 453
UNION ALL SELECT 20, 'Sales','',678
UNION ALL SELECT 22, 'IT',400000,324

// output is: You have made changes to the database. Rows affected: 4

// to insert data to works table:

INSERT INTO Works (eid, did, hours)
SELECT 101, 10, 15
UNION ALL SELECT 105, 10, 20
UNION ALL SELECT 101, 12, 25
UNION ALL SELECT 122, 20, 20
UNION ALL SELECT 122, 22, 20
UNION ALL SELECT 105, 20, 10
UNION ALL SELECT 120, 12, 40
UNION ALL SELECT 105, 12, 10

// output is: You have made changes to the database. Rows affected: 8

4. To get the budget and department name for Jones, run the query:

select dname, budget
FROM department dep
INNER JOIN Works wor ON dep.did = wor.did
INNER JOIN Employee emp ON emp.eid = wor.eid where emp.ename ='Jones';

// output is:

Number of Records: 2

** According to chegg's policy if more than 4 parts are present in a question than only 4 needs to be answered. If you want answers for the rest of the questions, you can post them as another question.

*** Please please like the answer to provide the CF Score as I need it. Thanks in advance for the like.

dname budget Accounting 250000 Research 100000

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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