CMSI 486: Welcome to Week 12

This Week's Class Agenda

What's Going On This Week

1. Big Data
2. Data Mining Schemas
3. Security and Database Vulnerabilities
4. Project SCRUMs

Hello, I'm Apache – I'll be your server today… has been postponed to next week.

Big Data

In the modern world, companies, governments, schools, and people are drowning in data but at the same time are starving for information. This interesting dichotomy is brought about by the very difficult set of tasks that is required to produce information from the mountains of data that exist in the world now. There are several primary reasons for this situation:

The way these organizations have developed their information systems [in an evolutionary way] and the databases that support them, is very fragmented. Since successful organizations tend to grow over time, and their information systems requirements also grow and change, the process of both developing and maintaining those information systems is an organic and evolutionary one. In stark contrast to the way we would like to develop our data management systems, the real-life process is rather chaotic. Further, the databases are often based on a variety of hardware and software, as well as COTS applications, and the process is exacerbated by the consolidations, mergers, reorganizations, and fluctuations that are prevalent in the normal business environment. Secondly, most business systems are developed to support business operations. Usually, there is very little thought or planning given to the tools that are needed for analysis of data or for obtaining truly useful information from the raw data. Decision-making tools are focused on the daily operations of the organization, which makes the design of the supporting database systems narrowly focused on optimizing for a small subset of the data which are related to the business' operational transactions.

• The prevalence of web-based and mobile applications provide modern organizations [particularly businesses] with a HUGE amount of data in the form of click streams which help them understand customer behaviors and patterns, and create data which can indicate business opportunities if the correct algorithms can be applied. Such data as time, IP address, pages visitied on the site, context from where the page request was made, elapsed time on the page, and other such details can be captured from the interactive data stream, in addition to the more normal data like customer transactions and GPS-enabled location data.

The requirements and abilities of business tools which can be used to make sense [and profit!] from this situation fall under the broad heading of Data Warehousing. The current crop of data mining tools is based on this idea. There are two main topics to be covered here: 1) how does a data warehouse relate to databases in existing operational systems; and 2) how do implementers and users interact with these systems.

Basic Concepts and Needs Analysis

A data warehouse has the following four main attributes:

• Subject-oriented: organized around the key subjects of the enterprise, including customers, students, products, time, and other important data
• Time-variant: data have a time dimension to reflect their applicability and current nature of the data
• Integrated: data are defined using consistent naming conventions, consistent formats, consistent encoding, and other related characteristics which are gathered from several internal systems of the organization and/or even external entities, so that the data warehouse holds a single version of the truth
• Non-updatable: data in a data warehouse are not designed to be updated like normal database values. Data are there for mining, and while data may be *added*, it is never *updated* in the normal CRUD sense

Thus, a data warehouse fills a need to provide an integrated, organization-wide view of high-quality information, and at the same time, requires the information systems department to separate operational and informational systems to improve the performance of each.

To understand the difficulty of this situation, consider the following issues that need to be resolved when constructing a data store to be used for data mining:

• inconsistent keys or key structures: primary keys may not be aligned
• synonymous keys: different names applied to what is intended to reflect the same key
• free-form fields: dissimilar ways of handling typical data such as names or addresses
• inconsistent data values: data in different entities that don't match properly for the same attribute, such as phone numbers
• missing data: values that are null in one attribute that are present in another

Here are three tables [assuming they are in different systems] that show these issues and effects:

STUDENT_DATA
Student_No	Last_Name	MI	First_Name	Phone	Status	…
123-45-6789	Enderly	T.	Mark	213-432-9876	Freshman
234-56 7890	Fahrensworth	Q.	Chester	214-432-9877	Sophomore
345-67-8901	Kadushpah	L.	Jimmy	432-357-9876	Senior

STUDENT_EMPLOYEE
Student_ID	Address	Dept	Hours	…
123-45-6789	1234 Anystreet Circle Terrace, Lost Angles, CA, 98765	Sociology	8
345-67-8901	357 Mesatable Blvd., Burbank, CA. 97531	Mathematics	10

STUDENT_HEALTH
Student_Name	Phone_No	Insurance	Student_ID	…
Mark T. Enderly	213-432-9876	Blue Cross	123-45-6789
Chester Q. Farnsworth	214-432-9876	Aetna	234-56 7890
Jimmy M. Kadushpah	432-357-9876	Aetna	345-67-8901

