Milestone 2 Conceptual Model & Logical Design Overview This milestone has 3 parts: creating the EERD, developing the dictionary entries (metadata) for your data, and submitting team member journals. The deliverable will have your teams name and logo and all sections are part of one file (.pdf). 1. Create the EERD Review the business rules described in your projects summary, and create an EERD illustrating the entities/relationships needed to support your database design using Gliffy. Make sure to follow all rules of EER diagramming. Include all entities, label relationships if there are multiple relationships between 2 entities, include all attributes and constraints where necessary and identify all primary and foreign keys (no PK/FK on your diagrams). For full credit, part 1 deliverable must include: 1. EERD incorporating all entities identified in the project business rules (see your project specs) 2. Relationships between those entities are drawn with min/max cardinalities. All primary and foreign keys will be identified as in past exercises/assignments. 3. Do not break the rules of EERD diagramming. 2. Create data dictionary entries Data definitions must be in the form of a table (shown below), defining briefly each attribute of each

SkyMall is a specialty publishing firm headquartered in Phoenix, Arizona. Founded in 1990, the company produces a quarterly in-flight publication, SkyMall, with an annual circulation of approximately 20 million copies distributed in airplane seat pockets. SkyMall is a multi-channel, direct marketer offering products through direct marketers and manufacturers through its SkyMall catalog. The SkyMall catalog is seen by approximately 88% of all domestic air passengers in the United States, reaching more than 650 million air travelers annually. SkyMall also offers turn-key merchandise and fulfillment solutions for loyalty marketing programs. It originally offered goods from other companies' catalogs for same-day delivery to customers arriving at select U.S. airports or, if the customer preferred, to their home or office via express shipment. To accomplish the same-day delivery promise, SkyMall operated its own warehouses located near the selected airports. Shortly after launching, the company purchased contracts from another company that allowed it to offer catalog merchandise to travelers flying many of the nation's air carriers. This move required a significant shift in SkyMall's focus and operations, ultimately forcing the company to abandon its same-day delivery service. SkyMall now offers goods from other companies' catalogs for dropship to the customer via third-party transport firms. Several companies that participate in the catalog include: Goods offered are organized in the following categories: The company needs improved customer relations. SkyMall has seen the need to streamline their catalog sales in order to keep up with the growing customer demand for products. Presently, each system is manual. Inventory is difficult to track and too many errors are made in counting inventory levels. Stock orders to suppliers are not organized and many times, more than a few products are out of stock. Product backorders are becoming difficult to handle and customers often complain of late deliveries. More telephone operators have been allotted for the upcoming season, but current phone inquiries cannot be handled in a timely manner because the salespeople do not have access to present inventory levels. Last month, the entire customer billing system broke down. Because they were not able to collect receivables, they could not page 2 process any more sales. There has also been a major demand to change the catalogs from paper-based to electronic-based to satisfy recent environmental demands. SkyMall has decided to ask for very detailed Requests For Proposal (RFP) from various groups in competition for the lucrative contract to revise their current system into a complete redesign from scratch, moving from manual systems to an integrated, auditable SQL Server database with an Access front end and Tableau connection to help them make better business decisions. Your team has decided to compete for the contract and will revise the current manual systems starting with a redesign of the database. SkyMall would like to keep data on each company and will store their inventory, customer orders, customer billing and customer shipping data including shipping charges. The goal of your team is to computerize the inventory system by storing it in a database to control inventory levels and to keep track of customer orders. The following are outputs that are mandatory for SkyMall needs. These must be in your final deliverable, and you should create your own samples based on SkyMalls business and your database design. 1. Which company is most popular? 2. Names of customers making more than 5 purchases in the previous month. 3. Names of customers that have not made a purchase in the last 3 months. 4. Descriptions of items that have never been sold. 5. Items that have not been sold in the past 4 months. 6. Items for which QOH is less than 5. 7. A listing of customers in decreasing order by number of purchases. 8. Customers making three or more purchases of the same item (different sales; one purchase involving multiple sales of the same item is not considered). 9. Items with monthly average sales exceeding last years average for the same month. (choose any month) You interview the staff at SkyMall, read the procedure manuals and look through the current system. From this activity, you develop the following business rules: 1. Each customer can have many invoices, each invoice is for one customer. 2. Each invoice contains many inventory items, each inventory item can be on many invoices. 3. Each inventory item comes from one supplier, each supplier supplies many inventory items. 4. Each inventory item is for one company catalog, each company catalog offers many inventory items. 5. Each invoice has one transport company, each transport company delivers many invoices. 6. Each invoice is created by one employee, each employee creates many invoices. 7. Each invoice and customer (from invoice) can have many receivables, each receivable is for one invoice. 8. Each invoice can have many payments, each payment can be for many invoices. (Note** Receivables can be used for the associative in this instance!)

Using the SkyMall information, create an eerd using all of the listed rules using crows foot notation.