Image Post Format

tumblr_ms9mpnB2o61st5lhmo1_1280Maecenas faucibus mollis interdum. Donec sed odio dui. Sed posuere consectetur est at lobortis. Vestibulum id ligula porta felis euismod semper.

Etiam porta sem malesuada magna mollis euismod. Integer posuere erat a ante venenatis dapibus posuere velit aliquet. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor. Duis mollis, est non commodo luctus, nisi erat porttitor ligula, eget lacinia odio sem nec elit. Donec ullamcorper nulla non metus auctor fringilla. Praesent commodo cursus magna, vel scelerisque nisl consectetur et. Maecenas faucibus mollis interdum.

Cras justo odio, dapibus ac facilisis in, egestas eget quam. Duis mollis, est non commodo luctus, nisi erat porttitor ligula, eget lacinia odio sem nec elit. Curabitur blandit tempus porttitor. Donec id elit non mi porta gravida at eget metus. Cras mattis consectetur purus sit amet fermentum. Etiam porta sem malesuada magna mollis euismod. Sed posuere consectetur est at lobortis.

Etiam porta sem malesuada magna mollis euismod. Maecenas sed diam eget risus varius blandit sit amet non magna. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus. Nulla vitae elit libero, a pharetra augue. Nulla vitae elit libero, a pharetra augue.

Standard Post Format

tumblr_n7fgaogdzA1st5lhmo1_1280
Nulla vitae elit libero, a pharetra augue. Nulla vitae elit libero, a pharetra augue. Nullam quis risus eget urna mollis ornare vel eu leo. Cras mattis consectetur purus sit amet fermentum. Aenean lacinia bibendum nulla sed consectetur. Nullam quis risus eget urna mollis ornare vel eu leo. Vestibulum id ligula porta felis euismod semper. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor. Aenean eu leo quam. Pellentesque ornare sem lacinia quam venenatis vestibulum. Maecenas sed diam eget risus varius blandit sit amet non magna. Nulla vitae elit libero, a pharetra augue. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor. Integer posuere erat a ante venenatis dapibus posuere velit aliquet. Aenean eu leo quam. Pellentesque ornare sem lacinia quam venenatis vestibulum. Vestibulum id ligula porta felis euismod semper. Aenean eu leo quam. Pellentesque ornare sem lacinia quam venenatis vestibulum. Sed posuere consectetur est at lobortis. Aenean eu leo quam. Pellentesque ornare sem lacinia quam venenatis vestibulum. Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Curabitur blandit tempus porttitor. Maecenas faucibus mollis interdum. Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Aenean lacinia bibendum nulla sed consectetur. Vestibulum id ligula porta felis euismod semper.

Evaluation of Database Modeling & RDBMS

Database Model: A database model is a blueprint for how data is stored in a database and is similar to an architectural approach for how data is stored—a pretty picture commonly known as an entity relationship diagram (a database on paper). A database, on the other hand, is the implementation or creation of a physical database on a computer. A database model is used to create a database.

The evolution of database modeling techniques

File System: Using a file system database model implies that no modeling techniques are applied and that the database is stored in flat files in a file system, utilizing the structure of the operating system alone. The term “flat file” is a way of describing a simple text file, containing no structure whatsoever—data is simply dumped in a file.

Hierarchical Database Model: The hierarchical database model is an inverted tree-like structure. The tables of this model take on a child-parent relationship. Each child table has a single parent table, and each parent table can have multiple child tables. Child tables are completely dependent on parent tables; therefore, a child table can exist only if its parent table does. The result of this structure is that the hierarchical database model supports one-to-many relationships.

Network Database Model: The network database model is essentially a refinement of the hierarchical database model. The network model allows child tables to have more than one parent, thus creating a networked-like table structure. Multiple parent tables for each child allows for many-to-many relationships, in addition to one-to-many relationships. In an example, an employee can be assigned many tasks, and a task can be assigned to many different employees.

Relational Database Model: The relational database model improves on the restriction of a hierarchical structure, not completely abandoning the hierarchy of data. Any table can be accessed directly without having to access all parent objects. The trick is to know what to look for—if you want to find the address of a specific employee, you have to know which employee to look for, or you can simply examine all employees. You don’t have to search the entire hierarchy, from the company downward, to find a single employee.

According to Gavin Powell:

Relational Database Management System: A relational database management system (RDBMS) is a term used to describe an entire suite of programs for both managing a relational database and communicating with that relational database engine. In other words, an RDBMS is both the database engine and any other tools that come with it. RDBMS is just another name for a relational database product. It’s no big deal.

According to Gartner: 

A database management system (DBMS) that incorporates the relational-data model, normally including a Structured Query Language (SQL) application programming interface. It is a DBMS in which the database is organized and accessed according to the relationships between data items. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence.

A relational DBMS is special system software that is used to manage the organization, storage, access, security and integrity of data.  This specialized software allows application systems to focus on the user interface, data validation and screen navigation.  When there is a need to add, modify, delete or display data, the application system simply makes a “call” to the RDBMS. 

Object Database Model: An object database model provides a three-dimensional structure to data where any item in a database can be retrieved from any point very rapidly. Whereas the relational database model lends itself to retrieval of groups of records in two dimensions, the object database model is efficient for finding unique items. Consequently, the object database model performs poorly when retrieving more than a single item, at which the relational database model is proficient. 

Object-Relational Database Model: The object database model is somewhat spherical in nature, allowing access to unique elements anywhere within a database structure, with extremely high performance. The object database model performs extremely poorly when retrieving more than a single data item. The relational database model, on the other hand, contains records of data in tables across two dimensions. The relational database model is best
suited for retrieval of groups of data, but can also be used to access unique data items fairly efficiently. The object-relational database model was created in answer to conflicting capabilities of relational and object database models.

Reference: Powell, Gavin. 2006. Beginning Database Design. Indiana, USA. Wiley Publishing, Inc.

Random Tips

Note: Throughout this course, the words keyword, clause, and statement are used as follows:

    • A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords.
    • A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, … is a clause.
    • A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement.

Arithmetic with Dates

Operation Result Description
date + number Date Adds a number of days to a date
date – number Date Subtracts a number of days from a date
date – date Number of days Subtracts one date from another
date + number/24 Date Adds a number of hours to a date

Using Date Functions

Assume SYSDATE = ’25-JUL-03′

Function Result
ROUND(SYSDATE,’MONTH’) 01-AUG-03
ROUND(SYSDATE ,’YEAR’) 01-JAN-04
TRUNC(SYSDATE ,’MONTH’) 01-JUL-03
TRUNC(SYSDATE ,’YEAR’) 01-JAN-03
MONTHS_BETWEEN (’01-SEP-95′,’11-JAN-94′) 19.6774194
ADD_MONTHS (’11-JAN-94′,6) ’11-JUL-94′
NEXT_DAY (’01-SEP-95′,’FRIDAY’) ’08-SEP-95′
LAST_DAY (’01-FEB-95′) ’28-FEB-95′

General Function

      • NVL Converts a null value to an actual value. Example: NVL(number_column,9), NVL(date_column, ’01-JAN-95′), NVL(character_column, ‘Unavailable’)
      • NVL2 If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type. Example: NVL2(commission_pct, ‘SAL+COMM’, ‘SAL’)

continue…