Question
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
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.
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: Please provide me the answer for No.2
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started