These examples show the reason why an organization would need to separate the operational systems from the informational systems in the organization. Systems that run the organization in its day-to-day operations need to be fast, reliable, updatable, and must support multiple concurrent users with all aspects of the CRUD operations – as well as maintaining the principles of ACIDity. However, data mining operations require access to database systems that are sprinkled throughout the enterprise and have specifically different requirements on the data engines. The following table illustrates the differences:

Characteristic	Operational System	Informational System
Primary Purpose	Run the organization on a current basis	Support decision-making
Type of data	Current representation of the state of the organization	Historical point-in-time [snapshots] and predictions
Primary users	Clerks, Sales, Admins	Organization leads, analysts, customers
Scope of usage	Narrow, planned, and simple updates and queries	Broad, ad hoc, complx queries and analysis
Design goal	Performance, throughput, availability	Easy, flexible access and use
volume	Many, constant updates and queries on one or a few table rows	Periodic batch updates and queries requiring many or all rows

Data Warehouse Architectures

Data Warehouse architectures have evolved over time, and have many variations. However, there still remain two main versions of the architectural structure that are used for most implementations. The first is the so-called three-level architecture which is characterized by a bottom-up approach. The second is also a three-tier approach that is more top-down in its philosophy. Although they are different in their development philosophy, there are still similarities to be explored and exploited.

There are four basic steps required in the process of building a data warehouse:

1. extract the data from the various internal and external sources. This sounds easy on the surface, but there may be dozens or even hundreds of such systems in a large organization, or that the organization has/needs to access for the data
2. transform and integrate the data before being loaded into the data warehouse store. The data must be verified to be consistent, and any errors that are found must be scrubbed. The data is usually staged as part of this process, so that is can be cleaned before loading into the resulting data store.
3. organize the data warehouse tables and optimize them for decision support. This may require specific tasks to organize/reorganize the data based on its types, structures, relationships, etc.
4. set the database up so that it can be accessed by a variety of different query languages, API's and other analytical tools, so as to provide the informational access that is required. This includes setting up the proper security measures to protect against harm.

These four operations are summed up in the three-letter acronym ETL which means Extract, Transform, and Load. The following diagrams will show some of the important features and operations involved.

From Hoffer, Prescott and Tofi, Modern Database Management, ISBN 978-0-13-600391-5

Data Characteristics

As you might expect, there are several different types of data which are applicable to the three layers; some show up as contrasts within a specific layer, and some are common.

To start with, there is the idea of status data, which is contrasted with event data. The former comprises the state of the database between transactions, much like a baseline or snapshot as we've seen in other contexts. The latter is concerned with the events that have moved the database from one state to a different state, like with a transaction. We've seen examples of these things earlier in the semester. The important point here, though, is that even though both of these can be stored in a data warehouse, the usual type of data to be found there is the status data. The data warehouse is able to provide more useful information based on a series of baseline states; the event data is more useful for maintaining the consistency of a live database.

Another type of data to be dealt with consists of transient data as compared to periodic data. Transient data are data in which changes to the existing records are written over previous records, thus destroying the previous data content entirely. records are deleted without any attempt to preserve the previous contents. On the other hand, periodic data are the data that are never written over, physically altered, or deleted once they are added to the warehouse store. In this case, records usually have some sort of identifier, frequently a time stamp that indicates when the most recent update occurred.

Besides these two contrasts, there are six other kinds of changes to data in a data warehouse store that must be accommodated:

• new descriptive attributes which may be new characteristics of products or of customers that have become important to the operations, and which must be stored and saved; handling these is often done by adding new attributes to the schema
• new organization activity attributes that may evolve due to the operations of the organization, which need to be reflected in the data store; this may be more difficult to handle, based on the specificity of the data that must be added [time of day vs. days of week vs. months]
• new classes of descriptive attributes which requires adding new tables to the database
• descriptive attributes becoming more granular causes multiple problems and can be difficult to solve gracefully without a significant refactoring effort
• descriptive data being related to other descriptive data in the database which causes a new set of relationships to be formed; this may be easy to handle in a graph database, but not so easy in a doc store or relational one
• new sources of data which appear on the scene after the data warehouse is already operational; this situation can cause almost any of previously mentioned changes due to the other data types

Star Schema

The database design which is most often used for a data warehouse is known as a star schema. It has a particular shape, conceptually, which gives rise to its name. It typically had a central table, known as a fact table which provides factual or quantitiative data about an organization, such as the number of units sold, the number of students enrolled, or the number of fatal shootings recorded. The second type of table in the star schema is known as the dimension table. These are the source of the various attributes which are used to qualify, summarize, or categorize the facts to provide output. A single data warehouse might contain several smaller databases, known as data marts, and each of these might also contain several star schemas. The reason for the name star schema is that conceptually the fact table is usually shown at the center of the ERD, and the dimension tables are shown radiating from that hub, so that the shape appears similar to a star. The following diagram should make this clear.

