Creating Book_store Database Using MySQL

Here we will creating the book_store database using mysql:

CREATE TABLE PUBLISHER
(
publisher_id int,
publisher_name char (50),
CONSTRAINT pk_publisher PRIMARY KEY (publisher_id)
);
GO
CREATE TABLE CUSTOMER
(
customer_numb int,
first_name varchar (30),
last_name varchar (30),
street varchar (50),
city varchar (30),
state_province char (2),
zip_postcode char (10),
contact_phone char (12),
CONSTRAINT pk_customer PRIMARY KEY (customer_numb)
);
GO
CREATE TABLE AUTHOR
(
author_numb int,
author_last_first char (128),
CONSTRAINT pk_author PRIMARY KEY (author_numb)
);
GO
CREATE TABLE WORK
(
work_numb int,
author_numb int,
title char (50),
CONSTRAINT pk_work PRIMARY KEY (work_numb),
CONSTRAINT AuthorWork FOREIGN KEY (author_numb) REFERENCES Author
);
GO
CREATE TABLE CONDITION_CODES
(
condition_code int,
condition_description varchar (128),
CONSTRAINT pk_condition_codes PRIMARY KEY (condition_code)
);
GO

CREATE TABLE SALE
(
sale_id int,
customer_numb int,
sale_date date,
sale_total_amt decimal (8,2),
credit_card_numb char (20),
exp_month int,
exp_year int,
CONSTRAINT pk_sale PRIMARY KEY (sale_id),
CONSTRAINT SaleCustomer FOREIGN KEY (customer_numb) REFERENCES Customer
);
GO
CREATE TABLE BOOK
(
ISBN char (17),
work_numb int,
publisher_id int,
edition int,
binding char (20),
copyright_year char (4),
CONSTRAINT pk_book PRIMARY KEY (ISBN),
CONSTRAINT PublisherBook FOREIGN KEY (publisher_id) REFERENCES Publisher,
CONSTRAINT workBook FOREIGN KEY (work_numb) REFERENCES Work
);
GO
CREATE TABLE VOLUME
(
inventory_id int,
ISBN char (17),
condition_code int,
date_acquired date,
asking_price decimal (7,2),
selling_price decimal (7,2),
sale_id int,
CONSTRAINT pk_volume PRIMARY KEY (inventory_id),
CONSTRAINT BookVolume FOREIGN KEY (ISBN) REFERENCES Book,
CONSTRAINT ConditionVolume FOREIGN KEY (condition_code) REFERENCES Condition_codes,
CONSTRAINT SaleVolume FOREIGN KEY (sale_id) REFERENCES Sale
);
GO

We are also providing other database related help like:

• Get Help In DML

• Get Help In ER Diagram

• Shema Diagram

• Database queries

Send request at realcode4you@gmail.com to get help in:

• Database Assignments

• Database projects

• Database Homework