Book4u Database Project: Relational Database Design for Bookstores
VerifiedAdded on 2023/03/21
|29
|6439
|33
Project
AI Summary
The Book4u database project aims to develop a state-of-the-art database system for a small-sized book publisher to improve bookkeeping and customer service. The system maintains details about books, publishers, customers, receipts, and payments. The project involves designing an enhanced E-R diagram, transforming it into a relational database with identified primary and foreign keys, and normalizing the relations to 3NF. The database includes tables for Publishers, Authors, Books, BookAuthors, Customers, Orders, Order_Items, Stock, Suppliers, and Returns, along with appropriate constraints. The project also includes creating queries for various tasks such as identifying top customers, listing books by author or subject, and managing inventory. Furthermore, the project involves designing user sign-in and registration forms, data entry forms for database administration, and various reports for sales analysis and customer statements. The data dictionary provides the SQL CREATE TABLE statements for each relation, defining the schema of the Book4u database.
Book4u Database
Submitted By:
Date:
Submitted By:
Date:
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Introduction
A small sized book publisher has been keeping track of its business mainly on paper. The owner
is planning to grow her business. She would like to improve the bookkeeping, and as a result
provide better service to customers, through a state-of-the-art database system (Setyawan and
Curtarolo, 2010). As she is not familiar with recent technology and tools, she would like to hire
a college student who would develop such a system over the next summer.
The new system will help the bookstore maintain details about books, publishers, customers,
receipts/payments, etc. A book may be a textbook, a novel, a comic, a children’s book, or a
cookbook. Publishers are the suppliers of the books. The bookstore buys books from different
publishers. Typical customers of the store are libraries, institutions, or individuals such as
students, faculty, and others. Customers may open an account with the store if they wish and
receive a customer number (Ghosh, Naik and Karandikar, 2015). The bookstore is planning to
build a website that will allow customers to perform certain activities on-line.
When customers place an order on-line, they can pay either by a major credit card or store
credit. If the transaction takes place in the store, then customers can pay by cash or check in
addition to credit card and store credit. If the required number of copies is available for on-line
orders, the store sends the books and updates the stock level. If the required number of copies
is not available, the store may send a partial consignment, provided that it is acceptable to the
customer (Nwosu, Thuraisingham and Berra, 2012).
The bookstore owner would like to send information about new books, new editions of a book,
or deals to customers based on their profiles. If a customer were a faculty member at a
university, then the owner would like to offer a free copy of a new textbook or a new edition of
an existing textbook. If a customer were a student who liked reading science fiction, the owner
would like to send monthly notices about new releases (Cakir and Oktug, 2010). Thus, the
system should be able to identify potential customers of new books and new editions and
generate reports that contain their contact information.
The owner of the book store would also like to use this system as decision support tool. Every
month she receives a list of new books from the publishers, and she has to decide which ones
to order and how many. Before she decides, she checks her database and goes through the
sales data for the past six months to see how many books of that particular author were sold,
how many books on related topics were sold, how many books in similar areas were returned,
etc. Moreover, she has to make decisions about her existing stock (Neal, Din and Rubin, 2014).
The new system will prepare every week a list of books that fall under a certain level of
availability. She then has to decide whether she wants to replenish her stock or not.
A small sized book publisher has been keeping track of its business mainly on paper. The owner
is planning to grow her business. She would like to improve the bookkeeping, and as a result
provide better service to customers, through a state-of-the-art database system (Setyawan and
Curtarolo, 2010). As she is not familiar with recent technology and tools, she would like to hire
a college student who would develop such a system over the next summer.
The new system will help the bookstore maintain details about books, publishers, customers,
receipts/payments, etc. A book may be a textbook, a novel, a comic, a children’s book, or a
cookbook. Publishers are the suppliers of the books. The bookstore buys books from different
publishers. Typical customers of the store are libraries, institutions, or individuals such as
students, faculty, and others. Customers may open an account with the store if they wish and
receive a customer number (Ghosh, Naik and Karandikar, 2015). The bookstore is planning to
build a website that will allow customers to perform certain activities on-line.
When customers place an order on-line, they can pay either by a major credit card or store
credit. If the transaction takes place in the store, then customers can pay by cash or check in
addition to credit card and store credit. If the required number of copies is available for on-line
orders, the store sends the books and updates the stock level. If the required number of copies
is not available, the store may send a partial consignment, provided that it is acceptable to the
customer (Nwosu, Thuraisingham and Berra, 2012).
The bookstore owner would like to send information about new books, new editions of a book,
or deals to customers based on their profiles. If a customer were a faculty member at a
university, then the owner would like to offer a free copy of a new textbook or a new edition of
an existing textbook. If a customer were a student who liked reading science fiction, the owner
would like to send monthly notices about new releases (Cakir and Oktug, 2010). Thus, the
system should be able to identify potential customers of new books and new editions and
generate reports that contain their contact information.
The owner of the book store would also like to use this system as decision support tool. Every
month she receives a list of new books from the publishers, and she has to decide which ones
to order and how many. Before she decides, she checks her database and goes through the
sales data for the past six months to see how many books of that particular author were sold,
how many books on related topics were sold, how many books in similar areas were returned,
etc. Moreover, she has to make decisions about her existing stock (Neal, Din and Rubin, 2014).
The new system will prepare every week a list of books that fall under a certain level of
availability. She then has to decide whether she wants to replenish her stock or not.
Background of the Project/Research
Generally, a bookstore application is a database system which has the capabilities of storing
and retrieving information about books, customers, and orders. In this project, we are
considering to develop a sample centralized relational Bookstore application database for the
clerks and managers at a book store (Birgegård, Norring and Clinton, 2012). The overview of
this application is given in Figure 1. This application may be further used for additional
developments. This database can be used to store the records of customers and their
preferences, the technical/nontechnical books, magazines to which customers can be
subscribed, and the customer orders to the shop (e.g. telephone orders of customer for books,
etc.), to be sent to their address for example, by regular mail.
The design of the entity relationship model marked the start of our project. In this first step the
scope of our final program was decided on. The reason for this is that every object added to this
diagram would mean programming a whole new table in the final application (Lamb, Fuller and
Bear, 2012). As work progressed the diagram was slimmed down a bit and two objects that we
decided using at first were dropped later due to massive time pressure. Our final diagram
consists of five objects, which all have their own attributes, ranging from just three to ten. The
relationships between different objects are quite straightforward, but for completeness we will
discuss them here. Customer has a one to many relationship with Orders, for the reason that
one and the same order can’t be placed by different customers, but one customer is allowed to
place multiple orders. Orders has a one to one relationship with Book for the reason that as to
complexity reasons each order can only contain one book (Asher, McCormick and McGirt,
2013). Furthermore Book has a many to one relationship with Author, for the simple reason
that one author can write multiple books, but in our simplified world multiple authors can’t
write the same book. The existence of coauthoring is hereby disregarded. The last object is
Magazine, whereto customers can have a subscription, which explains the many to many
relationship.
Database Requirements
The following are the main entity types of the bookstore database. For each entity type, we
provide some of the corresponding attributes. Use this information in order to: (a) Build an
Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database.
Identify the primary key(s) and the foreign key(s) for each relation (Torrey, Bond and Swain,
2012). Draw the relational integrality constraints; (c) For each of the relations created, indicate
its normal form. If the relation is not in the 3NF, decompose it into 3NF relations.
Customer: The main attributes are customer identification number, name, address, reading
preferences, and profession.
Generally, a bookstore application is a database system which has the capabilities of storing
and retrieving information about books, customers, and orders. In this project, we are
considering to develop a sample centralized relational Bookstore application database for the
clerks and managers at a book store (Birgegård, Norring and Clinton, 2012). The overview of
this application is given in Figure 1. This application may be further used for additional
developments. This database can be used to store the records of customers and their
preferences, the technical/nontechnical books, magazines to which customers can be
subscribed, and the customer orders to the shop (e.g. telephone orders of customer for books,
etc.), to be sent to their address for example, by regular mail.
The design of the entity relationship model marked the start of our project. In this first step the
scope of our final program was decided on. The reason for this is that every object added to this
diagram would mean programming a whole new table in the final application (Lamb, Fuller and
Bear, 2012). As work progressed the diagram was slimmed down a bit and two objects that we
decided using at first were dropped later due to massive time pressure. Our final diagram
consists of five objects, which all have their own attributes, ranging from just three to ten. The
relationships between different objects are quite straightforward, but for completeness we will
discuss them here. Customer has a one to many relationship with Orders, for the reason that
one and the same order can’t be placed by different customers, but one customer is allowed to
place multiple orders. Orders has a one to one relationship with Book for the reason that as to
complexity reasons each order can only contain one book (Asher, McCormick and McGirt,
2013). Furthermore Book has a many to one relationship with Author, for the simple reason
that one author can write multiple books, but in our simplified world multiple authors can’t
write the same book. The existence of coauthoring is hereby disregarded. The last object is
Magazine, whereto customers can have a subscription, which explains the many to many
relationship.
Database Requirements
The following are the main entity types of the bookstore database. For each entity type, we
provide some of the corresponding attributes. Use this information in order to: (a) Build an
Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database.
Identify the primary key(s) and the foreign key(s) for each relation (Torrey, Bond and Swain,
2012). Draw the relational integrality constraints; (c) For each of the relations created, indicate
its normal form. If the relation is not in the 3NF, decompose it into 3NF relations.
Customer: The main attributes are customer identification number, name, address, reading
preferences, and profession.
Customer History: The main attributes are history identification number, ISBN of the book
ordered, date of order, date of delivery, quantity delivered, and comments.
Book: The main attributes are ISBN, title, author, amount in the inventory, price, subject area,
and year of publication.
Publisher: The main attributes are publisher identification number, name, address, and contact
number.
Transaction: The main attributes are transaction number, number of books sold/bought, and
amount of money involved in the transaction.
Queries:
Every holiday season, the bookstore sends coupons to the top 10% of its customers. Customers
that have spent the most during the current year make the top of the list (Cakir and Oktug,
2010). Generate a list with names and addresses of these customers.
College professors are targeted when advertising new textbooks. List all customers who are
college professors.
Students are the main customers for textbooks. The bookstore informs the students about
promotions on textbooks, sends out coupons, and advertises new textbook editions. List all
customers of the bookstore who are currently students in the nearby university.
Developing efficient search engines facilitates the process of retrieving the necessary
information from the database. Create queries that do the following:
o List all the books published by an author.
o List all the books in a given subject area.
o List the books that sold out quickly (Nwosu, Thuraisingham and Berra, 2012).
o List the most popular authors.
o List the month’s/year’s bestseller.
o List the number of copies sold for a book in a specific subject area on a certain topic.
The new database helps the bookstore to manage the inventory in a more efficient way. List for
each book the quantity on-hand and the quantity on-order.
The bookstore is interested to learn about customers’ satisfaction with its service. List the
names of the customers who canceled at least three orders in the last month.
ordered, date of order, date of delivery, quantity delivered, and comments.
Book: The main attributes are ISBN, title, author, amount in the inventory, price, subject area,
and year of publication.
Publisher: The main attributes are publisher identification number, name, address, and contact
number.
Transaction: The main attributes are transaction number, number of books sold/bought, and
amount of money involved in the transaction.
Queries:
Every holiday season, the bookstore sends coupons to the top 10% of its customers. Customers
that have spent the most during the current year make the top of the list (Cakir and Oktug,
2010). Generate a list with names and addresses of these customers.
College professors are targeted when advertising new textbooks. List all customers who are
college professors.
Students are the main customers for textbooks. The bookstore informs the students about
promotions on textbooks, sends out coupons, and advertises new textbook editions. List all
customers of the bookstore who are currently students in the nearby university.
Developing efficient search engines facilitates the process of retrieving the necessary
information from the database. Create queries that do the following:
o List all the books published by an author.
o List all the books in a given subject area.
o List the books that sold out quickly (Nwosu, Thuraisingham and Berra, 2012).
o List the most popular authors.
o List the month’s/year’s bestseller.
o List the number of copies sold for a book in a specific subject area on a certain topic.
The new database helps the bookstore to manage the inventory in a more efficient way. List for
each book the quantity on-hand and the quantity on-order.
The bookstore is interested to learn about customers’ satisfaction with its service. List the
names of the customers who canceled at least three orders in the last month.
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Forms:
Create a user sign-in form together with a registration form for new users.
Create the following data entry forms that are used for database administrative functions (e.g.,
buy/sell transactions, books, customers, etc). These forms allow the user to add, update, and
delete information about buy/sell transactions, books, customers, etc. (Ghosh, Naik and
Karandikar, 2015).
Create a form that allows the bookstore employees to browse through the names of the
authors registered in the database. Create a subform to present for each author the list of
books that they have published.
Create a form that browses the publisher’s information. Create a subform that presents for
each publisher a list of books that they offer.
You developed a few queries that enable retrieving information from the database. Create a
form that allows the user to choose to run one of the queries. In this case you can use
command buttons that, when clicked on, open one of the queries already created (Setyawan
and Curtarolo, 2010).
Create a form that allows the user to choose the name of a customer, say from a combo box.
Once the customer’s name is chosen, in a subform (included in this form) present all the buy
transactions performed by this customer. Calculate the total amount of money the customer
has spent so far.
Reports:
Report the name, address, profession, and amount of money spent by the top 10% customers
of the current year. Customers that spend the most during the current year make the top of the
list.
Report details about new books or new editions of books. Report a list of the bestsellers of the
year. Include in this list the book title, author name(s), and ISBN.
Report the most popular authors of the year. Include in this list author name(s), list of the
books written by them, the publication year for each book, and the name of the publisher.
Report monthly statements for customers who have an outstanding balance.
Create a report that will be used at the end of each month to present the monthly
costs/revenues of the bookstore. This report should present the costs/revenues from the
transactions, grouped by publishers. Create another report that groups the costs/revenues by
Create a user sign-in form together with a registration form for new users.
Create the following data entry forms that are used for database administrative functions (e.g.,
buy/sell transactions, books, customers, etc). These forms allow the user to add, update, and
delete information about buy/sell transactions, books, customers, etc. (Ghosh, Naik and
Karandikar, 2015).
Create a form that allows the bookstore employees to browse through the names of the
authors registered in the database. Create a subform to present for each author the list of
books that they have published.
Create a form that browses the publisher’s information. Create a subform that presents for
each publisher a list of books that they offer.
You developed a few queries that enable retrieving information from the database. Create a
form that allows the user to choose to run one of the queries. In this case you can use
command buttons that, when clicked on, open one of the queries already created (Setyawan
and Curtarolo, 2010).
Create a form that allows the user to choose the name of a customer, say from a combo box.
Once the customer’s name is chosen, in a subform (included in this form) present all the buy
transactions performed by this customer. Calculate the total amount of money the customer
has spent so far.
Reports:
Report the name, address, profession, and amount of money spent by the top 10% customers
of the current year. Customers that spend the most during the current year make the top of the
list.
Report details about new books or new editions of books. Report a list of the bestsellers of the
year. Include in this list the book title, author name(s), and ISBN.
Report the most popular authors of the year. Include in this list author name(s), list of the
books written by them, the publication year for each book, and the name of the publisher.
Report monthly statements for customers who have an outstanding balance.
Create a report that will be used at the end of each month to present the monthly
costs/revenues of the bookstore. This report should present the costs/revenues from the
transactions, grouped by publishers. Create another report that groups the costs/revenues by
book. A similar report is used at the end of the year to present the total revenues generated.
Present the total monthly/yearly balance of the business.
Data Dictionary
CREATE TABLE PUBLISHERS (
PUBLISHER_ID CHAR(5),
NAME VARCHAR(30),
URL VARCHAR(80)
);
CREATE TABLE AUTHORS (
AUTHOR_ID CHAR(4) NOT NULL,
NAME CHAR(25),
URL CHAR(80)
);
CREATE TABLE BOOKS (
TITLE VARCHAR(60),
ISBN CHAR(13) NOT NULL,
PUBLISHER_ID CHAR(5),
URL VARCHAR(80),
PRICE DECIMAL(10,2)
);
CREATE TABLE BOOKSAUTHORS (
ISBN CHAR(13) NOT NULL,
AUTHOR_ID CHAR(4) NOT NULL,
SEQ_NO TINYINT
);
Present the total monthly/yearly balance of the business.
Data Dictionary
CREATE TABLE PUBLISHERS (
PUBLISHER_ID CHAR(5),
NAME VARCHAR(30),
URL VARCHAR(80)
);
CREATE TABLE AUTHORS (
AUTHOR_ID CHAR(4) NOT NULL,
NAME CHAR(25),
URL CHAR(80)
);
CREATE TABLE BOOKS (
TITLE VARCHAR(60),
ISBN CHAR(13) NOT NULL,
PUBLISHER_ID CHAR(5),
URL VARCHAR(80),
PRICE DECIMAL(10,2)
);
CREATE TABLE BOOKSAUTHORS (
ISBN CHAR(13) NOT NULL,
AUTHOR_ID CHAR(4) NOT NULL,
SEQ_NO TINYINT
);
CREATE TABLE CUSTOMERS(
CUSTOMER_ID VARCHAR(55) NOT NULL,
FIRST_NAME VARCHAR(55),
LAST_NAME VARCHAR(55),
CUSTOMER_CITY VARCHAR(55),
CUSTOMER_STREET VARCHAR(55),
CUSTOMER_PCODE VARCHAR(55) NOT NULL,
CUSTOMER_EMAIL VARCHAR(55),
CUSTOMER_PHONE VARCHAR(55) NOT NULL,
PRIMARY KEY (CUSTOMER_ID)
);
CREATE TABLE ORDERS(
ORDER_ID VARCHAR(55) NOT NULL,
CUSTOMER_ID VARCHAR(55) NOT NULL,
ORDER_DATE VARCHAR(55),
CREDIT_CARD_NUMB VARCHAR(55) NOT NULL,
CREDIT_CARD_EXPIRY VARCHAR(55) NOT NULL,
PRIMARY KEY (ORDER_ID)
);
CREATE TABLE ORDER_ITEMS (
ITEM_ID VARCHAR(55) NOT NULL,
ORDER_ID VARCHAR(55) NOT NULL,
STOCK_ID VARCHAR(55) NOT NULL,
ISBN VARCHAR (55) NOT NULL,
SUPPLIER_ID VARCHAR(55) NOT NULL,
RETAIL_PRICE VARCHAR(55),
QTY_SOLD VARCHAR (55) NOT NULL,
PRIMARY KEY (ITEM_ID)
);
CREATE TABLE STOCK(
CUSTOMER_ID VARCHAR(55) NOT NULL,
FIRST_NAME VARCHAR(55),
LAST_NAME VARCHAR(55),
CUSTOMER_CITY VARCHAR(55),
CUSTOMER_STREET VARCHAR(55),
CUSTOMER_PCODE VARCHAR(55) NOT NULL,
CUSTOMER_EMAIL VARCHAR(55),
CUSTOMER_PHONE VARCHAR(55) NOT NULL,
PRIMARY KEY (CUSTOMER_ID)
);
CREATE TABLE ORDERS(
ORDER_ID VARCHAR(55) NOT NULL,
CUSTOMER_ID VARCHAR(55) NOT NULL,
ORDER_DATE VARCHAR(55),
CREDIT_CARD_NUMB VARCHAR(55) NOT NULL,
CREDIT_CARD_EXPIRY VARCHAR(55) NOT NULL,
PRIMARY KEY (ORDER_ID)
);
CREATE TABLE ORDER_ITEMS (
ITEM_ID VARCHAR(55) NOT NULL,
ORDER_ID VARCHAR(55) NOT NULL,
STOCK_ID VARCHAR(55) NOT NULL,
ISBN VARCHAR (55) NOT NULL,
SUPPLIER_ID VARCHAR(55) NOT NULL,
RETAIL_PRICE VARCHAR(55),
QTY_SOLD VARCHAR (55) NOT NULL,
PRIMARY KEY (ITEM_ID)
);
CREATE TABLE STOCK(
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
STOCK_ID VARCHAR(55) NOT NULL,
ISBN VARCHAR(55) NOT NULL,
AUTHOR_NAME VARCHAR(55),
TITLE_NAME VARCHAR(55),
PUBLISHER_NAME VARCHAR(55),
PUBLISHED_YEAR VARCHAR(55),
SUPPLIER_ID VARCHAR(55) NOT NULL,
RETAIL_PRICE VARCHAR (55) NULL,
PRIMARY KEY (STOCK_ID)
);
CREATE TABLE SUPPLIERS(
SUPPLIER_ID VARCHAR(55)NOT NULL,
STOCK_ID VARCHAR (55)NOT NULL,
SUPPLIER_NAME VARCHAR(55) NOT NULL,
SUPPLIER_CITY VARCHAR(55),
SUPPLIER_STREET VARCHAR(55),
SUPPLIER_PCODE VARCHAR(55) NOT NULL,
SUPPLIER_EMAIL VARCHAR(55),
SUPPLIER_PHONE VARCHAR(55) NOT NULL,
PRIMARY KEY (SUPPLIER_ID)
);
CREATE TABLE RETURNS(
RETURN_ID VARCHAR(55)NOT NULL,
ITEM_ID VARCHAR(55) NOT NULL,
STOCK_ID VARCHAR(55) NOT NULL,
RETAIL_PRICE VARCHAR(55),
PRIMARY KEY (RETURN_ID)
);
ISBN VARCHAR(55) NOT NULL,
AUTHOR_NAME VARCHAR(55),
TITLE_NAME VARCHAR(55),
PUBLISHER_NAME VARCHAR(55),
PUBLISHED_YEAR VARCHAR(55),
SUPPLIER_ID VARCHAR(55) NOT NULL,
RETAIL_PRICE VARCHAR (55) NULL,
PRIMARY KEY (STOCK_ID)
);
CREATE TABLE SUPPLIERS(
SUPPLIER_ID VARCHAR(55)NOT NULL,
STOCK_ID VARCHAR (55)NOT NULL,
SUPPLIER_NAME VARCHAR(55) NOT NULL,
SUPPLIER_CITY VARCHAR(55),
SUPPLIER_STREET VARCHAR(55),
SUPPLIER_PCODE VARCHAR(55) NOT NULL,
SUPPLIER_EMAIL VARCHAR(55),
SUPPLIER_PHONE VARCHAR(55) NOT NULL,
PRIMARY KEY (SUPPLIER_ID)
);
CREATE TABLE RETURNS(
RETURN_ID VARCHAR(55)NOT NULL,
ITEM_ID VARCHAR(55) NOT NULL,
STOCK_ID VARCHAR(55) NOT NULL,
RETAIL_PRICE VARCHAR(55),
PRIMARY KEY (RETURN_ID)
);
ALTER TABLE ORDERS
ADD FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMERS (CUSTOMER_ID);
ALTER TABLE ORDER_ITEMS
ADD FOREIGN KEY (ORDER_ID)
REFERENCES ORDERS (ORDER_ID);
ALTER TABLE ORDER_ITEMS
ADD FOREIGN KEY (SUPPLIER_ID)
REFERENCES SUPPLIERS (SUPPLIER_ID);
ALTER TABLE ORDER_ITEMS
ADD FOREIGN KEY (STOCK_ID)
REFERENCES STOCK (STOCK_ID);
ALTER TABLE SUPPLIERS
ADD FOREIGN KEY (STOCK_ID)
REFERENCES STOCK (STOCK_ID);
ALTER TABLE RETURNS
ADD FOREIGN KEY (ITEM_ID)
REFERENCES ORDER_ITEMS (ITEM_ID);
ADD FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMERS (CUSTOMER_ID);
ALTER TABLE ORDER_ITEMS
ADD FOREIGN KEY (ORDER_ID)
REFERENCES ORDERS (ORDER_ID);
ALTER TABLE ORDER_ITEMS
ADD FOREIGN KEY (SUPPLIER_ID)
REFERENCES SUPPLIERS (SUPPLIER_ID);
ALTER TABLE ORDER_ITEMS
ADD FOREIGN KEY (STOCK_ID)
REFERENCES STOCK (STOCK_ID);
ALTER TABLE SUPPLIERS
ADD FOREIGN KEY (STOCK_ID)
REFERENCES STOCK (STOCK_ID);
ALTER TABLE RETURNS
ADD FOREIGN KEY (ITEM_ID)
REFERENCES ORDER_ITEMS (ITEM_ID);
ALTER TABLE RETURNS
ADD FOREIGN KEY (STOCK_ID)
REFERENCES STOCK (STOCK_ID);
INSERT INTO Authors VALUES ('CEAR', 'Cearly, Kent', '');
INSERT INTO Authors VALUES ('CHIN', 'Chin, Francis', '');
INSERT INTO Authors VALUES ('CHU1', 'Chu, Kenny', '');
INSERT INTO Authors VALUES ('DUNT', 'Duntemann, Jeff', '');
INSERT INTO Authors VALUES ('EWRI', 'Erwin, Mike', '');
INSERT INTO Authors VALUES ('EVAN', 'Evans, Tim', '');
INSERT INTO Authors VALUES ('FOUS', 'Foust, Jeff', '');
INSERT INTO Authors VALUES ('FOX1', 'Fox, David', 'found.cs.nyu.edu/dfox/index.html');
INSERT INTO Authors VALUES ('GAIT', 'Gaither, Mark', '');
--SELECT * FROM Authors
ADD FOREIGN KEY (STOCK_ID)
REFERENCES STOCK (STOCK_ID);
INSERT INTO Authors VALUES ('CEAR', 'Cearly, Kent', '');
INSERT INTO Authors VALUES ('CHIN', 'Chin, Francis', '');
INSERT INTO Authors VALUES ('CHU1', 'Chu, Kenny', '');
INSERT INTO Authors VALUES ('DUNT', 'Duntemann, Jeff', '');
INSERT INTO Authors VALUES ('EWRI', 'Erwin, Mike', '');
INSERT INTO Authors VALUES ('EVAN', 'Evans, Tim', '');
INSERT INTO Authors VALUES ('FOUS', 'Foust, Jeff', '');
INSERT INTO Authors VALUES ('FOX1', 'Fox, David', 'found.cs.nyu.edu/dfox/index.html');
INSERT INTO Authors VALUES ('GAIT', 'Gaither, Mark', '');
--SELECT * FROM Authors
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
INSERT INTO Books VALUES ('Beyond HTML', '0-07-882198-3', '00788', '', 27.95);
INSERT INTO Books VALUES ('10 Minute Guide to HTML', '0-78970541-9', '07897',
'www.mcp.com/cgi-bin/bag?isbn=1-07897-0541-9&userID=&last=/bookstore', 15.00);
INSERT INTO Books VALUES ('Advanced HTML & CGI Writer''s Companion', '0-12623540-6',
'01262', '', 29.95);
INSERT INTO Books VALUES ('Creating Cool Web Pages with HTML', '1-56-884454-9', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-454-9', 19.99);
INSERT INTO Books VALUES ('Creating Web Pages for Dummies', '1-56884645-2', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-645-2', 20.00);
INSERT INTO Books VALUES ('How to Use HTML 3', '1-56276390-3', '15627',
'www.mcp.com/169486041707673/cgi-bin/bag?isbn=1-56276-390-3&last=/bookstore', 24.99);
INSERT INTO Books VALUES ('HTML 3 How-To', '1-57-169050-7', '15716',
'w3.waite.com/waite/waite/books.new/HTML_3_How_To/html/html3htcov.html', 39.99);
INSERT INTO Books VALUES ('HTML 3 Interactive Course', '1-57169066-2', '15716',
'w3.waite.com/waite/waite/books.new/HTML_3_Interactive_Course/html/h3iccov.html',
39.99);
INSERT INTO Books VALUES ('10 Minute Guide to HTML', '0-78970541-9', '07897',
'www.mcp.com/cgi-bin/bag?isbn=1-07897-0541-9&userID=&last=/bookstore', 15.00);
INSERT INTO Books VALUES ('Advanced HTML & CGI Writer''s Companion', '0-12623540-6',
'01262', '', 29.95);
INSERT INTO Books VALUES ('Creating Cool Web Pages with HTML', '1-56-884454-9', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-454-9', 19.99);
INSERT INTO Books VALUES ('Creating Web Pages for Dummies', '1-56884645-2', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-645-2', 20.00);
INSERT INTO Books VALUES ('How to Use HTML 3', '1-56276390-3', '15627',
'www.mcp.com/169486041707673/cgi-bin/bag?isbn=1-56276-390-3&last=/bookstore', 24.99);
INSERT INTO Books VALUES ('HTML 3 How-To', '1-57-169050-7', '15716',
'w3.waite.com/waite/waite/books.new/HTML_3_How_To/html/html3htcov.html', 39.99);
INSERT INTO Books VALUES ('HTML 3 Interactive Course', '1-57169066-2', '15716',
'w3.waite.com/waite/waite/books.new/HTML_3_Interactive_Course/html/h3iccov.html',
39.99);
INSERT INTO Books VALUES ('HTML 3.0 Manual of Style (2nd Ed.)', '1-56-276352-0', '15627',
'www.mcp.com/zdpress/features/html3.html', 24.95);
INSERT INTO Books VALUES ('HTML by Example', '0-78970812-4', '07897',
'www.mcp.com/26417620761498/cgi-bin/bag?isbn=0-7897-0812-4&last=/bookstore', 34.99);
INSERT INTO Books VALUES ('HTML CD- An Internet Publishing Toolkit - Windows Version', '0-
13-232331-1', '01356', 'www.prenhall.com/013/232330/ptr/23233-0.html', 40.00);
INSERT INTO Books VALUES ('HTML for Dummies 2''nd Edition', '1-56-884647-9', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-330-5', 30.00);
INSERT INTO Books VALUES ('HTML For Dummies Quick Reference', '1-56884990-7', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-990-7', 12.99);
INSERT INTO Books VALUES ('HTML For Fun and Profit: Gold Signature Edition', '0-13-242488-6',
'01356', 'www.sun.com/smi/ssoftpress/books/Morris/Morris.html', 40.00);
INSERT INTO Books VALUES ('HTML for the World Wide Web: Visual Quick Start Guide', '0-
02188448-8', '00218', 'www.peachpit.com/peachpit/titles/catalog/88448.html', 17.95);
INSERT INTO Books VALUES ('HTML In Action', '1-55615948-X', '15561',
'www.microsoft.com/MSPRESS/BOOKS/DES/5-948-xA.HTM', 29.95);
INSERT INTO Books VALUES ('HTML Plus!', '0-53451626-2', '05345',
'scholar.lib.vt.edu/jpowell/htmlplus/', 36.00);
-- SELECT * FROM Books
INSERT INTO BooksAuthors VALUES ('0-07-882198-3', 'KARP', 1);
INSERT INTO BooksAuthors VALUES ('0-78970541-9', 'EVAN', 1);
INSERT INTO BooksAuthors VALUES ('0-12623540-6', 'SCHE', 1);
'www.mcp.com/zdpress/features/html3.html', 24.95);
INSERT INTO Books VALUES ('HTML by Example', '0-78970812-4', '07897',
'www.mcp.com/26417620761498/cgi-bin/bag?isbn=0-7897-0812-4&last=/bookstore', 34.99);
INSERT INTO Books VALUES ('HTML CD- An Internet Publishing Toolkit - Windows Version', '0-
13-232331-1', '01356', 'www.prenhall.com/013/232330/ptr/23233-0.html', 40.00);
INSERT INTO Books VALUES ('HTML for Dummies 2''nd Edition', '1-56-884647-9', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-330-5', 30.00);
INSERT INTO Books VALUES ('HTML For Dummies Quick Reference', '1-56884990-7', '15688',
'db.www.idgbooks.com/database/book/isbn/generic-book.tmpl?query=1-56884-990-7', 12.99);
INSERT INTO Books VALUES ('HTML For Fun and Profit: Gold Signature Edition', '0-13-242488-6',
'01356', 'www.sun.com/smi/ssoftpress/books/Morris/Morris.html', 40.00);
INSERT INTO Books VALUES ('HTML for the World Wide Web: Visual Quick Start Guide', '0-
02188448-8', '00218', 'www.peachpit.com/peachpit/titles/catalog/88448.html', 17.95);
INSERT INTO Books VALUES ('HTML In Action', '1-55615948-X', '15561',
'www.microsoft.com/MSPRESS/BOOKS/DES/5-948-xA.HTM', 29.95);
INSERT INTO Books VALUES ('HTML Plus!', '0-53451626-2', '05345',
'scholar.lib.vt.edu/jpowell/htmlplus/', 36.00);
-- SELECT * FROM Books
INSERT INTO BooksAuthors VALUES ('0-07-882198-3', 'KARP', 1);
INSERT INTO BooksAuthors VALUES ('0-78970541-9', 'EVAN', 1);
INSERT INTO BooksAuthors VALUES ('0-12623540-6', 'SCHE', 1);
INSERT INTO BooksAuthors VALUES ('1-56-884454-9', 'TAYL', 1);
INSERT INTO BooksAuthors VALUES ('1-56884645-2', 'SMIT', 1);
INSERT INTO BooksAuthors VALUES ('1-56884645-2', 'BEBA', 2);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'TITT', 1);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'GAIT', 2);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'HASS', 3);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'ERWI', 4);
INSERT INTO BooksAuthors VALUES ('1-56276390-3', 'ARPA', 1);
-- SELECT * FROM BooksAuthors
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('22','Nicki','Clifford','Dublin','88 south lotts','D4','Nickiclif@hotmail.com',
'0876662222');
INSERT INTO BooksAuthors VALUES ('1-56884645-2', 'SMIT', 1);
INSERT INTO BooksAuthors VALUES ('1-56884645-2', 'BEBA', 2);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'TITT', 1);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'GAIT', 2);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'HASS', 3);
INSERT INTO BooksAuthors VALUES ('1-56884703-3', 'ERWI', 4);
INSERT INTO BooksAuthors VALUES ('1-56276390-3', 'ARPA', 1);
-- SELECT * FROM BooksAuthors
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('22','Nicki','Clifford','Dublin','88 south lotts','D4','Nickiclif@hotmail.com',
'0876662222');
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('23','Lyn','Russell','Dublin','87 Claremont ave','D5','lyna@hotmail.com',
'0877777222');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('24','fay','Allen','Dublin','88 Serpentine ave','D3','fay@hotmail.com',
'0877777111');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('25','Rose','Curry','Waterford','203 Glennmore road','6152','Rose@gmail.com',
'0871118930');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('26','Geraldine','Allen','Dublin','233 Farney Park','D4','Geraldine@gmail.com',
'0868302810');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('27','Len','Allen','Wexford','15 Waterside road','051','len@gmail.com',
'0871113940');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('29','Nick','Burridge','Aukland','333 bayside ave','NZ 22','NickB@gmail.com',
'0053-111-8930');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('30','Paul','Weldon','Melboure','33 Golden Point road','AUS 331-
00','Paulyweldon@gmail.com', '051 -200-3421 ');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('32','Ben','Freeney','London','22 Bow lane','P.O.BOX
152','BenFoom@gmail.com', '0044 74738930');
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('23','Lyn','Russell','Dublin','87 Claremont ave','D5','lyna@hotmail.com',
'0877777222');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('24','fay','Allen','Dublin','88 Serpentine ave','D3','fay@hotmail.com',
'0877777111');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('25','Rose','Curry','Waterford','203 Glennmore road','6152','Rose@gmail.com',
'0871118930');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('26','Geraldine','Allen','Dublin','233 Farney Park','D4','Geraldine@gmail.com',
'0868302810');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('27','Len','Allen','Wexford','15 Waterside road','051','len@gmail.com',
'0871113940');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('29','Nick','Burridge','Aukland','333 bayside ave','NZ 22','NickB@gmail.com',
'0053-111-8930');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('30','Paul','Weldon','Melboure','33 Golden Point road','AUS 331-
00','Paulyweldon@gmail.com', '051 -200-3421 ');
INSERT INTO Customers (Customer_ID,First_name,Last_name,Customer_City,
Customer_Street,Customer_PCode,Customer_email,Customer_phone)
VALUES ('32','Ben','Freeney','London','22 Bow lane','P.O.BOX
152','BenFoom@gmail.com', '0044 74738930');
INSERT INTO Orders (Order_Id,Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD691','22','13-10-2016','1717-22-233','11-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD022','23','12-05-2016','1217-11-283','08-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD391','24','13-06-2015','8717-22-243','09-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD067','25','14-11-2016','1788-33-443','05-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD054','26','1-08-2016','1448-33-443','07-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD097','27','23-10-2016','9488-33-293','06-17');
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD691','22','13-10-2016','1717-22-233','11-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD022','23','12-05-2016','1217-11-283','08-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD391','24','13-06-2015','8717-22-243','09-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD067','25','14-11-2016','1788-33-443','05-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD054','26','1-08-2016','1448-33-443','07-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD097','27','23-10-2016','9488-33-293','06-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD093','29','26-09-2016','2288-13-543','05-18');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD0013','30','1-11-2016','158-35-543','09-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD117','32','13-07-2016','1111-33-343','05-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD127','57','23-09-2015','4444-63-446','07-17');
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD093','29','26-09-2016','2288-13-543','05-18');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD0013','30','1-11-2016','158-35-543','09-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD117','32','13-07-2016','1111-33-343','05-17');
INSERT INTO Orders (Order_Id, Customer_ID,
Order_Date,Credit_card_numb, Credit_card_expiry)
Values ('ORD127','57','23-09-2015','4444-63-446','07-17');
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM007','ORD691','STK001','0-410-121-1','SUP001','16.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM008','ORD022','STK002','0-230-166-1','SUP002','26.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM009','ORD391','STK003','0-090-881-1','SUP003','22.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM010','ORD067','STK004','0-22-121-1','SUP004','20.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM011','ORD054','STK005','0-413-331-1','SUP005','29.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM012','ORD097','STK006','9-330-121-1','SUP006','29.99','1');
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM007','ORD691','STK001','0-410-121-1','SUP001','16.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM008','ORD022','STK002','0-230-166-1','SUP002','26.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM009','ORD391','STK003','0-090-881-1','SUP003','22.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM010','ORD067','STK004','0-22-121-1','SUP004','20.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM011','ORD054','STK005','0-413-331-1','SUP005','29.99','1');
INSERT INTO Order_Items (Item_Id, Order_ID, Stock_ID,
isbn,Supplier_ID, Retail_Price, Qty_sold)
Values ('ITM012','ORD097','STK006','9-330-121-1','SUP006','29.99','1');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK001','0-332-3233-4','J.R.R Tolken','The Hobbit',
'Allen & Unwin','1937','SUP001', '16.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK002','0-3113-443-4','J.R.R Tolken','The Lord of the Rings : Fellowship of the ring',
'Allen & Unwin','2000','SUP002','26.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK003','0-113-473-8','J.R.R Tolken','The Lord of the Rings : The two towers',
'Allen & Unwin','2006', 'SUP003', '20.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK001','0-332-3233-4','J.R.R Tolken','The Hobbit',
'Allen & Unwin','1937','SUP001', '16.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK002','0-3113-443-4','J.R.R Tolken','The Lord of the Rings : Fellowship of the ring',
'Allen & Unwin','2000','SUP002','26.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK003','0-113-473-8','J.R.R Tolken','The Lord of the Rings : The two towers',
'Allen & Unwin','2006', 'SUP003', '20.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK004','0-993-433-3','J.R.R Tolken','The Lord of the Rings : Return of the King',
'Allen & Unwin','2014','SUP004', '29.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK005','0-293-333-6','Tanith Lee','The Castle of Dark',
'Macmilla','1987', 'SUP005', '29.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK006','0-023-179-4','Tanith Lee','The Winter Players',
'Macmilla','1977','SUP006', '29.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK007','0-023-179-3','Edgar Rice Burroughs','Tarzan and the forbidden city',
'New English Library','1914','SUP007','28.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK008','0-113-139-6','Edgar Rice Burroughs','Tarzan of the Apes',
'New English Library','1999','SUP008','19.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK009','0-444-139-6','Robert Ludlum','The Gemini Contenders',
'Dial Press','2009','SUP009','21.99');
'Allen & Unwin','2014','SUP004', '29.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK005','0-293-333-6','Tanith Lee','The Castle of Dark',
'Macmilla','1987', 'SUP005', '29.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK006','0-023-179-4','Tanith Lee','The Winter Players',
'Macmilla','1977','SUP006', '29.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK007','0-023-179-3','Edgar Rice Burroughs','Tarzan and the forbidden city',
'New English Library','1914','SUP007','28.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK008','0-113-139-6','Edgar Rice Burroughs','Tarzan of the Apes',
'New English Library','1999','SUP008','19.99');
INSERT INTO STOCK (STOCK_ID,isbn,Author_name,
Title_name, Publisher_name, Published_year,
Supplier_ID, Retail_price)
VALUES ('STK009','0-444-139-6','Robert Ludlum','The Gemini Contenders',
'Dial Press','2009','SUP009','21.99');
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP001','STK001','Gates & Co','Dublin','Georges
Street','D2','Gatesco@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP002','STK002','Books & Co','London','Bourke Street','PO BOX
02','Booksco@gmail.com','22222374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP003','STK003','Berlin LTD','Berlin','Schillstraße
8','B8','Berlinltd@gmail.com','12755574');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP004','STK004','Dublin Press','Dublin','Dame
Street','D3','Dpress@gmail.com','11178374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP005','STK005','Capital City Distribution','Galway','riverside
Street','G5','CapitolCD@gmail.com','2278374');
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP001','STK001','Gates & Co','Dublin','Georges
Street','D2','Gatesco@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP002','STK002','Books & Co','London','Bourke Street','PO BOX
02','Booksco@gmail.com','22222374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP003','STK003','Berlin LTD','Berlin','Schillstraße
8','B8','Berlinltd@gmail.com','12755574');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP004','STK004','Dublin Press','Dublin','Dame
Street','D3','Dpress@gmail.com','11178374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP005','STK005','Capital City Distribution','Galway','riverside
Street','G5','CapitolCD@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP006','STK006','Press and Co','Bermingham','Little britan Street','PO BOX
3','Pressco@gmail.com','9998374');
INSERT INTO Suppliers (Supplier_Id,Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP007','STK007','Books and Co','Belfast','Falls road','PO BOX
08','Booksco@gmail.com','99999374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP008','STK008','Atlas Books','Waterford','green
street','D2','Gatesco@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP009','STK009','Global Press','New York','Wall
Street','NYC09','Global@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP010','STK010','International supply','los angeles','Sunny
Street','LA2','international@gmail.com','2278374');
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP006','STK006','Press and Co','Bermingham','Little britan Street','PO BOX
3','Pressco@gmail.com','9998374');
INSERT INTO Suppliers (Supplier_Id,Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP007','STK007','Books and Co','Belfast','Falls road','PO BOX
08','Booksco@gmail.com','99999374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP008','STK008','Atlas Books','Waterford','green
street','D2','Gatesco@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP009','STK009','Global Press','New York','Wall
Street','NYC09','Global@gmail.com','2278374');
INSERT INTO Suppliers (Supplier_Id, Stock_ID, Supplier_name,
Supplier_City,Supplier_Street, Supplier_PCode, Supplier_Email, Supplier_phone)
Values ('SUP010','STK010','International supply','los angeles','Sunny
Street','LA2','international@gmail.com','2278374');
INSERT INTO Returns (Return_Id, Item_ID,Stock_ID, Retail_Price)
Values ('RTN001','ITM007','STK001','16.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN002','ITM008','STK002','26.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN003','ITM009','STK003','22.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN004','ITM010','STK004','20.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN005','ITM011','STK005','29.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN006','ITM012','STK006','29.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN007','ITM011','STK007','28.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN008','ITM012','STK008','19.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN009','ITM013','STK009','21.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN010','ITM014','STK010','29.99');
Values ('RTN001','ITM007','STK001','16.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN002','ITM008','STK002','26.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN003','ITM009','STK003','22.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN004','ITM010','STK004','20.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN005','ITM011','STK005','29.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN006','ITM012','STK006','29.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN007','ITM011','STK007','28.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN008','ITM012','STK008','19.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN009','ITM013','STK009','21.99');
INSERT INTO Returns (Return_Id, Item_ID, Stock_ID, Retail_Price)
Values ('RTN010','ITM014','STK010','29.99');
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
SQL Queries:
SELECT * FROM Orders
WHERE Order_Date BETWEEN'13-10-201'AND'17-10-2010';
SELECT * FROM Orders
WHERE Order_Date BETWEEN'13-10-201'AND'17-10-2010';
SELECT * FROM stock
order by publisher_name;
SELECT * from stock where Published_year = 2014
order by publisher_name;
SELECT * from stock where Published_year = 2014
SELECT (Return_Id), (Item_ID), SUM(Retail_Price) AS 'TOTAL RETURNS'
FROM RETURNS
GROUP BY Return_Id with ROLLUP;
FROM RETURNS
GROUP BY Return_Id with ROLLUP;
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
SELECT (Item_Id), (Order_ID), SUM(Retail_Price) AS 'TOTAL SALES'
FROM Order_Items
Group by Order_id with rollup;
SELECT Stock.ISBN, COUNT(Stock.ISBN)
FROM Stock
GROUP BY Stock.ISBN;
FROM Order_Items
Group by Order_id with rollup;
SELECT Stock.ISBN, COUNT(Stock.ISBN)
FROM Stock
GROUP BY Stock.ISBN;
Technical points:
Databases often hold the backbone of an organization; Its’ transactions, customers, employee
info, financial data for both the company and its customers, and much more are all held in
databases, often left to the power of a database administrator with no security training.
Database security and integrity are essential aspects of an organization’s security posture.
o Ensure your database administrators both understand the business value and
importance of ensuring your databases are secured and extending them the resources
to do so properly.
o Protect against SQL injections by using parameterized queries to keep malicious queries
out of your database.
o Static Code Analysis is an essential tool for organizations developing applications as
portals to databases to slash SQL injection, buffer overflow, and mis-configuration
issues.
o Maintain CIA by keeping your databases up to date, removing any unknown
components, and enforcing least privilege parameters to ensure the confidentiality,
integrity and availability of your databases.
o To maintain availability, employ an Uninterruptible Power Supply, or UPS, to ensure any
forced shutdown doesn’t cause data loss.
o Keep features and services only to what is essential for the company to work smoothly
with the databases – the more extras you have, the more you need to stay up-to-date
with, the more holes hackers have a chance to poke through.
Databases often hold the backbone of an organization; Its’ transactions, customers, employee
info, financial data for both the company and its customers, and much more are all held in
databases, often left to the power of a database administrator with no security training.
Database security and integrity are essential aspects of an organization’s security posture.
o Ensure your database administrators both understand the business value and
importance of ensuring your databases are secured and extending them the resources
to do so properly.
o Protect against SQL injections by using parameterized queries to keep malicious queries
out of your database.
o Static Code Analysis is an essential tool for organizations developing applications as
portals to databases to slash SQL injection, buffer overflow, and mis-configuration
issues.
o Maintain CIA by keeping your databases up to date, removing any unknown
components, and enforcing least privilege parameters to ensure the confidentiality,
integrity and availability of your databases.
o To maintain availability, employ an Uninterruptible Power Supply, or UPS, to ensure any
forced shutdown doesn’t cause data loss.
o Keep features and services only to what is essential for the company to work smoothly
with the databases – the more extras you have, the more you need to stay up-to-date
with, the more holes hackers have a chance to poke through.
o Data masking, or allowing users to access certain info without being able to view it –
credit card processing or during database testing and development, for example, helps
maintain the confidentiality of the database.
credit card processing or during database testing and development, for example, helps
maintain the confidentiality of the database.
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
REFERENCES
Setyawan, W. and Curtarolo, S., 2010. High-throughput electronic band structure calculations:
Challenges and tools.
Computational materials science,
49(2), pp.299-312.
Ghosh, S., Naik, G. and Karandikar, A., 2015, February. OpenPAWS: An open source PAWS and
UHF TV white space database implementation for India. In
Communications (NCC), 2015
Twenty First National Conference on (pp. 1-6). IEEE.
Nwosu, K. C., Thuraisingham, B. and Berra, P. B. eds., 2012.
Multimedia Database Systems:
design and implementation strategies. Springer Science & Business Media.
Cakir, A. R. and Oktug, S., 2010. A nanonetwork environment with messaging and humoral
immunity-based database implementation.
Nano Communication Networks, 1(3),
pp.189-198.
Neal, R. D., Din, N. U. and Rubin, G., 2014. Comparison of cancer diagnostic intervals before and
after implementation of NICE guidelines: analysis of data from the UK General Practice
Research Database.
British journal of cancer, 110(3), p.584.
Birgegård, A., Norring, C. and Clinton, D., 2012. DSM-IV versus DSM-5: Implementation of
proposed DSM-5 criteria in a large naturalistic database.
International Journal of Eating
Disorders, 45(3), pp.353-361.
Lamb, A., Fuller, M. and Bear, C., 2012. The vertica analytic database: C-store 7 years later.
Proceedings of the VLDB Endowment,
5(12), pp.1790-1801.
Asher, A. L., McCormick, P. C. and McGirt, M. J., 2013. The National Neurosurgery Quality and
Outcomes Database and NeuroPoint Alliance: rationale, development, and
implementation.
Neurosurgical focus, 34(1), p.E2.
Torrey, W. C., Bond, G. R. and Swain, K., 2012. Evidence-based practice implementation in
community mental health settings: The relative importance of key domains of
implementation activity.
Administration and Policy in Mental Health and Mental Health
Services Research, 39(5), pp.353-364.
Setyawan, W. and Curtarolo, S., 2010. High-throughput electronic band structure calculations:
Challenges and tools.
Computational materials science,
49(2), pp.299-312.
Ghosh, S., Naik, G. and Karandikar, A., 2015, February. OpenPAWS: An open source PAWS and
UHF TV white space database implementation for India. In
Communications (NCC), 2015
Twenty First National Conference on (pp. 1-6). IEEE.
Nwosu, K. C., Thuraisingham, B. and Berra, P. B. eds., 2012.
Multimedia Database Systems:
design and implementation strategies. Springer Science & Business Media.
Cakir, A. R. and Oktug, S., 2010. A nanonetwork environment with messaging and humoral
immunity-based database implementation.
Nano Communication Networks, 1(3),
pp.189-198.
Neal, R. D., Din, N. U. and Rubin, G., 2014. Comparison of cancer diagnostic intervals before and
after implementation of NICE guidelines: analysis of data from the UK General Practice
Research Database.
British journal of cancer, 110(3), p.584.
Birgegård, A., Norring, C. and Clinton, D., 2012. DSM-IV versus DSM-5: Implementation of
proposed DSM-5 criteria in a large naturalistic database.
International Journal of Eating
Disorders, 45(3), pp.353-361.
Lamb, A., Fuller, M. and Bear, C., 2012. The vertica analytic database: C-store 7 years later.
Proceedings of the VLDB Endowment,
5(12), pp.1790-1801.
Asher, A. L., McCormick, P. C. and McGirt, M. J., 2013. The National Neurosurgery Quality and
Outcomes Database and NeuroPoint Alliance: rationale, development, and
implementation.
Neurosurgical focus, 34(1), p.E2.
Torrey, W. C., Bond, G. R. and Swain, K., 2012. Evidence-based practice implementation in
community mental health settings: The relative importance of key domains of
implementation activity.
Administration and Policy in Mental Health and Mental Health
Services Research, 39(5), pp.353-364.
1 out of 29
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
© 2024 | Zucol Services PVT LTD | All rights reserved.