From Hoffer, Prescott and Tofi, Modern Database Management, ISBN 978-0-13-600391-5

Notice a few things about this diagram. First, note that the fact table comprises a series of primary keys which are foreign keys into the dimension tables. This makes the primary key of the fact table what we now know [from our studies this semester] as a composite key that consists of the concatenation of the foreign keys [and perhaps some other components that may not correspond to the dimensions]. Second, the relationship between the each dimension table and the fact table, which is a one-to-many relationship, provides the join paths that allow the queries to execute easily. Third, you can see that the star schema is a denormalized implementation of the relational data model. This is a frequent occurrence in data warehousing and star schemas, because it is better to have all the data available in a few tables than lots of little ones, and because this is not a real-time query situation so speed is of secondary importance to pattern finding. Finally, it should be apparent that the range of questions that will be asked must be known in advance, in order to design the data for efficient, optimal, and easy processing; further once the questions are no longer interesting to the organization, the data mart can actually be disposed of, and new ones can be built to answer new questions. This flexibility is based on the fact that the data mart is extracted from the persistent stores of multiple sources, and does not subsume or replace the data in those stores, so the original data is always available again. Indeed, since much of the data that is incorporated into the data mart is housed in live databases, that data had better not go away, since it is being constantly used by the organization for its operational purposes.

Data Mining Tools

Several flavors of tools exist for data mining. Since users are looking for patterns or trends within a collection of data, there are three main goals:

• Explain some observed event or condition, such as why sales of jacuzzis have increased in Chicago [explanatory]
• Confirm a hypothesis, for example, whether single people are more likely to purchase fire insurance on an apartment [confirmatory]
• Analyze data to find new or unexpected relationships, such as whether the shooter in Las Vegas has links to any domestic terror organization [exploratory]

Tools for performing these kinds of activities fall into a couple of categories, which are all based on the idea of On Line Analytical Processing or OLAP. This term is intended to contrast with the more traditional idea of On Line Transaction Processing [OLTP] which we've seen before. OLAP is sometimes called multidimensional analysis, a term which reflects the star schema nature of the data store that is involved. Several variants of OLAP exist such as multidimensional OLAP [MOLAP] as well as hybrid OLAP [HOLAP], and of course, relational OLAP [ROLAP]. All of these flavors take the basic idea of OLAP and add a specificity to the processing that adapts them to the application for which they are used.

Finally the following table summarizes several of the common data mining techniques in current use:

TECHNIQUE	FUNCTION
Regression	test or discover relationships from historical data
Decision Tree Induction	test or discover if … then rules for decisions
Clustering and signal processing	discover subgroups or segments
Affinity	discover things that have strong mutual relationships
Sequence association	discover cycles of events and behaviors
Case-based reasoning	derive rules from real-world case examples
Fractals	compress large databases without losing information
Neural networks	develop predictive models based on principles modeled after the human brain

Security and Database Vulnerabilities

Security is one of the biggest hot buttons in the world right now. There have been multiple hacks of various sizes and enormous impact over the last several years. I have shown you in class quite a few articles containing tables of data which show these facts.

Here is a short list [definitely NOT comprehensive or exhaustive!] of database security issues:

• Malware infections which can cause unauthorized access, leakage or disclosure of personal or proprietary data, deletion of or damage to the data or programs, interruption or denial of authorized access to the database, attacks on other systems and the unanticipated failure of database services
• Unauthorized activity by authorized database users, database administrators, or network/systems managers
• Misuse by authorized database users, database administrators, or network/systems managers, or inappropriate changes to the database programs, structures or security configurations
• Unauthorized activity by unauthorized users or hackers including all the activities listed above
• Physical damage to database servers caused by computer room fires or floods, overheating, lightning, accidental liquid spills, static discharge, electronic breakdowns/equipment failures and obsolescence
• Data corruption and/or loss caused by the entry of invalid data or commands, mistakes in database or system administration processes, sabotage/criminal damage etc.
• Design flaws and programming bugs in databases and the associated programs and systems, creating various security vulnerabilities (e.g. unauthorized privilege escalation), data loss/corruption, performance degradation etc.

