Patent Issued for System And Method Of Insurance Database Optimization Using Social Networking (USPTO 10,331,664)

2019 JUL 04 (NewsRx) -- By a News Reporter-Staff News Editor at Insurance Daily News -- Hartford Fire Insurance Company has been issued patent number 10,331,664, according to news reporting originating out of Alexandria, Virginia, by NewsRx editors.

The patent’s inventor is Meade, Kevin (Norwich, CT).

This patent was filed on September 23, 2011 and was published online on July 8, 2019.

From the background information supplied by the inventors, news correspondents obtained the following quote: “Insurance companies deal with large amounts of data. This data includes: account information, such as insurance premiums, deductibles, coverage limits, and the like; profile information, such as social security numbers, age of each individual covered by a plan, marital status, employment information, residence address, length of commute to work, accident reports, driving records and the like; and property information, such as the types of properties covered by the insurance including homes, boats, cars, recreational vehicles and the like. By way of non-limiting example, an insurance company may have records about an individual representing the type of car that is owned including make, year, model, color, where the car is domiciled, whether the car is garaged, driveway parked, or parked on the street, the individual’s age, residence, and commute distance, family history, and medical conditions. An individual’s information may be expanded depending on the number of insurance or other products that the individual has with an insurance company. Factor this amount of data with the hundreds of thousands or even millions of individuals that an insurance company represents, and it is evident that the amount of data is extremely large.

“In order to handle the data efficiently, insurance companies have turned to databases, and in particular to relational databases. A relational database is a grouping of data using common characteristics within the data set. The performance of a relational database is based on the amount of data that is included in the database, the commonality of the characteristics, and the query optimizer of the database. As the amount of data contained in relational databases has increased in the growing computer explosion, and the fact that the computer age has allowed insurance companies to incorporate and track more information, the performance of these relational databases has suffered.

“A performance degradation of relational databases can have a dramatic impact on insurance companies. For example, insurance companies reduce the queries that are performed, thereby working with less information than an optimized database can provide. Additionally, the backup file sizes for these ever growing databases impart an ever increasing cost based on the size and the ability to create backup files. Further, random access memory and buffer need to be larger and/or more data pages need to be processed from disk.

“This leaves the query optimizer as a driver of reversing the trend of slower relational datable performance as the optimizer minimizes the impact associated with the overall size of the database. The query optimizer operates by understanding facts about the data in the database. How well these facts are known, how many facts are understood, and the ability to collect the underlying facts determine the ability and functionality of the query optimizer.

“In order to clearly set forth the issue related to the query optimizer and statistics collection, an analogy will focus on two rooms of boxes with a goal of moving all of the boxes together into a single room. By way of analogy, the rooms are data tables in a relational database and the boxes represent rows in the tables. Having two rooms presents two options--move all of the boxes from the first room to the second room or vice versa. Not knowing anything about the rooms, the boxes or the number of boxes in each room, makes the decision of which set of boxes to move blind as to which option is cheaper and more efficient. That is, easier to achieve. So without any information, a blind decision is made and all of the boxes in the first room are moved to the second room and the goal of getting all of the boxes in one room is achieved. However, it is unclear if the goal was achieved in the most efficient manner.

“Now using the same initial scenario, some information about the number of boxes in each room is known; the first room has ten boxes and the second room has one. Using this information, the most efficient solution would be to move the one box from the second room to the first room.

“Now with ten boxes in the first room and one box in the second room, it is determined that each of the ten boxes in the first room weighs one pound and the one box in the second room weighs five-hundred pounds. With this additional information, a more efficient solution is to move the ten one pound boxes from the first room to the second room.

“Now in this scenario, additional information arises that there is a hand truck available in the second room. Using the hand truck, the five-hundred pound box is not too difficult to move and therefore the more efficient solution is to use the hand truck and move the one box from the second room to the first room.

“These scenarios demonstrate, by way of analogy, that even with only two options, as more information is provided about the environment, the cost associated with selecting one room of boxes to be moved over the boxes in the other room changed and thus one of the options outweighed the other in efficiency. Gaining information about the number of boxes in a room and the weight of the boxes is statistics collection. Statistics collection provides information to optimize the task at hand. Providing information helps in selecting the most efficient optimization strategy, but misinformation and/or partial information may often lead to the selection of an inefficient solution.

“Thus, there exists a need for a system and method that enables collection of statistics associated with a database, and identifies situations where only partial information about the database is obtained.”

Supplementing the background information on this patent, NewsRx reporters also obtained the inventor’s summary information for this patent: “A system and method of collecting statistics on a database for performing at least one insurance related structured query language (SQL) query are disclosed. The system for collecting statistics on a database for performing at least one insurance related query includes a processor for processing an insurance related query and a memory device configured to store information in a database, said database containing insurance related data organized in a plurality of tables relating the data in the database, wherein the plurality of tables are responsive to the insurance related query. The relationships of the plurality of tables are determined responsive to the insurance related query, and the relationships are transformed into a grouping of communities of tables.

“The method of collecting statistics on a database for performing at least one insurance related structured query language (SQL) query includes processing at least one insurance related SQL query, determining the relationships of tables of a database stored in a memory device, the tables responsive to the at least one insurance related SQL query, and transforming the determined relationships of tables into a grouping representing communities of tables. The tables represent insurance data.

