--Creating the tables USE Bobs_Home_Repair; DROP TABLE IF EXISTS dbo.BOB_EMPLOYEE; CREATE TABLE dbo.BOB_EMPLOYEE ( Employee_ID INT NOT NULL UNIQUE, First_Name VARCHAR(30) NOT NULL, Last_Name VARCHAR(30) NOT NULL, Address VARCHAR(100) NOT NULL, Phone_Number VARCHAR(20) NOT NULL, Hourly_Rate MONEY NOT NULL ); DROP TABLE IF EXISTS dbo.CUSTOMER; CREATE TABLE dbo.CUSTOMER ( Customer_ID INT NOT NULL UNIQUE, First_Name VARCHAR(30) NOT NULL, Last_Name VARCHAR(30) NOT NULL, Address VARCHAR(100) NOT NULL, Phone_Number VARCHAR(20) NOT NULL, ); DROP TABLE IF EXISTS dbo.INVOICE; CREATE TABLE dbo.INVOICE ( Invoice_ID INT NOT NULL UNIQUE, Job_ID INT NOT NULL UNIQUE, Customer_ID INT NOT NULL UNIQUE, Material_ID INT NOT NULL UNIQUE, Invoice_Date DATE NOT NULL, Invoice_Amount MONEY NOT NULL, Invoice_Due DATE NOT NULL, Invoice_Paid_Full_Amount CHAR(1) NOT NULL, Balance_Paid MONEY NOT NULL, Balance_Remaining MONEY NOT NULL ); DROP TABLE IF EXISTS dbo.MATERIALS; CREATE TABLE dbo.MATERIALS ( Material_ID INT NOT NULL UNIQUE, Job_ID INT NOT NULL UNIQUE, Supplier_ID INT NOT NULL UNIQUE, Description VARCHAR(500) NOT NULL, Unit_Of_Issue VARCHAR(10) NOT NULL, Quantity INT NOT NULL, Price MONEY NOT NULL, Order_Date DATE NOT NULL, Receive_Date DATE NOT NULL ); DROP TABLE IF EXISTS dbo.PAYMENT; CREATE TABLE dbo.PAYMENT ( Payment_ID INT NOT NULL UNIQUE, Invoice_ID INT NOT NULL UNIQUE, Supplier_ID INT NOT NULL UNIQUE, Payment_Type VARCHAR(15) NOT NULL, Date_Paid DATE NOT NULL, Amount_Paid MONEY NOT NULL ); DROP TABLE IF EXISTS dbo.SUPPLIER; CREATE TABLE dbo.SUPPLIER ( Supplier_ID INT NOT NULL UNIQUE, Account_ID INT NOT NULL UNIQUE, Account_Balance MONEY NOT NULL, Payment_Due_Date DATE NOT NULL, Supplier_Name VARCHAR(100) NOT NULL, Supplier_Address VARCHAR(100) NOT NULL, Supplier_Phone VARCHAR(20) NOT NULL, Supplier_Contact VARCHAR(50) NOT NULL ); DROP TABLE IF EXISTS dbo.EMPLOYEE_JOBS; CREATE TABLE dbo.EMPLOYEE_JOBS ( Job_ID INT NOT NULL UNIQUE, Customer_ID INT NOT NULL UNIQUE, Employee_ID INT NOT NULL UNIQUE, Description VARCHAR(500) NOT NULL, Hours_Estimated INT NOT NULL, Hours_Worked INT NOT NULL, Total_Cost MONEY NOT NULL, Date_Estimate_Sent DATE NOT NULL, Customer_Approved_Estimate CHAR(1) NOT NULL, Customer_Follow_Up_Date DATE NOT NULL, Date_Estimate_Approved DATE NULL, Date_Job_Started DATE NULL, Date_Job_Ended DATE NULL, Customer_Satisfaction VARCHAR(1000) NOT NULL, Customer_Misc VARCHAR(500) NOT NULL ); --Primary Key for Employee_ID on BOB_EMPLOYEE ALTER TABLE dbo.BOB_EMPLOYEE ADD CONSTRAINT PK_BOB_EMPLOYEE PRIMARY KEY(Employee_ID); --Primary Key for Customer_ID on CUSTOMER ALTER TABLE dbo.CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY(Customer_ID); --Primary Key for Invoice_ID on INVOICE ALTER TABLE dbo.INVOICE ADD CONSTRAINT PK_INVOICE PRIMARY KEY(Invoice_ID); --Primary Key for Material_ID on MATERIALS ALTER TABLE dbo.MATERIALS ADD CONSTRAINT PK_MATERIALS PRIMARY KEY(Material_ID); --Primary Key for Payment_ID on PAYMENT ALTER TABLE dbo.PAYMENT ADD CONSTRAINT PK_PAYMENT PRIMARY KEY(Payment_ID); --Primary Key for Supplier_ID on SUPPLIER ALTER TABLE dbo.SUPPLIER ADD CONSTRAINT PK_SUPPLIER PRIMARY KEY(Supplier_ID); --Primary Key for Job_ID on EMPLOYEE_JOBS ALTER TABLE dbo.EMPLOYEE_JOBS ADD CONSTRAINT PK_EMPLOYEE_JOBS PRIMARY KEY(Job_ID); --Foreign Key for Job_ID on INVOICE and MATERIALS ALTER TABLE dbo.INVOICE ADD FOREIGN KEY (Job_ID) REFERENCES dbo.MATERIALS(Job_ID); --Foreign Key for Job_ID on INVOICE and EMPLOYEE JOBS ALTER TABLE dbo.INVOICE ADD CONSTRAINT FK_Job_ID FOREIGN KEY(Job_ID) REFERENCES dbo.EMPLOYEE_JOBS(Job_ID); --Foreign Key for Customer_ID on INVOICE and EMPLOYEE JOBS ALTER TABLE dbo.INVOICE ADD CONSTRAINT FK_Cusomter_ID FOREIGN KEY(Customer_ID) REFERENCES dbo.EMPLOYEE_JOBS(Customer_ID); --Foreign Key for Material_ID on INVOICE and MATERIALS ALTER TABLE dbo.INVOICE ADD CONSTRAINT FK_Material_ID FOREIGN KEY(Material_ID) REFERENCES dbo.MATERIALS(Material_ID); --Foreign Key for Job_ID on MATERIALS and EMPLOYEE_JOBS ALTER TABLE dbo.MATERIALS ADD CONSTRAINT FK_Job_ID FOREIGN KEY(Job_ID) REFERENCES dbo.EMPLOYEE_JOBS(Job_ID); --Foreign Key for Supplier_ID on MATERIALS and SUPPLIER ALTER TABLE dbo.MATERIALS ADD CONSTRAINT FK_Supplier_ID FOREIGN KEY(Supplier_ID) REFERENCES dbo.SUPPLIER(Supplier_ID); --Foreign Key for Invoice_ID on PAYMENT and INVOICE ALTER TABLE dbo.PAYMENT ADD CONSTRAINT FK_Invoice_ID FOREIGN KEY(Invoice_ID) REFERENCES dbo.INVOICE(Invoice_ID); --Foreign Key for Supplier_ID on PAYMENT and SUPPLIER ALTER TABLE dbo.PAYMENT ADD CONSTRAINT FK_Supplier_ID FOREIGN KEY(Supplier_ID) REFERENCES dbo.SUPPLIER(Supplier_ID); --Foreign Key for Employee_ID on EMPLOYEE_JOBS and BOB_EMPLOYEE ALTER TABLE dbo.EMPLOYEE_JOBS ADD CONSTRAINT FK_Employee_ID FOREIGN KEY(Employee_ID) REFERENCES dbo.BOB_EMPLOYEE(Employee_ID); i wrote this and i am getting this error and don't know how to fix it

