1 / 32

cingular wireless

cingular wireless. Jennifer Conanan Ilya Dvoiris Saurabh Sangla Tariq Shaikh Nancy Tariga Yenny Usman Wen Wang. Overview. Company Profile Proposal EER Diagram Relational Schema Normalization Queries Conclusion. Company Profile.

wesley
Download Presentation

cingular wireless

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. cingular wireless Jennifer Conanan Ilya Dvoiris Saurabh Sangla Tariq Shaikh Nancy Tariga Yenny Usman Wen Wang

  2. Overview • Company Profile • Proposal • EER Diagram • Relational Schema • Normalization • Queries • Conclusion

  3. Company Profile • Cingular Wireless is the second largest wireless company in the U.S. • Cingular Wireless is a joint venture between the domestic wireless divisions of SBC and BellSouth. SBC owns 60 percent of the company and BellSouth owns 40 percent, based on the value of the assets both contributed to the venture.

  4. Project Proposal • Design a database system for a Cingular Wireless Warehouse in Dublin, California • Keep track of about 50 various electronic components of the transmission boxes • Determine the geographical placement of the transmission boxes

  5. EER Diagram

  6. (0,N) has (1,N) (0,N) has longitude/latitude V3 AID ANTENA NEW REFURBISHED (1,1) (1,N) (1,1) by V4 NID RID GEOGRAPHICAL COORDINATES d attached to d (0,N) HI-CAP (1,N) (1,N) (1,1) (1,1) made by MANUFACTURER (1,1) d PRODUCT (1,1) placed at has LOCATION CABINET CID (1,N) MID (1,N) (1,N) (1,1) (0,N) is type of represents requests PART REPLACEMENT has (1,1) DID (1,N) (0,N) what type (1,N) (1,1) INVENTORY ITEM DISTRIBUTOR OTHER RID PART TYPE (1,N) OID (0,N) RADIO PID (1,1) (0,N) includes from BID ORDER BATTERY (1,1 ) d connected to (1,1) ComID (1,1) COMBINER TRUCK TID d (2,2) tests (0,N) placed by (0,N) SIGNAL AMPLIFIER SAID WIRES done at WID uses (0,N) (0,N) (0,N) (0,N) (0,N) assigned to JOB FIELD TECH d EMPLOYEE (1,1) EER Diagram OTHER JID WAREHOUSE WORKER

  7. Relational Schema

  8. 1 Location LID address city state zip country description period_between_maintenance last_date_of_maintenance X Y 11 FieldTech_Employee EIDFTID 12 FieldTechUsesTruck warehouseIDFTID mileage 2 Antenna warehouseID type range frequency 3 Battery warehouseID type 13 FieldTechTestsPart warehouseIDFTID 14 Inventory IIDwarehouseID TimeIn TimeOut 15 Job JID LID FTID starttime endtime 4 Location_Of_Part warehouseID LID 16 Job_Description descriptionspecialtool 17 MadeBy warehouseID MID 19 New warehouseID warranty price 18 Manufacturer MID name email fax address city state zip country 20 Order OID EID DIDwarehouseID date 5 Cabinet warehouseID type LID Range 31 Hicap warehouseID 6 Combiner warehouseID 21 Truck warehouseID make model year lastdateofmaintenance PeriodBetweenMaintenance 22 PartReplacement PartReplacedID ReplacingPartID JID 7 Distributor DID companyname contactname phone email fax fixedordercost leadtime desc 29 V4 warehouseID 8 Distributor_Represents_Manufacturer DIDMID 23 PartType warehouseID PID DID partname holdingCost length width height 24 Product warehouseID MID OID 28 V3 warehouseID 30 WarehouseWorker EID WID 9 Employee EID ssn fname lname mname salary hiredate phone email in_out 10 FieldTechSpecialization FTID Specialization 25 Radio warehouseID 26 Refurbished warehouseID warranty price 27 SignalAmplifier warehouseID

  9. Normalization Job JID LID FTID startdate enddate specialtool description 2nd Normal Form FD1 FD2 Job JID LID FTID startdate enddate description 3rd Normal Form Job_Description specialtool description

  10. Queries • Service coverage • Economic Order Quantity • Scheduled Maintenance of Location • Special tools • Field Technician Specialization

  11. Coverage Query Checks to see if a certain area has Cingular coverage; if it does, lists the Location ID, along with the address and city of all transmission boxes that cover the specified area.

  12. Coverage Query • Identify transmission towers and radius of coverage • Calculate the distance between the specified point and each tower • Identify if any tower(s) provide service to the specified point

  13. Coverage Query - 4.1 Calculate Distance SELECT LC.LID, (Sqr((LC.X-Xparameter)^2 + (LC.Y-Yparameter)^2)) AS DIST,LC.rangeFROM LocationsofCabinets AS LC; - 4.2 Locations of CabinetsSELECT [Location].[LID], [Location].[X], [Location].[Y], c.rangeFROM Location, Cabinet AS cWHERE c.LID = [Location].[LID]; - 4.3 CoverageSELECT [CD.LID] AS LID,L.address, L.cityFROM Location AS L, CalculateDistances AS CDWHERE CD.Dist<CD.Range AND CD.LID = L.LID;

  14. Coverage Query

  15. Coverage Query

  16. EOQ Query • Lists the PID, part name, the EOQ, and the current inventory level of each item. • Minimizes the amount of orders so that total variable costs required to order and hold inventory are balanced • EOQ = 2 * Ordering Cost * Demand • Holding Cost

  17. EOQ Query • 2.1 DemandSELECT o.warehouseid, p.pid, p.partname, o.date FROM [order] AS o, parttype AS p WHERE (((o.warehouseid)=p.warehouseid)); • 2.2 Fixed CostSELECT o.warehouseid, p.pid, p.partname, o.date FROM [order] AS o, parttype AS p WHERE (((o.warehouseid)=p.warehouseid)); • 2.3 Holding CostSELECT DISTINCTROW sqr(1/[holdingcost]) AS holding, [pid], [did] FROM parttype; - 2.4 Count SELECT count([warehouseid]) AS [count], [pid] FROM demand GROUP BY [pid];

  18. EOQ Query - 2.4 EOQPARAMETERS Forms![EOQ]!BeginningDate DateTime, Forms![EOQ]!BeginningDate_plus_one_year DateTime; SELECT DISTINCT PT.PID, (([holdingcost].[holding])*([count].[count])*([fixedcost].[sqrt_fc])) AS EOQ, i.Inventory, PT.PARTNAME FROM holdingcost, demand, parttype AS pt, Fixedcost, INVENTORY_levels AS i, [count] WHERE pt.pid=[demand].[pid] And [holdingcost].[pid]=[demand].[pid] And [holdingcost].[did]=[fixedcost].[did] And [Forms]!EOQ!BeginningDate<=[demand].[date] And [Forms]![EOQ]!BeginningDate_plus_one_year>=[demand].[date] And [count].[pid]=[holdingcost].[pid] And i.pid=[holdingcost].[pid];

  19. EOQ Query

  20. EOQ Query

  21. Scheduled Maintenance Query Lists locations and their addresses that are scheduled for maintenance by a given date Allows for scheduling of field technicians to be more efficient and to keep proper inventory levels of parts required more maintenance jobs PARAMETERS Forms![date Dialog]![Date] DateTime; SELECT [lid], [address], [city], [state], [zip], [country], ([LastDateofMaintainence]+[PeriodBtwMain]) AS Scheduled_Date FROM location WHERE [Forms]![date Dialog]!Date>=([LastDateofMaintainence]+[PeriodBtwMain]);

  22. Scheduled Maintenance Query

  23. Scheduled Maintenance Query

  24. Special Tool Query Lists the special tools needed for each type of job Allows the technician do the job more efficiently by immediately knowing which tools are needed without having to retrieve unnecessary ones. SELECT JD.specialtoolFROM jobdesciption AS JDWHERE JobDesc = JD.description;

  25. Special Tool Query

  26. Special Tool Query

  27. FT Specialization Query Retrieve the FT id, last name, first name, email and phone number of those who are qualified to do a specific job Allows warehouse workers to quickly find which technician is able to repair specific parts SELECT E.EID, E.FNAME, E.LNAME, E.email, E.phoneFROM employee As E, FieldtechSpecialization AS FTS, FieldTechEmployee AS FTEWHERE FTE.EID=E.EID And FTE.FTID=FTS.FTID And specific = FTS.specialization;

  28. FT Specialization Query

  29. FT Specialization Query

  30. Conclusion • Company Profile • Proposal • EER Diagram • Relational Schema • Normalization • Queries • Conclusion

  31. Any questions?

  32. Any questions?

More Related