cingular wireless Jennifer Conanan Ilya Dvoiris Tariq Shaikh

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

Overview overview • • company profile proposal eer diagram Company Profile Proposal EER Diagram Relational Schema Normalization Queries Conclusion relational schema queries normalization queries conclusion

Company Profile overview company profile proposal eer diagram • 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 Bell. South. SBC owns 60 percent of the company and Bell. South owns 40 percent, based on the value of the assets both contributed to the venture. relational schema queries normalization queries conclusion

Project Proposal overview company profile proposal eer diagram • 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 relational schema queries normalization queries conclusion

overview company profile proposal eer diagram EER Diagram relational schema queries normalization queries conclusion

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

overview company profile proposal eer diagram Relational Schema relational schema queries normalization queries conclusion

1 Location LID address city state zip country description period_between_maintenance last_date_of_maintenance X Y 11 Field. Tech_Employee EID FTID 2 Antenna warehouse. ID type range frequency 3 Battery warehouse. ID type overview 4 Location_Of_Part warehouse. ID LID 13 Field. Tech. Tests. Part warehouse. ID FTID 6 Combiner warehouse. ID 14 Inventory IID warehouse. ID Time. In Time. Out company profile 16 Job_Description description specialtool 5 Cabinet warehouse. ID type LID Range 12 Field. Tech. Uses. Truck warehouse. ID FTID mileage 15 Job JID LID FTID starttime endtime proposal eer diagram 17 Made. By warehouse. ID MID 18 Manufacturer MID name email fax address city state zip country 19 New warehouse. ID warranty price 20 Order OID EID DID warehouse. ID date 21 Truck warehouse. ID make model year lastdateofmaintenance Period. Between. Maintenance 7 Distributor DID companyname contactname phone email fax fixedordercost leadtime desc 22 Part. Replacement Part. Replaced. ID Replacing. Part. ID JID 8 Distributor_Represents_Manufacturer 23 Part. Type DID MID warehouse. ID PID DID partname holding. Cost length width height 9 Employee EID ssn fname lname mname salary hiredate phone email in_out 10 Field. Tech. Specialization schema FTIDrelational Specialization queries 25 normalization Radio warehouse. ID 31 Hicap warehouse. ID 24 Product warehouse. ID MID OID 28 V 3 warehouse. ID 26 Refurbished queries warehouse. ID warranty price 29 V 4 warehouse. ID 30 Warehouse. Worker EID WID 27 Signal. Amplifier conclusion warehouse. ID

Normalization overview company profile proposal eer diagram Job JID LID FTID startdate enddate specialtool description FD 1 2 nd Normal Form FD 2 Job JID LID FTID startdate enddate description Job_Description specialtool description relational schema queries normalization queries 3 rd Normal Form conclusion

Queries overview company profile proposal eer diagram 1. Service coverage 2. Economic Order Quantity 3. Scheduled Maintenance of Location 4. Special tools 5. Field Technician Specialization 6. relational schema queries normalization queries conclusion

Coverage Query overview company profile proposal eer diagram 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. relational schema queries normalization queries conclusion

Coverage Query overview company profile proposal eer diagram • 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 relational schema queries normalization queries conclusion

Coverage Query overview - - - company profile proposal eer diagram 4. 1 Calculate Distance SELECT LC. LID, (Sqr((LC. X-Xparameter)^2 + (LC. Y-Yparameter)^2)) AS DIST, LC. range FROM Locationsof. Cabinets AS LC; 4. 2 Locations of Cabinets SELECT [Location]. [LID], [Location]. [X], [Location]. [Y], c. range FROM Location, Cabinet AS c WHERE c. LID = [Location]. [LID]; 4. 3 Coverage SELECT [CD. LID] AS LID, L. address, L. city FROM Location AS L, Calculate. Distances AS CD WHERE CD. Dist<CD. Range AND CD. LID = L. LID; relational schema queries normalization queries conclusion

Coverage Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

Coverage Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

EOQ Query overview company profile proposal eer diagram 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 = relational schema queries 2 * Ordering Cost * Demand Holding Cost normalization queries conclusion

EOQ Query overview company profile proposal eer diagram – 2. 1 Demand SELECT o. warehouseid, p. partname, o. date FROM [order] AS o, parttype AS p WHERE (((o. warehouseid)=p. warehouseid)); – 2. 2 Fixed Cost SELECT o. warehouseid, p. partname, o. date FROM [order] AS o, parttype AS p WHERE (((o. warehouseid)=p. warehouseid)); – 2. 3 Holding Cost SELECT DISTINCTROW sqr(1/[holdingcost]) AS holding, [pid], [did] FROM parttype; - 2. 4 Count SELECT count([warehouseid]) AS [count], [pid] FROM demand GROUP BY [pid]; relational schema queries normalization queries conclusion

EOQ Query overview company profile proposal eer diagram - 2. 4 EOQ PARAMETERS Forms![EOQ]!Beginning. Date. Time, Forms![EOQ]!Beginning. Date_plus_one_year Date. Time; SELECT DISTINCT PT. PID, (([holdingcost]. [holding])*([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!Beginning. Date<=[demand]. [date] And [Forms]![EOQ]!Beginning. Date_plus_one_year>=[demand]. [date] And [count]. [pid]=[holdingcost]. [pid] And i. pid=[holdingcost]. [pid]; relational schema queries normalization queries conclusion

EOQ Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

EOQ Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

Scheduled Maintenance Query overview company profile proposal eer diagram 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] Date. Time; SELECT [lid], [address], [city], [state], [zip], [country], ([Last. Dateof. Maintainence]+[Period. Btw. Main]) AS Scheduled_Date FROM location WHERE [Forms]![date Dialog]!Date>=([Last. Dateof. Maintainence]+[Period. Btw. Main]); relational schema queries normalization queries conclusion

Scheduled Maintenance Query overview company profile proposal eer diagram 1. relational schema queries normalization queries conclusion

Scheduled Maintenance Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

Special Tool Query overview company profile proposal eer diagram 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. specialtool FROM jobdesciption AS JD WHERE Job. Desc = JD. description; relational schema queries normalization queries conclusion

Special Tool Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

Special Tool Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

FT Specialization Query overview company profile proposal eer diagram 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. phone FROM employee As E, Fieldtech. Specialization AS FTS, Field. Tech. Employee AS FTE WHERE FTE. EID=E. EID And FTE. FTID=FTS. FTID And specific = FTS. specialization; relational schema queries normalization queries conclusion

FT Specialization Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

FT Specialization Query overview relational schema queries company profile normalization proposal queries eer diagram conclusion

Conclusion overview • • company profile proposal eer diagram Company Profile Proposal EER Diagram Relational Schema Normalization Queries Conclusion relational schema queries normalization queries conclusion

Any questions?

Any questions?