Answer to Question #311468 in Databases | SQL | Oracle | MS Access for Denla

Question

Write SQL statements for the following.

● Provide screenshots of your answers. Copy and paste your screenshots

below each item

Create a database named Library.

2. Create tables with appropriate integrity constraints:

● Authors

o AuthorID – Primary Key, 5 characters

o AuthorName –30 characters

o Nationality – 20 characters

● Books

o BookID – Primary Key, 5 characters

o AuthorID – Foreign Key related to Authors

o BookTitle – 50 characters

o Genre – 30 characters

o YearPublished – year

Answer 1

CREATE DATABASE Library;

USE Library;

CREATE TABLE Authors (
	AuthorID varchar(5) PRIMARY KEY,
	AuthorName varchar(30) NOT NULL,
	Nationality varchar(20) NOT NULL
) ENGINE=InnoDB;


CREATE TABLE Books (
	BookID varchar(5) PRIMARY KEY,
    AuthorID varchar(5),
    BookTitle varchar(50) NOT NULL,
	Genre varchar(30) NOT NULL,
	YearPublished YEAR,
	KEY `author_ind` (`AuthorID`),
	CONSTRAINT `book_ibfk_1` FOREIGN KEY (`AuthorID`)
	REFERENCES `Authors` (`AuthorID`) ON DELETE CASCADE
) ENGINE=InnoDB;


drop table Books;
drop table Authors;


INSERT INTO Authors(AuthorID, AuthorName, Nationality)
VALUES('a0001', 'William Shakespeare','British'),
	('a0002','Joanne Rowling','British'),
    ('a0003', 'Osamu Tezuka', 'Japanese');
    
INSERT INTO Books(BookID, AuthorID, BookTitle, Genre, YearPublished)
VALUES('b0001', 'a0002','Harry Potter', 'children`s fantasy', 1999),
	('b0002','a0001','Romeo and Julie', 'tragic', 1997),
	('b0003','a0001','Hamlet', 'tragic', 1909);
    
SELECT * FROM Books;