--Creating the tables USE Bobs_Home_Repair; DROP TABLE IF EXISTS dbo.BOB_EMPLOYEE; CREATE TABLE dbo.BOB_EMPLOYEE ( Employee_ID INT NOT NULL UNIQUE, First_Name VARCHAR(30) NOT NULL, Last_Name VARCHAR(30) NOT NULL, Address VARCHAR(100) NOT NULL, Phone_Number VARCHAR(20) NOT NULL, Hourly_Rate MONEY NOT NULL ); DROP TABLE IF EXISTS dbo.CUSTOMER; CREATE TABLE dbo.CUSTOMER ( Customer_ID INT NOT NULL UNIQUE, First_Name VARCHAR(30) NOT NULL, Last_Name VARCHAR(30) NOT NULL, Address VARCHAR(100) NOT NULL, Phone_Number VARCHAR(20) NOT NULL, ); DROP TABLE IF EXISTS dbo.INVOICE; CREATE TABLE dbo.INVOICE ( Invoice_ID INT NOT NULL UNIQUE, Job_ID INT NOT NULL UNIQUE, Customer_ID INT NOT NULL UNIQUE, Material_ID INT NOT NULL UNIQUE, Invoice_Date DATE NOT NULL, Invoice_Amount MONEY NOT NULL, Invoice_Due DATE NOT NULL, Invoice_Paid_Full_Amount CHAR(1) NOT NULL, Balance_Paid MONEY NOT NULL, Balance_Remaining MONEY NOT NULL ); DROP TABLE IF EXISTS dbo.MATERIALS; CREATE TABLE dbo.MATERIALS ( Material_ID INT NOT NULL UNIQUE, Job_ID INT NOT NULL UNIQUE, Supplier_ID INT NOT NULL UNIQUE, Description VARCHAR(500) NOT NULL, Unit_Of_Issue VARCHAR(10) NOT NULL, Quantity INT NOT NULL, Price MONEY NOT NULL, Order_Date DATE NOT NULL, Receive_Date DATE NOT NULL ); DROP TABLE IF EXISTS dbo.PAYMENT; CREATE TABLE dbo.PAYMENT ( Payment_ID INT NOT NULL UNIQUE, Invoice_ID INT NOT NULL UNIQUE, Supplier_ID INT NOT NULL UNIQUE, Payment_Type VARCHAR(15) NOT NULL, Date_Paid DATE NOT NULL, Amount_Paid MONEY NOT NULL ); DROP TABLE IF EXISTS dbo.SUPPLIER; CREATE TABLE dbo.SUPPLIER ( Supplier_ID INT NOT NULL UNIQUE, Account_ID INT NOT NULL UNIQUE, Account_Balance MONEY NOT NULL, Payment_Due_Date DATE NOT NULL, Supplier_Name VARCHAR(100) NOT NULL, Supplier_Address VARCHAR(100) NOT NULL, Supplier_Phone VARCHAR(20) NOT NULL, Supplier_Contact VARCHAR(50) NOT NULL ); DROP TABLE IF EXISTS dbo.EMPLOYEE_JOBS; CREATE TABLE dbo.EMPLOYEE_JOBS ( Job_ID INT NOT NULL UNIQUE, Customer_ID INT NOT NULL UNIQUE, Employee_ID INT NOT NULL UNIQUE, Description VARCHAR(500) NOT NULL, Hours_Estimated INT NOT NULL, Hours_Worked INT NOT NULL, Total_Cost MONEY NOT NULL, Date_Estimate_Sent DATE NOT NULL, Customer_Approved_Estimate CHAR(1) NOT NULL, Customer_Follow_Up_Date DATE NOT NULL, Date_Estimate_Approved DATE NULL, Date_Job_Started DATE NULL, Date_Job_Ended DATE NULL, Customer_Satisfaction VARCHAR(1000) NOT NULL, Customer_Misc VARCHAR(500) NOT NULL ); --Primary Key for Employee_ID on BOB_EMPLOYEE ALTER TABLE dbo.BOB_EMPLOYEE ADD CONSTRAINT PK_BOB_EMPLOYEE PRIMARY KEY(Employee_ID); --Primary Key for Customer_ID on CUSTOMER ALTER TABLE dbo.CUSTOMER ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY(Customer_ID); --Primary Key for Invoice_ID on INVOICE ALTER TABLE dbo.INVOICE ADD CONSTRAINT PK_INVOICE PRIMARY KEY(Invoice_ID); --Primary Key for Material_ID on MATERIALS ALTER TABLE dbo.MATERIALS ADD CONSTRAINT PK_MATERIALS PRIMARY KEY(Material_ID); --Primary Key for Payment_ID on PAYMENT ALTER TABLE dbo.PAYMENT ADD CONSTRAINT PK_PAYMENT PRIMARY KEY(Payment_ID); --Primary Key for Supplier_ID on SUPPLIER ALTER TABLE dbo.SUPPLIER ADD CONSTRAINT PK_SUPPLIER PRIMARY KEY(Supplier_ID); --Primary Key for Job_ID on EMPLOYEE_JOBS ALTER TABLE dbo.EMPLOYEE_JOBS ADD CONSTRAINT PK_EMPLOYEE_JOBS PRIMARY KEY(Job_ID); --Foreign Key for Job_ID on INVOICE and MATERIALS ALTER TABLE dbo.INVOICE ADD FOREIGN KEY (Job_ID) REFERENCES dbo.MATERIALS(Job_ID); --Foreign Key for Job_ID on INVOICE and EMPLOYEE JOBS ALTER TABLE dbo.INVOICE ADD CONSTRAINT FK_Job_ID FOREIGN KEY(Job_ID) REFERENCES dbo.EMPLOYEE_JOBS(Job_ID); --Foreign Key for Customer_ID on INVOICE and EMPLOYEE JOBS ALTER TABLE dbo.INVOICE ADD CONSTRAINT FK_Cusomter_ID FOREIGN KEY(Customer_ID) REFERENCES dbo.EMPLOYEE_JOBS(Customer_ID); --Foreign Key for Material_ID on INVOICE and MATERIALS ALTER TABLE dbo.INVOICE ADD CONSTRAINT FK_Material_ID FOREIGN KEY(Material_ID) REFERENCES dbo.MATERIALS(Material_ID); --Foreign Key for Job_ID on MATERIALS and EMPLOYEE_JOBS ALTER TABLE dbo.MATERIALS ADD CONSTRAINT FK_Job_ID FOREIGN KEY(Job_ID) REFERENCES dbo.EMPLOYEE_JOBS(Job_ID); --Foreign Key for Supplier_ID on MATERIALS and SUPPLIER ALTER TABLE dbo.MATERIALS ADD CONSTRAINT FK_Supplier_ID FOREIGN KEY(Supplier_ID) REFERENCES dbo.SUPPLIER(Supplier_ID); --Foreign Key for Invoice_ID on PAYMENT and INVOICE ALTER TABLE dbo.PAYMENT ADD CONSTRAINT FK_Invoice_ID FOREIGN KEY(Invoice_ID) REFERENCES dbo.INVOICE(Invoice_ID); --Foreign Key for Supplier_ID on PAYMENT and SUPPLIER ALTER TABLE dbo.PAYMENT ADD CONSTRAINT FK_Supplier_ID FOREIGN KEY(Supplier_ID) REFERENCES dbo.SUPPLIER(Supplier_ID); --Foreign Key for Employee_ID on EMPLOYEE_JOBS and BOB_EMPLOYEE ALTER TABLE dbo.EMPLOYEE_JOBS ADD CONSTRAINT FK_Employee_ID FOREIGN KEY(Employee_ID) REFERENCES dbo.BOB_EMPLOYEE(Employee_ID); i wrote this and i am getting this error and don't know how to fix it

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

