Solved

SQL Syntax help

Posted on 2008-10-18
8
273 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select only the top record in a left join 13 35
export sql results to csv 6 35
string fuctions 4 26
SQL Server - Set Field Values ito Zero Based on Related Table 4 24
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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