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)

ITEMS(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.

Create/Define the table.

Answer 1

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)
);

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)
);

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

Please note, the column names user ans password are database reserved words so they are enclosed in double quotes. Here the datatype and presicion of the attributes are used according to the relevant rules of data modelling. You may alter it according to your requirement.