hw2 table:

create.sql

-- represents college departments

CREATE TABLE solution.department (

id bigserial primary key ,

name varchar(50) not null,

email varchar(50),

building varchar(50) not null,

room varchar(20) not null

);

-- represents courses offered by college departments

CREATE TABLE solution.course (

id bigserial primary key,

name varchar(255) not null,

dept bigint references solution.department not null,

units int not null

);

-- sections of these courses

CREATE TABLE solution.section (

id bigserial primary key,

course bigint references solution.course not null,

term int not null,

yr int not null,

secNum char(2) not null default '01' -- char because lab sections often use letters

);

-----------------------------//--------------------------------------------

insert.sql

-- insert departments

insert into solution.department (name, email, building, room) values ('English', null, 'Music', '2579');

insert into solution.department (name, email, building, room) values ('Computer Science', 'cs@csueastbay.edu', 'Student and Faculty Support', '570');

insert into solution.department (name, email, building, room) values ('Chemistry', 'chem@csueastbay.edu', 'Science', 'North 431');

-- insert some courses

insert into solution.course (name, dept, units) values ('College Writing: Stretch I', 1, 3);

insert into solution.course (name, dept, units) values ('College Writing Lab', 1, 1);

insert into solution.course (name, dept, units) values ('Database Architecture', 2, 3);

insert into solution.course (name, dept, units) values ('Computer Graphics', 2, 3);

insert into solution.course (name, dept, units) values ('Intro to College Chemistry', 3, 3);

-- insert some sections

insert into solution.section (course, term, yr, secnum) values (1, 9, 2020, '01');

insert into solution.section (course, term, yr, secnum) values (1, 5, 2021, '01');

insert into solution.section (course, term, yr, secnum) values (1, 5, 2021, '02');

insert into solution.section (course, term, yr, secnum) values (2, 9, 2020, '01');

insert into solution.section (course, term, yr, secnum) values (2, 5, 2021, '01');

insert into solution.section (course, term, yr, secnum) values (2, 5, 2021, '02');

insert into solution.section (course, term, yr, secnum) values (3, 5, 2021, '01');

insert into solution.section (course, term, yr, secnum) values (3, 5, 2021, '02');

insert into solution.section (course, term, yr, secnum) values (3, 5, 2021, '03');

insert into solution.section (course, term, yr, secnum) values (3, 9, 2020, '01');

insert into solution.section (course, term, yr, secnum) values (4, 9, 2020, '01');

insert into solution.section (course, term, yr) values (5, 9, 2020);

QUESTION 14 Suppose the hw2 tables were actually used in an application with real users. If we found that the most common query was to list the sections offered by a given department name during a particular term, what would be an appropriate way to denormalize the data to improve performance? Assume that course and department names are unique a. Create a new relationship table with foreign keys for section and department b. add columns for course name and department name to the section table and remove the name attributes of the department and course table O c. add columns for course name and department name to the section table d. Combine all 3 tables into one table