Fundamentals DataBase Course. a. Write SQL DDL corresponding to the following sc
ID: 3712669 • Letter: F
Question
Fundamentals DataBase Course.
a. 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).
b. 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
Answer:
Primary key : Author_ID in Author Table
Foreign Key : Book_authors_books Table referenced from Author_ID from Author and ISBN from Book_descriptions.
Creating tables:
CREATE TABLE Author
(
Author_ID int NOT NULL,
FIRST_NAME varchar(30),
LAST_NAME varchar(30),
PRIMARY KEY (Author_ID)
);
Create TABLE Book_descriptions
(
ISBN int NOT NULL,
Title varchar(20),
Price money,
publisher varchar(15),
Pub_date date,
Edition int,
Pages int
);
Create TABLE Book_authors_books
(
ISBN int NOT NULL,
Author_ID int NOT NULL,
FOREIGN KEY (ISBN)
REFERENCES Book_descriptions(ISBN)
FOREIGN KEY (Author_ID)
REFERENCES Author(Author_ID)
);
Inserting values:
Insert into Author values (111,'Ali','Sami');
Insert into Author values (222,'Mohammed','Khalid');
Insert into Author values (333,'Saad','Ahmed');
Insert into Book_descriptions values (15111,'Math1',37.99,'New Math','January 20,2016',1,256);
Insert into Book_descriptions values (16222,'The Art of Strategy',26.94,'AWP','June21 20,2014',3,250);
Insert into Book_descriptions values (17222,'Calculus',25,'New Math','January 11,2017',1,753);
Insert into Book_descriptions values (18111,'Game Theory',40.99,'MIT','January 01,2016',2,333);
Insert into Book_descriptions values (15111,'Graph Theory',100,'John Wiley','January 01,2018',1,320);
Insert into Book_authors_books values(15111,111);
Insert into Book_authors_books values(16222,222);
Insert into Book_authors_books values(17222,222);
Insert into Book_authors_books values(18111,111);
Insert into Book_authors_books values(15111,333);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.