MIS 327 Database Management system Dr Monther Tarawneh

MIS 327 Database Management system Dr. Monther Tarawneh Week 6: Database Design: Example Rolling Thunder MIS 327: DBMS Monther Tarawneh Dr. 1

Main objects �The rolling Thunder Bicycle Company builds custom bicycles. �Its Database application is much more complete and provides an example of how the pieces of database system fit together. �The task is similar to any other business.

Description The most important task at the rolling Thunder Bicycle company is to take order for new bicycles. Several features have been included to non experts to select a good bicycle. As the bicycles are built, the employees record the instruction on the assembly form. When the bicycle is shipped, the customers are billed. Customer payments are recorded on the financial forms. As the components are installed on bicycle, the inventory quantity is automatically decreased. Merchandise is ordered from supplier and payments are made when the shipment arrive.

Basic company operations �Customer orders. : Customers can place orders directly with the company, or they can order with the assistance of a local bicycle store. �Bicycles are assembled and shipped in about two weeks. �Basic components are ordered in advance because of shipping delays. �Inventory count is maintained by the computer. As workers assemble a bicycle, they record the use of each component. �Customer payments can be made at the time of purchase, or as monthly payments. �Suppliers are paid only after receipt of shipments.

Rolling Thunder Bicycles: The Company

Rolling Thunder: Top-Level Sales Bicycle Assembly Employee Location Purchasing Based on the main activities. We need to look at each one separately 6

Rolling Thunder: Sales Customer Bicycle: : Bicycle 1… 1 Customer. ID Phone First. Name Last. Name Address Zip. Code City. ID Balance. Due 1… 1 0…* Retail Store Customer Transaction Customer. ID Transaction. Date Employee. ID Amount Description Reference 0…* Store. ID Store. Name Phone Contact. First. Name Contact. Last. Name Address Zip. Code City. ID 0… 1 Bicycle. ID … Customer. ID Store. ID …

Rolling Thunder: Bicycle Model. Type Description Paint. ID Color. Name Color. Style Color. List Date. Introduced Date. Discontinued Letter. Style. ID Description Bicycle 1… 1 0…* 1… 1 Serial. Number Customer. ID Model. Type Paint. ID Frame. Size Order. Date Start. Date Ship. Employee Frame. Assembler Painter Construction Water. Bottle. Braze. On Custom. Name Letter. Style. ID Store. ID Employee. ID Top. Tube Chain. Stay … Bicycle. Tube. Used 1… 1 1…* Serial. Number 1… 1 Tube. ID Quantity 0…* Bike. Parts Serial. Number Component. ID Substitute. ID Location Quantity Date. Installed Employee. ID

Rolling Thunder: Assembly 1… 1 Bicycle: : Bike. Parts Serial. Number Component. ID. . . Bicycle: : Bicycle. Tube. Used Serial. Number Tube. ID Quantity 0…* 1… 1 0…* Component. ID Manufacturer. ID Product. Number Road Category Length Height Width Description List. Price Estimated. Cost Quantity. On. Hand Tube. Material Tube. ID Material Description Diameter … 1… 1 0…* Group. Components Group. ID Component. ID 0…* Groupo Group. ID 1… 1 Group. Name Bike. Type Component. Name 1… 1 Component. Name Assembly. Order Description

Rolling Thunder: Purchasing Purchase. Order Purchase. ID Employee. ID Manufacturer. ID Total. List Shipping. Cost Discount Order. Date Receive. Date Amount. Due 1… 1 0…* Manufacturer 1… 1 Manufacturer. ID Manufacturer. Name 1… 1 Contact. Name Phone Address Zip. Code City. ID Balance. Due Manufacturer. Trans Purchase. Item Purchase. ID Component. ID Price. Paid Quantity. Received 1…* 0…* Manufacturer. ID Transaction. Date Reference Employee. ID Amount Description 0…* 1… 1 Assembly: : Component. ID Manufacturer. ID 0…* Product. Number

Rolling Thunder: Location Sales: : Customer. ID … City. ID 1… 1 0…* Sales: : Retail. Store. ID … City. ID City 1… 1 City. ID 1… 1 Zip. Code 1… 1 City State 1… 1 Area. Code Population 1990 Population 1980 Country Latitude Longitude 0…* 0… 1 State. Tax. Rate State Tax. Rate Employee: : Employee. ID … City. ID Purchasing: : Manufacturer. ID … 0…* City. ID

Rolling Thunder: Employee 1… 1 Serial. Number … Employee. ID Ship. Employee Frame. Assembler Painter 0…* 0…* Bicycle: : Bike. Parts Serial. Number Component. ID … Employee. ID 0…* worker Employee. ID 1… 1 Taxpayer. ID 1… 1 Last. Name First. Name Home. Phone Address Zip. Code 0…* City. ID Date. Hired Date. Released Current. Manager 0… 1 manager Salary. Grade Salary Title Work. Area manages Bicycle: : Bicycle Employee Purchasing: : Purchase. Order Purchase. ID … Employee. ID

Q ue st io ns

Homework A dentist who runs a small office by himself, three hygienists and receptionist want a database to schedule appointments. He has a commercial billing system to handle payments and insurance but appointments are currently written on paper form(date & time, Dr. name, patient name, fee and procedure [i. e. cleaning, X-ray, filling and so on. . . ]). He wants to add a little more detail and list all the procedures planned, the estimated fee for each procedure, and the estimated insurance coverage. The base fees should be stored with the procedures, but the insurance amounts are highly variable and will be entered by the receptionist who can estimates from the billing system. The system also has to track the patient’s phone, email and how he/she wants to be notified. Eventually, it could send e-mail messages to patients as remainders. Create the class diagram for this case.