1. Consider a database schema consisting of two relations, whose schemas are:

Product (maker, model, type)

PC (model, speed, ram, hd, price)

The Product relation gives the manufacturer, model number and type (PC, laptop, or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types (i.e., model forms the key for Product). The PC relation gives for each model number that is a PC, the speed (of the processor in GHz), the amount of RAM (in megabytes), the size of the hard disk (in gigabytes), and the price.

Write the SQL CREATE TABLE (SQL DDL) statements for the above schema, specifying any primary key and unique constraints, and assuming appropriate data types.

Also specify the foreign key constraint that every PC model must appear as a model in the Product table. Also if a row in the product is deleted, then the rows in the PC that refer to that Product must also be deleted.

Note: You can test your foreign key constraint by adding a row in the Product table, and a row in the PC table that reference the row in the Product table. See the Product and the PC table after the Product row has been deleted.

Include the SQL statements and indicate whether your statements compiled correctly against Oracle DB (using SQL Developer). If your statement did not compile, include the error message that you got from Oracle. [2 pts]

2. Consider the schema for FreeChecking Bank, that we designed in HW2 (given below). Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign key constraints). Include your SQL statements, and indicate whether your statements compiled without errors. If there were errors, you must include the error messages. [1 pt]

3. Consider the E/R schema with weak entity sets shown below. Come up with the SQL CREATE TABLE statements (with appropriate primary key, unique and foreign key constraints). Include the SQL statements, and indicate whether your statements compiled without errors. If there were errors, you must include the error messages. [1.5 pts]

4. Consider entity type Employee with attributes empNumber (this is unique), empName, and the year when he/she was hired. An employee can either be a technical employee, a marketing employee, or a business executive. For a technical employee, we know his/her undergraduate degree major. For a marketing employee, we know the revenue generated by this employee to date. For a business executive, we know the number of years of experience.

Model the above using ER, translate the ER schema to SQL DDL. Your turn-in should include the ER schema, the SQL statements, and indicate whether your statements compiled without errors. If there were errors, you must include the error messages. [1.5 pts]

5. Consider the ER schema below for the MoneyMaking supermarket chain problem from HW2. Translate the given ER schema into SQL CREATE TABLE statement. In your solution, include the SQL statements, and indicate whether your statements compiled without errors. If there were errors, you must include the error messages. [4 pts]

SsNo Name number Customers | owns balance Accounts phone address type