Extract: The PPI order transaction database is a fairly straight forward schema to think about. Each object connects to the next object in a logical manner, without too many incongruities to muck up the overall design. This results in a model that contains only ten classes: CUSTOMER, PAYMENT, SHIPPING, ORDER, EMPLOYEE, PRODUCT, STOCK, STOCK_LINK, PROD_LINK, and CUS_LINK.
Extract: Front end validation ensures that data entered into the system by a user or process is in correct form in relation to business rules. It safeguards that what passes the interface boundary into the application is in a usable form by the system itself. However, the front end application is agnostic when it comes to placing that data in the database.
Extract: SQL standards across platforms is a hot subject for some database administrators and a moot point for others. The primary reason is in environments where more than one architecture is being used. For instance, an Oracle database for one application and a Microsoft database for another. If queries from one platform are to be used in the other, then there is a high probability that the queries will not work on one or the other of the DBMSs due to proprietary incompatibility.
Extract: The primary advantage of a join and/or nested query is the ability to consolidate information without permanently denormalizing a database. In essence, using a join or nested query is the equivalent of a temporary localized denormalization of a database, which does not affect the operational capacity of the entire entity set which makes up that database. Also, it allows for the gathering of information from various tables based on specific rules and/or joins which are not natural in terms of how the database is formed. For instance, given two separate tables which have no foreign keys to each other, if a data link exists between a column in the first table and a column in the second table, a join can be made regardless of any built in keys.
SQL Queries: Indexes, Views, and Stored Procedures
Extract: An index can increase performance, but only at a cost of storage increase and possible system performance degradation elsewhere. This does not mean that the index is a bad thing, only that one must apply indexes in a knowledgeable fashion. Haphazard database indexing can actually be detrimental to the overall perceived performance boost. Lastly, for major system changes always bring the final decision to the appropriate stake holders for confirmation.
Concurrency Control Types: Optimistic vs. Pessimistic Concurrency
Extract: The primary benefit of optimistic concurrency for a database warehouse is in its ability to allow multiple database connections on the same data. This means users or applications can access the warehouse in a timely manner without worrying about waiting for another person or application’s transaction to complete. However, the downside to this is that if a person or process must make a change to data, and their data change conflicts with another user or process’ change, then they must redo their entire transaction.
Extract: One type of data mining technique is called Association, which is where correlations are uncovered between various data points in order to make business decisions or suggestions about said data. For instance, when you shop on Amazon.com and the suggested items list comes up at the bottom. This list is determined based on what you and other shoppers have purchased in conjunction with the current item. This is predictive in that the historical data suggests that you will likely purchase one of these items, as well, even more so if the item is advertised to you directly.