Scribd
Upload
7 views4 pages

SQL Database Schema for Books and Authors

The document outlines the SQL schema for a database containing tables for authors, books, chapters, and their relationships. It includes the creation of tables with primary and foreign key constraints, as well as sample data insertion and updates for royalty rates. Additionally, it demonstrates the deletion of records from all tables.

Uploaded by

Trần Minh Hiếu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views4 pages

SQL Database Schema for Books and Authors

The document outlines the SQL schema for a database containing tables for authors, books, chapters, and their relationships. It includes the creation of tables with primary and foreign key constraints, as well as sample data insertion and updates for royalty rates. Additionally, it demonstrates the deletion of records from all tables.

Uploaded by

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

/* (1) */

CREATE TABLE AUTHOR (

first_name VARCHAR(40) NOT NULL,

last_name VARCHAR(40) NOT NULL,

date_of_birth DATE NOT NULL,

address VARCHAR(200),

CONSTRAINT author_pkey PRIMARY KEY (first_name, last_name, date_of_birth)

);

CREATE TABLE BOOK (

ISBN VARCHAR(17) NOT NULL,

title VARCHAR(200) NOT NULL,

note TEXT,

type_of_book VARCHAR(30) NOT NULL,

short_summary TEXT,

main_hero VARCHAR(100),

CONSTRAINT book_pkey PRIMARY KEY (ISBN)

);

CREATE TABLE CHAPTER (

ISBN VARCHAR(17) NOT NULL,

chapter_number INT NOT NULL,

title VARCHAR(200) NOT NULL,

motto VARCHAR(200),

CONSTRAINT chapter_pkey PRIMARY KEY (ISBN, chapter_number),

CONSTRAINT chapter_book_fkey FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN));


CREATE TABLE WRITES (

first_name VARCHAR(40) NOT NULL,

last_name VARCHAR(40) NOT NULL,

date_of_birth DATE NOT NULL,

ISBN VARCHAR(17) NOT NULL,

start_date DATE NOT NULL,

CONSTRAINT writes_pkey PRIMARY KEY (first_name, last_name, date_of_birth, ISBN),

CONSTRAINT writes_author_fkey FOREIGN KEY (first_name, last_name, date_of_birth)

REFERENCES AUTHOR(first_name, last_name, date_of_birth),

CONSTRAINT writes_book_fkey FOREIGN KEY (ISBN) REFERENCES BOOK(ISBN)

);

/* (2) */

INSERT INTO AUTHOR VALUES

('Hieu','Tran','2000-05-12','Sydney NSW'),

('Nghia','Tran','2005-09-03','Melbourne VIC');

INSERT INTO BOOK VALUES

('9780000000001','Starlight','Debut novel','SCIENCE FICTION','A colony-ship saga','Nova'),

('9780000000002','Heartsong','Bestseller','ROMANCE','A seaside love story','Lily');

INSERT INTO CHAPTER VALUES

('9780000000001',1,'Awakening',NULL),

('9780000000001',2,'Departure','To the stars'),

('9780000000002',1,'Chance Meeting',NULL),

('9780000000002',2,'Storm','Follow your heart');


INSERT INTO WRITES VALUES

('Jane','Doe','1980-05-12','9780000000001','2020-01-01'),

('John','Smith','1975-09-03','9780000000002','2019-06-15');

/* (3) */

ALTER TABLE BOOK

ADD CONSTRAINT book_check_type

CHECK (type_of_book IN ('ROMANCE','SCIENCE FICTION'));

/* (4) */

ALTER TABLE WRITES

ADD COLUMN royalty_rate DECIMAL(4,2) NOT NULL DEFAULT 0.10,

ADD CONSTRAINT writes_check_royalty

CHECK (royalty_rate BETWEEN 0.05 AND 0.25);

UPDATE WRITES SET royalty_rate = 0.15 WHERE ISBN='9780000000001';

UPDATE WRITES SET royalty_rate = 0.12 WHERE ISBN='9780000000002';

/* (5) */

DELETE FROM WRITES;

DELETE FROM CHAPTER;

DELETE FROM BOOK;


DELETE FROM AUTHOR;

You might also like