Create tables using SQL according to the given schema. You must create your data

Question

Create tables using SQL according to the given schema. You must create your database using exactly the same name for tables and attributes, using the given type.

Schema

Customer (cid, name, address);

Orders (oid, order_date, cid) foreign key (cid) references Customer(cid);

Order_book(oid, isbn, no_of_copy) foreign key (oid) references Orders(oid), foreign key(isbn) references Book (isbn);

Publisher (pid, name, address, phone);

Book (isbn, title, btype, price, pub_date, pid)

            foreign key (pid) references Publisher (pid);

Author (aid, fname, lname);

Written_by(isbn, aid)

            foreign key (isbn) references Book (isbn), foreign key (aid) references Author (aid);

Editor (eid, fname, lname);

Edited_by(eid, isbn)

foreign key (isbn) references Book (isbn), foreign key (eid) references Editor (eid);

Explanation / Answer

CREATE TABLE Customer(
cid varchar(30) NOT NULL,
name varchar(255),
address varchar(255),
PRIMARY KEY (cid)
);

CREATE TABLE Orders(
oid varchar(30) NOT NULL,
order_date DATE,
cid varchar(30) FOREIGN KEY REFERENCES Customer(cid),
PRIMARY KEY (oid)
);

CREATE TABLE Order_book(
oid varchar(30) FOREIGN KEY REFERENCES Orders(oid),
isbn varchar(30) FOREIGN KEY REFERENCES Book(isbn),
no_of_copy Number(8)
);

CREATE TABLE Publisher(
pid varchar(30) NOT NULL,
name varchar(255),
address varchar(255),
phone varchar(20),
PRIMARY KEY (pid)
);

CREATE TABLE Book(
isbn varchar(30) NOT NULL,
title varchar(30),
btype varchar(30),
price Number(8,2),
pub_date DATE,
pid varchar(30) FOREIGN KEY REFERENCES Publisher(pid),
PRIMARY KEY (isbn)
);

CREATE TABLE Author(
aid varchar(30) NOT NULL,
fname varchar(30),
lname varchar(30),
PRIMARY KEY (aid)
);

CREATE TABLE Written_by(
isbn varchar(30) FOREIGN KEY REFERENCES Book(isbn),
aid varchar(30) FOREIGN KEY REFERENCES Author(aid)
);

CREATE TABLE Editor(
eid varchar(30) NOT NULL,
fname varchar(30),
lname varchar(30),
PRIMARY KEY (eid)
);

CREATE TABLE Edited_by(
eid varchar(30) FOREIGN KEY REFERENCES Editor(eid),
isbn varchar(30) FOREIGN KEY REFERENCES Book(isbn),
);

**//Please Note that I have assumed Primary keys based on Foreign keys. Also assumed data types and used mostly varchar. Hit like if you liked the answer or comment in case of any further query.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.