Can someone help me with my sql database, Does it look correct? andI believe tha
ID: 3868293 • Letter: C
Question
Can someone help me with my sql database, Does it look correct? andI believe that my foreign constraints are incorrect
CREATE TABLE TCustomers
(
intCustomerID INTEGER NOT NULL
,strFirstName VARCHAR(30) NOT NULL
,strLastName VARCHAR(30) NOT NULL
,strAddress VARCHAR(30) NOT NULL
,strCity VARCHAR(30) NOT NULL
,intStateID INTEGER NOT NULL
,strZipcode VARCHAR(30) NOT NULL
,CONSTRAINT TCustomers_PK PRIMARY KEY( intCustomerID )
)
CREATE TABLE TStates
(
intStateID INTEGER NOT NULL
,strState VARCHAR(30) NOT NULL
,CONSTRAINT TStates_PK PRIMARY KEY( intStateID )
)
CREATE TABLE TJobStatus
(
intJobStatusID INTEGER NOT NULL
,strJobStatus VARCHAR(30) NOT NULL
,CONSTRAINT TJobStatus_PK PRIMARY KEY( intJobStatusID )
)
CREATE TABLE TJobs
(
intJobID INTEGER NOT NULL
,intCustomerID INTEGER NOT NULL
,intJobStatusID INTEGER NOT NULL
,strJobDescription VARCHAR(2000) NOT NULL
,dteStartDate DATE NOT NULL
,dteEndDate DATE NOT NULL
,CONSTRAINT TJobs_PK PRIMARY KEY( intJobID )
)
CREATE TABLE TWorkers
(
intWorkerID INTEGER NOT NULL
,strFirstName VARCHAR(30) NOT NULL
,strLastName VARCHAR(30) NOT NULL
,dteDateOfHire DATE NOT NULL
,monHourlyRate MONEY NOT NULL
,CONSTRAINT TWorkers_prk PRIMARY KEY( intWorkerID )
)
CREATE TABLE TJobWorkers
(
intJobID INTEGER NOT NULL
,intWorkerID INTEGER NOT NULL
,intTotalHours INTEGER NOT NULL
,CONSTRAINT TJobWorkers_PK PRIMARY KEY( intJobID,intWorkerID )
)
CREATE TABLE TSkills
(
intSkillId INTEGER NOT NULL
,strSkillDescription VARCHAR(30) NOT NULL
,CONSTRAINT TSkills_PK PRIMARY KEY( intSkillId )
)
CREATE TABLE TWorkerSkills
(
intWorkerID INTEGER NOT NULL
,intSkillId INTEGER NOT NULL
,CONSTRAINT TWorkerSkills_PK PRIMARY KEY( intWorkerID,intSkillID )
)
CREATE TABLE TMaterials
(
intMaterialID INTEGER NOT NULL
,strMaterialName VARCHAR(30) NOT NULL
,monMaterialCost MONEY NOT NULL
,CONSTRAINT TMaterials_PK PRIMARY KEY( intMaterialID)
)
CREATE TABLE TJobMaterials
(
intMaterialID INTEGER NOT NULL
, intJobID INTEGER NOT NULL
,intQuantity INTEGER NOT NULL
,CONSTRAINT TJobMaterials_PK PRIMARY KEY( intJobID, intMaterialID)
)
ALTER TABLE TWorkerSkills ADD CONSTRAINT TWorkerSkills_TSkills_FK
FOREIGN KEY ( intSkillId ) REFERENCES TSkills ( intSkillId )
ALTER TABLE TWorkerSkills ADD CONSTRAINT TWorkerSkills_TWorkers_FK
FOREIGN KEY ( intWorkerID) REFERENCES TWorkers ( intWorkerID )
ALTER TABLE TJobs ADD CONSTRAINT TJobs_TJobStatus_FK
FOREIGN KEY ( intJobStatusID ) REFERENCES TJobStatus ( intJobStatusID )
ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TMaterials_FK
FOREIGN KEY ( intMaterialID) REFERENCES TMaterials ( intMaterialID)
ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TJobs_FK
FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )
ALTER TABLE TJobs ADD CONSTRAINT TJobs_TCustomers_FK
FOREIGN KEY ( intCustomerID ) REFERENCES TCustomers ( intCustomerID )
ALTER TABLE TJobWorkers ADD CONSTRAINT TJobWorkers_TJobs_FK
FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID)
Explanation / Answer
The query as given, if not use the below the statements with output is given. Foreign key also works perfectly.
CREATE TABLE TCustomers(intCustomerID INTEGER NOT NULL, strFirstName VARCHAR(30) NOT NULL, strLastName VARCHAR(30) NOT NULL, strAddress VARCHAR(30) NOT NULL, strCity VARCHAR(30) NOT NULL, intStateID INTEGER NOT NULL, strZipcode VARCHAR(30) NOT NULL, CONSTRAINT TCustomers_PK PRIMARY KEY(intCustomerID))
You have made changes to the database.
CREATE TABLE TStates(intStateID INTEGER NOT NULL, strState VARCHAR(30) NOT NULL, CONSTRAINT TStates_PK PRIMARY KEY(intStateID))
You have made changes to the database.
CREATE TABLE TJobStatus(intJobStatusID INTEGER NOT NULL, strJobStatus VARCHAR(30) NOT NULL, CONSTRAINT TJobStatus_PK PRIMARY KEY(intJobStatusID))
You have made changes to the database.
CREATE TABLE TWorkers(intWorkerID INTEGER NOT NULL, strFirstName VARCHAR(30) NOT NULL, strLastName VARCHAR(30) NOT NULL, dteDateOfHire DATE NOT NULL, monHourlyRate MONEY NOT NULL, CONSTRAINT TWorkers_prk PRIMARY KEY(intWorkerID))
You have made changes to the database.
CREATE TABLE TJobWorkers(intJobID INTEGER NOT NULL, intWorkerID INTEGER NOT NULL, intTotalHours INTEGER NOT NULL, CONSTRAINT TJobWorkers_PK PRIMARY KEY(intJobID,intWorkerID))
You have made changes to the database.
CREATE TABLE TSkills(intSkillId INTEGER NOT NULL, strSkillDescription VARCHAR(30) NOT NULL,CONSTRAINT TSkills_PK PRIMARY KEY(intSkillId))
You have made changes to the database.
CREATE TABLE TMaterials(intMaterialID INTEGER NOT NULL, strMaterialName VARCHAR(30) NOT NULL, monMaterialCost MONEY NOT NULL, CONSTRAINT TMaterials_PK PRIMARY KEY(intMaterialID))
You have made changes to the database.
CREATE TABLE TWorkerSkills(intWorkerID INTEGER NOT NULL, intSkillId INTEGER NOT NULL, CONSTRAINT TWorkerSkills_PK PRIMARY KEY(intWorkerID, intSkillID) CONSTRAINT TWorkerSkills_TSkills_FK FOREIGN KEY (intSkillId) REFERENCES TSkills(intSkillId), CONSTRAINT TWorkerSkills_TWorkers_FK FOREIGN KEY (intWorkerID) REFERENCES TWorkers (intWorkerID))
You have made changes to the database.
CREATE TABLE TJobs(intJobID INTEGER NOT NULL, intCustomerID INTEGER NOT NULL, intJobStatusID INTEGER NOT NULL, strJobDescription VARCHAR(2000) NOT NULL, dteStartDate DATE NOT NULL, dteEndDate DATE NOT NULL, CONSTRAINT TJobs_PK PRIMARY KEY(intJobID), CONSTRAINT TJobs_TJobStatus_FK FOREIGN KEY (intJobStatusID) REFERENCES TJobStatus (intJobStatusID), CONSTRAINT TJobs_TCustomers_FK FOREIGN KEY (intCustomerID) REFERENCES TCustomers(intCustomerID))
You have made changes to the database.
CREATE TABLE TJobMaterials(intMaterialID INTEGER NOT NULL, intJobID INTEGER NOT NULL, intQuantity INTEGER NOT NULL, CONSTRAINT TJobMaterials_PK PRIMARY KEY(intJobID, intMaterialID), CONSTRAINT TJobMaterials_TMaterials_FK FOREIGN KEY (intMaterialID) REFERENCES TMaterials (intMaterialID), CONSTRAINT TJobMaterials_TJobs_FK FOREIGN KEY (intJobID) REFERENCES TJobs (intJobID))
You have made changes to the database.
CREATE TABLE TJobWorkers(intJobID INTEGER NOT NULL, intWorkerID INTEGER NOT NULL, intTotalHours INTEGER NOT NULL, CONSTRAINT TJobWorkers_PK PRIMARY KEY(intJobID, intWorkerID), CONSTRAINT TJobWorkers_TJobs_FK FOREIGN KEY (intJobID) REFERENCES TJobs (intJobID))
You have made changes to the database.