Task 1 Candidate Keys. Consider each entity implemented previously as employee_01, job_01, tool_01,loan_01, reserve_01, and hold_01. Identify the candidate keys for each entity. (The create statements from the previous assignment are included here for your convenience.) If there are not any candidate keys for an entity, then state that. Several of the entities are analyzed for you to get you started with Task 1.

CREATE TABLE employee_01 ( employee_email VARCHAR2(50) NOT NULL, employee_name VARCHAR2(100) NOT NULL, supervisor VARCHAR2(100) NULL );

Task 1 analysis of employee_01: An employee email address is unique across the company; thus, employee_email is a candidate key. An employee_name is not necessarily unique across the company since there may be several employees named 'John Garcia', for example. The supervisor is, presumably, an employee, but one supervisor can supervise many employees so supervisor is not a candidate key.

CREATE TABLE job_01 ( job_desc VARCHAR2(100) NOT NULL, job_customer VARCHAR2(100), job_date DATE NOT NULL, job_status VARCHAR2(12) NOT NULL, job_status_change_date DATE NOT NULL );

Task 1 analysis of job_01: A job_desc value is not unique across all jobs because, for example, some jobs might be done frequently or repeatedly such as 'Replace light bulb in ceiling fixture in conference room.' Thus, job_desc is not, by itself, a candidate key. Neither is job_customer since the same customer may make more than one request for a job. The job_date cannot uniquely identify a row since many jobs could be requested on the same date (even though the time stamp included with date data type could help identify a unique time value for a job request). Job status has only two or three values allowed, based on the business rule, so that cannot be used to uniquely identify a row. The date the job status changes, which is, for example, the date the job completes, is problematic as a key in the same way that job date is. It may be possible to come up with a composite candidate key, but it would be complex, such as (job_desc, job_customer, job_date, job_status, job_status_change_date), namely, all the attributes. Thus, for the job table it appears that the rows are unique and so the candidate key is a composite key of all the attributes, but this may not be desirable to be implemented as the primary key due to complexity. Thus, it may be desirable to create a surrogate key for this table.

CREATE TABLE tool_01 ( tool_no NUMBER(8) NOT NULL, tool_desc VARCHAR2(100) NOT NULL, tool_id_from_mfg VARCHAR2(50) NOT NULL, tool_mfg VARCHAR2(50) NOT NULL );

Task 1 analysis of tool_01: The tool_desc, tool_id_from_mfg, and tool_mfg values uniguely identify a type of tool, such as a 21 oz. Milled Face Steel Framing Hammer produced and sold by HART and given an id of HH21SCM by the manufacturer. But maybe this is a popular tool that needs to be used for many jobs, and so the tool library needs to have 5 of these identical tools in the tool library. So, for this entity, you need another number that identifies the particular tool as, say, number 2 of the 5 total that are kept by the tool library. So tool_no is is going to be an integer that, combined with some of the other column values in this entiry, forms a unique identifer for a particular tool. How many other columns are required is debatable, but I would suggest that tool_desc plus tool_no are sufficient, provided that the tool_descriptions are the manufacurer's tool descriptions, as opposed to generic tool descriptions invented for the tool library, such as 'hammer'. Thus, the candidate key is the composite of tool_no and tool_desc but other combinations could also work.

Now you analyze the three tables left to determine what the candidate keys are for the tables.

CREATE TABLE loan_01 ( tool_no NUMBER(8) NOT NULL, tool_desc VARCHAR2(100) NOT NULL, employee_email VARCHAR2(50) NOT NULL, job_desc VARCHAR2(100), tool_out_date DATE NOT NULL, due_date DATE, loan_active VARCHAR2(8) NOT NULL, loan_status_change_date DATE NOT NULL );

CREATE TABLE reserve_01( employee_email VARCHAR2(50) NOT NULL, tool_desc VARCHAR2(100) NOT NULL, job_desc VARCHAR2(100), reserve_active VARCHAR2(8) NOT NULL, reserve_status_change_date DATE NOT NULL );

CREATE TABLE hold_01( tool_no NUMBER(8) NOT NULL, tool_desc VARCHAR2(100) NOT NULL, employee_email VARCHAR2(50) NOT NULL, job_desc VARCHAR2(100), hold_date DATE NOT NULL, expire_date DATE, hold_active VARCHAR2(8) NOT NULL, hold_status_change_date DATE NOT NULL );

Summarize your analysis of candidate keys in a table. The table will have columns for name of table, candidate key, justification, and how to fix.