Searching for Pianos
Article #5
Bryce McCall, Tavin Petruzzi, Ekaterina Minchenkova
A database is a tool for collecting and structuring information. The main purpose of a database is to keep track of multiple things, such as storing data about people, products, orders and many other things.
Dean has a very unique problem that could be solved with a multitude of solutions, varying in difficulty and size. Needing to keep track of his plethora of pianos stored on his property, he needs to create some sort of spreadsheet to manage, identify, and locate all the various instruments he has in his collection. The two most popular routes would be to use either Microsoft Excel or Microsoft Access, both being database management programs used to organize and track various inputs, in this case his pianos. Keeping track of pianos quantities and figuring out how many Dean has could be easily done using Microsoft Excel, because this application allows to store simple spreadsheets. Therefore, Microsoft Excel would be a viable option for Dean to use. Designing his table in Microsoft Excel would be fairly simple. Each individual piano would be designated its own row, or record, and each attribute he wishes to take note of would be placed in its own column, or field. At the same time, using Microsoft Excel could pose some issues further down the line within his business. It won’t provide any other information, therefore it will take more time to organize all data. Based on figure 5-33, the spreadsheet could become very wide and it may be hard to consume all of the pertinent data at one time without having to scroll across the screen. If Dean chose to use Microsoft Excel, each piano would automatically be assigned a number in the spreadsheet and he can manually search through the data, via the search function, for specific attributes he might be looking for. For example, if Dean needed to find information on a Baldwin pinet, like the example within the case study, all he would have to do is open the search function and type in “Baldwin pinet”. This would then take Dean to the first cell that contains those specific words and he can manually look through all the collected data for that individual piano. He could then click “find next” and it would take him to the next cell with that exact text, provided that there is another cell like this. Unfortunately this can become time consuming and tedious and may be why Dean would want to use Microsoft Access. Microsoft Access has several advantages over Microsoft Excel, such as adding new information to the database, for example, a new piano’s item number, modifying information already in the database, deleting information, for instance, if the particular piano has been sold or disposed of, organizing and viewing data in various ways, sharing data with other people via Internet and constructing reports, forms, queries, and web applications. With Microsoft Access, Dean could input all the data he would have used in the Excel spreadsheet and create an individual record for each piano. Each record would have the same specific fields that would have been in the Excel spreadsheet, but would be more individualized to the different instruments. Once all the data is compiled, Dean will now have a file for each piano that he has throughout his property. While this takes more time and may be more complicated to set up within Microsoft Access, Dean would benefit in the long run with searchability. Now, instead of having to manually skim through all the data with the search function in Microsoft Excel, Dean can run a query based on the information he is specifically looking for and the program will retrieve all the records fitting his specific criteria. Going back to the example previously mentioned, Dean could now generate a query for “Baldwin pinet” and Microsoft Access will retrieve all the individual records containing that specific attribute and compile the information in a table, or file. This will allow Dean to easily see how many of these specific pianos he has, where they may be located, what kind of hardware the piano has, and any other information he may need to view.
Upon examination of figure 5-33, we have come to the conclusion that Dean may benefit from adding some additional fields to aid in his querying. Based on the picture in figure 5-31, it seems that some pianos have benches and others do not, so the addition of a “Bench” field for the pianos in inventory may help narrow down a search for a customer looking for a full package and not wanting to purchase a bench separately. Dean does not have a specific “Condition” field in his design either. Each piano could be assigned a number on a scale of 1 through 10 to represent their current state. This could be very useful to him in finding pianos that need to be sold, or are far too deteriorated to sell and should be given away. He could also use this as an organizational technique to help group the pianos together in specific locations, this way when customers come to look for pianos they don’t have to search through the workshop, shed, and then tents to see all of their options. He could consolidate all the pianos in a specific range in one location and another range in another location. For example, the better quality pianos, say 7 through 10 may be stored in the workshop where they are out of the natural elements whereas pianos with a rank of 1-3 may be placed in tents outside, seeing as they are already in poor condition. Lastly, one final recommendation would be to add a “Price” field to all the pianos. Adding a field specifically for price can help narrow down options for a customer that is in the market for a piano within a specific price range. Rather than having to cross reference with condition, or any other attribute for that matter, Dean could start a query based on a price range and narrow out options based on the results he gets back from the program. If we are consulting Dean as a group, our recommendations are going to be based on their cost and benefit. While all these fields of information would be useful to have, it would require someone to physically go out and re-inspect all the pianos he has in inventory. Seeing as Dean doesn’t have any employees to help with this process and that Dean himself is occupied with his clown business, he may not have the time needed to dedicate to this endeavor. He could always hire someone to help with this task, but the chance he could find someone willing to take on this project is very slim. This would result in having to pay someone for their time, and with respect to the declining piano market Dean is facing, this may not be a viable option. Unfortunately, since the model Dean has created is very specific to his particular needs, there are not going to be many options as far as seeking help with the addition of these fields. Most piano inventory models would be used by stores or factories that would be dealing with pianos of higher quality and resale value, as well as dealing with enclosed storage or warehouses. This makes his model very specific to his business, and not a generalized program available to businesses of this type.
Based on what Dean has envisioned for the manufactures’ table, he will have to do some additional research for each of the brand names he has in inventory. First, Dean will have to review his data in order to see what manufacturers he will need to perform research on. Much like the cost benefit aspect of adding the additional fields mentioned before, he does not want to waste time looking up information on brands he does not have in his possession. Seeing as his time is valuable, doing research on a manufacture that he does not have will take time away from performing other tasks that would be more pertinent to the inventory he does currently possess, such as researching and adding the information to the additional fields recommended previously. He would also have to assume that all of these manufacturers are still operational which, based on the current piano market, may not be the case. If there is a manufacturer that is no longer in business, finding all the information needed on the company may be redundant considering Dean or the customer can no longer get in contact with them.
Question 5-17? No figure to go off of
The statement “A database is an abstraction of some aspect of a business” refers to the information contained in the actual database. This means that the data is only pertinent to an area within the business, and not the business as a whole. For example, in this case study the database refers to all the pianos Dean has in his inventory. The database has no information pertaining to any other aspect of his business other than the pianos and the information directly related to these instruments. Dean doesn’t have data about his customers stored, nor does he have anything about his administrative endeavors logged into the database. He doesn’t have how many hours in a day he works on researching these pianos, any financial information of the business, or any customer information stored in the database according to the figures provided. Much like any other business, while this information may be helpful in other areas, they are not related to the core business and would be more suitable for a database designed around administrative attributes. There would not be very many solutions available to tie all the information together with the use of foreign and primary keys either, so the addition of this information could potentially lead to confusion if not implemented properly. Sometimes less is more when it comes to researching and correlating data, so it may be best to create a whole new model for these types of information. Having only the pertinent information needed about the pianos and nothing more allows Dean and his customers to review what he has in inventory and select the best fit for the customers’ needs. This dramatically increases efficiency as opposed to having to physically walk the property in search of the perfect piano. Just as in any other business, time is money so having only the information you need cuts down on search times and is vastly superior over the alternative of retrieving data by hand each time. However, this is based on the assumption that the data is still valid and usable when the need arises.
As we have seen from the data provided, the pianos have a finite life and are subject to deterioration. Knowing this, Dean will need to periodically verify that his data is still current and correct. Much like a periodic inventory system, occasionally he will have to re-asses every piano he has in stock and make sure the database accurately reflects the current condition. The most efficient way to go about this process would be to develop a rotation of manageable areas that Dean can handle himself. For example, based on the time he has available to dedicate to the project, Dean could set up a monthly rotation of storage locations he can go through in order to re-evaluate each piano. It would be far too daunting of a task to look at the property as a whole so perhaps one month he could work through the workshop, and then the next month he can work though the shed, and so on. Another option would be to evenly divide the inventory level by his desired work time and evenly distribute a quantity of pianos rather than a location. For example, he has roughly 100 pianos on his property, Dean could divide 100 by 12 months and find out how many he needs to review each month in order to stay current. This all comes down to how much time he has available to dedicate to the project outside of his clown business and any other personal obligations he may have.