Christopher Knapp
University of Akron, Fall 2011
Database Management
Final Project
Contents
Page 1Solution to Problem 1
Page 2Solution to Problem 2
Page 3Solution to Problem 3
Page 4Solution to Problem 4
Page 6Solution to Problem 5
Page 8Solution to Problem 6
Page 10Solution to Problem 7
Page 12Solution to Problem 8
Page 13Solution to Problem 9
Page 14Full Code
Page | 1
Solution to Problem 1
Below are the Schemas and Entity/Relationship diagram used in the database. The Transactions relation contains all “sales/returns” transactions that were completed successfully (sufficient for Bonus problem 9). Relations were also created for each of the two relationships. Note the Referential Integrity constraints in the two relationships; if a transaction’sSalesmenID or PartNumber did not exist in the Salesmen or Inventory tables, an error would occur, and an entry would not be made in the Transactions table. Furthermore, the database structure makes it impossible for an entry to have more than one value for these variables. The combination of the last two statements implies referential integrity (exactly one value for each transaction).
Inventory(PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)
Salesmen(ID, SalesmenName, YTDCommission, CommissionRate, Office, Phone, YTDHours)
Transactions(TransactionDay, TransactionNumber, CustomerID, TransactionQuantity)
SoldBy(TransactionDay, TransactionNumber, SalesmenID)
PartSold(TransactionDay, TransactionNumber, PartNumber)
Solution to Problem 2
Completed Problem 2 – See full code for details.
Solution to Problem 3
Completed Problem 2 – See full code for details. Note the use of “style” through procedures for extra credit.
Solution to Problem 4
PART A
Derivation of formula:
YTDCommission = CommissionRate * YTDSales
YTDCommission / CommissionRate = YTDSales
SELECT ID, salesmenName, YTDCommission/CommissionRate AS YTDSales
FROM Salesmen
PART B
The East Office had the most sales.
SELECTSUM(YTDCommission/CommissionRate)AS YTDSales, Office
FROM Salesmen
GROUPBY Office
ORDERBY YTDSales
PART C
There were five rejected transactions for Day 1:
Sale #6:Not enough inventory to support this sale
Sale #8:Part does not exist in inventory
Sale #9:Part does not exist in inventory
Sale #19:Part does not exist in inventory
Purchase #7:Not enough inventory to support this sale
PART D
Four parts resulted.
SELECT PartNumber
FROM Inventory
WHERE PartDescription LIKE'%Long Reach%'
Solution to Problem 5
PART A
Jacob (7001) has the highest YTD Sales.
Derivation of formula:
YTDCommission = CommissionRate * YTDSales
YTDCommission / CommissionRate = YTDSales
SELECT ID, salesmenName, YTDCommission/CommissionRate AS YTDSales
FROM Salesmen
ORDERBY YTDSales
PART B
The total value in inventory is 6,550,980.96.
SELECTSUM(Price*Quantity)AS TotalValue
FROM Inventory;
PART C
There were five rejected transactions for Day 2:
Sale #2:Not enough inventory to support this sale
Sale #9:Not enough inventory to support this sale
Sale #20:Not enough inventory to support this sale
Sale #21:Not enough inventory to support this sale
Sale #22:Not enough inventory to support this sale
Sale #24:Not enough inventory to support this sale
Purchase #4:Not enough inventory to support this Purchase
Purchase #8:Can’t write off a new part
Solution to Problem 6
PART A
Jacob (7001) has the highest YTD Commission at 25723.99.
SELECT SalesmenName, ID, YTDCommission
FROM Salesmen
ORDERBY YTDCommission DESC;
PART B
Jacob (7001) has the greatest number of hours – 515 hours.
SELECT SalesmenName, ID, YTDHours
FROM Salesmen
ORDERBY YTDCommission DESC;
PART C
There were five rejected transactions for Day 3:
Sale #1:Not enough inventory to support this sale
Sale #6:Not enough inventory to support this sale
Sale #7:Not enough inventory to support this sale
Sale #13:Part does not exist in inventory
Purchase #7:Not enough inventory to support this Purchase
Solution to Problem 7
PART A
CREATEVIEW prob7 AS
SELECT PartType, PartFamily, Quantity, Price
FROM Inventory;
PART B
SELECT PartType,SUM(Quantity*Price)ASValue
FROM prob7
GROUPBY PartType
ORDERBYValueDESC;
PART C
SELECT PartFamily,SUM(Quantity*Price)AS TotalValue
FROM prob7
GROUPBY PartFamily
HAVINGSUM(Quantity*Price)<20000;
Solution to Problem 8
Completed Problem 8 – See full code for details.
Solution to Problem 9
Problem 9 was implemented in the database – See full code for details.
PART A
SELECT INV.PartType,SUM(TRANS.TransactionQuantity)AS TotalQuantity
FROM Inventory INV, Transactions TRANS, PartSold CONN
WHERE INV.PartNumber = CONN.PartNumber
AND CONN.TransactionDay=TRANS.TransactionDay
AND CONN.TransactionNumber=TRANS.TransactionNumber
GROUPBY INV.PartType
ORDERBY TotalQuantity DESC;
PART B
With the current database structure, this can be queried (the wording on this problem was slightly ambiguous, but this is how our group interpreted it):
SELECT INV.PartType,SUM(TRANS.TransactionQuantity*INV.Price)AS TotalSalesSinceDay1
FROM Inventory INV, Transactions TRANS, PartSold CONN
WHERE INV.PartNumber = CONN.PartNumber
AND CONN.TransactionDay=TRANS.TransactionDay
AND CONN.TransactionNumber=TRANS.TransactionNumber
GROUPBY INV.PartType
ORDERBY TotalSalesSinceDay1 DESC;
Full Code
CREATETABLE Transactions
(
TransactionDay INT,
TransactionNumber INT,
CustomerID INT,
TransactionQuantity INT,
PRIMARYKEY(TransactionDay, TransactionNumber)
);
CREATETABLE SoldBy
(
TransactionDay INT,
TransactionNumber INT,
SalesmenID INT,
PRIMARYKEY(TransactionDay, TransactionNumber)
);
CREATETABLE PartSold
(
TransactionDay INT,
TransactionNumber INT,
PartNumber VARCHAR(30),
PRIMARYKEY(TransactionDay, TransactionNumber)
);
CREATETABLE Inventory
(
PartNumber VARCHAR(30)PRIMARYKEY,
Quantity INT,
PartDescription VARCHAR(100),
PartType VARCHAR(25),
PartFamily VARCHAR(25),
Price DECIMAL(6,2)
);
CREATETABLE Salesmen
(
YTDHours INT,
ID INTPRIMARYKEY,
SalesmenName VARCHAR(30),
YTDCommission DECIMAL(10,2),
CommissionRate DECIMAL(2,2),
Office VARCHAR(10),
Phone BIGINT
);
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('3660-CHAS&BKPLN=',13,'^3660 chassis, fans, and enterprise backplane spare','ACCESSORY','3600', 358.16 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CISCO3660-MB-1FE=',4,'^Single Port Fast Ethernet Motherboard Spare for Cisco 3660','ACCESSORY','3600', 360.30 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACC245LA-R',209,'2.4 and 5 GHz Lightning Arrestor, RP-TNC Connector','ACCESSORY','AIRANT', 42.33 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2430V-R=',50,'2.4-GHz 3 dBi Triple Omni Antenna 3 RP-TNC; Spare','ACCESSORY','AIRANT', 30.12 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2440NV-R=',24,'2.4 GHz 4 dBi 802.11n Omni wall mount antenna','ACCESSORY','AIRANT', 59.85 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2450S-R=',2,'2.4 GHz, 5 dBi, 135-degree Sector w/ RP-TNC Connector','ACCESSORY','AIRANT', 28.63 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2450V-N=',127,'2.4 GHz, 5.0 dBi Omni Ant. with N Connect','ACCESSORY','AIRANT', 19.63 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2451NV-R=',91,'2.4 GHz 2.5dBi/5 GHz 3.5dBi 802.11n Omni Antenna, RP-TNC','ACCESSORY','AIRANT', 62.81 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2451V-R=',19,'2.4 GHz, 3 dBi; 5 GHz,3.5 dBi Omni Ant w/RP-TNC Connectors','ACCESSORY','AIRANT', 47.28 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2452V-R=',31,'2.4 GHz 5.2 dBi Diversity pillar mount ant,RP-TNC Connectors','ACCESSORY','AIRANT', 40.52 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT2460NP-R=',38,'2.4 GHz 6 dBi 802.11n directional antenna','ACCESSORY','AIRANT', 44.74 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT5140NV-R=',11,'5 GHz 4 dBi 802.11n Omni wall mount antenna','ACCESSORY','AIRANT', 59.87 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT5140V-R=',43,'5-GHz 4 dBi Triple Omni Antenna 3 RP-TNC; Spare','ACCESSORY','AIRANT', 28.52 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ANT5160NP-R=',95,'5 GHz 6 dBi 802.11n directional antenna','ACCESSORY','AIRANT', 40.33 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACC2662',14,'Antenna Mount for use with ANT1949 and ANT5195','ACCESSORY','AIRCMN', 15.37 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCBRGB=',1,'Aironet 1400 Series Grounding Block','ACCESSORY','AIRCMN', 3.77 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACC-CLIP-20=',3,'Converter Clips:Small grid ceilings. For 10 APs or Antennas','ACCESSORY','AIRCMN', 13.35 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCMFM1400=',58,'Aironet 1400 Multi Function Mount','ACCESSORY','AIRCMN', 20.52 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCPMK1500',3,'NOT USED Aironet 1500 Pole Mount Kit','ACCESSORY','AIRCMN', 28.68 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCPMK1500=',14,'Aironet 1500 Pole Mount Kit','ACCESSORY','AIRCMN', 40.06 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCPMK1520=',17,'1520 Series Pole Mount Kit','ACCESSORY','AIRCMN', 74.84 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCRWM1400',3,'Aironet 1400 Roof/Wall Mount Kit','ACCESSORY','AIRCMN', 6.73 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-ACCWAMK1300=',27,'Aironet 1300 Wall Mount Kit','ACCESSORY','AIRCMN', 15.03 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-CAB005LL-R',51,'5 ft Low Loss RF cable w/RP-TNC connectors','ACCESSORY','AIRCMN', 16.92 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-CAB020LL-R',37,'20 ft LOW LOSS CABLE ASSEMBLY W/RP-TNC CONNECTORS','ACCESSORY','AIRCMN', 19.75 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-CAB050LL-R',18,'50 ft. LOW LOSS CABLE ASSEMBLY W/RP-TNC CONNECTORS','ACCESSORY','AIRCMN', 30.37 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-CAB100ULL-R',12,'100 ft. ULTRA LOW LOSS CABLE ASSEMBLY W/RP-TNC CONNECTORS','ACCESSORY','AIRCMN', 96.33 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-CAB150ULL-R',47,'150 ft. ULTRA LOW LOSS CABLE ASSEMBLY W/RP-TNC CONNECTORS','ACCESSORY','AIRCMN', 136.00 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('RM-RGD-ETSI=',55,'ETSI rack-mount kit for Cisco CGS 2520','ACCESSORY','CGS2500', 16.61 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CSS8-IDM-MEM-HD=',1,'^Spare Integrated Disk Module (IDM) - Hard Drive','ACCESSORY','CSS', 439.54 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CTS-P52D-GRILLKIT=',1,'Profile 52 dual grill kit','ACCESSORY','CTSMPS', 152.97 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('RCKMNT-REC-1.5RU=',6,'RECESSED 1.5RU RACK MOUNT FOR 3550,3750','ACCESSORY','DSBUOTH', 41.67 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-7937-MIC-KIT=',8,'Microphone Kit (7 ft) for 7937','ACCESSORY','PHONE', 87.45 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-89/9900-LK-K-C=',4,'Locking Wallmount Kit for 8900 or 9900 with KEM Charcoal','ACCESSORY','PHONE', 12.25 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-89/9900-LWMK-C=',125,'Locking Wallmount Kit for 8900 and 9900 Series, Charcoal','ACCESSORY','PHONE', 11.52 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-89/9900-UHSCL=',152,'Bundle of 40 PK Char Slim HS, C Cable','ACCESSORY','PHONE', 0.80 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-WMK-C-6900=',59,'Charcoal Locking Wallmount Kit for 6900 Series','ACCESSORY','PHONE', 11.08 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('MAS-7200-CBLMGMT=',2,'Cisco NPE-G1/NPE-G2 Cable Management Bracket','ACCESSORY','UBR7200', 27.46 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('N20-CAK=',1,'Access. kit for 5108 Blade Chassis incl Railkit, KVM dongle','ACCESSORY','UCSB', 81.10 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('MNT-2PST-RACK=',45,'2-post rack shelf for WAVE 274 and WAVE 474','ACCESSORY','WAAS', 41.34 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-BATT-7920-EXT=',43,'Cisco 7920 Battery, Extended','ACCESSORY','WPHONE', 14.39 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-BATT-7920-STD=',15,'Cisco 7920 Battery, Standard','ACCESSORY','WPHONE', 12.61 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-DSKCH-BUN-7920',16,'^Cisco 7920 Desk Top Charger; Power Supply Not Included','ACCESSORY','WPHONE', 12.09 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CP-SHOULDER-7921G=',6,'Cisco 7921G Shoulder Strap','ACCESSORY','WPHONE', 6.06 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AIR-VPN-4100-K9=',1,'^VPN/enhanced security module for 4100 Series WLAN controller','APPSWIND','AIRCTI', 393.30 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WS-PB8-1FEFD-TX-32',1,'^SwitchProbe, One Port FE, FDX, TX, Eth Sdbnd, 32MB incl Tap','APPSWIND','CDICOLD', 5277.80 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WS-PB8-2FEHD-TX-32',2,'^SwitchProbe, Two Port FE, Half Duplx, TX, Eth Sideband, 32MB','APPSWIND','CDICOLD', 4837.80 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WS-PB8-2T1D-ETH-32',2,'^SwitchProbe, 2 Port T1/D WAN & Eth, 32MB incl RJ48 Tap','APPSWIND','CDICOLD', 6597.80 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WS-PB8-4FE-TX-64',1,'^SwitchProbe, Four Port FE, TX, Eth Sdbnd, 64MB incl FEFD Tap','APPSWIND','CDICOLD', 7917.80 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WS-PB8-HSSIETH-128',1,'^SwitchProbe, HSSI and Ethernet, 128MB, incl HSSI Tap','APPSWIND','CDICOLD', 8898.85 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WS-PROBE-FE-FD-FX',1,'^SwitchProbe, Fast Ethernet Full-Dup FX, w/Eth sideband, 16M','APPSWIND','CDICOLD', 6522.75 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CSACSE-1111-K9',4,'^Cisco Secure ACS 3.2 Solution; includes HW and SW','APPSWIND','CSACS', 1555.00 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CSACSE-1111-UP-K9',1,'^Upgrade from ACS 3.X, CSUNIX 2.X to Cisco 1111 w/ACS 3.2','APPSWIND','CSACS', 1555.00 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CWIPCSA-1SENSOR',90,'CiscoWorks IPC Sensor A Service Replacement 1 Sensor','APPSWIND','CUCMS', 199.86 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CWVPS-1110-HW',1,'^VLAN Policy Server 1110, HW Only for use w/URT, 110/220v','APPSWIND','SPMAOLD', 1160.87 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('CUWL-LIC',1,'CUWL Top Level','APPSWIND','UWLU',0.00 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15216-DCU-1550=',1,'DCF of -1550 ps/nm','ASSEMBLY','15216', 1005.53 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15216-FLB-2-42.9=',10,'ITU-100 GHz 2-Ch, FlexMod-1542.94, 1542.14','ASSEMBLY','15216', 372.33 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15310-CBLMGT=',5,'MA Optical and Front Access Electrical cable management.','ASSEMBLY','15310', 145.76 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15310-CL-SHIPKIT=',25,'Shelf install accessories','ASSEMBLY','15310', 94.66 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15310-DOOR-KIT=',36,'15310-MA Door Kit','ASSEMBLY','15310', 97.20 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-AEP=',1,'15454 Alarm Expansion Panel - ANSI support only','ASSEMBLY','15454', 397.04 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-AIR-RAMP=',18,'ONS 15454 Air Ramp / Baffle for the ANSI Chassis','ASSEMBLY','15454', 73.33 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-BLANK=',91,'Empty slot Filler Panel','ASSEMBLY','15454', 62.67 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-BRACKET-23=',8,'15454 23 INCH MOUNTING BRACKET FOR SA-HD AND DEEP DOOR','ASSEMBLY','15454', 161.43 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-EIA-AMP-A84=',1,'^Elect I/F, 84 AMP, A Side, I-Temp, EOS 05-2005(2481)','ASSEMBLY','15454', 538.75 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-HD-DD-KIT=',1,'15454 Deep Door Kit for use with SA-HD, SA-ANSI','ASSEMBLY','15454', 351.41 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454E-19IEC-KIT=',3,'Brackets/Hardware req. for 19in IEC Rack (IEC 297-2)','ASSEMBLY','15454E', 48.15 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454E-AIR-RAMP=',16,'ONS 15454 SDH Air Ramp comes with ETSI Rack Brackets','ASSEMBLY','15454E', 66.16 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-EAP=',12,'Ethernet Adapater Panel','ASSEMBLY','15454W', 150.56 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-EAP-MF=',8,'Ethernet Adapater Panel Mechanical Frame','ASSEMBLY','15454W', 150.21 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-FBR-STRG=',1,'Fiber Storage Shelf','ASSEMBLY','15454W', 216.00 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15454-MS-EXT-24=',8,'ONS 15454- EXTERNAL MSM SOLUTION- 24 PORTS-DC POWER','ASSEMBLY','15454W', 147.36 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15530-PWR-AC=',1,'^ONS 15530 120-240 VAC Power Supply','ASSEMBLY','15530', 331.48 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15500-XVRA-03B1=',3,'1G GE / FC / FICON - 1310nm SM','ASSEMBLY','15540', 62.30 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15500-XVRA-03B2=',5,'2G FC / FICON - 1310nm SM','ASSEMBLY','15540', 35.32 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15540-LCMB-1200=',5,'^ONS 15540 ESPx - Supports 4 Type I and II TSP w/o Splitter','ASSEMBLY','15540', 1270.90 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('15540-MDXD-08A0=',6,'^ONS 15540 ESPx - 8 Ch. Mux/Demux Band AB w/ OSC','ASSEMBLY','15540', 3616.25 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('PWR-3660-UNV-DC=',2,'^Spare Power Supply For Cisco 3660 Series','ASSEMBLY','3600', 88.35 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('AS5201=',2,'^AS5200, Base Chassis','ASSEMBLY','5400',730.08 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('MAS-7010=',2,'7010 Chassis','ASSEMBLY','7500',1408.00 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('MAS-7K=',7,'^Cisco 7000 Chassis','ASSEMBLY','7500',1870.36 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('MAS-7KBLANK=',4,'^Cisco 7500 Series Chassis Slot Blank','ASSEMBLY','7500', 66.51 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('MAS-RSP7000CI=',2,'^SPARE CISCO7000 CI ASSY-7K/RSP ONLY','ASSEMBLY','7500', 181.47 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('INTAKEPNL-09=',4,'air intake panel (rqd for 7609/6509-NEB-A air filter)','ASSEMBLY','7600', 37.16 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('WATM-CAM-2P=',2,'^LightStream 1010/C5500 Carrier Module (spare)','ASSEMBLY','8510', 227.14 );
INSERTINTO Inventory (PartNumber, Quantity, PartDescription, PartType, PartFamily, Price)VALUES('RTP300-NA',249,'Broadband Router with 2 Phone Ports','ASSEMBLY','ACCS', 0.00 );