Question

Listed below is the relational database schema for an online store(SQL/Java):

MEMBER(last_name, first_name, email, password, user,street, city, state, zip, card_type, card_no, expiration, name_on_card)

book_SALE(listing_no, seller, isbn, condition, price)

ORDERS(order_no, buyer, order_date, tot)

ITEM(order_no, listing_no)

BOOK(isbn, title, author, edition, publisher, keywords)

The bold attribute(s) in a relation is the primary key of that relation. The italized attributes in some relations denote foreign keys. The seller attribute in the book_SALE relation is a foreign key to the user attribute in the MEMBER relation. The buyer attribute in the ORDERS relation is a also foreign key to the user attribute of the MEMBER relation. The isbn attribute in the book_SALE relation is a foreign key to the isbn attribute of the BOOK relation. The order_no attribute in the ITEM relation is a foreign key to the order_no attribute in the ORDERS relation and the listing_no attribute in the ITEMS relation is a foreign key to the listing_no attribute in the book_SALE relation.

Create/Define the table.

Answer 1

Please find my Answer:

create table member (
last_name varchar(100),
first_name varchar(100),
email varchar(100),
"password" varchar(100),
"user" Integer,
street varchar(100),
city varchar(50),
state varchar(50),
zip Integer,
card_type varchar(50),
card_no varchar(100),
expiration date,
name_on_card varchar(100),
primary key("user")
);

create table book_SALE (
listing_no Integer,
seller varchar(100),
isbn Integer,
condition varchar(100),
price Integer,
primary key(listing_no),
FOREIGN KEY (isbn) REFERENCES BOOK(isbn)
);

create table ORDERS (
order_no Integer,
buyer varchar(100),
order_date date,
tot Integer,
primary key (order_no)
);

create table ITEMS (
order_no Integer,
listing_no Integer,
primary key (order_no, listing_no),
FOREIGN KEY (order_no) REFERENCES ORDERS(order_no)
);

create table BOOK (
isbn Integer,
title varchar(100),
author varchar(100),
edition varchar(100),
publisher varchar(100),
keywords varchar(100),
primary key (isbn)
);