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