2- MySQL Database Scripting 2. Write a complete script in mysql to create a new
ID: 3701363 • Letter: 2
Question
2- MySQL Database Scripting
2. Write a complete script in mysql to create a new database (also called 'schema' in MySQL) Bookstore. This
database or schema should contain exactly the same pair of tables, Book and Subject, as given. That is, your
script must contain SQL DDL statements of CREATE DATABASE and CREATE TABLE(s), plus SQL
DML INSERT statements to insert 30 books and 12 subjects into these two tables, respectively. All
constraints, including data types, primary keys, not null, and the foreign key must be included in your script.
3. You should test your script by executing the entire page as a whole to see if it can create Bookstore
database without errors. For this test, use the 3rd icon, the yellow lighting without 'I', to execute the entire
script of statements as one batch. If it runs with no errors, you can click 'Schemas' tab in Navigator and right-click
on the white space area inside Schemas tab and select 'Refresh All' to view your Bookstore in detail
(very similar to how it works in SSMS).
Explanation / Answer
#All the below script should be saved in a sql file and can be run at once.
#Create the schema
create database Bookstore;
#create Subject table having primary key as SubjectCode
CREATE TABLE bookstore.subject (
SubjectCode VARCHAR(3) NOT NULL,
Subject VARCHAR(15) DEFAULT NULL,
PRIMARY KEY (SubjectCode)
)
ENGINE = INNODB;
#Create book table having primary key as ISBN
#Also it has foreign key as Subject_Code referring to SubjectCode column of Subject table
CREATE TABLE bookstore.book (
ISBN VARCHAR(13) NOT NULL,
Title VARCHAR(50) DEFAULT NULL,
Author VARCHAR(30) DEFAULT NULL,
Publisher VARCHAR(30) DEFAULT NULL,
Subject_Code VARCHAR(3) DEFAULT NULL,
Subject_Location VARCHAR(7) DEFAULT NULL,
Fiction VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (ISBN),
FOREIGN KEY (Subject_Code)
REFERENCES subject(SubjectCode)
ON DELETE CASCADE
)
ENGINE = INNODB;
#insert dummy data (12 rows) into subject table
insert into Bookstore.subject VALUES('S01','OS');
insert into Bookstore.subject VALUES('S02','AI');
insert into Bookstore.subject VALUES('S03','Data Structure');
insert into Bookstore.subject VALUES('S04','RDBMS');
insert into Bookstore.subject VALUES('S05','Automata');
insert into Bookstore.subject VALUES('S06','IS');
insert into Bookstore.subject VALUES('S07','Networking');
insert into Bookstore.subject VALUES('S08','Java');
insert into Bookstore.subject VALUES('S09','C Programming');
insert into Bookstore.subject VALUES('S10','Microprocessor');
insert into Bookstore.subject VALUES('S11','Compiler Design');
insert into Bookstore.subject VALUES('S12','Discrete Mahs');
#insert dummy data (30 rows) into book table
insert into Bookstore.book VALUES('1001','Title 1','Author 1', 'Publisher 1', 'S01', 'Loc1', 'Fiction 1');
insert into Bookstore.book VALUES('1002','Title 1','Author 2', 'Publisher 2', 'S02', 'Loc1', 'Fiction 8');
insert into Bookstore.book VALUES('1003','Title 1','Author 3', 'Publisher 1', 'S01', 'Loc3', 'Fiction 3');
insert into Bookstore.book VALUES('1004','Title 1','Author 4', 'Publisher 2', 'S03', 'Loc1', 'Fiction 4');
insert into Bookstore.book VALUES('1005','Title 1','Author 5', 'Publisher 3', 'S01', 'Loc2', 'Fiction 5');
insert into Bookstore.book VALUES('1006','Title 1','Author 6', 'Publisher 3', 'S05', 'Loc1', 'Fiction 6');
insert into Bookstore.book VALUES('1007','Title 1','Author 7', 'Publisher 3', 'S01', 'Loc3', 'Fiction 7');
insert into Bookstore.book VALUES('1008','Title 1','Author 8', 'Publisher 1', 'S06', 'Loc1', 'Fiction 8');
insert into Bookstore.book VALUES('1009','Title 1','Author 9', 'Publisher 1', 'S07', 'Loc4', 'Fiction 9');
insert into Bookstore.book VALUES('1010','Title 1','Author 10', 'Publisher 4', 'S08', 'Loc3', 'Fiction 10');
insert into Bookstore.book VALUES('1011','Title 1','Author 11', 'Publisher 4', 'S09', 'Loc1', 'Fiction 1');
insert into Bookstore.book VALUES('1012','Title 1','Author 12', 'Publisher 5', 'S01', 'Loc1', 'Fiction 2');
insert into Bookstore.book VALUES('1013','Title 1','Author 13', 'Publisher 6', 'S01', 'Loc5', 'Fiction 3');
insert into Bookstore.book VALUES('1014','Title 1','Author 14', 'Publisher 3', 'S01', 'Loc6', 'Fiction 4');
insert into Bookstore.book VALUES('1015','Title 1','Author 15', 'Publisher 1', 'S11', 'Loc1', 'Fiction 5');
insert into Bookstore.book VALUES('1016','Title 1','Author 16', 'Publisher 1', 'S10', 'Loc2', 'Fiction 6');
insert into Bookstore.book VALUES('1017','Title 1','Author 17', 'Publisher 1', 'S04', 'Loc7', 'Fiction 7');
insert into Bookstore.book VALUES('1018','Title 1','Author 18', 'Publisher 2', 'S12', 'Loc1', 'Fiction 9');
insert into Bookstore.book VALUES('1019','Title 1','Author 19', 'Publisher 7', 'S06', 'Loc1', 'Fiction 8');
insert into Bookstore.book VALUES('1020','Title 1','Author 20', 'Publisher 6', 'S01', 'Loc2', 'Fiction 10');
insert into Bookstore.book VALUES('1021','Title 1','Author 21', 'Publisher 5', 'S12', 'Loc2', 'Fiction 1');
insert into Bookstore.book VALUES('1022','Title 1','Author 22', 'Publisher 4', 'S01', 'Loc7', 'Fiction 2');
insert into Bookstore.book VALUES('1023','Title 1','Author 23', 'Publisher 4', 'S01', 'Loc4', 'Fiction 3');
insert into Bookstore.book VALUES('1024','Title 1','Author 24', 'Publisher 4', 'S03', 'Loc1', 'Fiction 4');
insert into Bookstore.book VALUES('1025','Title 1','Author 25', 'Publisher 6', 'S01', 'Loc5', 'Fiction 5');
insert into Bookstore.book VALUES('1026','Title 1','Author 26', 'Publisher 2', 'S04', 'Loc1', 'Fiction 6');
insert into Bookstore.book VALUES('1027','Title 1','Author 27', 'Publisher 1', 'S01', 'Loc6', 'Fiction 7');
insert into Bookstore.book VALUES('1028','Title 1','Author 28', 'Publisher 6', 'S03', 'Loc6', 'Fiction 8');
insert into Bookstore.book VALUES('1029','Title 1','Author 29', 'Publisher 3', 'S01', 'Loc1', 'Fiction 9');
insert into Bookstore.book VALUES('1030','Title 1','Author 30', 'Publisher 1', 'S09', 'Loc4', 'Fiction 10');
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.