Assignment on Database Implementation

Database Implementation(Part
2)
TABLE OF CONTENTS
TASK 1.......................................................................................................................................................8
Top down and bottom up approaches.....................................................................................................8
TASK 2.......................................................................................................................................................9
TASK 3......................................................................................................................................................10

TASK 4......................................................................................................................................................14
TASK 5......................................................................................................................................................37
REFERENCES..........................................................................................................................................38

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.
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.
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.
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 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.
TASK 1
Top down and bottom up approaches
Top down and bottom up are considered as the strategies that includes a variety of features in
order to a better rate of the operations and management. A top down approach is classified as doing the
division of a system in various sub parts. The classification is being done in an efficient and appropriate
manner so that the processing of the operations can seem easy enough to be implemented. Bottom up
approaches are considered as combining the sub parts in order to make the final one. Also, it can be
classified as a part of information processing system. Usually, it is being used in complex systems where
the classification has been done in a way that it seems hard enough to analyse the factors of the
operations. Bottom up approach helps in making these systems effective enough so that it can further help
in achieving a better rate of popularity as well as productivity.