Adv DB: Web DBMS Tools

Developers need tools to design web-DBMS interfaces for dynamic use of their site for either e-commerce (Amazon storefront), decision making (National Oceanographic and Atmospheric Administration weather forecast products), or forgather information (Survey Monkey), etc.  ADO.NET and Fusion Middleware are two of many tools and middleware that can be used to develop web-to-database interaction (MUSE, 2015).

ADO.NET (Connolly & Begg, 2014)

Microsoft’s approach to a web-centric middleware for the web-database interface, which provides compatibility with .NET class library, support to XML (used excessively as an industry standard), and connection/disconnection data access.  It has two tiers: dataset (data table collection, XML) and .NET Framework Data Provider (connection, command, data reader, data adapter, for the database).

Pros: Built on standards to allow for non-Microsoft products to use it.  Automatically creates XML interfaces for the application to be turned into a Web Operable Service.  Even the .NET classes conform to XML and other standards.  Other development tools for further expanding the GUI set can be added and bound to the Web Service.

Cons: According to the Data Developer Center website (2010),  with connected data access, you must explicitly manage all database resources, and not doing so can cause resource mismanagement (connections are never freed up).  Other functions in certain classes are missing, like mapping to table-valued functions in the Entity Framework.

Fusion Middleware (Connolly & Begg, 2014):

Oracle’s approach to a web-centric middleware for the web-database interface, which provides development tools, business intelligence, content management, etc.  It has three tiers: Web (using Oracle web cache and HTTP Server), Middle Tier (apps, security services, web logic servers, other remote servers, etc.), and data (the database).

Pros: Scalable. It is based on a Java Platform (full Java EE 6 implementation).  Allows Apache modules like those that route HTTP Requests, for store procedures on a database server, for transparent single sign-on, SHTTP, etc. Their Business Intelligence function allows you to extract and analyze data to create reports and charts (statically or dynamically) for decision analysis.

Cons: The complexity of their system along with their new approach creates a steep learning curve, and requires skilled developers.

The best approach for me was Microsoft: If you want to connect to many other Microsoft applications, this is one route to consider.  It has a nice learning curve (from personal experience).  Another aspect, was when I was building apps for the Library at the University of Oklahoma, the DBAs and I didn’t really like the grid view basic functionalities, so we exploited the aforementioned pro of interfacing with third-party codes, to create more interactive table view of our data.  What is also nice is that our data was on an Oracle database, and all we had to do was switch the pointer from SQL to Oracle, without needed to change the GUI code.

Resources

Adv DB: Indexes for query optimization

Information sought in a database can be extracted through a query.  However, the bigger the database, the slower the processing time it would take for a query to go through, hence query optimization techniques are conducted.  Another reason for optimization can occur with complex queries operations.

Rarely see that an index is applied on every column in every table

Using indices for query optimization is like using the index at the back of the book to help you find the information/topic you need quickly. You could always scan all the tables just like you can read the entire book, but that is not efficient (Nevarez, 2010).  You can use an index seek (ProductID = 77) or an index scan via adding an operand (ABS(ProductID) = 77), though a scan takes up more resources than a seek.  You can combine them (ProductID = 77 AND ABS(SalesOrderID) = 12345), where you would seek via ProductID and scan for SalesOrderID.  Indexing can be seen as an effective way to optimize your query, besides other methods like applying heuristic rules or ordering the query operations for efficient use of resources (Connolly & Begg, 2014).  However, indices not being used have no use to us, as they take up space on our system (Nevarez, 2010) which can slow down your operations.  Thus, they should be removed.  That is why indexing shouldn’t be applied to every column in every table.  Indexing in every column may not be necessary because it can also depend on the size of the table, indexing is not needed if the table is 3*4, but may be needed if a table is 30,000 * 12.

Thoughts on how to best manage data files in a database management system (DBMS)

Never assume, verify any changes you make with cold hard data. When considering how best to manage a database one must first learn if the data files or the data within the database are dynamic (users create, insert, update, delete regularly) or static (changes are minimal to non-existant) (Connolly & Begg, 2014).  Database administrators need to know when to fine-tune their databases with useful indices on tables that are widely used and turn off those that are not used at all.  Turning off those that are not used at all will saving space, optimize updated functions, and improving resource utilization (Nevarez, 2010). Knowing this will help us understand the nature of the database user. We can then re-write queries that are optimized via correct ordering of operations, removing unnecessary loops and do joins instead, how join, right join or left join properly, avoiding the wildcard (*) and call on data you need, and ensure proper use of internal temporary tables (those created on a server while querying).  Also, when timing queries, make sure to test the first run against itself and avoid the accidental time calculation which includes data stored in the cache. Also, caching your results, using the cache in your system when processing queries is ideal.  A disadvantage of creating too many tables in the same database is slower interaction times, so creating multiple databases with fewer tables (as best logic permits) may be a great way to help with caching your results (MySQL 5.5 Manual, 2004).

Resources