Question

Problem 1. Please create the following tables for UMBC bookstore’s textbook management system with appropriate primary keys & foreign keys.

Please include drop table statements with cascade constraints before your create table statements so it is easier to grade your create table statements.

E.g., suppose you will create table tab1, tab2, including the following before creating them:

Drop table tab1 cascade constraints;

Drop table tab2 cascade constraints;

Assumptions:

1. Each teacher can teach one or more scheduled course sections. Each scheduled course section is taught by one teacher.
2. Each course is scheduled in different year, semester and may have multiple sections.
3. Each course belongs to a program. Each program has multiple courses.
4. Each scheduled course section has one or more textbooks.
5. Each text book has one or more authors, each author can write one or more textbooks.

The list of tables are:

Program table:

pid, --- program id

pname, --- program name

ptype --- program type (1: undergraduate, 2: graduate)

Teacher table:

tid, --- teacher id

tname --- teacher name

Author table:

aid, --- author id

aname --- author name

Textbook table:

tbid, --- text book id

title, ---- title of book

publisher, --- publisher name

edition, --- edition, e.g., 3 means 3rd edition

quantity, --- number of copies in store

price --- price of the book

Course table:

cid, --- course id

cname, --- course name

pid --- program id

Schedule table: (scheduled course sections)

sid, --- schedule id, unique for each scheduled class section

cid, --- class id

snumber, --- section number, e.g., 2 means section 2 of the class

semester, --- spring, fall, summer, winter,

year, --- year of the class

tid, --- teach id who teaches this section

num_registered --- number of registered students for this class section

Textbook_author table:

aid, --- author id

tbid --- text book id

Textbook_schedule table:

sid, --- schedule id

tbid --- text book id

Problem 2. Insert at least three rows of data to each table created in Problem 1. Make sure you keep the primary key and foreign key constraints.

Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. Note that you can only use conditions specified in the task description and cannot manually look up data and add conditions.

Task 1: return title of textbooks with price over $100.

Task 2: return number of courses sections scheduled for each year and semester. Please return year, semester, and number of courses.

Task 3: Return names of all courses in Information Systems undergraduate program.

Task 4: return titles and prices of textbooks for IS 420 section 1 in spring 2019.

Task 5: return names of courses offered in 2019 spring and have at least 2 sections.

Task 6: for each text book, return the title of the text book and total number of students registered for courses using that text book in spring 2019.

Hint: use join + group by

Answer 1

According to HomeworkLib policy we answer the first one.

Answer 1)

For unique and references primary key and foreign keys are used, for multiple values in a field check constraint are used.

Program table:

pid, --- program id

pname, --- program name

ptype --- program type (1: undergraduate, 2: graduate)

create table program (pid varchar(5) primary key,pname varchar(50),ptype int,constraint chk1 check (ptype in (1,2)));

Teacher table:

tid, --- teacher id

tname --- teacher name

create table teacher(tid varchar(5) primary key,tname varchar(50));

Author table:

aid, --- author id

aname --- author name

create table author(aid varchar(5) primary key,aname varchar(50));

Textbook table:

tbid, --- text book id

title, ---- title of book

publisher, --- publisher name

edition, --- edition, e.g., 3 means 3rd edition

quantity, --- number of copies in store

price --- price of the book

create table textbook(tbid varchar(5) primary key,title varchar(50), publisher varchar(50),edition int,quantity int,price decimal(10,2));

Course table:

cid, --- course id

cname, --- course name

pid --- program id

create table course(cid varchar(50) primary key, cname varchar(50),pid varchar(50), constraint fk1 foreign key (pid) references progam(pid));

Schedule table: (scheduled course sections)

sid, --- schedule id, unique for each scheduled class section

cid, --- class id

snumber, --- section number, e.g., 2 means section 2 of the class

semester, --- spring, fall, summer, winter,

year, --- year of the class

tid, --- teach id who teaches this section

num_registered --- number of registered students for this class section

create table schedule (sid varchar(50) primary key,cid varchar(5),snumber int,semester varchar(10),year int,tid varchar(5),num_registered int,constraint fk2 foreign key (cid) references course(cid),constraint fk3 foreign key(tid) references teacher(tid), constraint chk2 check (semester in ('spring','fall','summer','winter')));

Textbook_author table:

aid, --- author id

tbid --- text book id

create table Textbook_author(aid varchar(5),tbid varchar(5), constraint fk4 foreign key(aid) references author(aid),constraint fk5 foreign key(tbid) references textbook(tbid));

Textbook_schedule table:

sid, --- schedule id

tbid --- text book id

create table Textbook_schedule(sid varchar(5),tbid varchar(5),constraint fk6 foreign key(sid) references Schedule(sid),constraint fk7foreign key(tbid) references textbook(tbid));