Analyze the proposed project table structure below and determine the candidate a
ID: 3755181 • Letter: A
Question
Analyze the proposed project table structure below and determine the candidate and primary key fields. Follow the rules listed in the “Elements of a Candidate Key” and “Elements of a Primary Key” sections on the internet. Complete the following steps:
Step 1. Before you can determine the primary keys and candidate keys, you will need to ask a series of questions (Q&A) about the project table shown below. List your questions and answers, then follow Step 2 below. You may need to make some assumptions (e.g., the purpose of the project table); if so, document them.
Step 2. For each of the fields in the proposed projects table, list details, and assumptions based on the information you collected in Step 1.
Step 3. Based on the information you gathered in Steps 1 and 2, make any necessary changes to the project table structure. Feel free to propose additional tables in order to meet the requirements. Describe the rationale for your assignments of the primary key and candidate keys in your table(s) and explain any changes that you propose to the project table structure or any additional table(s).
Project Table
ProjDescription
ProjLocation
ProjStartDate
DeptName
EmpName
TimeOnJob
Parts
ProjDescription
ProjLocation
ProjStartDate
DeptName
EmpName
TimeOnJob
Parts
Explanation / Answer
Answer)
Project Table
ProjDescription , ProjLocation, ProjStartDate, DeptName, EmpName, TimeOnJob, Parts
Step 1.
Let the purpose of the table is to store that which employees are related to which project and this is a project table.
Questions and Answers)
Q: How can we determine the candidate key here?
A: Candidate keys are the keys in a table which can uniquely identify a tuple.
Q: What's a primary key then?
A: A primary key is the minimal candidate key, and thus the smallest key that can uniquely identify a tuple. This the primary key is also one of the candidate keys.
Q: Candidate keys for the above table?
A: Candidate keys here can be: {ProjDescription , ProjLocation, ProjStartDate, DeptName, EmpName}, {},{ProjDescription, ProjStartDate, DeptName, EmpName}, {ProjDescription, DeptName, EmpName}
Q: Primary keys for the above table?
A: Primary key can only be one thus it is: {ProjDescription, DeptName, EmpName}, so we understand that this is the minimal key that can identify a row uniquely.
Step 2.
ProjDescription , ProjLocation, ProjStartDate, DeptName, EmpName, TimeOnJob, Parts
ProjDescription is the description of the project.
ProjLocation is the location of the project.
ProjStartDate is the start date of the project.
DeptName is the name of the department.
EmpName is the name of the Employee.
TimeOnJob is the time of the hours or days worked on the project.
Parts defines the parts worked on the project.
Step 3.
The above table structure is perfectly fine but it will be easier if there is an additional primary key column for the above table.
The additional column would be ProjID, which will serve as the primary key.