Now that we have a database system installed and configured, let’s pick a practical application and see if we can build it in each of the environments. The one that comes to mind is a library card catalog system that allows students to check out and check in books. With this system we will create a table that contains information about books and create a table that contains basic information about students. I am picking this example for two reasons. First, it is a simple problem that we should be able to build. Second, my kids school needs a library system and building it helps the school as well as helps me learn. The origional design will be simple. Once we get this design done we will look at more advanced features like protecting columns in a table with encryption, partitioning the data so that we can drop last semesters transactions, backing up the data securely, and restricting table access to prevent dropping or deleting entries from a repository. We might also want to integrate this database with a business process engine to generate reports and late fee reminders.
It is important to keep an eye on our exercise. I am trying to build a foundation of knowledge in database design, administration, and deployment to help learn the Oracle database products and its surrounding packages. I am going to build this exact solution on multiple operating systems and multiple databases as would be done at a corporation. The intention is to be a learning exercise. At each step I will generate a summary of the cost of this solution and effort required to build the “product”. I will try to build this using Oracle Unbreakable Linux, Oracle Database Express Edition, and SQL Developer to provide a zero cost solution to anyone who wants to take this and run with it.
One commercial/non-profit web site that I will use for this exercise is www.librarything.com. This site allows me to create a virtual card catalog of my book collection and export the list as an Excel or CVS file. From this I will import the data into a database table and use it as the list of books that are in our library.
next step, design a data definition for our project. This needs to include table definition, entity relation diagrams, and use cases for our library. I would like to create this solution as a web interface to allow books to be added to the library, students to be added to the school, and books to be checked in and out from the library.