/* ===== create-bks.sql create the tables for a simple bookstore database originally from: Ann Burroughs modified by: Sharon Tuttle last modified: 2026-01-23 ===== */ /* ===== publisher table contains information about publishers of titles potentially carried by this bookstore * pub_min_for_disc is this publisher's minimum order total to receive a discount, given in pub_disc_pct * if a publisher does not make discounts available, pub_min_for_disc will be NULL, and pub_disc_pct will be 0 ===== */ drop table publisher cascade constraints; create table publisher (pub_id number(3) generated by default as identity, pub_name varchar2(30) not null, pub_city varchar2(15) not null, pub_state char(2) not null, pub_min_for_disc number(7,2), pub_disc_pct number(3,2) not null check(pub_disc_pct between 0 and .99), constraint publisher_pk primary key (pub_id) ); /* ===== title table contains information about the different titles carried by this bookstore (one title row represents all of the copies of that title) * ttl_auth_lname is the last name of what is considered to be the primary/first author of this title * ttl_order_point is the number of copies at which an order is considered to be needed for restocking this title * ttl_auto_order_qty is the quantity to normally/automatically order when the quantity on hand reaches the order point ===== */ drop table title cascade constraints; create table title (ttl_isbn char(13), ttl_name varchar2(45) not null, ttl_auth_lname varchar2(30), ttl_cost number(7,2) not null, ttl_price number(7,2) not null, ttl_qty number(3) not null, ttl_order_point number(3) not null, ttl_auto_order_qty number(3) not null, pub_id number(3) not null, constraint title_pk primary key (ttl_isbn), constraint title_fk_pub foreign key (pub_id) references publisher ); /* ===== order_stock represents an order of titles from a publisher; the details of each title being ordered are given in a related row in order_line_item * fun fact: Oracle DBMS will NOT let you have a table whose name is order...! ===== */ drop table order_stock cascade constraints; create table order_stock (ord_num number(6) generated by default as identity, pub_id number(3) not null, ord_date_placed date not null, ord_date_complete date, constraint order_stock_pk primary key (ord_num), constraint order_stock_fk_pub foreign key (pub_id) references publisher ); /* ===== order_line_item has the order details for one of the titles in an order * this bookstore likes to number these (line item 1, line item 2, etc.) for the titles within an order; that is the meaning of attribute ord_line_num * ord_line_qty is the quantity of this title being ordered in this order ===== */ drop table order_line_item cascade constraints; create table order_line_item (ord_num number(6), ord_line_num number(2), ttl_isbn char(13) not null, ord_line_qty number(3) not null, constraint order_line_item_pk primary key (ord_num, ord_line_num), constraint order_line_item_fk_order foreign key (ord_num) references order_stock, constraint order_line_item_fk_title foreign key (ttl_isbn) references title ); /* ===== order_receipt represents a shipment received of one of the titles in an order * note that an order_receipt instance may only be partial, with more copies of that title still to be received later ===== */ drop table order_receipt cascade constraints; create table order_receipt (ord_receipt_id number(7) generated by default as identity, ord_num number(6) not null, ord_line_num number(5) not null, qty_rcvd number(3) not null, ord_rec_date date not null, constraint order_receipt_pk primary key (ord_receipt_id), constraint order_receipt_fk_ord_line foreign key (ord_num, ord_line_num) references order_line_item ); /* ===== a row is added to order_needed when it is determined that an order is needed for a title * (typically because its ttl_qty has gone below its ttl_order_point, meaning it is time to order more, * BUT could also be because a bookstore manager is preparing for a special event, anticipating demand, etc.) * foreign key ord_num will be null until an order is actually placed ===== */ drop table order_needed cascade constraints; create table order_needed (ord_needed_id number generated by default as identity, ttl_isbn char(13) not null, qty_desired number(3) not null, date_need_noted date not null, ord_num number(6), constraint order_needed_pk primary key (ord_needed_id), constraint order_needed_fk_order foreign key (ord_num) references order_stock, constraint order_needed_fk_title foreign key (ttl_isbn) references title ); /* ===== sale represents a single sale of titles to someone * the details of each title sold in this sale are given in a related row in sale_line_item * this bookstore is particularly noted for not keeping customer information (to protect customer privacy) ===== */ drop table sale cascade constraints; create table sale (sale_num number(8) generated always as identity, sale_date date not null, constraint sale_pk primary key (sale_num) ); /* ===== sale_line_item has the sale details for one of the titles in a sale * this bookstore likes to number these (line item 1, line item 2, etc.) for the titles within a sale; that is the meaning of attribute sale_line_num * sale_line_qty is the quantity of this title being sold in this sale ===== */ drop table sale_line_item cascade constraints; create table sale_line_item (sale_num number(8), sale_line_num number(2), sale_line_qty number(3) not null, ttl_isbn char(13) not null, constraint sale_line_item_pk primary key (sale_num, sale_line_num), constraint sale_line_item_fk_sale foreign key (sale_num) references sale, constraint sale_line_item_fk_title foreign key (ttl_isbn) references title ); commit;