Purpose and Benefits of an RDBMS PDF

## Purpose and Benefits of an RDBMS The following table lists the different purposes of data | Process | Description | |---|---| | Collect Data | Provides tools to capture data, such as data loading tools (for batch loading) and data entry tools (for single entry). | | Store Data | Provides a robu...

## Purpose and Benefits of an RDBMS The following table lists the different purposes of data | Process | Description | |---|---| | Collect Data | Provides tools to capture data, such as data loading tools (for batch loading) and data entry tools (for single entry). | | Store Data | Provides a robust storage solution with failover features. | | Process Data | Works with Structured Query Language (SQL) and Procedural Languages/Structured Query Language (PL/SQL) to allow data processing. | | Distribute Data | - Provides several users concurrent access to the same data while maintaining integrity. - Has replication/synchronization features that allow quick and reliable data distribution. | | Present Data | Works with software, such as Oracle® Forms and Reports that allows quick and meaningful presentation of data as well as Integrated Development Environment (IDEs), such as SQL Developer allowing easy display of SQL statement results. | | Utilize Data | Works with various 3rd party software that allows data to be utilized in various ways. | ## Process Flow of an RDBMS This is a diagram that shows the flow of data in an RDBMS. * **Collect Data** * Data Encoders * **Store Data** * Disk * Tape * **Process Data** * Batch Data Program * **Distribute Data** * **Utilize Data** * Internet System * Web * **Present Data** * Report * **User 1** * **User 2** * **User 3** ## Data Storage Methods ### Types of Storage Methods - File Based Data Management - Virtual Storage Access Method (VSAM) - Hierarchical Database - Network Database - RDBMS ### File Based Data Management - File based data management systems are the first method used in storing data for computers (used a lot in the early mainframe systems). - Sequential reading refers to retrieving data from the file by reading from the beginning until the data is found. - To delete data in the file, the whole file is re-written without that data. - To update data, the whole file is re-written with the new value of the data. - Normally referred to as 'flat files'. ### Disadvantages of File Based System - Redundancy of Data - Since no relationships are established between the files, data must exist for every file. - Data Integrity Issues - Since data is stored repeatedly in several files, updating one file might not be sufficient, and updates may be necessary for other files with the same set of data. - Complex Data Manipulation - To resolve data integrity issues, a programmer must track down all files requiring updates once a certain data is updated. - Complex File Access - Actual file locations must be used to access data, which creates additional complexity. ### VSAM Based Systems - VSAM were introduced by IBM® during the early 70s. - A key is used to locate a given record. Minimal input/output (I/O) (ideally a single read) is required to fetch data. - VSAM introduced the unique key. - Secondary keys can be defined using VSAM **Example:** Phone Book Data of a Cell Phone: | Name | Phone Number | Instrument | |---|---|---| | John Miller | +1 417 323 3513 | Strum | | Max Grey | +1 325 321 0963 | Marimba | | Julia King | +1 271 898 9862 | Strum | VSAM file for email Address: | Name | Email | |---|---| | John Miller | [email protected] | | Max Grey | [email protected] | | Julia King | [email protected] | ### Disadvantages of VSAM Based Systems - Referencing keys are not available. - Data is still redundantly created for various sets of data. ### Hierarchical Database - Introduced by IBM® in the mid 60s through Information Management System (IMS). It is the first logical database model. - Data has a tree-like structure. - Allows a 1:N or 1-to-many relationship. - Child elements are accessed through the parent node only **Example:** Name and Address details stored hierarchically: | NAME | Address 1 | Address 2 | | --- | --- | --- | | | Phone 1 | Phone 2 | | | Phone 3 | Phone 4 | ### Disadvantages of Hierarchical Databases - To access a child node, one must go through the parent node. - Hierarchal databases do not allow a many-to-many relationship. A child can only have one parent. - The design of the hierarchical tree is usually based on one type of application. To serve another application, a different type of hierarchical tree would be required. With this situation, data redundancy would be inevitable. ### Network Database - Developed in the late 60s and was standardized by Conference On Data Systems Languages (CODASYL). - Example of a popular network database: Integrated Database Management System (IDMS) from Computer Associates. - Allowed N:N or many-to-many relationships. - Pointers are used to link daťa elements. - Data redundancy was eliminated completely. **Example:** | Manager | Clerk | Store | Transaction | Customer | Discounts | |---|---|---|---|---|---| ### Disadvantages of Network Databases - The pointer concept was cumbersome to use and access the system. - Network Database was not user-friendly and was geared towards highly advanced programmer. - Maintenance of relationships was cumbersome due to the pointer concept. ## Relational Data Base Management System (RDBMS) - RDBMS was developed in IBM® by Dr. E. F. Codd in the early 70s, as a result of investigating ways to solve the limitations of existing models. - The model was introduced in the paper as 'a Relational Model of Data for Large Shared Databanks'. - RDBMS is based on relational algebra (a mathematical model). - The table (also called a relation) is used to store data. This is the core of the relational model. Tables are composed of multiple rows and columns. - Data stored in tables is independent from the application. This implies that several applications can use the same set of tables. - Maintenance of relationships between tables is easy because tables can be created and removed any time. - RDBMS provides a robust Standardized Query Language (SQL) ## Overview of the RDBMS Advantage | Name | Level | Dept | Dept | Location | |---|---|---|---|---| | MARK | STF | IT | IT | Building A | | JEN | STD | CAFE | HR | Building B | | CAROL | STF | IT | CAFE | Building C | | JERRY | SUP | HR | ADMIN | Building B | - An employee is defined by his/her name, level, and department, while a department is defined by its department name (Dept) and location. A relationship exists between the two tables via the department name. - We can see that Mark is an IT personnel and is therefore located in Building A. In case the IT department moves location from Building A to B, it can be easily concluded here that Mark will also be moved from Building A to B. - The relationship between tables or constraints are independent from the table and can be created and removed any time without affecting the table. Unlike previous models, removing relationships means breaking down the data structure itself. ## Difference Between Relational and Hierarchical Database | Category | Hierarchical | Relational (RDBMS) | |---|---|---| | Navigational/ Data Access | Fixed | Dynamic | | Data Structure Flexibility | Permanent | Easier to Change/Modify | | Speed of Access | Fast | Possibly Slower | | Development Cycle | Slow | Faster | | Data Integrity | Very Little | Has various ways of ensuring data integrity | ## Different Uses of Databases **Most common uses:** - Looking for a phone number in your mobile phone - Receiving an automatic email from a website you visited - Receiving book suggestions from an online bookstore - Receiving your pay slip - Swiping your company ID to enter company premises - An online music store providing intelligent play lists - Purchasing a downloadable software from the Web - Viewing the sales forecast based on last year's trends **Two Common Types of Databases:** - **Online Transaction Processing (OLTP) Database** - This type of database is used for applications that handle immediate user transactions, such as entering data or retrieving specific data from a screen or printed report. Multiple users can access this database simultaneously without having problems related to data contention or integrity. - **Example:** - The database used in creating a web-based email account. - The database used in a government office issuing Social Security Numbers. - **Online Analytical Processing (OLAP) Database** - This type of database is used for applications that handle massive data analysis used for report generation and forecasting. Data is entered into an OLAP massively from various systems in batch mode. Normally, users are not looking for a specific set of data, but rather, patterns or behaviors of data. - **Example:** - The database used to generate sales trends. - The database used to capture customer behavior. - Both these examples use data warehouses that collect data over a period of time to generate trends and behavioral patterns.