Set #3: Preliminary Database Design
You have been asked to design a preliminary database
for a company called AAAAA Tech, Inc.
Question #1 (60-point). Draw an ER diagram for the
following situation (40-point) and state any assumptions
you believe you have to make in order to develop a
complete diagram (20-point):
AAAAA Tech, Inc., has 10 departments and about 4000
employees. The company needs an operational database
to keep track of all employees, departments, and
projects. Every employee has an employee number,
social security number, employed year, current name,
and other names used. Each employee has a certain
position in a department, and may have dependents. For
each dependent, a dependent name and age are stored
in the database. There are two types of employees:
marketing employees and engineering employees. Each
marketing employee has a list of products. Each
engineering employee works in one or more research
areas, and the company has defined a set of research
areas. Each department has a unique department
number, a department name, and an address. Each
employee can be involved in up to 3 projects, and a
project is carried out by up to 10 employees. Each
project must have one project head (i.e., an employee), a
unique project number, and a project name. There are
several other companies who supply various parts for the
projects. AAAAA Tech wants to keep track of these
suppliers, the parts they supply, and their prices. Each
supplier is represented by a unique supplier number,
supplier name, and address. A part can be a component
of another part. For each part, a unique part number and
a part name are stored. A project may need various parts
and a part can be used in multiple projects.
Question #2 (40-point). Convert your ER diagram into a
relational database schema. Give the primary key, all
candidate key(s), and all foreign key(s) of each relation
schema. Note that, for this assignment, we don't
consider integrity constraints, database normalization,
and queries. Naming convention: use capital letters to
write a relational schema name, and use lowercase
letters to write a relational instance name. For example,
“ACCOUNT” is a relational schema, “account” is a
relational instance, and “account (ACCOUNT)” is a
relation. Rename is allowed when it’s necessary.
Example:
account (ACCOUNT): ACCOUNT = pk = ck= fk=
OR
account(ACCOUNT): ACCOUNT = {acc_num, balance,
branch} pk = {acc_num} ck={acc_num} fk=
set1:
Homework Set #1
Install MySQL or Oracle server free version, read their getting started manuals, and run the following
queries in SQL (the queries are from MySQL 8.0 Reference Manual):
Question 1: CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(
20), sex CHAR(1), birth DATE, death DATE);
Question 2: INSERT INTO pet VALUES (‘Puffball’, ‘Diane’, ‘hamster’, ‘f’, ‘1990-0330’, NULL); INSERT INTO pet VALUES (‘Fluffy’, ‘Harold’, ‘cat’, ‘f’, ‘1993-0204’, NULL); INSERT INTO pet VALUES (‘Bowser’, ‘Diane’, ‘bird’, ‘f’, ‘1979-08-31’, ‘1985-07-29’);
Question 3: SELECT * FROM pet;
Question 4: SELECT * FROM pet WHERE birth >= ‘1990-01-01’;
Question 5: DELETE FROM pet WHERE birth >= ‘1990-0101’; SELECT * FROM pet WHERE birth >= ‘1990-01-01’;
Set:2
For this assignment, use Oracle or MySQL.
Hand in the printouts of your interaction with the DBMS.
On the cover page, indicate the class number, your
name, student ID, and the DBMS you used. The simple
manuals are available at the course web site and the
DBMS product’s web site.
1. [40-point] Create a database with the following tables
(relations):
supplier (SupplierNo, SupplierName, City):
item (ItemNo, ItemName):
supplied (SupplierNo, ItemNo, Quantity):
Create the relations and index structures. Then insert the
above tuples into the tables. The types of attribute
Quantity is integer, while the types of all other attributes
should be varchar. Print out all three tables using "select
* ~".
2. [60-point] Write the following queries in SQL:
a) List all different items (ItemNo, ItemName) supplied by
at least two suppliers (no repetition). b) List all suppliers
(SupplierNo, SupplierName) who supply item "Bolt". c)
List all items (ItemNo, ItemName) supplied by either S2
or S4. d) List all items (ItemNo, ItemName) supplied by
both S2 and S4. e) For each supplier that supplies at least
one item, list SupplierNo and the total quantity supplied
by the supplier. f) For each item supplied at least once,
list ItemNo and the number of suppliers that supply the
item.
Print the statements of your queries as well as the actual
results of the queries.
Enjoy.
Good luck.
Purchase answer to see full
attachment