Question

student takes ID course_id sec_id semester year name dept_name tot_cred grade section advisor course id sec_id course course_id title dept_name credits semester department dept_name building budget Slid i id year building room_no time_slot_id time_slot time slot_id day start_time end_time instructor classroom building room_no capacity prereq course_id prereg_id ID name dept_name salary teaches course_id sec_id semester year

Assume that the takes relation has not yet been defined. Give an SQL statement that adds the takes relation to the database.

Assume that:

•Id is an integer

•course_id is a string of up to10 characters.

•sec_id should be a non-negative integer.

•the semester is a one-character field, equal to either ‘F’ or ‘S’, and the

•year is an integer.

•grade can be null, but if not, it should be a letter in the string ‘ABCDF’.

Your statement must ensure that no attributes, other than the grade, are allowed to be null in this relation, identify a suitable primary key, and indicate appropriate foreign key(s). Include not null constraints only on attributes where the domain is not already constrained to exclude nulls.

.

Answer 1

The Table can be created in two ways

• Using Constraint Clause
• Without Constraint Clause

Without Constraint Clause

  

CREATE TABLE Takes ( Id int,

                                      Course_id Varchar2(10),

                                        Sec_id int CHECK(Sec_id>=0),

                                        Semester varchar(1) CHECK (Semester IN(‘F’,’S’)),

                                         Year int,

                                         Grade varchar2(5) NOT NULL DEFAULT ‘ABCDF’,

                              PRIMARY KEY    (Id,   Course_id, Sec_id, Semester, Year),

                              FOREIGN KEY(Id) REFERENCES Student(Id),

                              FOREIGN KEY ( Course_id, Sec_id, Semester, Year) REFERENCES (Section))

This SQL statement will create table with all the constraints but there is no CONSTRAINT clause.

With Constraint Clause

CREATE TABLE Takes ( Id int,

                                      Course_id Varchar2(10),

                                        Sec_id int,

                                        Semester varchar(1),

                                         Year int,

                                         Grade varchar2(5) NOT NULL DEFAULT ‘ABCDF’,

                              CONSTRAINT secid CHECK(Sec_id>=0),

                              CONSTRAINT Sem_Val CHECK (Semester IN(‘F’,’S’)),

                              CONSTRAINT Takes_pk PRIMARY KEY    (Id,   Course_id, Sec_id, Semester, Year),

                              CONSTRAINT Takes_fk1 FOREIGN KEY(Id) REFERENCES Student(Id),

                              CONSTRAINT Takes_fk2

FOREIGN KEY ( Course_id, Sec_id, Semester, Year) REFERENCES (Section))

This has the CONSTRAINT Clause.

Constraint Clause is used to define the integrity constraint. If we name the constraint we will be able to alter the constraint in a easy way using alter table.

Thank You!!!