1. Write an SQL statement that adds an advisorid attribute to relation student,

Question

1. Write an SQL statement that adds an advisorid attribute to relation student, and sets it to be a foreign key to facultyid in relation faculty. In case a faculty’s id is changed, the change would be reected on advisorid attribute; in case a student’s advisor left the school, advisorid would be set to NULL.
2. Write an SQL statement that adds a constraint to the student relation to make sure that the gpa attribute cannot be NULL, and that the value of this attribute has to be between 0 and 4. Furthermore, the default value for this attribute should be 3.

Consider the following library database schema and example instance storing book bookid price total_copies 84.66 uction of Algorit Database System Concepts74.99 Stochastic Calulus for Finance 41.02 Stochastic Calculus for Finance II 55.22 course courseid titl structor text DB Organization Advanced DB Organization Math Finance I Math Finance II student faculty enroll studentid courseid studentid name gpa Tom 3.3 John 38 Mary 3.0 Kris 3.6 Alex 3.5 facultyiod James70000 Sarah60000 Jay80000 Rache 70000 Pau85000 book checkout bookid studentid date 2017-08-29 2017-09-02 2017-09-07 Hints: All the attributes that have integer values are of type INT; numbers with decimal point are of type NUMERIC; the attribute date of book checkout relation is of type DATE; others are of type VARCHAR Attributes with black background form the primary key of an relation The attribute instructorid of relation course is a foreign key to relation facuity, and tertbookid is a foreign key to relation book. The attribute studentid of relation enroll is a foreign key to relation student, and courseid is a foreign key to relation course. The attribute bookid of relation book_checkout is a foreign key to relation book, and studentid is a foreign key to relation student.

Explanation / Answer

Question 1:

ALTER TABLE student

ADD advisorid int

CONSTRAINT student_faculty_id

FOREIGN KEY (advisorid) REFERENCES faculty (facultyid)

ON DELETE SET NULL

ON UPDATE CASCADE;

Here We are adding advisorid column to student table and making it a foreign key to facultyid in faculty table using line# 4 and creating a constraint for it in line# 3. And setting up it as on update to Cascade(when facultyid in faculty chages then advisorid will get change) in Line# 6, on delete to null in Line# 5(When faculty record deleted from faaculty table then advisorid will set to null)

Question 2:

ALTER TABLE student

ALTER COLUMN gpa numeric(10,1) NOT NULL;

ALTER TABLE student

ADD CONSTRAINT DF_gpa DEFAULT 3.0 FOR gpa,

CONSTRAINT check_gpa CHECK (gpa >= 0.0 AND gpa <= 4.0);

Here first setting the gpa to not null using alter column and then adding two different constraints to set the default value and to check the gpa value.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---