Trusted by 2+ million users, 1000+ happy students everyday
Showing pages 1 to 8 of 38 pages
Database Implementation(Part 2) TABLE OF CONTENTS TASK 1.......................................................................................................................................................8 Top down and bottom up approaches.....................................................................................................8 TASK 2.......................................................................................................................................................9 TASK 3......................................................................................................................................................10
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 applicationisgiveninFigure1.Thisapplicationmaybefurtherusedforadditional 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. Developingefficientsearchenginesfacilitatestheprocessofretrievingthenecessary information from the database. Create queries that do the following: oList all the books published by an author. oList all the books in a given subject area. oList the books that sold out quickly (Nwosu, Thuraisingham and Berra, 2012). oList the most popular authors. oList the month’s/year’s bestseller. oList 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
deleteinformationaboutbuy/selltransactions,books,customers,etc.(Ghosh,Naikand 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.
Found this document preview useful?
You are reading a preview Upload your documents to download or Become a Desklib member to get accesss