Solved

SQL Syntax help

Posted on 2008-10-18
8
269 Views
Last Modified: 2010-08-05
Hi Everyone-

I have attached the code for a class project.  i was hoping someone would look at the Foreign key requirement in the desigh document and look to see if my code meets the requirements.  The requirements are:

I have used the conatraint ALTER but am not sure if it is doing what is asked.

Thanks in Advance.

Supply the SQL Server data types when creating the tables. In the Employee table, create an Employee ID field that will generate a unique number for each employee and designate the field as the Primary Key.  In the Job Title table, you will need to either utilize one of the listed fields as the tables primary key or you will need to create an additional field to use as the primary key.  The primary key from the Job Title table will appear as the foreign key in the Employee table.
USE AlbertaScott

CREATE TABLE Employee

(	Emp_id					int					primary key IDENTITY,

	Last_name				varchar(40)			NOT NULL,

	First_name				varchar(40)			NOT NULL,

	Address					varchar(40)			NOT NULL,

	City					varchar(20)			NOT NULL,

	State					char(2)				NOT NULL,	

	Telephone_area_code		char(3)				Not Null,

	Telephone_number		char(7)				Not Null,

	EE0_1_classification	varchar(40)			Not Null,

	Hire_date				smalldatetime		Not Null,

	Salary					money				Not Null,

	Gender					char(1)				Not Null,

	Age						char(3)				Not Null,

)

Use AlbertaScott

CREATE TABLE Job_title
 

(Job_title					varchar(128)			primary key Not Null,

Emp_id						int					NOT NULL,

EE0_1_classification		varchar(128)		Not Null,

Job_Description				varchar(512)		Not Null,

Exempt_non_exempt_status	char(40)				Not Null,

)
 

ALTER TABLE Employee 

ADD CONSTRAINT fk_Emp_id FOREIGN KEY(Emp_id)

REFERENCES Employee

(Emp_id)

Open in new window

0
Comment
Question by:salberta
  • 5
  • 2
8 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22747851
your implementation sounds just right.
is there anything that makes you doubt?
the ALTER is doing a ALTER TABLE, the table exists already (it is created just above), to ADD a CONSTRAINT .
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22748545
Well, I disagree with AngelIII - I think it is wrong. You would not have a foreign key lookup on the Employee table reference back to itself. The foreign key is meant to maintain referential integrity, meaning that if employee is used in the Job table, then you have the foreign key on emp_id in Jobs table referring back to the Employee table. Make sense ?

Think there is a small "trick" to the question :

>>The primary key from the Job Title table will appear as the foreign key in the Employee table.<<

To me that would indicate creating the job table a bit differently... In fact, have shown three different ways... For a start, try not to use such a large alphabetic column as an index - it is simply inefficient...

And sorry about the formatting - seems to have gone west..



USE AlbertaScott           -- only need to mention once
 

-- step 1 create the employee table
 

CREATE TABLE Employee

(	Emp_id					int					primary key IDENTITY,

	Last_name				varchar(40)			NOT NULL,

	First_name				varchar(40)			NOT NULL,

	Address					varchar(40)			NOT NULL,

	City					varchar(20)			NOT NULL,

	State					char(2)				NOT NULL,	

	Telephone_area_code		char(3)				Not Null,

	Telephone_number		char(7)				Not Null,

	EE0_1_classification	varchar(40)			Not Null,

	Hire_date				smalldatetime		Not Null,

	Salary					money				Not Null,

	Gender					char(1)				Not Null,

	Age						char(3)				Not NulL

)
 
 

-- step 2 create a job_title table - use a Job ID AND the Emp ID as primary key
 

CREATE TABLE Job_title
 

(

Emp_id						int					NOT NULL,

Job_ID						int					NOT NULL,

Job_title					varchar(128)		Not Null,

EE0_1_classification		varchar(128)		Not Null,

Job_Description				varchar(512)		Not Null,

Exempt_non_exempt_status	char(40)			Not Null,
 

PRIMARY KEY (Job_ID, EMP_ID)

)
 

-- step 3 Add the foreign key constraint

 

ALTER TABLE Job_Title ADD CONSTRAINT fk_Emp_id FOREIGN KEY(Emp_id)

REFERENCES Employee (Emp_id)
 
 

-- step 4 - alternate job table - create a job_title table - use a Job ID AND the Emp ID as primary key and foreign key all in the table create
 

CREATE TABLE Job_title1 
 

(

Emp_id						int					NOT NULL REFERENCES Employee (emp_id),

Job_ID						int					NOT NULL,

Job_title					varchar(128)		Not Null,

EE0_1_classification		varchar(128)		Not Null,

Job_Description				varchar(512)		Not Null,

Exempt_non_exempt_status	char(40)			Not Null,
 

PRIMARY KEY (Job_ID, EMP_ID)

)
 
 

