data table foriegn key problem

I am just learning how to program in SQL, so please be patient with me

ok, here it is, i am getting the following errors with my script when i try to execute, and although i realize that this is not the best style to write my script in, i am trying to learn why this is erroring. the errors are:

Msg 1769, Level 16, State 1, Line 9

Foreign key 'Employee2Job_Title' references invalid column 'Title' in referencing table 'Employee'.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint. See previous errors.

now i realize that the second error is because of the first, and that if i can fix the first, the second will go away. Thanks for the help.

Here is my script

use inventory

go

CREATE TABLE Job_Title

(Job_Title_Title char(25) NOT NULL ,

Job_Title_EEO1_Classification char(25) ,

Job_Title_Job_Description Varchar(45) ,

Job_Title_Exempt_Status Varchar(15)

, PRIMARY KEY (Job_Title_Title)

);

CREATE TABLE Employee

(Employee_Emp_ID integer NOT NULL ,

Employee_last_name varchar(15) ,

Employee_first_name varchar(15) ,

Employee_address varchar(30) ,

Employee_city varchar(15) ,

Employee_state char(2) ,

Employee_Telephone_area_code char(3) ,

Employee_Telephone_number char(8) ,

Employee_EEO1_Classification char(25) ,

Employee_Hire_Date char(8) ,

Employee_Salary char(6) ,

Employee_Gender Varchar(1) ,

Employee_Age char(2) ,

Employee_Title char(25)

, PRIMARY KEY (Employee_Emp_ID)

, constraint Employee2Job_Title FOREIGN KEY (Title

) REFERENCES Job_Title

);

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(1, 'Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, '555-0199', 'Sales Workers', 10/7/2003, 21500.00, 'M', 64, 'Cashier')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(2, 'McMullen', 'Eric', '762 Church Street', 'Lemon Grove', 'CA', 619, '555-0133', 'Sales Workers', 11/1/2002, 13500.00, 'M', 20, 'Bagger')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(3, 'Slenj', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, '555-0123', 'Officials & Managers', 6/1/2000, 48000.00, 'M', 34, 'Assistant Manager')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(4, 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA', 760, '555-0100', 'Sales Workers', 3/12/2003, 10530.00, 'F', 24, 'Bagger - 30 hours/wk')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(5, 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 619, '555-0154', 'Office/Clerical', 11/1/2003, 15000.00, 'M', 18, 'Stocker')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(6, 'Esquivez', 'David', '10983 North Coast Highway Apt 902', 'Encinitas', 'CA', 760, '555-0108', 'Operatives (Semi skilled)', 7/25/2003, 18500.00, 'M',25, 'Asst. - Butchers & Seafood Specialists')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(7, 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, '555-0135', 'Sales Workers', 7/12/2003, 21000.00, 'F', 24, 'Cashier')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Accounting Clerk', 'Office/Clerical', 'Computes, Classifies, records, and verifies numerical data for use in maintaining accounting records.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Assistant store manager', 'Officials & Mangers', 'Supervises and coordintes activities of workers in department of food store. Assist store manager in daily operations of store.', 'Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Bagger', 'Sales Worker', 'Places customer orders in bags. Performs carry out duties for customers.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Cashier', 'Sales Worker', 'Operates Cash register to itemize and total customers purchases in grocercy store', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Computer Support Specialist', 'Technician', 'Installs, Modifies, and makes minor repairs to personal computer hardware and software systems and provides technical assistance and training to system users.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Director of Finance & Accounting', 'Officials & Mangers', 'Plans and directs finance and accounting activites for Kudlser Fine Foods.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Bakery & Pastry', 'Craft Workers (Skilled)', 'Obtains or prepares Bakery and Pastry items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)', 'Obtains or prepares Meat and Seafood items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays in store.', 'Non-Exempt')

In your foreign key that you are creating with:

constraint Employee2Job_Title FOREIGN KEY (Title) REFERENCES Job_Title

The column named Title needs to exist in the Employee table on which you are creating the constraint. Then for the column it points to with the REFERENCES part, the needs to be in the format of ReferenceTable(ReferenceTableColumnName)

If I'm guessing right at your tables, I think what you are looking for is more along the lines of

constraint Employee2Job_Title FOREIGN KEY (Employee_Title) REFERENCES Job_Title(Job_Title_Title)

-Sue

|||ok, here is the code i ended up with that cured that problem

CREATE TABLE Job_Title
(Job_Title_Title char(25) NOT NULL ,
Job_Title_EEO1_Classification char(25) ,
Job_Title_Job_Description Varchar(45) ,
Job_Title_Exempt_Status Varchar(15)
, PRIMARY KEY (Job_Title_Title)
);

CREATE TABLE Employee
(Employee_Emp_ID integer NOT NULL ,
Employee_last_name varchar(15) ,
Employee_first_name varchar(15) ,
Employee_address varchar(30) ,
Employee_city varchar(15) ,
Employee_state char(2) ,
Employee_Telephone_area_code char(3) ,
Employee_Telephone_number char(8) ,
Employee_EEO1_Classification char(25) ,
Employee_Hire_Date char(8) ,
Employee_Salary char(6) ,
Employee_Gender Varchar(1) ,
Employee_Age char(2) ,
Employee_Title char(25)
, PRIMARY KEY (Employee_Emp_ID)
, constraint Employee2Job_Title FOREIGN KEY (Employee_Title
) REFERENCES Job_Title
);

but now i am on to new and even more frustrating errors, and i will do some work on them myself, on the morrow, then if i am still having problems, i will again avail myself of this resource.

thanks much sue, your assistance has been invaluable!sql

Data Mining : Drug Trial Question

I have a a simple situation. I am trying to determine for how long (in months) two drugs should be given to a patient. Both the drugs are administered at frequent intervals and their dosage and the condition of the patient is recorded. How do I determine for how long should I continue to adminster the drugs,at what point should I stop? I am dealing with the following variables : Drug 1 dosage,Drug 2 dosage, Period of medication (in months). Can I use SQL Server 2005 data mining capabilites to get the answer? Please let me know.

No you cannot. An MD should evaluate the patient and decide to stop or increase/decrease dosage. Technology is yet far from of replacing an MD.|||

I have to agree with the other response that this decision should be made by a qualified MD. What data mining can do is tell you the relationships between the dosages and the medication periods. Note that since these results are dependent on data, and in this case the data is determined by the recommendations of MD's, essentially what the algorithm is doing is determining patterns in behavior of MD's prescribing drugs for an array of patients. The results could be skewed by a number of factors. For example, if all of the data came from a single MD, you would only be seeing the pattern of that doctor's prescription behavior.

What could the results be used for? I believe in this case, you could use the results for budgeting and forecasting - for example, you see the dosages and you can give an estimate to a patient as to when their treatment would end or to a payment provider as to the total cost of treatment. Another usage would be to present the patterns to MD's - e.g. as a logistic regression scorecard, tree, or some other easily consumable form - that they could use as a guideline to help them make decisions.

However, as the other responder noted, any decisions should rest in the hands of a qualified MD.

|||

Thank you, Jamie for responding to the question. I think you have very well summarized the solution, I am looking for. I am looking to find the dosages so that I can give an estimate to a patient as to when their treatment would end(an interval estimate) or to a payment provider as to the total cost of treatment (an interval estimate). Can I use logistic regression for the purpose ?

|||You could use Logistic Regression, Neural Nets, or Decision Trees. DT will give the most understandable model. I would try all three and use the accuracy chart to see which model does the best job at prediction.