/* Query 4. Write one DML statement to add data of the following six projects in
ID: 3736370 • Letter: #
Question
/* Query 4.
Write one DML statement to add data of the following six projects into
Project table.
ProjNum Pname Budget
------- ----- --------
p1 UH28K 1890.00
p2 UB555 1500.00
p3 UB5AA 2009.00
p4 UH200 11000.00
p5 UH2XG 9000.00
p6 UB5J6 4400.00
*/
/* Query 5.
When Query 4 is successfully executed, write one DDL statement to add a
foreign
key constraint called 'FK_ProjAss_Proj' in ProjAssignment table. It should
define
Pno as a foreign key which references the ProjNum of Project.
After executing this statement, updating a ProjNum value in Project should
automatically update the Pno to the same in ProjAssignment. However, with
the
constraint enforced, a project cannot be deleted if its ProjNum value is
referenced
by Pno of one or more rows of ProjAssignment.
Hint: p.350~1
*/
E EEE dbo.ProjAssignment Columns o Pno (PK, char(3), not null) StulD (PK, FK, char(3), not null) E EEE dbo.Student Columns StulD (PK, char(3), not null) GPA (decimal(3,2), nul)Explanation / Answer
Please find my answer.
Query 4) Assuming Table project has been already created and ProjNum is declared as the primary key. Multiple rows are added to the table Project using single insert statememt.
INSERT INTO Project (ProjNum, Pname, Budget)
VALUES
('p1','UH28K',1890.00),
('p1','UB555', 1500.00),
('p3', 'UB5AA', 2009.00),
('p4','UH200',11000.00),
('p5','UH2XG',9000.00),
('p6','UB5J6', 4400.00);
Query 5) Alter table command is used to add the foreign key constraint to the existing table ProjAssignment. Pno field of table ProjAssignment is created as a foreign key referencing ProjNum of Project table.
ALTER TABLE ProjAssignment
ADD CONSTRAINT FK_ProjAss_Proj
FOREIGN KEY (Pno) REFERENCES Project (ProjNum);
Please upvote my answer if it helped you!!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.