DAY 22 MySQL: Constraints, Normalization (9th JULY 24)

Which query adds a primary key constraint to the 'id' column

in the 'students' table?

• ALTER TABLE students ADD CONSTRAINT PRIMARY

  KEY (id);

• ALTER TABLE students ADD PRIMARY KEY (id);

• UPDATE TABLE students SET PRIMARY KEY (id);

• MODIFY TABLE students SET PRIMARY KEY (id);

Which query creates a table 'enrollments' with a foreign key

constraint referencing 'students' ?

• CREATE TABLE enrollments (student_id INT, course_id

  INT, FOREIGN KEY (student_id) REFERENCES

  students(id));

• CREATE TABLE enrollments (student_id INT, course_id

  INT, SET FOREIGN KEY (student_id) TO students(id));

• CREATE TABLE enrollments (student_id INT, course_id

  INT, ADD FOREIGN KEY (student_id) REFERENCES

  students(id));

• CREATE TABLE enrollments (student_id INT, course_id

  INT, ADD CONSTRAINT FOREIGN KEY (student_id)

  REFERENCES students(id));

Which query removes a unique constraint from the 'email'

column in the 'students' table?

• ALTER TABLE students DROP UNIQUE (email);

• ALTER TABLE students REMOVE UNIQUE (email);

• ALTER TABLE students DELETE UNIQUE (email);

• ALTER TABLE students DROP CONSTRAINT UNIQUE

  (email);

Which query sets a default value of 'Active' for the 'status'

column in the 'students' table?

• ALTER TABLE students SET DEFAULT 'Active' FOR

  status;

• ALTER TABLE students CHANGE DEFAULT 'Active' FOR

  status;

• ALTER TABLE students MODIFY status SET DEFAULT

  'Active';

• ALTER TABLE students ALTER COLUMN status SET

  DEFAULT 'Active';

Which query ensures that the 'age' column in the 'students'

table cannot have null values?

• ALTER TABLE students SET NOT NULL (age);

• ALTER TABLE students MODIFY age NOT NULL;

• ALTER TABLE students ALTER COLUMN age SET NOT

  NULL;

• ALTER TABLE students UPDATE age SET NOT NULL;

Which normalization form ensures that all non-key attributes

are fully functional dependent on the primary key?

• First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)

• Boyce-Codd Normal Form (BCNF)

Which query removes the foreign key constraint from the

'student_id' column in the 'enrollments' table?

• ALTER TABLE enrollments DROP FOREIGN KEY

  (student_id);

• ALTER TABLE enrollments REMOVE FOREIGN KEY

  (student_id);

• ALTER TABLE enrollments DROP CONSTRAINT

  FOREIGN KEY (student_id);

• ALTER TABLE enrollments DROP CONSTRAINT

  student_id;

Which query creates a table 'courses' with a unique constraint

on the 'course_code' column?

• CREATE TABLE courses (course_id INT, course_name

  VARCHAR(100), course_code VARCHAR(20) UNIQUE);

• CREATE TABLE courses (course_id INT, course_name

  VARCHAR(100), UNIQUE course_code VARCHAR(20));

• CREATE TABLE courses (course_id INT, course_name

  VARCHAR(100), ADD UNIQUE (course_code)

  VARCHAR(20));

• CREATE TABLE courses (course_id INT, course_name

  VARCHAR(100), SET UNIQUE (course_code));

Which normalization form ensures that there are no transitive

dependencies in a table?

• First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)

• Boyce-Codd Normal Form (BCNF)

Which query adds a check constraint to ensure the 'age'

column in the 'students' table is always greater than 18?

• ALTER TABLE students ADD CONSTRAINT age_check

  CHECK (age > 18);

• ALTER TABLE students SET CONSTRAINT CHECK (age

  > 18);

• ALTER TABLE students ADD CHECK (age > 18);

• ALTER TABLE students MODIFY CHECK (age > 18);