--Creating the tables
USE Bobs_Home_Repair;

DROP TABLE IF EXISTS dbo.BOB_EMPLOYEE;

CREATE TABLE dbo.BOB_EMPLOYEE
(
Employee_ID INT NOT NULL UNIQUE,
First_Name VARCHAR(30) NOT NULL,
Last_Name VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
Phone_Number VARCHAR(20) NOT NULL,
Hourly_Rate MONEY NOT NULL
);

DROP TABLE IF EXISTS dbo.CUSTOMER;

CREATE TABLE dbo.CUSTOMER
(
Customer_ID INT NOT NULL UNIQUE,
First_Name VARCHAR(30) NOT NULL,
Last_Name VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
Phone_Number VARCHAR(20) NOT NULL,
);

DROP TABLE IF EXISTS dbo.INVOICE;

CREATE TABLE dbo.INVOICE
(
Invoice_ID INT NOT NULL UNIQUE,
Job_ID INT NOT NULL UNIQUE,
Customer_ID INT NOT NULL UNIQUE,
Material_ID INT NOT NULL UNIQUE,
Invoice_Date DATE NOT NULL,
Invoice_Amount MONEY NOT NULL,
Invoice_Due DATE NOT NULL,
Invoice_Paid_Full_Amount CHAR(1) NOT NULL,
Balance_Paid MONEY NOT NULL,
Balance_Remaining MONEY NOT NULL
);

