Please submit your answer in word document with screen shot showing rows in the created tables. Other formats such as PDF or JPEG are not accepted. Please include drop table with cascade constraints commands before create table and insert.

Problem 1. Please create the following tables for a bookstores textbook management system with appropriate primary keys & foreign keys. [50 points]

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 is:

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. Make sure you satisfy the primary key and foreign key constraints. [50 points]