Draw an ERD with one entity (rectangle) for each of your normalized relations and appropriate relationship lines
Question:
Draw an ERD with one entity (rectangle) for each of your normalized relations and appropriate relationship lines with cardinality clearly noted. This should be quite easy to do once normalization is complete: simply draw a line from each foreign key to the matching primary key and mark the foreign key end of the line as "many" and the primary key end as "one."
Start the Problem with theses Steps
1. Study each of the user views in the preceding description, along with the business rules.
2. You may have to make some assumptions if you have questions that the description does not answer.
3. Apply the normalization process, normalizing each view to relations that are in at least third normal form. Be careful to consolidate the normalized relations you develop as you go. For the purposes of this exercise, no two relations should share the same primary key. Explain/justify the normalization form for each table.
4. Clearly indicate the primary key of each relation. Remember that a primary key can be one or more attributes within the relation.
Database Management System
A company is in the retail reselling (obtained from publishers) of books to individuals via mail and telephone orders. They are looking to expand their services by offering online ordering via the Internet, and in doing so, have a compelling need to build a database to hold their business information.
The user views, "sale" and "price" are references to the retail sale of a book to a customer, whereas "purchase" and "cost" are references to the purchase of books from a publisher. Each user view is described briefly with a list of the attributes in the view following each description. Per previously set convention, multivalued attributes and repeating groups are enclosed in parentheses.
The Book Catalog lists all the books that the company has for sale. Each book is uniquely identified by the International Standard Book Number (ISBN). Although an ISBN uniquely identifies a book, it is essentially a surrogate key, so there is no way to tell what edition a particular book is simply by looking at the ISBN. When new editions come out, the company typically has leftover stock of prior editions and offers them at a reduced price. The previous edition code in the Book Catalog is intended to help the buyer find the prior edition, if there is one. Books are organized by subject, with each book having only one subject. Any book may have multiple authors. (Although the catalog shows only author names, keep in mind that people's names are seldom unique, and nothing would stop two people with the same name from both writing books). Here is the information in the Book Catalog:
BOOK CATALOG: SUBJECT CODE, SUBJECT DESCRIPTION, BOOK TITLE, BOOK ISBN, BOOK PRICE, PREVIOUS EDITION ISBN, PREVIOUS EDITION PRICE, (BOOK AUTHORS), PUBLISHER NAME
The Book Inventory Report helps the warehouse manager control the inventory in
The warehouse. The Recommended Quantity is the reorder point, meaning when on hand inventory falls below the recommended quantity; it is time to order more books of that title.
INVENTORY REPORT: BOOK ISBN, BOOK EDITION CODE, COST,
SELLING PRICE, QUANTITY ON HAND, QUANTITY ON ORDER, RECOMMENDED QUANTITY
The Customer Book Orders view shows orders placed by customers for purchases of books:
CUSTOMER BOOK ORDERS: CUSTOMER ID, CUSTOMER NAME,
STREET ADDRESS, CITY, STATE, ZIP CODE (ISBN, BOOK EDITION CODE, QUANTITY, PRICE), ORDER DATE, TOTAL PRICE
The company bills customers as books are shipped. An invoice is created for each shipment. (An order can have zero, one, or more invoices, but each invoice belongs to only one order.) The Book Sales Invoice looks like this:
BOOK SALES INVOICE: SALES INVOICE NUMBER, CUSTOMER ID,
CUSTOMER NAME, CUSTOMER STREET ADDRESS,
CUSTOMER CITY, CUSTOMER STATE,
CUSTOMER ZIP CODE, (BOOK ISBN, TITLE, EDITION CODE, (BOOK AUTHORS), QUANTITY, PRICE, PUBLISHER NAME), SHIPPING CHARGES, SALES TAX
The Master Billing Report helps the Collections and Customer Service Departments manage customer accounts. A system for recording customer payments against invoices is out of scope for the current project, but the CBC project sponsors do want to keep a running balance showing what each customer owes the company. As invoices are generated, a database trigger will be used to add invoice totals to the Balance Due. As payments are received, the staff will manually adjust the Balance Due. The Master Billing Report attributes are as follows:
MASTER BILLING REPORT: CUSTOMER ID, NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, PHONE, BALANCE DUE
Each time the company buys books from a publisher, the publisher sends an invoice to the company. To assist in managing inventory cost, CBC wishes to store the Purchase Invoice information and report it using this view:
PURCHASE INVOICE: PUBLISHER ID, PUBLISHER NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, PURCHASE INVOICE NUMBER, INVOICE DATE, (BOOK ISBN, EDITION CODE, TITLE, QUANTITY, COST EACH, EXTENDED COST), TOTAL COST
Note that Extended Cost is calculated as Cost Each * Quantity. (Extended Cost = Cost Each * Quantity)
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi