Scribd
Upload
3 views2 pages

Code

The document outlines the creation of a database schema for a book management system, including tables for BOOK, AUTHOR, CUSTOMERS, and ORDER_DETAILS. It specifies primary and foreign keys, as well as sample data insertion and a query to retrieve recent orders. Additionally, it introduces a BOOK_AUTHOR table to associate authors with books and provides a query to list authors of available books.

Uploaded by

bc240209697juf
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views2 pages

Code

The document outlines the creation of a database schema for a book management system, including tables for BOOK, AUTHOR, CUSTOMERS, and ORDER_DETAILS. It specifies primary and foreign keys, as well as sample data insertion and a query to retrieve recent orders. Additionally, it introduces a BOOK_AUTHOR table to associate authors with books and provides a query to list authors of available books.

Uploaded by

bc240209697juf
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE BOOK (

bookID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,


title VARCHAR(20) NOT NULL,
publisher VARCHAR(120),
publicationYear NUMBER(4),
genre VARCHAR2(20),
price NUMBER(10,2)
stock_q NUMBER
);

CREATE TABLE AUTHOR (


authorID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
full_name VARCHAR2(100) NOT NULL,
email VARCHAR2(120),
DateOfBirth DATE,
Bio CLOB
);

CREATE TABLE CUSTOMERS (


customerID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
full_name VARCHAR2(100) NOT NULL,
email VARCHAR2(120),
phone VARCHAR2(30),
billing_address VARCHAR2(50),
shipping_address VARCHAR2(50)
);

CREATE TABLE ORDER_DETAILS (


orderID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customerID NUMBER NOT NULL,
bookID NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
quantity NUMBER(3),
total_amount NUMBER(10,2),

CONSTRAINT fk_customer FOREIGN KEY (customerID) REFERENCES


CUSTOMER(customerID),
CONSTRAINT fk_book FOREIGN JEY (bookID) REFERENCES BOOK(bookID)
);

Q2 part 2

Primary kes: bookID, authorID, customerID, orderID


Foreign keys:ORDER_DETAILS.customerID -> CUSTOMER.customerID
ORDER_DETAILS.bookID -> BOOK.bookID
Q2 part 3

INSERT INTO BOOK (title, publisher, publicationYear, genre, price, stock_q) VALUES
('Jeweler palace','bookWorld press', 2020, 'suspence, adventure', 25.6 , 15 );
INSERT INTO AUTHOR (full_name, email, DateOfBirth, Bio) VALUES ('ayash jakie',
'[email protected]', TO_DATE('1998-03-12', 'YYYY-MM-DD'), 'award winning novelist');
INSERT INTO CUSTOMERS (full_name, email, phone, billing_address, shipping_address)
VALUES ('alice johnson', '[email protected]', '123-456-789','ABC house', 'ABC
house');
INSERT INTO ORDER_DETAILS (customerID, bookID, quantity, total_amount) VALUES
(1,1,2,51.98);
Q2 4
SELECT
B.title, O.order_date, C.full_name, B.stock_q, O.total_amount
FROM
ORDER_DETAILS O
JOIN BOOK B ON O.bookID = B.bookID
JOIN CUSTOMERS C ON O.customerID = C.customerID
WHERE
O.order_date >= SYSDATE - 30;

Q2 5

CREATE TABLE BOOK_AUTHOR


(
bookID NUMBER NOT NULL,
authorID NUMBER NOT NULL,
role VARCHAR(50),
CONSTRAINT pk_ba_author PRIMARY KEY (bookID, authorID),
CONSTRAINT fk_ba_book FOREIGN KEY (bookID) REFERENCES BOOK(bookID),
CONSTRAINT fk_ba_author FOREIGN KEY (authorID) REFERENCES AUTHOR(authorID)
);

INSERT INTO BOOK_AUTHOR (bookID, authorID, role) VALUES (1,1, 'primary author');

SELECT DISTINCT
A.full_name
FROM AUTHOR A
JOIN BOOK_AUTHOR BA ON A.authorID = BA.authorID
JOIN BOOK B BA.bookID = B.bookID
WHERE
B.stock_q > 0;

You might also like