1. Consider the following scenario: an IT department at a large company must kee
ID: 3882484 • Letter: 1
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 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.]
Explanation / Answer
ans)
a)
Employee table schema
Employee(Employee_Number: var char2(9),Employee_last_name: char(30),Employee_first_name: char(20),Corporate_email : varchar2(30),birth_date:Date,home_address: varchar2(40),company_phone_number: number(6),Home_phone_number:number(10))
Employee and workstation link table schema
EmpWorkStationLink(Employee_Number:var char2(9),Inventory_Number : number(5))
*)to link the workstation table with employee we just need two primary key attributes. nothing else we need. if we add we will increase the data redundency or we can add the candidate keys to table
b)
primary key for Employee: Employee_Number
primaey key for EmpWorkStationLink: Employee_Number
alternative key for Employee is: Corporate_email or (birth_date and FirstName)
alternative key for EmpWorkStationLink is: Inventory_Number
c)
foriegn key needed for EmpWorkStationLink from workstation is: Inventory_Number (and if we include candidate key then those two)
another foriegn key was Employee_Number from employee table
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.