computer science

CS 649 Database Management Systems Fall 2017

Instructor: Prof. Ping-Tsai Chung

Mini-Project

(Total: 400 Points) Due: December 11, Monday, 2017

Consider the Company Database given in the handout. This Database contains 6 relations, namely: EMPLOYEE, DEPARTMENT, DEPT_LOCATION, WORKS_ON, PROJECT

and DEPENDENT. Each table is defined in the handout.

Please send your work in one file to my email account [email protected] (i.e.,

[email protected]) and submit a hard copy in class, Thanks.

(I) (80 Points) Using any two available ER Tools to draw the ER Diagram for the Company

Database. The Requirements were discussed in the class. Write two-page report to discuss your

comparative results.

Note that ER Tools such as ERWin Software, http://erwin.com/products/data-modeler,

ERDPlus, https://erdplus.com/, ER Assist Tool and Smartdraw,

https://www.smartdraw.com/

(II) (50 Points) First using Oracle SQL * Plus, create the schema of this database. You need to

check the database referential integrity to decide the order to create tables. Then follow the

Oracle syntax to create tables, please see Create-Tables-Notes-Company-DB at the end of this

notes for your reference, and

https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN11004

https://www.techonthenet.com/oracle/tables/create_table.php

https://www.w3schools.com/sql/sql_create_table.asp

https://www.javatpoint.com/oracle-create-table

http://www.sqlinfo.net/oracle/oracle_Create_table.php

Continue by inserting the data records as presented in the handout. You should print out

the result of final tables.

If you want to learn “Insert Multiple Records”, please see an example at the end of this notes

for your reference.

(III) (100 Points) Solve the following queries in SQL. For each query, you need to specify the

SQL and show the result of each query if applied to the Company Database.

(a) Retrieve the names of employees in department 5 who work more than 10 hours per

week on the 'ProductX' project.

(b) List the names of employees who have a dependent with the same first name as

themselves.

(c) Find the names of employees that are directly supervised by 'Franklin Wong'.

(d) For each project, list the project name and the total hours per week (by all

employees) spent on that project.

(e) Retrieve the names of employees who work on every project.

(f) Retrieve the names of employees who do not work on any project.

(g) For each department, retrieve the department name, and the average salary of

employees working in that department.

(h) Retrieve the average salary of all female employees.

(i) Find the names and addresses of employees who work on at least one project located

in Houston but whose department has no location in Houston.

(j) List the last names of department managers who have no dependents.

(IV) (30 Points) Solve the following queries in SQL. For each query, you need to specify the

SQL using the concept of nested queries and show the result of each query if applied to the

Company Database.

(k) Retrieve the names of all employees who work in the department that has the employee with

the highest salary among all employees,

(l) Retrieve the names of all employees who supervisor’s supervisor has ‘888665555’ for ssn.

(m) Retrieve the names of employees who make at least $10,000 more than the employee who is

paid the least in the company.

(V) (70 Points) Specify the following queries in (III) (a), (b), (c), (e), (f), (i), (j) on the

Company relational database schema using the Relation Algebra Statements (i.e., the

relational operators). Also, show the intermediate result of each query if applied to the

Company Database.

(VI) (70 Points) Specify the following queries in (III) (a), (b), (c), (e), (f), (i), (j) on the

Company relational database schema in both tuple and domain relational calculus.

Subject: Syntax for Inserting Multiple Records in one Oracle

Insertion Statement

You can use the following Oracle Insertion Statement Syntax to insert multiple

records:

INSERT ALL

INTO table_name (column1, column2, column3) VALUES ('val1.1', 'val1.2',

'val1.3')

INTO table_name (column1, column2, column3) VALUES ('val2.1', 'val2.2',

'val2.3')

INTO table_name (column1, column2, column3) VALUES ('val3.1', 'val3.2',

'val3.3')

SELECT * FROM dual;

Example: (for Inserting Multiple Records in one Oracle Insertion Statement into

DEPARTMENT table)

INSERT ALL

into Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) VALUES

('Research', 5, 333445555, '05,22,1988')

into Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) VALUES

('Administration', 4, 987654321, '01,01,1995')

into Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) VALUES

('Headquarters', 1, 888665555, '06,19,1981')

SELECT * FROM dual;

Database Notes - Using an ALTER TABLE statement Prof. P. T. Chung

Using an ALTER TABLE statement

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name

add CONSTRAINT constraint_name

FOREIGN KEY (column1, column2, ... column_n)

REFERENCES parent_table (column1, column2, ... column_n);

Example:

ALTER TABLE products

add CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id)

REFERENCES supplier(supplier_id);