DROP TABLE IF EXISTS dbo.MATERIALS;

CREATE TABLE dbo.MATERIALS
(
Material_ID INT NOT NULL UNIQUE,
Job_ID INT NOT NULL UNIQUE,
Supplier_ID INT NOT NULL UNIQUE,
Description VARCHAR(500) NOT NULL,
Unit_Of_Issue VARCHAR(10) NOT NULL,
Quantity INT NOT NULL,
Price MONEY NOT NULL,
Order_Date DATE NOT NULL,
Receive_Date DATE NOT NULL
);

DROP TABLE IF EXISTS dbo.PAYMENT;

CREATE TABLE dbo.PAYMENT
(
Payment_ID INT NOT NULL UNIQUE,
Invoice_ID INT NOT NULL UNIQUE,
Supplier_ID INT NOT NULL UNIQUE,
Payment_Type VARCHAR(15) NOT NULL,
Date_Paid DATE NOT NULL,
Amount_Paid MONEY NOT NULL
);

DROP TABLE IF EXISTS dbo.SUPPLIER;

CREATE TABLE dbo.SUPPLIER
(
Supplier_ID INT NOT NULL UNIQUE,
Account_ID INT NOT NULL UNIQUE,
Account_Balance MONEY NOT NULL,
Payment_Due_Date DATE NOT NULL,
Supplier_Name VARCHAR(100) NOT NULL,
Supplier_Address VARCHAR(100) NOT NULL,
Supplier_Phone VARCHAR(20) NOT NULL,
Supplier_Contact VARCHAR(50) NOT NULL
);

