During the Fall 2015 semester at UNK, I took CSIT425 Database Administration. The semester project for that course was the creation of a custom database based on the needs of a client. In my case, the database was designed to serve my own needs. A side affect of my hobby as a computer enthusiast is that I have acquired several computer hardware components over the years and constructed numerous PCs for purposes ranging from hardcore gaming to basic office-work at local businesses. It would be beneficial to keep a basic database tracking where all of the hardware I acquired was stored or installed as well as a list of records contain details of each computer I’ve constructed and what parts are currently installed in it.
I received an A on this project.
Completed Project Goals
- 3 Tables: Components, Devices, Storage units
- User interface
- Menus
- Forms
- Ad-hoc Queries
- Clean looking Reports
- Data entry validation
- Components linked to a storage unit or a device (but not both)
Materials
- Example copy of database (MS Access DB file. Contains example records)
Forms
Part of the requirements for this project involved the creation of forms to manipulate the data. While I did accomplish this, it was not a primary focus of my project goals. I preferred entering data directly into the database table so the forms weren’t given as much love and attention as other parts of the database. Per the course requirements, a help form was created to provide some assistance in using the table manipulation forms.
A splash screen was designed as the initial starting point when opening the database file. There are options to load any of the table manipulation forms, directly access the tables themselves, view the help documentation, or open the primary report (Components by Devices).
-
- Splash screen
-
- Manipulate devices form
-
- Help screen
Tables
The database tracks what device or storage unit a component is in and can be manipulated using custom-built forms in Access. In the devices and storage units tables, you can expand the record to see all attached components. This allows you to quickly view or edit the components connected to the given device or storage unit.
Components are recorded in a single table with records containing data such as: component type, manufacturer (brand), model, price, date of purchase, and location. Devices are recorded based on their name (usually the hostname), creation date, and their type of use. Storage units are just recorded by a descriptive name. In my case, they’re named based on the label-maker labels I put on a series of drawers (ex. Hard Drives 1).
Device table showing expansion of an individual device’s connected components.
Reports
Components by Device
Three reports were created but one report in particular was given precedence as I felt it was the most useful of the three. Components by Device arranges components based on what device they’re installed in and shows their details. This is useful for seeing at-a-glance which devices have what components. The second report, Components by Type, was created so I could see a list of all my components grouped by the type of hardware they are (CPU, Hard Drive, Graphics card, etc). The final report was a compilation of Components by Year which grouped everything by the year they were purchased. This was useful to find components which were old and might need upgrading.
Final Thoughts
This project was initially something I had felt wasn’t going to serve much purpose, but as I continued through the project I realized the usefulness of it. The experience of setting up a fully functional database was interesting and the resulting Components database is something I still use to this day. While MS Access is not really standard in a business setting, I did work a little bit on a parallel, but incomplete, project in MySQL Workbench. The course covered a number of the basic and more complex SQL statements which I did find useful on this project as I was able to manually configure queries using the more advance query construction tools in Access.
On a more meta note, I have also had to occasionally dink around with this website’s SQL database when the CMS system doesn’t do quite what I want it do.