Transcript MIS 327
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 CustomerID Phone FirstName LastName Address ZipCode CityID BalanceDue 1…1 0…* 0…* Retail Store Customer Transaction CustomerID TransactionDate EmployeeID Amount Description Reference 0…* StoreID StoreName Phone ContactFirstName ContactLastName Address ZipCode CityID 0…1 BicycleID … CustomerID StoreID … Rolling Thunder: Bicycle ModelType ModelType Description Paint PaintID ColorName ColorStyle ColorList DateIntroduced DateDiscontinued LetterStyle LetterStyleID Description Bicycle 1…1 0…* 1…1 0…* 0…* 1…1 SerialNumber CustomerID ModelType PaintID FrameSize OrderDate StartDate ShipDate ShipEmployee FrameAssembler Painter Construction WaterBottleBrazeOn CustomName LetterStyleID StoreID EmployeeID TopTube ChainStay … 1…1 1…1 BicycleTubeUsed 1…* SerialNumber TubeID Quantity 0…* BikeParts SerialNumber ComponentID SubstituteID Location Quantity DateInstalled EmployeeID Rolling Thunder: Assembly 1…1 Bicycle::BikeParts SerialNumber ComponentID ... Bicycle:: BicycleTubeUsed SerialNumber TubeID Quantity 0…* 1…1 0…* Component ComponentID ManufacturerID ProductNumber Road Category Length Height Width Description ListPrice EstimatedCost QuantityOnHand TubeMaterial TubeID Material Description Diameter … 1…1 0…* 0…* GroupComponents GroupID ComponentID 0…* Groupo GroupID 1…1 GroupName BikeType 1…1 ComponentName ComponentName AssemblyOrder Description Rolling Thunder: Purchasing PurchaseOrder PurchaseID EmployeeID ManufacturerID TotalList ShippingCost Discount OrderDate ReceiveDate AmountDue 1…1 1…1 0…* Manufacturer ManufacturerID ManufacturerName ContactName Phone Address ZipCode CityID BalanceDue ManufacturerTrans PurchaseItem PurchaseID ComponentID PricePaid Quantity QuantityReceived 1…* 0…* ManufacturerID TransactionDate Reference EmployeeID Amount Description 1…1 1…1 0…* 1…1 Assembly:: Component ComponentID ManufacturerID 0…* ProductNumber Rolling Thunder: Location Sales:: Customer CustomerID … CityID 1…1 0…* Sales:: RetailStore StoreID … CityID City 1…1 CityID 1…1 ZipCode 1…1 City State 1…1 AreaCode Population1990 Population1980 Country Latitude Longitude 0…* 0…1 StateTaxRate State TaxRate Employee:: Employee EmployeeID … CityID Purchasing:: Manufacturer ManufacturerID … 0…* CityID Rolling Thunder: Employee Bicycle:: Bicycle SerialNumber … EmployeeID ShipEmployee FrameAssembler Painter 1…1 0…* 0…* 0…* 0…* Bicycle:: BikeParts SerialNumber ComponentID … EmployeeID 0…* Employee EmployeeID TaxpayerID LastName FirstName HomePhone Address ZipCode CityID DateHired DateReleased CurrentManager SalaryGrade Salary Title WorkArea 0…* worker 1…1 manages 1…1 0…1 manager Purchasing:: PurchaseOrder 0…* PurchaseID … EmployeeID 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.