Question One Write SQL DDL corresponding to the following schema. Make any reaso
ID: 3716191 • Letter: Q
Question
Question One
Write SQL DDL corresponding to the following schema. Make any reasonable assumptions about data types and be sure to declare primary and foreign keys.
Author (Author_ID, FIRST_NAME, LAST_NAME)
Book_authors_books (ISBN, Author_ID)
Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages)
Question Two
Based on the four tables that are created in the first question, you should store (insert) the following data using SQL statements:
Author_ID
FIRST_NAME
LAST_NAME
111
Ali
Sami
222
Mohammed
Khalid
333
Saad
Ahmed
ISBN
Author_ID
15111
111
16222
222
17222
222
18111
111
15111
333
ISBN
Title
Price
publisher
Pub_date
Edition
Pages
15111
Math 1
37.99
New Math
January 20, 2016
1
256
16222
The Art of Strategy
26.94
AWP
June 21, 2014
3
250
17222
Calculus
25
New Math
January 11, 2017
1
753
18111
Game Theory
40.99
MIT
January 01, 2016
2
333
15111
Graph Theory
100
John Wiley
January 01, 2018
1
320
Author_ID
FIRST_NAME
LAST_NAME
111
Ali
Sami
222
Mohammed
Khalid
333
Saad
Ahmed
Explanation / Answer
create table Author (Author_ID integer primairy key, FIRST_NAME string, LAST_NAME string);
insert into Author (Author_ID, FIRST_NAME, LAST_NAME) values (111,'Ali','Sami');
insert into Author (Author_ID, FIRST_NAME, LAST_NAME) values (222,'Mohammad','Khalid');
insert into Author (Author_ID, FIRST_NAME, LAST_NAME) values (333,'Saad','Ahmed');
Number of Records: 3
Author_ID
FIRST_NAME
LAST_NAME
111
Ali
Sami
222
Mohammad
Khalid
333
Saad
Ahmed
create table Book_authors_books (ISBN integer primairy key, Author_ID integer references Author(Author_ID));
insert into Book_authors_books (ISBN, Author_ID) values (15111,111);
insert into Book_authors_books (ISBN, Author_ID) values (16222,222);
insert into Book_authors_books (ISBN, Author_ID) values (17222,222);
insert into Book_authors_books (ISBN, Author_ID) values (18111,111);
insert into Book_authors_books (ISBN, Author_ID) values (15111,333);
ISBN
Author_ID
15111
111
16222
222
17222
222
18111
111
15111
333
create table Book_descriptions (ISBN integer references Book_authors_books (ISBN), Title string, Price float, Publisher string, Pub_date date, Edition integer, Pages integer);
insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values (15111,'Math 1',37.99,'New Math','January 20 2016',1,256);
insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values (16222,'The Art of Strategy',26.94,'New Math','June 21 2014',3,250);
insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values (17222,'Calculus',25,'New Math','January 11 2017',1,753);
insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values (18111,'Game Theory',40.99,'MIT','January 01 2016',2,333);
insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values (15111,'Game Theory',100,'John Wiley','January 01 2018',1,320);
Number of Records: 5
ISBN
Title
Price
Publisher
Pub_date
Edition
Pages
15111
Math 1
37.99
New Math
January 20 2016
1
256
16222
The Art of Strategy
26.94
AWP
June 21 2014
3
250
17222
Calculus
25
New Math
January 11 2017
1
753
18111
Game Theory
40.99
MIT
January 01 2016
2
333
15111
Game Theory
100
John Wiley
January 01 2018
1
320
Author_ID
FIRST_NAME
LAST_NAME
111
Ali
Sami
222
Mohammad
Khalid
333
Saad
Ahmed
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.