The Oconee County Libraries (OCL) has to keep track of information on over 100 pieces of electronics (computers, monitors, printers, etc.) Currently, a combination of Excel spreadsheets and an online tool from webjunction.org are used to record inventory information. The result is an unwieldy system that is time consuming to keep updated. The goal of this project is to create a database to record essential inventory data for both branches. It is hoped that, if the system performs well, it may one day be expanded to cover the other libraries in the Athens Regional Library System (ARLS). Most frequently, the system will be used to record or update the location and relation (e.g., which monitor is hooked up to which computer) of pieces of equipment. In its current incarnation, the database will only be accessed by the author (OCL’s sole IT staffer), but in part that will entail translating requests from the Library’s Manager and Regional Business Office staff into database queries.
Business Rules
General Rules
- · A computer consists of a laptop or a desktop and monitor. Computers within a given library have unique names.
- · Every piece of equipment has a unique serial number and a non-unique ARLS purchase code. Equipment belongs to a type (e.g., “Desktop”) and a model (e.g., “Optiplex 740.”) Models are made by one manufacturer (e.g., “Dell.”)
- · Each purchase has a unique ARLS purchase code. A purchase may include many different models but one purchase comes from only one vendor and one date. Orders from more than one vendor on the same day, or from the same vendor over many days, will each have their own purchase (and ARLS purchase code.)
- · Vendors have a phone number and/or website address. Vendors may have a sales rep attached them, who may have their own phone number and/or email address.
- · A library contains one or more locations. While of course an actual location only exists in one library, libraries may share names of locations (e.g., the Watkinsville library may have a location called “Adult” and so may the Bogart library.) Therefore a location in the database may be contained by one or more locations.
- · When equipment is disposed of, it must be removed from the active inventory but a historical record of its information must be maintained. It is not necessary, however, to retain previous libraries or location if a piece of equipment is moved.
- · Zero to many pieces of equipment are located in each library and location. Every piece of equipment has one library and location.
- · A piece of equipment belongs to one model. Each model may define one or many pieces of equipment.
- · A model belongs to a type (of equipment.) A type contains one or more models.
- · Active computers (both desktops and laptops) are identified by a unique name. Active desktops are connected to a monitor.
- · A desktop (computer) may be connected to zero or one monitors. A monitor may be connected to zero or one desktops.
- · A purchase code identifies one or more pieces of equipment purchased at the same time. Each piece of equipment is identified by only one purchase code.
- · Each purchase is purchased from one vendor. Vendors may supply equipment for many different purchases.
Relationships
Entity Relationship Diagram
Data Dictionary
See Appendix.
Initializing the Database
Create Tables
CREATE TABLE Equipment (
eq_serial_num VARCHAR(50) NOT NULL PRIMARY KEY,
eq_status ENUM(‘ACTIVE’,'STORAGE’,'ORDERED’,'DELETED’) NOT NULL,
eq_price DECIMAL(8,2),
mod_id int NOT NULL,
lib_id int NOT NULL,
loc_id int NOT NULL,
pur_arls_code VARCHAR(8) NOT NULL,
FOREIGN KEY (mod_id) REFERENCES Model(mod_id),
FOREIGN KEY (lib_id) REFERENCES Library(lib_id),
FOREIGN KEY (loc_id) REFERENCES Location(loc_id),
FOREIGN KEY (pur_arls_code) REFERENCES Purchase(pur_arls_code)
);
CREATE TABLE Vendor (
ven_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
ven_name VARCHAR(255),
ven_url VARCHAR(255),
ven_phone CHAR(10),
ven_sales_rep VARCHAR(255),
ven_rep_email VARCHAR(255),
ven_rep_phone CHAR(10)
);
CREATE TABLE Purchase (
pur_arls_code VARCHAR(8) NOT NULL PRIMARY KEY,
pur_date DATETIME,
ven_id INT,
FOREIGN KEY (ven_id) REFERENCES Vendor(ven_id)
);
CREATE TABLE Computer (
comp_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
comp_name VARCHAR(20) NOT NULL,
comp_serial_num VARCHAR(50) UNIQUE NOT NULL,
monitor_serial_num VARCHAR(50),
FOREIGN KEY (comp_serial_num) REFERENCES Equipment(eq_serial_num),
FOREIGN KEY (monitor_serial_num) REFERENCES Equipment(eq_serial_num)
);
CREATE TABLE Library (
lib_id TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
lib_name VARCHAR(255)
);
CREATE TABLE Location (
loc_id TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
loc_name VARCHAR(255)
);
CREATE TABLE Model (
mod_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
mod_name VARCHAR(255) NOT NULL,
mod_manufacturer VARCHAR(255),
type_id TINYINT,
FOREIGN KEY (type_id) REFERENCES Type(type_id)
);
CREATE TABLE Type (
type_id TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(255)
);
Insert Data
INSERT INTO Library(lib_name)
VALUES
(‘WAT’),
(‘BOG’);
INSERT INTO Location(loc_name)
VALUES
(‘Adult’),
(‘Reference’),
(‘Kids’),
(‘Circ Desk’),
(‘Workroom’);
INSERT INTO Type(type_name)
VALUES
(‘Desktop’),
(‘Laptop’),
(‘Monitor’),
(‘Printer’),
(‘Copier’);
INSERT INTO Vendor(ven_name,ven_url,ven_phone,ven_sales_rep,ven_rep_email,ven_rep_phone)
VALUES (‘Dell’,'dell.com’,’8001234567′,’Lumumba Weems’,'lweems@dell.com’,’8001234568′);
INSERT INTO Vendor(ven_name,ven_url,ven_phone)
VALUES (‘Gordon Docs’,'gordondocuments.com’,’7701234567′);
INSERT INTO Vendor(ven_name,ven_url)
VALUES
(‘Amazon’,'amazon.com’),
(‘HP’,'HP.com/gov’);
INSERT INTO Purchase(pur_arls_code,pur_date,ven_id)
VALUES
(‘S-08-02′,’2007-08-01′,’2′),
(‘S-02-04′,’2002-11-19′,’2′),
(‘S-05-12′,’2005-06-22′,’3′),
(‘S-08-06′,’2008-03-18′,’4′),
(‘S-07-01′,’2006-07-28′,’4′),
(‘S-03-04′,’2002-09-01′,’4′),
(‘S-07-05′,’2005-01-30′,’1′),
(‘S-08-01′,’2007-08-01′,’1′),
(‘S-08-08′,’2008-04-11′,’1′),
(‘S-08-09′,’2008-05-03′,’1′);
INSERT INTO Model(mod_manufacturer,mod_name,type_id)
VALUES
(‘Konica Minolta’,'C252′,5),
(‘Sharp’,’1000k’,5),
(‘HP’,'lj2420dn’,4),
(‘HP’,'clj2600n’,4),
(‘HP’,'lj4050′,4),
(‘HP’,'dc5000′,1),
(‘HP’,’7550′,3),
(‘Dell’,'Optiplex 740′,1),
(‘Dell’,'M150′,3),
(‘Dell’,'Optiplex 755′,1),
(‘Dell’,'M170-DX’,3),
(‘Dell’,'XRT26′,3),
(‘Dell’,'Latitude D260′,2);
– broken into two inserts b/c VSU server won’t accept such a long imput
INSERT INTO Equipment
(eq_serial_num,eq_status,eq_price,mod_id,lib_id,loc_id,pur_arls_code)
VALUES
(’1278433′,’ACTIVE’,3125.25,2,2,1,’S-02-04′),
(’1278432′,’ACTIVE’,3125.25,2,1,2,’S-02-04′),
(‘YW-987-100012-545′,’ACTIVE’,NULL,7,2,3,’S-03-04′),
(‘YW-987-100012-546′,’ACTIVE’,NULL,7,2,3,’S-03-04′),
(‘YW-987-100012-547′,’ACTIVE’,NULL,7,1,3,’S-03-04′),
(‘YW-987-100012-548′,’ACTIVE’,NULL,7,1,3,’S-03-04′),
(‘YW-987-100012-549′,’ACTIVE’,NULL,7,1,3,’S-03-04′),
(‘YW-987-100012-550′,’ACTIVE’,NULL,7,1,3,’S-03-04′),
(‘YW-987-100012-551′,’STORAGE’,NULL,7,1,2,’S-03-04′),
(‘YW-987-100012-552′,’STORAGE’,NULL,7,1,2,’S-03-04′),
(‘mv-010-432478′,’ACTIVE’,989.85,6,1,3,’S-03-04′),
(‘mv-010-432479′,’ACTIVE’,989.85,6,1,3,’S-03-04′),
(‘mv-010-432480′,’ACTIVE’,989.85,6,1,3,’S-03-04′),
(‘mv-010-432481′,’ACTIVE’,989.85,6,1,3,’S-03-04′),
(‘mv-010-432482′,’ACTIVE’,989.85,6,2,3,’S-03-04′),
(‘mv-010-432483′,’ACTIVE’,989.85,6,2,3,’S-03-04′),
(‘mv-010-432484′,’STORAGE’,989.85,6,1,2,’S-03-04′),
(‘mv-010-432485′,’STORAGE’,989.85,6,1,2,’S-03-04′),
(‘h1-8909-3213′,’ACTIVE’,481.32,3,1,4,’S-05-12′);
– broken into two inserts b/c VSU server won’t accept such a long imput
INSERT INTO Equipment
(eq_serial_num,eq_status,eq_price,mod_id,lib_id,loc_id,pur_arls_code)
VALUES
(’321-dsa2-32190′,’ACTIVE’,788.09,5,2,1,’S-07-01′),
(’1338-QPZ-983S32′,’STORAGE’,1089.98,13,1,5,’S-07-05′),
(’1338-QPZ-983T98′,’STORAGE’,1089.98,13,1,5,’S-07-05′),
(’10303-OJP-13′,’ACTIVE’,NULL,9,1,4,’S-07-05′),
(’10303-OJP-14′,’ACTIVE’,NULL,9,1,4,’S-07-05′),
(’10303-OJP-11′,’ACTIVE’,NULL,9,1,2,’S-07-05′),
(’10303-OJP-12′,’ACTIVE’,NULL,9,1,2,’S-07-05′),
(’10303-OJP-10′,’ACTIVE’,NULL,9,2,5,’S-07-05′),
(‘GX-321h890-F3′,’ACTIVE’,770.33,8,1,4,’S-07-05′),
(‘GX-321h890-G3′,’ACTIVE’,770.33,8,1,4,’S-07-05′),
(‘GX-321h890-D3′,’ACTIVE’,770.33,8,1,2,’S-07-05′),
(‘GX-321h890-E3′,’ACTIVE’,770.33,8,1,2,’S-07-05′),
(‘GX-321h890-H3′,’ACTIVE’,770.33,8,2,5,’S-07-05′),
(’21398-UUP-992′,’ACTIVE’,NULL,11,1,2,’S-08-01′),
(’21398-UUP-993′,’ACTIVE’,NULL,11,1,2,’S-08-01′),
(’21398-UUP-990′,’STORAGE’,NULL,11,1,5,’S-08-01′),
(’21398-UUP-991′,’ACTIVE’,NULL,11,1,5,’S-08-01′),
(‘GX-923k987-025′,’ACTIVE’,722.01,10,1,2,’S-08-01′),
(‘GX-923k987-026′,’ACTIVE’,722.01,10,1,2,’S-08-01′),
(‘GX-923k987-023′,’ACTIVE’,722.01,10,1,5,’S-08-01′),
(‘GX-923k987-024′,’ACTIVE’,722.01,10,1,5,’S-08-01′),
(‘CLM1201′,’ACTIVE’,5285.05,1,1,5,’S-08-02′),
(’312-fds324-32132′,’ACTIVE’,654.21,4,1,4,’S-08-06′),
(’20123-UXP-490′,’ACTIVE’,NULL,11,1,5,’S-08-08′),
(’20123-UXP-491′,’ACTIVE’,NULL,11,1,5,’S-08-08′),
(‘GX-923k987-343′,’ACTIVE’,626.09,10,1,5,’S-08-08′),
(‘GX-923k987-344′,’ACTIVE’,626.09,10,1,5,’S-08-08′),
(‘hidw395-32hj-ii8988′,’ACTIVE’,399.99,12,1,5,’S-08-09′);
INSERT INTO Computer(comp_name,comp_serial_num,monitor_serial_num)
VALUES
(‘CHL-01′,’mv-010-432482′,’YW-987-100012-545′),
(‘CHL-02′,’mv-010-432483′,’YW-987-100012-546′),
(‘CIRC-01′,’GX-321h890-H3′,’10303-OJP-10′),
(‘CHL-01′,’mv-010-432478′,’YW-987-100012-547′),
(‘CHL-02′,’mv-010-432479′,’YW-987-100012-548′),
(‘CHL-03′,’mv-010-432480′,’YW-987-100012-549′),
(‘CHL-04′,’mv-010-432481′,’YW-987-100012-550′),
(‘CIRC-01′,’GX-321h890-F3′,’10303-OJP-13′),
(‘CIRC-02′,’GX-321h890-G3′,’10303-OJP-14′),
(‘REF-01′,’mv-010-432484′,’YW-987-100012-551′),
(‘REF-02′,’mv-010-432485′,’YW-987-100012-552′),
(‘REF-01′,’GX-321h890-D3′,’10303-OJP-11′),
(‘REF-02′,’GX-321h890-E3′,’10303-OJP-12′),
(‘REF-03′,’GX-923k987-025′,’21398-UUP-992′),
(‘REF-04′,’GX-923k987-026′,’21398-UUP-993′),
(‘STAFF-CHL’,'GX-923k987-344′,’20123-UXP-491′),
(‘STAFF-IT’,'GX-923k987-023′,’hidw395-32hj-ii8988′),
(‘STAFF-MANAGER’,'GX-923k987-024′,’21398-UUP-991′),
(‘STAFF-REF’,'GX-923k987-343′,’20123-UXP-490′),
(‘LAPTOP-01′,’1338-QPZ-983T98′,NULL),
(‘LAPTOP-02′,’1338-QPZ-983S32′,NULL);
Sample SQL Statements
Selects
– List the name, manufacturer, model, serial numer and location of all active computers at the Watkinsville Library
SELECT
C.comp_name AS Computer,
M.mod_manufacturer AS Manufacturer,
M.mod_name AS Model,
C.comp_serial_num AS “S/N”,
Loc.loc_name AS Location
FROM
Model AS M
NATURAL JOIN
(Equipment AS E
INNER JOIN
Computer AS C
ON C.comp_serial_num=E.eq_serial_num
NATURAL JOIN
Location AS Loc)
WHERE
E.lib_id = (SELECT Lib.lib_id FROM Library AS Lib WHERE lib_name=”WAT”)
AND
E.eq_status = “ACTIVE”
ORDER BY Loc.loc_name;
– List the owning library, location, manufacturer, and model for all printers and copiers
SELECT
Lib.lib_name AS Library,
Loc.loc_name AS Location,
M.mod_manufacturer AS Manufacturer,
M.mod_name AS Model
FROM
Equipment AS E
NATURAL JOIN
Model AS M
NATURAL JOIN
Type AS T
NATURAL JOIN
Location AS Loc
NATURAL JOIN
Library AS Lib
WHERE
T.type_name IN (‘Printer’,'Copier’)
AND
E.eq_status = “ACTIVE”
ORDER BY M.mod_name;
– List the name, owning, library, manufacturer, and model of all public computers
SELECT
C.comp_name AS Computer,
Lib.lib_name AS Library,
Loc.loc_name AS Location,
M.mod_manufacturer AS Manufacturer,
M.mod_name AS Model
FROM
(Equipment AS E
NATURAL JOIN
Model AS M
NATURAL JOIN
Location AS Loc
NATURAL JOIN
Library AS Lib)
INNER JOIN
Computer AS C
ON C.comp_serial_num=E.eq_serial_num
WHERE
Loc.loc_name NOT IN (‘Circ’,'Workroom’)
AND
E.eq_status = “ACTIVE”
ORDER BY Lib.lib_name,Loc.loc_name,C.comp_name;
– Get detailed info on an active computer (and attached monitor), given only the name and library
SELECT
Loc.loc_name AS Location,
C.comp_name AS Computer,
CONCAT(M.mod_manufacturer,” “,M.mod_name) AS Model,
C.comp_serial_num AS “S/N”,
E.pur_arls_code AS “ARLS Code”,
CONCAT(
(SELECT M.mod_manufacturer
FROM
Model AS M
NATURAL JOIN
(Equipment AS E
INNER JOIN
Computer AS C
ON C.monitor_serial_num=E.eq_serial_num
)
WHERE
C.comp_name = “STAFF-IT”
AND
E.lib_id = (SELECT Lib.lib_id FROM Library AS Lib WHERE lib_name=”WAT”)
AND
E.eq_status = “ACTIVE”
),
” “,
(SELECT M.mod_name
FROM
Model AS M
NATURAL JOIN
(Equipment AS E
INNER JOIN
Computer AS C
ON C.monitor_serial_num=E.eq_serial_num)
WHERE
C.comp_name = “STAFF-IT”
AND
E.lib_id = (SELECT Lib.lib_id FROM Library AS Lib WHERE lib_name=”WAT”)
AND
E.eq_status = “ACTIVE”
)
) AS “Monitor Model”,
C.monitor_serial_num AS “Monitor S/N”,
(SELECT E.pur_arls_code
FROM
Equipment AS E
INNER JOIN
Computer AS C
ON C.monitor_serial_num=E.eq_serial_num
WHERE
C.comp_name = “STAFF-IT”
AND
E.lib_id = (SELECT Lib.lib_id FROM Library AS Lib WHERE lib_name=”WAT”)
AND
E.eq_status = “ACTIVE”
) AS “Monitor ARLS Code”
FROM
Model AS M
NATURAL JOIN
(Equipment AS E
INNER JOIN
Computer AS C
ON C.comp_serial_num=E.eq_serial_num
NATURAL JOIN
Location AS Loc)
WHERE
C.comp_name = “STAFF-IT”
AND
E.lib_id = (SELECT Lib.lib_id FROM Library AS Lib WHERE lib_name=”WAT”)
AND
E.eq_status = “ACTIVE”;
– List purchasing information for every unit purchased in Fiscal Year 2008
SELECT
LEFT(P.pur_date,10) AS “Purchase Date”,
P.pur_arls_code AS “ARLS Purchase Code”,
V.ven_name AS Vendor,
T.type_name AS Category,
M.mod_name AS Item,
E.eq_serial_num AS “S/N”
FROM
Equipment AS E
NATURAL JOIN
Purchase AS P
NATURAL JOIN
Vendor AS V
NATURAL JOIN
Model as M
NATURAL JOIN
Type AS T
WHERE
P.pur_date BETWEEN ’2007-07-01′ AND ’2008-06-30′
ORDER BY P.pur_arls_code,P.pur_date
– List summary of purchases made in FY2008, broken down by models ordered
SELECT
LEFT(P.pur_date,10) AS “Purchase Date”,
P.pur_arls_code AS “ARLS Purchase Code”,
V.ven_name AS Vendor,
M.mod_name AS Item,
COUNT(M.mod_name) AS “Units Purchased”,
E.eq_price AS “Price per Unit”,
SUM(E.eq_price) AS “Total Price”
FROM
Equipment AS E
NATURAL JOIN
Purchase AS P
NATURAL JOIN
Vendor AS V
NATURAL JOIN
Model as M
WHERE
P.pur_date BETWEEN ’2007-07-01′ AND ’2008-06-30′
GROUP BY M.mod_name,P.pur_arls_code
ORDER BY P.pur_arls_code,P.pur_date;
– List total spending by year, rounded to nearest dollar
SELECT
YEAR(P.pur_date) AS “Purchase Year”,
ROUND(SUM(E.eq_price),0) AS “Total Spending”
FROM
Equipment AS E
NATURAL JOIN
Purchase AS P
NATURAL JOIN
Vendor AS V
GROUP BY YEAR(P.pur_date)
ORDER BY P.pur_date
Operations
– Mark a piece of equipment deleted
UPDATE Equipment
SET eq_status = ‘DELETED’
WHERE eq_serial_num = ’21398-UUP-991′
LIMIT 1;
– Move a monitor out of storage and assign it to a computer
UPDATE Equipment
SET eq_status = ‘ACTIVE’
WHERE eq_serial_num = ’21398-UUP-990′
LIMIT 1;
UPDATE Computer
SET monitor_serial_num = ’21398-UUP-990′
WHERE comp_name = ‘STAFF-MANAGER’
LIMIT 1;
Project Evaluation
During the initial design phase, it was difficult to visualize how best to handle attributes that would necessarily have duplicates: the ARLS purchase codes and locations names shared by multiple libraries. Creating the ERD (using Dia diagramming software) clarified things. It became apparent that I needed to add a table to record purchases, and then I realized a linking table needed to bridge purchases and equipment (or so I thought at the time). As for the problem of similarly named locations, I considered alternative solutions but stuck with the design you see above.
While going through steps of normalization process, I realized that giving each purchase line a unique ID (and including it with the purchase ID and equipment’s serial number as a composite primary key) was unnecessary—a composite key of just those two other attributes is enough to uniquely identify each purchase line. After fixing that, I realized I had forgotten to include the ability to store the price of a purchase. After some thought, I added it to the purchase line table, so as to be able to easily capture prices of equipment on a per unit basis.
One complicating factor that turned up again and again was maintaining historical data for deleted items. It wasn’t necessary to track every computer a desktop or monitor was associated with over the course of its lifetime, but a record of serials numbers and purchase info for deleted items would be beneficial. My simple solution was to add “deleted” to the list of possible equipment statuses (the others being “active”, in “storage”, and “ordered” but not yet received.)
With the initial design completed, I began to insert data into the tables. I soon realized I hadn’t been clear enough with my business rules—ARLS purchase codes are unique, so the purchase table didn’t need a surrogate key. Then it became apparent that the table linking purchases and equipment was unnecessary—the relationship between the tables isn’t M:N, it’s 1:M. I deleted the linking table and moved the price and alrs code attributes to the equipment table, with arls code as a foreign key.
Since WAT and BOG can share computer names (e.g., they each could have a computer named “CIRC-01”, I thought I needed to add the owning library’s ID to the computer table as part of a composite primary key. However, I then found that deleted computers could easily have duplicate computer names and owning libraries, so I didn’t have a good primary key candidate between the two of them. I weighed using the desktop’s serial number as the primary key but instead decided that a surrogate key was better solution in this case.
I ended up revising the design of the database many times, both during the initial design phase and then over and over again as I inserted data and wrote queries. Knowing that I’d have to include the create and insert statements that would build a working copy of my table for the purposes of this paper made the development process frustratingly slow—in a “normal” development environment, I would have been freed to alter tables and data on the fly (and using a GUI), which would have made testing and refining simpler and quicker. I also encountered a moment when writing a select query where I got “stuck” and began to doubt my SQL skills. After a couple hours of slogging I realized that I’d actually made a series of data entry errors to one table—my SQL code was correct all along. The first select query I wrote was by far the hardest to get working—once I grasped that the normalized design I’d come up with facilitated a series of natural joins to link almost any of the tables to each other, I proceeded without much difficulty. My SQL skills improved greatly with this applied practice.
The one area of my design that I’m least happy with is the computer table, which links computer names to serial numbers for the unit’s desktop and monitor (or, for a laptop, just a single serial number). The solution I came up with works, as my queries demonstrate, but I consider it inelegant and it leads to very complex queries for anything related to computers’ monitors (as my queries also demonstrate.) As I continue to develop this database for my workplace, this is definitely one area I will look closely at. That weakness aside, I’m pleased with the database—it’s a fully functional system to track exactly what my library needs to track.
