TYPES OF DBMS & ADVANTAGES OF NODES() OVER OPENXML()
WHAT IS DATABASE? • A database can be a set of flat files stored on computer tape or disk or it could consist of database tables • In the early days of computing, disk storage was extremely expensive. Most application systems ran in batch mode using data that was stored on magnetic tape. Data had to be read sequentially from flat files. For performance reasons, the management of data was tightly integrated with the application system.
• As the cost of disk storage fell, opportunities to store data for real-time access arose. Specialized DBMS software emerged during the 1960s for the sole purpose of managing data.
• Application systems were then able to focus on the user interface, screen navigation, data validations etc. and could leave the data management tasks to the specialized DBMS technology. The application system simply had to call the DBMS when it needed to read or store data
WHAT IS DBMS? • Database Management System • Single or set of computer programs that are responsible for creating, editing, deleting and generally maintaining a database or collection of data records
• Types of database management system is determined by the database model.
• A database model is the manner in which the data collection is stored, managed and administered.
TYPES OF DBMS 1. Flat File DBMS
• • • •
Simplest and Primitive DBMS
•
Flat files in a formatted row and column model rely on assumptions that every item in a particular model consists of the same data
•
Example is CSV (Comma Separated Values) in spreadsheets .
Also called as Flat models Human readable text formats as well as in binary formats Ideal for stand alone applications, holding software configuration and native format storage models.
TYPES OF DBMS 2. Relational DBMS
• • • • •
•
Most widely used Easy to use As name indicates, this DBMS have a characteristic of normalizing the data which is usually stored in related tables. Relational models may be somewhat less efficient than other models; however this may not be a problem with the processing power and memory found in modern computers. Data in this type of model is stored is fixed predefined structures (i.e. tables) and are usually manipulated using Structured Query Language (SQL). Examples are Oracle, Ms SQL Server, IBM DB2, mySQL, SQLite and PostgreSQL.
TYPES OF DBMS 3. Object Oriented DBMS
• Borrow from the model of the Object-oriented programming paradigm • Object and its data or attributes are seen as one and accessed through pointers rather than stored in • • • • •
relational table models Object-oriented database models consist of diverse structures and is quite extensible. This data model was designed to work closely with programs built with Object-oriented programming languages thereby almost making the data and the program operate as one. With this model applications are able to treat the data as native code. There is only a little commercial implementation of this database model as it is still developing. Examples of Object-oriented database management systems include IBM DB4o and DTS/S1 from Obsidian Dynamics.
TYPES OF DBMS 4. Hierarchical DBMS
• Operates on a parent child tree-like model • These normally have a 1:N relationship and are good for storing data with items describing attributes, features and so on.
• These could store a book with information on chapters and verses. • They can also be used to store a database of songs, recipes, models of phones and anything that can be stored in a nested format.
• Hierarchical database management systems are not quite efficient for various real world operations. • Example is XML document (<bus><rider id="1">Jane</rider><rider id="2">John</rider></bus>)
TYPES OF DBMS 5. Network DBMS
• • •
Similar to Hierarchical
•
Network database management systems tend to be very flexible but are rarely used and were very quite common in the1960s and 1970s.
•
Searching for an item in this model requires the program to traverse the entire data set which is quit cumbersome.
• •
These have mainly been replaced by Relational database management systems in today's modern computing.
Difference - Tree structure in the Network models can have a many parent to many child relational model. (N:N) The Network model structure is based on records and sets and most of these databases use SQL for manipulation of their data.
Examples RDM Server, TurboIMAGE, Univac DMS-1100
TYPES OF DBMS 6. Cloud DBMS
• Hosted by a third-party service provider on a remote server and accessed over the Internet • A traditional database system is installed on a server at an organization’s site and data is stored and accessed directly or over a local area network (LAN)
• A cloud database management system, on the other hand, runs on a cloud provider’s platform and data can only be stored or accessed when there is an Internet connection.
• Can be deployed in 3 ways • Virtual Machine (VM) • The cloud provider sells virtual machine instances upon which a database management system can run. The provider is responsible for the infrastructure that supports the VM .
• Customer is responsible for uploading or purchasing the DBMS, making sure the DBMS is maintained properly and managing the databases it supports
TYPES OF DBMS 6. Cloud DBMS • Database as a service (DBaaS) • The cloud provider is responsible for supplying and maintaining the DBMS. • The customer is responsible for managing the database, DBMS support , paying for storage and compute resources.
• Managed Hosting
•
• The cloud provider installs, maintains and manages the entire database implementation Pre deployment factors/parameters (When and how)
• • • •
Performance Budget Data governance - regulatory compliance burden
Staffing
ADVANTAGES OF NODE() OVER OPENXML() • The nodes() method yields an internal reference for each selected node that can be used for additional query.
• The nodes() method is useful when you want to convert an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
• Multiple value() methods on xml data type in a SELECT clause to generate a rowset of extracted values. • The combination of the nodes() and value() methods can be more efficient in generating the rowset when it has several columns and, perhaps, when the path expressions used in its generation are complex.
• The nodes() method yields instances of a special xml data type, each of which has its context set to a different selected node. This kind of XML instance supports query(), value(), nodes(), and exist() methods and can be used in count(*)aggregations. All other uses cause an error.
ADVANTAGES OF NODE() OVER OPENXML() • OpenXml() creates an in-memory representation and uses work tables instead of the query processor. • OpenXml() allows you to access an edge table format for the XML data when the WITH clause is not specified. Also, it allows you to use the remaining XML value in a separate, "overflow" column.
• But it relies on the XPath version 1.0 processor of MSXML version 3.0, instead of the XQuery engine. The work tables are not shared among multiple calls to OpenXml(), even on the same XML instance. This limits its scalability.
• The combination of nodes() and value() functions uses XML indexes effectively. As a result, this combination can exhibit more scalability than OpenXml.
• When shredding XML using the nodes query, the performance is fine when just SELECT. However, when you want to INSERT the data to a table/temp table/table variable the performance becomes very poor.