One of the best ways to implement security on a database [or any computing system for that matter] is to implement and enforce a set of policies that by their nature provide an improved measure of security. The first of these is the principle of least privilege. What this means is that each user of the database, at whatever level of access they are assigned, is ONLY granted access to the parts of the system that they need to do their job. For example, a user of a banking database would need access to their account, but not to all the accounts in the system; on the other hand, the DBA will need access to the entire database system, and perhaps some of the underlying hardware or software applications on that system, but not other areas of the system which are used for non-database purposes. In line with this, users at the various levels can be assigned system roles and the system administrator can assign privileges to the roles which facilitates easier maintenance of the system.

Such segregation of roles and tasks needs to be designed into the system from the outset for maximum effect, but can often be added on later. To assist with this effort, there are vulnerability assessment tools which provide system administrators [and DBA's] with reports of what problems a system may be exhibiting in terms of security.

There are several tools that can assist in this effort. One really old set of tools is the WASSP/SECSCN vulnerability scanner program, developed by the US Navy. WASSP is an acronym for Windows Automated Security Scanning Program. It is a standalone program application that runs on all flavors of the modern Windows O/S [except Windows 10] and provides a comprehensive listing of all the vulnerabilities on a particular system. SECSCN is the UNIX/Linux/Solaris version. Both of these programs are now considered obsolete, but are still in use in the industry. WASSP/SECSCN have been superseded by two newer tools. The first of these is the Security Content Automation Protocol [SCAP] scanner, which has an open source version that is available from www.open-scap.org/. Finally, there is the latest and greatest of all of these, which is the Nessus scanning tool. This tool has been designed to assess the status of all the machines on an entire network from a central server, and can produce individual reports as well as aggregate information. All of these vulnerability scanners can assess the operating systems' problems, but only the last one, Nessus, is capable of assessing applications as well.

The National Institute of Standards and Technology [NIST] produces a list of known vulnerabilities, which is titled NIST 800-53: Vulnerability Management. This is a huge document that lists all the possible vulnerabilities by their categories.

No policy is any good unless there is adherence and a set of checks to verify that adherence. To that end, one of the things that is inherent in the 800-53 document is the idea of auditing the systems on a repeating basis. Auditing can be performed at numerous levels and with amazing granularity on modern computing systems, including database systems running on any type of operating system. In addition, many modern database systems have their own built-in auditing mechanisms, as we've seen from other discussions in this class. It is important to remember that the two most prevalent threats to any computing system are social engineering and internal malfeasance. Social engineering is typified by the Hi, I'm Joe from the IT department phone call [which I've described in class]. There are many variants of this method, all of which are intended for the bad guys to gain access through your account. The other item, internal malfeasance, is a bit more slippery. This situation occurs when a user that has valid access to a computing system does something that causes a problem or accesses a part of the system to which they do not have access. Audit logs are the first line of defense against this situation, but it can be difficult to prove that the perpetrator has actual harmful intent, rather than just making a simple mistake. It often doesn't matter though, to the security establishment, which frequently only makes a distinction between an infraction and a violation. Databases have been largely secured against hackers through network security measures such as firewalls, and through the used of network-based intrusion detection and correction systems. While network security controls remain valuable for such operations, it is even more valuable to complement those systems by securing the database systems themselves, and the programs, functions, and data within them. This methodology has become more critical as networks such as the Internet are increasingly opened to wider access. Furthermore, system, program, function and data access controls, along with the associated user identification, authentication and rights management functions, have always been important to track activities of authorized users and administrators.

There are many types of security controls which are appropriate for use with databases, including but no limited to the following:

• Authentication: the act of confirming the truth of an attribute of a single piece of data claimed true by an entity
• Access control: the selective restriction of access to a place or other resource; the act of accessing may mean consuming, entering, or using a resource or system, and permission to access a resource is called authorization – locks and login credentials are two analogous mechanisms of access control
• Auditing: involves observing a database so as to be aware of the actions of database users; DBAs often set up auditing for security purposes to ensure that only those who have explicit permission to access information may do so
• Integrity controls: the maintenance of, and the assurance of the accuracy and consistency of, data over its entire life-cycle is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data
• Application security: encompasses measures taken to improve the security of an application often by finding, fixing and preventing security vulnerabilities; different techniques are used to identify security vulnerabilities at different stages of an applications lifecycle
• Encryption: the process of encoding a message or information in such a way that only authorized parties can access it
• Backups: [more on this below]

Finally, one of the most overlooked and most necessary operations for the security of any database system is a comprehensive backup and recovery strategy. Backup operations need to be done at several different levels, from full backups to incremental daily [or nightly] backups. The backup process needs to be a well-defined and documented one, and the recovery process needs to be tested periodically, to ensure that it works properly.