cingular wireless

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?