DROP TABLE IF EXISTS dbo.EMPLOYEE_JOBS;

CREATE TABLE dbo.EMPLOYEE_JOBS
(
Job_ID INT NOT NULL UNIQUE,
Customer_ID INT NOT NULL UNIQUE,
Employee_ID INT NOT NULL UNIQUE,
Description VARCHAR(500) NOT NULL,
Hours_Estimated INT NOT NULL,
Hours_Worked INT NOT NULL,
Total_Cost MONEY NOT NULL,
Date_Estimate_Sent DATE NOT NULL,
Customer_Approved_Estimate CHAR(1) NOT NULL,
Customer_Follow_Up_Date DATE NOT NULL,
Date_Estimate_Approved DATE NULL,
Date_Job_Started DATE NULL,
Date_Job_Ended DATE NULL,
Customer_Satisfaction VARCHAR(1000) NOT NULL,
Customer_Misc VARCHAR(500) NOT NULL
);

--Primary Key for Employee_ID on BOB_EMPLOYEE
ALTER TABLE dbo.BOB_EMPLOYEE
ADD CONSTRAINT PK_BOB_EMPLOYEE
PRIMARY KEY(Employee_ID);

--Primary Key for Customer_ID on CUSTOMER
ALTER TABLE dbo.CUSTOMER
ADD CONSTRAINT PK_CUSTOMER
PRIMARY KEY(Customer_ID);

