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)
has
(0,N)
REFURBISHED
NEW
ANTENA
V3
longitude/latitude
AID
(1,1)
(1,N)
by
(1,1)
V4
NID
RID
GEOGRAPHICAL COORDINATES
d
attached to
d
(0,N)
HI-CAP
(1,N)
(1,N)
(1,1)
(1,1)
made by
MANUFACTURER
PRODUCT
d
(1,1)
placed at
(1,1)
has
CABINET
LOCATION
(1,N)
(1,N)
CID
MID
(1,N)
(1,1)
(0,N)
represents
is type of
requests
has
PART REPLACEMENT
(1,1)
DID
(1,N)
(0,N)
what type
(1,N)
INVENTORY ITEM
(1,1)
DISTRIBUTOR
OTHER
RID
PART TYPE
(1,N)
(0,N)
OID
RADIO
(1,1)
PID
includes
(0,N)
from
ORDER
BID
BATTERY
(1,1 )
connected to
(1,1)
d
(1,1)
ComID
TRUCK
COMBINER
TID
d
(2,2)
tests
placed by
(0,N)
(0,N)
SIGNAL AMPLIFIER
WIRES
SAID
done at
WID
uses
(0,N)
(0,N)
(0,N)
(0,N)
(0,N)
assigned to
EMPLOYEE
JOB
FIELD TECH
d
(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 EID FTID
12 FieldTechUsesTruck warehouseID FTID mileage
2 Antenna warehouseID type range frequency
3 Battery warehouseID type
13 FieldTechTestsPart warehouseID FTID
14 Inventory IID warehouseID TimeIn TimeOut
15 Job JID LID FTID starttime endtime
4 Location_Of_Part warehouseID LID
16 Job_Description description specialtool
17 MadeBy warehouseID MID
19 New warehouseID warranty price
18 Manufacturer MID name email fax address city
state zip country
20 Order OID EID DID warehouseID 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 DID MID
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
26 Refurbished warehouseID warranty price
25 Radio warehouseID
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.DistltCD.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.piddemand.pid And
  holdingcost.piddemand.pid And
  holdingcost.didfixedcost.did And
  Forms!EOQ!BeginningDateltdemand.date And
  Forms!EOQ!BeginningDate_plus_one_yeargtdemand
  .date And count.pidholdingcost.pid
  And i.pidholdingcost.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, (LastDateofMaintaine
ncePeriodBtwMain) AS Scheduled_Date FROM
location WHERE Forms!date Dialog!Dategt(LastD
ateofMaintainencePeriodBtwMain)
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, FieldtechSpecializatio
n AS FTS, FieldTechEmployee AS FTEWHERE
FTE.EIDE.EID And FTE.FTIDFTS.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?