1. Consider the following scenario: an IT department at a large company must keep information about workstations deployed throughout the company.

For each workstation, the following information must be kept:

inventory number (This is uniquely assignment by the IT department to each workstation it purchases and is etched onto the workstation.)

product serial number (This is uniquely assigned by the workstation vendor and is on a panel in the rear of the workstation.

Serial numbers of different vendors may overlap; that is, workstations of two different vendors could have the same serial number.)

workstation vendor (This is the name of the vendor of the workstation.

All vendors have different names.)

date purchased

purchase price

amount of RAM

size of disk storage

Solid state drive or hard drive

location (A room number, which is a three digit number.)

a. Write a a relational table schema for this information. Include a table name along with the column names and column domains as part of your schema.

Use this format: Table_name(col1: domain1; col2:domain2: ... ; coln:domainn)

b. Which column or set of columns can be used to uniquely identify the tuples in the table?

Use this information to identify the candidate keys for the table.

c. Identify all of the superkeys derived from the candidate keys from part b.

d. Identify the one primary key and any alternate keys from the candidate keys from part b.

Ans a): Workstations (Inventory Number: Number (5), Serial Number: Varchar2 (16), Vendor: Varchar2 (20), Purchase Date: Date, Price: Number (10, 2), RAM: Number (3), Disk Storage: Varchar2 (10), SSD_HDD: Varchar2 (10), Location: Number (3).

Ans b): To identify the tuples, the keys can be: - Inventory_ Number - (Vendor, Serial_ Number)

Ans c) : All super keys are, - Inventory_ Number, Purchase_ Date, Price, RAM, Disk_ Storage, SSD_HDD, Location - Vendor, Serial_ Number, Purchase_ Date, Price, RAM, Disk_ Storage, SSD_HDD, Location

Ans d): Primary key: Inventory_ Number Candidate key: (Vendor, Serial_ Number)

2. Suppose that the company from problem 1 has a table describing employees of the company which contains the following information:

Employee last name

Employee first name

Employee number (Each employee has a different number assignment by the company.)

Coporate email (Each employee has a different email.)

birth date

home address

company phone number (Not all employees have a separate line.)

home phone number Home addresses and home phone numbers may not be unique as married couples do work at the company.

The IT department keeps another table that links workstations to the employees to whom they are assigned.

Each employee has a workstation.

a. Write the table schema for the two tables described above.

Use the format from problem 1 for the schema.

b. Identify the primary and alternate keys for the two tables.You do not have to identify the superkeys.

c. Identify the necessary foreign keys in the IT department's table to link the employees and workstations.

[Note: Q 1. for Reference to solve Q.2 as said to refer Q1. I need answer for Q2. a,b,c.]