--Primary Key for Invoice_ID on INVOICE
ALTER TABLE dbo.INVOICE
ADD CONSTRAINT PK_INVOICE
PRIMARY KEY(Invoice_ID);

--Primary Key for Material_ID on MATERIALS
ALTER TABLE dbo.MATERIALS
ADD CONSTRAINT PK_MATERIALS
PRIMARY KEY(Material_ID);

--Primary Key for Payment_ID on PAYMENT
ALTER TABLE dbo.PAYMENT
ADD CONSTRAINT PK_PAYMENT
PRIMARY KEY(Payment_ID);

--Primary Key for Supplier_ID on SUPPLIER
ALTER TABLE dbo.SUPPLIER
ADD CONSTRAINT PK_SUPPLIER
PRIMARY KEY(Supplier_ID);

--Primary Key for Job_ID on EMPLOYEE_JOBS
ALTER TABLE dbo.EMPLOYEE_JOBS
ADD CONSTRAINT PK_EMPLOYEE_JOBS
PRIMARY KEY(Job_ID);

--Foreign Key for Job_ID on INVOICE and MATERIALS
ALTER TABLE dbo.INVOICE
ADD FOREIGN KEY (Job_ID)
REFERENCES dbo.MATERIALS(Job_ID);

--Foreign Key for Job_ID on INVOICE and EMPLOYEE JOBS
ALTER TABLE dbo.INVOICE
ADD CONSTRAINT FK_Job_ID
FOREIGN KEY(Job_ID)
REFERENCES dbo.EMPLOYEE_JOBS(Job_ID);

--Foreign Key for Customer_ID on INVOICE and EMPLOYEE JOBS
ALTER TABLE dbo.INVOICE
ADD CONSTRAINT FK_Cusomter_ID
FOREIGN KEY(Customer_ID)
REFERENCES dbo.EMPLOYEE_JOBS(Customer_ID);

--Foreign Key for Material_ID on INVOICE and MATERIALS
ALTER TABLE dbo.INVOICE
ADD CONSTRAINT FK_Material_ID
FOREIGN KEY(Material_ID)
REFERENCES dbo.MATERIALS(Material_ID);

--Foreign Key for Job_ID on MATERIALS and EMPLOYEE_JOBS
ALTER TABLE dbo.MATERIALS
ADD CONSTRAINT FK_Job_ID
FOREIGN KEY(Job_ID)
REFERENCES dbo.EMPLOYEE_JOBS(Job_ID);

--Foreign Key for Supplier_ID on MATERIALS and SUPPLIER
ALTER TABLE dbo.MATERIALS
ADD CONSTRAINT FK_Supplier_ID
FOREIGN KEY(Supplier_ID)
REFERENCES dbo.SUPPLIER(Supplier_ID);

--Foreign Key for Invoice_ID on PAYMENT and INVOICE
ALTER TABLE dbo.PAYMENT
ADD CONSTRAINT FK_Invoice_ID
FOREIGN KEY(Invoice_ID)
REFERENCES dbo.INVOICE(Invoice_ID);

--Foreign Key for Supplier_ID on PAYMENT and SUPPLIER
ALTER TABLE dbo.PAYMENT
ADD CONSTRAINT FK_Supplier_ID
FOREIGN KEY(Supplier_ID)
REFERENCES dbo.SUPPLIER(Supplier_ID);

--Foreign Key for Employee_ID on EMPLOYEE_JOBS and BOB_EMPLOYEE
ALTER TABLE dbo.EMPLOYEE_JOBS
ADD CONSTRAINT FK_Employee_ID
FOREIGN KEY(Employee_ID)
REFERENCES dbo.BOB_EMPLOYEE(Employee_ID);

i wrote this and i am getting this error and don't know how to fix it

Msg 2714, Level 16, State 5, Line 205
There is already an object named 'FK_Job_ID' in the database.
Msg 1750, Level 16, State 1, Line 205
Could not create constraint or index. See previous errors.

Completion time: 2020-07-23T20:01:29.0060092-07:00

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.