140 likes | 417 Views
MIS 327. Database Management system. Dr. Monther Tarawneh Week 6: Database Design : Example Rolling Thunder. Main objects. The rolling Thunder Bicycle Company builds custom bicycles.
E N D
MIS 327 Database Management system Dr. Monther Tarawneh Week 6: Database Design: Example Rolling Thunder MIS 327: DBMS Dr. Monther Tarawneh
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. • Suppliersare paid only after receipt of shipments.
Rolling Thunder: Top-Level Sales Bicycle Assembly Employee Location Purchasing Based on the main activities. We need to look at each one separately
Rolling Thunder: Sales Customer Bicycle::Bicycle 1…1 CustomerID Phone FirstName LastName Address ZipCode CityID BalanceDue BicycleID … CustomerID StoreID … 1…1 0…* 0…* Retail Store Customer Transaction StoreID StoreName Phone ContactFirstName ContactLastName Address ZipCode CityID 0…1 CustomerID TransactionDate EmployeeID Amount Description Reference 0…*
Rolling Thunder: Bicycle ModelType Bicycle BicycleTubeUsed 1…1 1…1 ModelType Description SerialNumber CustomerID ModelType PaintID FrameSize OrderDate StartDate ShipDate ShipEmployee FrameAssembler Painter Construction WaterBottleBrazeOn CustomName LetterStyleID StoreID EmployeeID TopTube ChainStay … 1…* SerialNumber TubeID Quantity 1…1 0…* 0…* Paint 1…1 PaintID ColorName ColorStyle ColorList DateIntroduced DateDiscontinued BikeParts 0…* SerialNumber ComponentID SubstituteID Location Quantity DateInstalled EmployeeID 0…* LetterStyle 1…1 LetterStyleID Description
Rolling Thunder: Assembly Component 1…1 1…1 Bicycle::BikeParts ComponentID ManufacturerID ProductNumber Road Category Length Height Width Description ListPrice EstimatedCost QuantityOnHand GroupComponents 0…* SerialNumber ComponentID ... GroupID ComponentID 0…* 0…* 0…* Groupo GroupID GroupName BikeType 1…1 ComponentName Bicycle:: BicycleTubeUsed TubeMaterial 1…1 1…1 ComponentName AssemblyOrder Description TubeID Material Description Diameter … SerialNumber TubeID Quantity 0…*
Rolling Thunder: Purchasing PurchaseOrder Manufacturer 1…1 1…1 1…1 PurchaseID EmployeeID ManufacturerID TotalList ShippingCost Discount OrderDate ReceiveDate AmountDue ManufacturerID ManufacturerName ContactName Phone Address ZipCode CityID BalanceDue 1…1 0…* ManufacturerTrans 0…* ManufacturerID TransactionDate Reference EmployeeID Amount Description PurchaseItem Assembly:: Component 1…* PurchaseID ComponentID PricePaid Quantity QuantityReceived 1…1 0…* ComponentID ManufacturerID ProductNumber 0…*
Rolling Thunder: Location Sales:: Customer Employee:: Employee City 1…1 1…1 CustomerID … CityID EmployeeID … CityID CityID ZipCode City State AreaCode Population1990 Population1980 Country Latitude Longitude 0…* 1…1 1…1 1…1 Sales:: RetailStore Purchasing:: Manufacturer StoreID … CityID ManufacturerID … CityID 0…* 0…* StateTaxRate 0…1 State TaxRate
Rolling Thunder: Employee Employee 1…1 0…* worker Bicycle:: Bicycle EmployeeID TaxpayerID LastName FirstName HomePhone Address ZipCode CityID DateHired DateReleased CurrentManager SalaryGrade Salary Title WorkArea 1…1 1…1 Purchasing:: PurchaseOrder SerialNumber … EmployeeID ShipEmployee FrameAssembler Painter 0…* PurchaseID … EmployeeID 0…* 0…* manages 0…* 0…* Bicycle:: BikeParts 0…1 manager SerialNumber ComponentID … EmployeeID 0…*
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, feeand 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.