3) We have a gender column on the students table. The type on this column is a C

Question

3) We have a gender column on the students table. The type on this column is a CHAR(1), but any single character value could be input into that field right now.

a) Name two different approaches you could use to make sure that only the ISO gender codes (M,F,U,N) are valid values to insert into the gender column. /4

b) Write the DDL you would use to implement both of these solutions (but don’t actually change anything in the database). /10

4) Currently in the sections table, it is possible for the num_enrolled to be higher than the max_enrollment. What could you add to make sure this doesn’t happen? Write the code that would accomplish this. /10

USING POSTGRESQL

d INT first name VARCHAR(100) last namo VARCHARC100) email-address VARCHAR(100) major_code VARCHAR(10) gender CHAR) d INT code VARCHAR(10 name VARCHAR(100) Departments id INT code VARCHAR10) University id INT name VARCHAR100) id INT student id INT section id INT grade NUMERIC status code VARCHARO COurse desc TEXT credit units NUMERIC uriversity id INT id INT id INT section code VARCHARC100 d INT employee number INT first name VARCHAR(100) last name VARCHARC100) seniority_date DATE email address VARCHAR100) lec type VARCHAR(10) term id INT enrolled INT num num waitlisted INT instructor id INT days VARCHAR3) start time TIME end time TIME location-code VARCHAR(10) ia INT code VARCHAR10) name VARCHARI500 start date DATE end date DATE

Explanation / Answer

3(a) We can use two approach to restrict gender column on the students table to accept only the ISO gender codes (M,F,U,N) :

3(b) DDL statement used to implement both of the above approach are:

ALTER TABLE students ADD CONSTRAINT checkGenderValue CHECK (gender in ('M','F','U','N')); (in ORACLE)

The above command will create a check constraint named checkGenderValue which will ensure that the value of gender is one of the 4 : M,F,U or N otherwise the record won't be added and it will raise error as check constraint will be violated.

2. Using INSERT BEFORE TRIGGER  to limit the value of gender to M,F,U or N :

create trigger trigger_gender

before insert

on students

for each row

begin

    IF (:new.gender != 'M' or :new.gender != 'F' or :new.gender != 'U' or :new.gender != 'N') THEN

        raise_application_error( -20001, 'No insertion with gender values other than M,F,U or N allowed');

    END IF;

end;

(Code in Oracle)

The above command will create a trigger named trigger_gender which will be raised whenever the value of gender is not M,F,U or N and thus insertion won't happen.

4. Currently in sections table, it is possible for num_enrolled to be higher than max_enrollment. In order to ensure this doesn't happens, we can create a CHECK constraint which will check if num_enrolled is less than max_enrollment and whenever, it is higher it will throw an error of constraint violation and will not allow the operation to occur.

ALTER TABLE students ADD CONSTRAINT CHECK checknumEnrolled CHECK (num_enrolled <= max_enrollment); (in ORACLE)

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.