-- step 5 - alternate job table - create a job_title table - use a Job ID as primary key and foreign key all in the table create - this is the way I would do it, but then there is that "trick" question...
 

CREATE TABLE Job_title2
 

(

Job_ID						int					PRIMARY KEY,

Emp_id						int					NOT NULL REFERENCES Employee (emp_id),

Job_title					varchar(128)		Not Null,

EE0_1_classification		varchar(128)		Not Null,

Job_Description				varchar(512)		Not Null,

Exempt_non_exempt_status	char(40)			Not Null,

)

Open in new window

0
 

Author Comment

by:salberta
ID: 22748549
Thanks Angelllll-

I was not sure I had the keys created correclty as asked the code request.  When adding data to the Job_title table is it ok to set the NOT NULL to NULL as this is only a relationship.....

Is there a way to veiw the table relationships in SQL 2005 DBMS visually.

Thanks in asvance.


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22748583
in fact, I could be wrong as well...

>>The primary key from the Job Title table will appear as the foreign key in the Employee table.<<

kind of says :

alter table employee add job_id int;

alter table employee ADD CONSTRAINT fk_Job_id FOREIGN KEY(Job_id)
REFERENCES Job_Table (Job_id)


But that would not make any sense... I would be reasonably confident that it is not typed up proerly because the foreign key lives on a table pointing to a candidate key on another, so, the wording Foreign key in the Employee table indicates that the addition of job_id is required... The only other column that they share is EEO_1_Classification, but are different in size which wont work...

So, assume that they are both 40, then we can answer the question (which is possible more accurate an inperpretation of the instructions):


CREATE TABLE Job_title3
 

(

EE0_1_classification		varchar(40)		Not Null Primary Key,

Emp_id						int					NOT NULL,

Job_title					varchar(128)		Not Null,

Job_Description				varchar(512)		Not Null,

Exempt_non_exempt_status	char(40)			Not Null,

)
 
 

ALTER TABLE Employee1 ADD CONSTRAINT fk_EE0_1 FOREIGN KEY(EE0_1_classification)

REFERENCES Job_Title3 (EE0_1_classification)

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22748594
Please look at my last post - the more I think about it, the more I think it is correct. Job_Title is the current Role - seems to be more of a HR exercise rather than a Job / Work Request, in which case using the EE0_1_ classification does make more sense...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22748601
Oh, and remove emp_id as a column from that sample Job_Title3
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 22748609
So, from the top...
CREATE TABLE Job_title
 

(

EE0_1_classification		varchar(40)		Not Null Primary Key,

Job_title					varchar(128)		Not Null,

Job_Description				varchar(512)		Not Null,

Exempt_non_exempt_status	char(40)			Not Null,

)
 

CREATE TABLE Employee

(	Emp_id					int					primary key IDENTITY,

	Last_name				varchar(40)			NOT NULL,

	First_name				varchar(40)			NOT NULL,

	Address					varchar(40)			NOT NULL,

	City					varchar(20)			NOT NULL,

	State					char(2)				NOT NULL,	

	Telephone_area_code		char(3)				Not Null,

	Telephone_number		char(7)				Not Null,

	EE0_1_classification	varchar(40)			Not Null,

	Hire_date				smalldatetime		Not Null,

	Salary					money				Not Null,

	Gender					char(1)				Not Null,

	Age						char(3)				Not NulL

)
 

-- as a seperate statement...
 

ALTER TABLE Employee ADD CONSTRAINT fk_EE0_1 FOREIGN KEY(EE0_1_classification)

REFERENCES Job_Title3 (EE0_1_classification)
 

-- or using in the create:
 

CREATE TABLE Employee

(	Emp_id					int					primary key IDENTITY,

	Last_name				varchar(40)			NOT NULL,

	First_name				varchar(40)			NOT NULL,

	Address					varchar(40)			NOT NULL,

	City					varchar(20)			NOT NULL,

	State					char(2)				NOT NULL,	

	Telephone_area_code		char(3)				Not Null,

	Telephone_number		char(7)				Not Null,

	EE0_1_classification	varchar(40)			Not Null REFERENCES Job_Title (EE0_1_classification),

	Hire_date				smalldatetime		Not Null,

	Salary					money				Not Null,

	Gender					char(1)				Not Null,

	Age						char(3)				Not NulL

)

Open in new window

0
 

Author Comment

by:salberta
ID: 22749228
Thank you both-
 
The keys had me quite confused, the EE0_1_classification satisfies the solution.  I should have been more aware of the table values and used the similar ones from each table.
The wording was very tricky indeed.
 
Thanks again.

n the Job Title table, you will need to either utilize one of the listed fields as the tables primary key or you will need to create an additional field to use as the primary key.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now