“A computer readable medium including hardware design code stored thereon which when executed by a processor cause the system to perform a method for collecting statistics on a database for performing at least one insurance related SQL query is also disclosed. The performed method includes processing at least one insurance related SQL query, determining the relationships of tables of a database stored in a memory device, the tables responsive to the at least one insurance related SQL query, and transforming the determined relationships of tables into a grouping representing communities of tables. The tables represent data associated with at least one aspect of insurance.

“The method of optimizing an database based on a structured query language (SQL) workload including at least one query of the database may include determining the relationships of tables of a database stored in a memory device, the tables responsive to the at least one insurance related SQL workload, transforming the determined relationships into a node form, formatting the node form into a data stream, and displaying the data stream in one or more pictures for visualizing communities of tables associated with the SQL workload in the database. The database is optimized by collecting statistics on communities of tables.”

The claims supplied by the inventors are:

“What is claimed is:

“1. A system for collecting statistics on a database for performing at least one structured query language (SQL) query, the system comprising: a memory for storing data in a database organized in a plurality of tables; and a processor coupled to the memory, the processor, accessing the memory, to: process the at least one SQL query related to insurance data; determine one or more interrelationships of the plurality of tables in the database by parsing the SQL query and identifying common dependencies of the tables after parsing the SQL query, wherein the determining interrelationships and identifying common dependencies at least includes identifying perfect communities and imperfect communities and for imperfect communities identifying one or more of the plurality of tables that reference another community; transform the determined interrelationships of the plurality of tables into a group representing communities of tables based on the common dependencies by quantifying a risk of the imperfect communities and adding in additional tables with the plurality of tables; perform the at least one SQL query on the transformed group of tables.

“2. The system of claim 1 wherein the plurality of tables are interrelated.

“3. The system of claim 1 further comprising a display on which a representation of the grouping of communities of tables is rendered.

“4. The system of claim 3 wherein the display presents a depiction of the interrelation using communities of tables.

“5. The system of claim 1 further comprising an optimizer for collecting statistics on the plurality of tables and the collected statistics are used to optimize the insurance related query.

“6. The system of claim 1 wherein the communities of tables represent at least one perfect community of tables.

“7. The system of claim 1 wherein the communities of tables represent at least one imperfect community of tables.

“8. The system of claim 1 wherein a risk associated with the at least one table referencing another community of tables is managed using hints.

“9. The system of claim 1 wherein a risk includes cascading.

“10. The system of claim 1 wherein the insurance related query is a query for a client request rate fact quote.

“11. A method of collecting statistics on a database for performing at least one structured query language (SQL) query, the method comprising: processing at least one SQL query related to insurance data; determining one or more interrelationships of tables in a database stored in a memory device by parsing the query and determining common dependencies of the tables identified after the parsed query, the one or more interrelationships of tables responsive to the at least one SQL query, wherein the determining interrelationships and identifying common dependencies at least includes identifying perfect communities and imperfect communities and for imperfect communities identifying one or more of the tables that reference another community; transforming the determined interrelationships of tables into a group representing communities of tables with the one or more interrelationships of tables based on the common dependencies by quantifying a risk of the imperfect communities and adding in additional tables with the tables; and perform the at least one SQL query on the transformed group of tables, wherein the one or more interrelationships of the plurality of tables in the database represent insurance data.

“12. The method of claim 11 further comprising collecting statistics on the at least one community of tables based on the at least one insurance related SQL query using the collected statistics to optimize the insurance related query.

“13. The method of claim 11 wherein the at least one insurance related SQL query comprises client request rate fact quotes.

“14. The method of claim 11 wherein the communities of tables represent at least one perfect community of tables.

“15. The method of claim 11 wherein the communities of tables represent at least one imperfect community of tables.

“16. The method of claim 15 further comprising denoting for each of the at least one imperfect community of tables the at least one table referencing another community of tables.

“17. The method of claim 16 further comprising managing a risk associated with the denoted at least one table.

“18. The method of claim 17 wherein the risk includes cascading.

“19. The method of claim 17 wherein the risk is managed using hints.

“20. The method of claim 11 further comprising displaying the grouping of tables to graphically depict the relationships of tables.

“21. A non-transitory computer readable medium including hardware design code stored thereon which when executed by a processor cause the system to perform a method for collecting statistics on a database for performing at least one insurance related SQL query, the method comprising: processing at least one insurance related SQL query; determining one or more interrelationships of tables of a database stored in a memory device by parsing the query and determining the dependencies of the tables identified after the parsing query, wherein the one or more relationships of tables responsive to the at least one insurance related SQL query, wherein the determining interrelationships and identifying common dependencies at least includes identifying perfect communities and imperfect communities and for imperfect communities identifying one or more of the plurality of tables that reference another community; transforming the determined interrelationships of tables into a group representing communities of tables with the interrelationships of tables based on common dependencies by quantifying a risk of the imperfect communities and adding in additional tables with the plurality of tables; and perform the at least one SQL query on the transformed group of tables, wherein the one or more interrelationships of tables represent data associated with at least one aspect of insurance data.”

For the URL and additional information on this patent, see: Meade, Kevin. System And Method Of Insurance Database Optimization Using Social Networking. U.S. Patent Number 10,331,664, filed September 23, 2011, and published online on July 8, 2019. Patent URL: http://patft.uspto.gov/netacgi/nph-Parser?Sect1=PTO1&Sect2=HITOFF&d=PALL&p=1&u=%2Fnetahtml%2FPTO%2Fsrchnum.htm&r=1&f=G&l=50&s1=10,331,664.PN.&OS=PN/10,331,664RS=PN/10,331,664

(Our reports deliver fact-based news of research and discoveries from around the world.)