It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Oracle Object Type

Object Types

Oracle implements Objects through the use of TYPEs, defined in a similar way to packages. Unlike packages where the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes/properties, defined in the same way as package variables, and member functions/procedures. If the a type contains member functions/procedures, the procedural work for these elements is defined in the TYPE BODY.
  • Define A TYPE
  • Define A TYPE BODY
  • Defining A Table
  • Constructors
  • Data Access
  • Issues


Define A TYPE

First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).
CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/

Define A TYPE BODY

Next we define a TYPE BODY to add functionality to the getAge member function.
CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;
/

Define A TYPE

First we define a type PersonObj to represent a person. In this example, a person is defined by three attributes (first_name, last_name, date_of_birth) and one member function (getAge).
CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);
/

Define A TYPE BODY

Next we define a TYPE BODY to add functionality to the getAge member function.
CREATE OR REPLACE TYPE BODY PersonObj AS
MEMBER FUNCTION getAge RETURN NUMBER AS
BEGIN
RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
END getAge; END;
/

Defining A Table

Now the object type is defined we can use it as a datatype in a table.
CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);

Constructors

To insert data into the PEOPLE table we must use the PersonObj() constructor. This can be done as part of a regular DML statement, or using PL/SQL.
INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')));
COMMIT;

DECLARE
  v_person  PersonObj;
BEGIN
  v_person := PersonObj('Jane','Doe',
              TO_DATE('01/01/1999','DD/MM/YYYY'));
  INSERT INTO people VALUES (2, v_person);
  COMMIT;
END;
/


Data Access

Once the data is loaded it can be accessed using the dot notation.
  • alias.column.attribute
  • alias.column.function()
SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;

        ID PERSON.FIRST_NAME                  AGE
---------- --------------------------- ----------
         1 John                                 2
         2 Jane                                 2

2 row selected.

SQL>

Issues

  • Once an object is used to define a table only it's BODY cannot be altered. To alter the type all table references to it must be dropped. This means the type definition should be very stable before it is used in a table. Note. Later releases of the database allow Type Evolution.
  • The Export & Import often have difficulties with Object Types.
  • The SQL*Plus COPY command does not work with Object Types.
  • There are a number of issues with database links in conjunction with Object Types. This makes the use of object tables in distributed systems impossible without the use of views to hide the object functionality. This defeats the "object" of Objects somewhat.


Entity Relationship Model

Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.

The very first data model could be flat data-models, where all the data used are to be kept in the same plane. Earlier data models were not so scientific, hence they were prone to introduce lots of duplication and update anomalies.


Entity-Relationship Model

Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints.
ER Model is best used for the conceptual design of a database.
ER Model is based on:
  • Entities and their attributes.
  • Relationships among entities.
These concepts are explained below.

  • Entity − An entity in an ER Model is a real-world entity having properties called attributes. Every attribute is defined by its set of values called domain. For example, in a school database, a student is considered as an entity. Student has various attributes like name, age, class, etc.
  • Relationship − The logical association among entities is called relationship. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of association between two entities.
    Mapping cardinalities:
    • one to one
    • one to many
    • many to one
    • many to many

        Relational Model

        The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-ary relation.
        Relational Model Table
        The main highlights of this model are:

        • Data is stored in tables called relations.
        • Relations can be normalized.
        • In normalized relations, values saved are atomic values.
        • Each row in a relation contains a unique value.
        • Each column in a relation contains values from a same domain.

        The ER model defines the conceptual view of a database. It works around real-world entities and the associations among them. At view level, the ER model is considered a good option for designing databases.

        Entity

        An entity can be a real-world object, either animate or inanimate, that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity.
        An entity set is a collection of similar types of entities. An entity set may contain entities with attribute sharing similar values. For example, a Students set may contain all the students of a school; likewise a Teachers set may contain all the teachers of a school from all faculties. Entity sets need not be disjoint.

        Attributes

        Entities are represented by means of their properties, called attributes. All attributes have values. For example, a student entity may have name, class, and age as attributes.
        There exists a domain or range of values that can be assigned to attributes. For example, a student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be negative, etc.

        Types of Attributes

        • Simple attribute − Simple attributes are atomic values, which cannot be divided further. For example, a student's phone number is an atomic value of 10 digits.
        • Composite attribute − Composite attributes are made of more than one simple attribute. For example, a student's complete name may have first_name and last_name.
        • Derived attribute − Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database, instead it can be derived. For another example, age can be derived from data_of_birth.
        • Single-value attribute − Single-value attributes contain single value. For example − Social_Security_Number.
        • Multi-value attribute − Multi-value attributes may contain more than one values. For example, a person can have more than one phone number, email_address, etc.
        These attribute types can come together in a way like −
        • simple single-valued attributes
        • simple multi-valued attributes
        • composite single-valued attributes
        • composite multi-valued attributes

        Entity-Set and Keys

        Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.
        For example, the roll_number of a student makes him/her identifiable among students.
        • Super Key − A set of attributes (one or more) that collectively identifies an entity in an entity set.
        • Candidate Key − A minimal super key is called a candidate key. An entity set may have more than one candidate key.
        • Primary Key − A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.

        Relationship

        The association among entities is called a relationship. For example, an employee works_at a department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.

        Relationship Set

        A set of relationships of similar type is called a relationship set. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes.

        Degree of Relationship

        The number of participating entities in a relationship defines the degree of the relationship.
        • Binary = degree 2
        • Ternary = degree 3
        • n-ary = degree

        Mapping Cardinalities

        Cardinality defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set.
        • One-to-one − One entity from entity set A can be associated with at most one entity of entity set B and vice versa.
        • One-to-one relation
        • One-to-many − One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.
        • One-to-many relation
        • Many-to-one − More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.
        • Many-to-one relation
        • Many-to-many − One entity from A can be associated with more than one entity from B and vice versa.
        • Many-to-many relation


        ER Diagram Representation

        Let us now learn how the ER Model is represented by means of an ER diagram. Any object, for example, entities, attributes of an entity, relationship sets, and attributes of relationship sets, can be represented with the help of an ER diagram.

        Entity

        Entities are represented by means of rectangles. Rectangles are named with the entity set they represent.
        Entities in a school database

        Attributes

        Attributes are the properties of entities. Attributes are represented by means of ellipses. Every ellipse represents one attribute and is directly connected to its entity (rectangle).
        Simple Attributes
        If the attributes are composite, they are further divided in a tree like structure. Every node is then connected to its attribute. That is, composite attributes are represented by ellipses that are connected with an ellipse.
        Composite Attributes
        Multivalued attributes are depicted by double ellipse.
        Multivalued Attributes
        Derived attributes are depicted by dashed ellipse.
        Derived Attributes

        Relationship

        Relationships are represented by diamond-shaped box. Name of the relationship is written inside the diamond-box. All the entities (rectangles) participating in a relationship, are connected to it by a line.

        Binary Relationship and Cardinality

        A relationship where two entities are participating is called a binary relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.
        • One-to-one − When only one instance of an entity is associated with the relationship, it is marked as '1:1'. The following image reflects that only one instance of each entity should be associated with the relationship. It depicts one-to-one relationship.
        • One-to-one
        • One-to-many − When more than one instance of an entity is associated with a relationship, it is marked as '1:N'. The following image reflects that only one instance of entity on the left and more than one instance of an entity on the right can be associated with the relationship. It depicts one-to-many relationship.
        • One-to-many
        • Many-to-one − When more than one instance of entity is associated with the relationship, it is marked as 'N:1'. The following image reflects that more than one instance of an entity on the left and only one instance of an entity on the right can be associated with the relationship. It depicts many-to-one relationship.
        • Many-to-one
        • Many-to-many − The following image reflects that more than one instance of an entity on the left and more than one instance of an entity on the right can be associated with the relationship. It depicts many-to-many relationship.
        • Many-to-many

        Participation Constraints

        • Total Participation − Each entity is involved in the relationship. Total participation is represented by double lines.
        • Partial participation − Not all entities are involved in the relationship. Partial participation is represented by single lines.
        Participation Constraints




        Generalization Aggregation

        The ER Model has the power of expressing database entities in a conceptual hierarchical manner. As the hierarchy goes up, it generalizes the view of entities, and as we go deep in the hierarchy, it gives us the detail of every entity included.
        Going up in this structure is called generalization, where entities are clubbed together to represent a more generalized view. For example, a particular student named Mira can be generalized along with all the students. The entity shall be a student, and further, the student is a person. The reverse is called specialization where a person is a student, and that student is Mira.

        Generalization

        As mentioned above, the process of generalizing entities, where the generalized entities contain the properties of all the generalized entities, is called generalization. In generalization, a number of entities are brought together into one generalized entity based on their similar characteristics. For example, pigeon, house sparrow, crow and dove can all be generalized as Birds.
        Generalization

        Specialization

        Specialization is the opposite of generalization. In specialization, a group of entities is divided into sub-groups based on their characteristics. Take a group ‘Person’ for example. A person has name, date of birth, gender, etc. These properties are common in all persons, human beings. But in a company, persons can be identified as employee, employer, customer, or vendor, based on what role they play in the company.
        Specialization
        Similarly, in a school database, persons can be specialized as teacher, student, or a staff, based on what role they play in school as entities.

        Inheritance

        We use all the above features of ER-Model in order to create classes of objects in object-oriented programming. The details of entities are generally hidden from the user; this process known as abstraction.
        Inheritance is an important feature of Generalization and Specialization. It allows lower-level entities to inherit the attributes of higher-level entities.
        Inheritance
        For example, the attributes of a Person class such as name, age, and gender can be inherited by lower-level entities such as Student or Teacher.
        Type of Model
        1. Business Process Model
        2. Conceptual Data Model
        3. Data Movement Model
        4. Logical Data Model
        5. Object-Oriented Model
        6. Physical Data Model


        Choosing a Primary Key: Natural or Surrogate?

        This post overviews strategies for assigning primary keys to a table within a relational database. In particular, it focuses on the issue of when to use natural keys and when to use surrogate keys. Some people will tell you that you should always use natural keys and others will tell you that you should always use surrogate keys. These people invariably prove to be wrong, typically they're doing little more than sharing the prejudices of their "data religion" with you. The reality is that natural and surrogate keys each have their advantages and disadvantages, and that no strategy is perfect for all situations. In other words, you need to know what you're doing if you want to get it right. This article discusses:



      • Common key terminology  
      • Strategies for assigning keys
      • Surrogate key implementation strategies
      • Tips for effective keys
      • What to do when you make the "wrong" choice


      • 1. Common Key Terminology

        Let's start by describing some common terminology pertaining to keys and then work through an example. These terms are:

        • Key. A key is one or more data attributes that uniquely identify an entity.  In a physical database a key would be formed of one or more table columns whose value(s) uniquely identifies a row within a relational table. 
        • Composite key. A key that is composed of two or more attributes. 
        • Natural key. A key that is formed of attributes that already exist in the real world.  For example, U.S. citizens are issued a Social Security Number (SSN)  that is unique to them (this isn't guaranteed to be true, but it's pretty darn close in practice).  SSN could be used as a natural key, assuming privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.). 
        • Surrogate key. A key with no business meaning.
        • Candidate key. An entity type in a logical data model will have zero or more candidate keys, also referred to simply as unique identifiers (note: some people don't believe in identifying candidate keys in LDMs, so there's no hard and fast rules). For example, if we only interact with American citizens then SSN is one candidate key for the Person entity type and the combination of name and phone number (assuming the combination is unique) is potentially a second candidate key. Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, an alternate key or perhaps not even a key at all within a physical data model. 
        • Primary key. The preferred key for an entity type.
        • Alternate key. Also known as a secondary key, is another unique identifier of a row within a table. 
        • Foreign key. One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.
        Figure 1 presents a physical data model (PDM) for a physical address using the UML notation. In Figure 1 the Customer table has the CustomerNumber column as its primary key and SocialSecurityNumber as an alternate key. This indicates that the preferred way to access customer information is through the value of a person's customer number although your software can get at the same information if it has the person's social security number.  The CustomerHasAddress table has a composite primary key, the combination of CustomerNumber and AddressID.  A foreign key is one or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.  Foreign keys are used to maintain relationships between rows.  For example, the relationships between rows in the CustomerHasAddress table and the Customer table is maintained by the CustomerNumber column within the CustomerHasAddress table. The interesting thing about the CustomerNumber column is the fact that it is part of the primary key for CustomerHasAddress as well as the foreign key to the Customer table. Similarly, theAddressID column is part of the primary key of CustomerHasAddress as well as a foreign key to the Address table to maintain the relationship with rows of Address.

        Figure 1. A simple PDM modeling Customer and Address.
          

        2. Comparing Natural and Surrogate Key Strategies

        There are two strategies for assigning keys to tables:

        1. Natural keys.  A natural key is one or more existing data attributes that are unique to the business concept.  For the Customer table there was two candidate keys, in this case CustomerNumber and SocialSecurityNumber
        2. Surrogate key. Introduce a new column, called a surrogate key, which is a key that has no business meaning. An example of which is the AddressID column of the Address table in Figure 1. Addresses don't have an "easy" natural key because you would need to use all of the columns of the Address table to form a key for itself (you might be able to get away with just the combination of Street and ZipCode depending on your problem domain), therefore introducing a surrogate key is a much better option in this case.  
        The advantage of natural keys is that they exist already, you don't need to introduce a new "unnatural" value to your data schema. However, the disadvantage of natural keys is that because they have business meaning they are effectively coupled to your business: you may need to rework your key when your business requirements change. For example, if your users decide to make CustomerNumber alphanumeric instead of numeric then in addition to updating the schema for the Customer table (which is unavoidable) you would have to change every single table whereCustomerNumber is used as a foreign key.

        There are several advantages to surrogate keys. First, they aren't coupled to your business and therefore will be easier to maintain (assuming you pick a good implementation strategy). For example, if the Customer table instead used a surrogate key then the change would have been localized to just the Customer table itself (CustomerNumber in this case would just be a non-key column of the table).  Of course, if you needed to make a similar change to your surrogate key strategy, perhaps adding a couple of extra digits to your key values because you've run out of values, then you would have the exact same problem.  Second, a common key strategy across most, or better yet all, tables can reduce the amount of source code that you need to write, reducing the total cost of ownership (TCO) of the systems that you build. The fundamental disadvantage of surrogate keys is that they're often not "human readable", making them difficult for end users to work with. The implication is that you might still need to implement alternate keys for searching, editing, and so on.
        The fundamental issue is that keys are a significant source of coupling within a relational schema, and as a result they are difficult to change. The implication is that you generally want to avoid keys with business meaning because business meaning changes. Having said that, I have a tendency to use natural keys for lookup/reference tables, particularly when I suspect that the key values won't change any time soon, as I describe below. Fundamentally, there is no clear answer as to whether or not you should prefer natural keys over surrogate keys, regardless of what the zealots on either side of this religious argument may claim, and that your best strategy is be prepared to apply one strategy or the other whenever it makes sense.

        3. Surrogate Key Implementation Strategies

        There are several common options for implementing surrogate keys:

        1. Key values assigned by the database.  Most of the leading database vendors – companies such as Oracle, Sybase, and Informix – implement a surrogate key strategy called incremental keys. The basic idea is that they maintain a counter within the database server, writing the current value to a hidden system table to maintain consistency, which they use to assign a value to newly created table rows.  Every time a row is created the counter is incremented and that value is assigned as the key value for that row.  The implementation strategies vary from vendor to vendor, sometimes the values assigned are unique across all tables whereas sometimes values are unique only within a single table, but the general concept is the same.
        2. MAX() + 1.  A common strategy is to use an integer column, start the value for the first record at 1, then for a new row set the value to the maximum value in this column plus one using the SQL MAX function.  Although this approach is simple it suffers from performance problems with large tables and only guarantees a unique key value within the table.  
        3. Universally unique identifiers (UUIDs).  UUIDs are 128-bit values that are created from a hash of the ID of your Ethernet card, or an equivalent software representation, and the current datetime of your computer system.  The algorithm for doing this is defined by the Open Software Foundation (www.opengroup.org).
        4. Globally unique identifiers (GUIDs)GUIDs are a Microsoft standard that extend UUIDs, following the same strategy if an Ethernet card exists and if not then they hash a software ID and the current datetime to produce a value that is guaranteed unique to the machine that creates it. 
        5. High-low strategy.  The basic idea is that your key value, often called a persistent object identifier (POID) or simply an object identified (OID), is in two logical parts: A unique HIGH value that you obtain from a defined source and an N-digit LOW value that your application assigns itself.  Each time that a HIGH value is obtained the LOW value will be set to zero. For example, if the application that you're running requests a value for HIGH it will be assigned the value 1701. Assuming that N, the number of digits for LOW, is four then all persistent object identifiers that the application assigns to objects will be combination of 17010000,17010001, 17010002, and so on until 17019999.  At this point a new value for HIGH is obtained, LOW is reset to zero, and you continue again. If another application requests a value for HIGH immediately after you it will given the value of 1702, and the OIDs that will be assigned to objects that it creates will be 17020000, 17020001, and so on.   As you can see, as long as HIGH is unique then all POID values will be unique.
        The fundamental issue is that keys are a significant source of coupling within a relational schema, and as a result they prove difficult to refactor. The implication is that you want to avoid keys with business meaning because business meaning changes. However, at the same time you need to remember that some data is commonly accessed by unique identifiers, for example customer via their customer number and American employees via their Social Security Number (SSN).  In these cases you may want to use the natural key instead of a surrogate key such as a UUID or POID.    

        4. Tips for Effective Keys

        How can you be effective at assigning keys?  Consider the following tips:
        1. Avoid "smart" keys.  A "smart" key is one that contains one or more subparts which provide meaning.  For example the first two digits of an U.S. zip code indicate the state that the zip code is in. The first problem with smart keys is that have business meaning.  The second problem is that their use often becomes convoluted over time. For example some large states have several codes, California has zip codes beginning with 90 and 91, making queries based on state codes more complex.  Third, they often increase the chance that the strategy will need to be expanded.  Considering that zip codes are nine digits in length (the following four digits are used at the discretion of owners of buildings uniquely identified by zip codes) it's far less likely that you'd run out of nine-digit numbers before running out of two digit codes assigned to individual states.
        2. Consider assigning natural keys for simple "look up" tables. A "look up" table is one that is used to relate codes to detailed information. For example, you might have a look up table listing color codes to the names of colors. For example the code 127 represents "Tulip Yellow". Simple look up tables typically consist of a code column and a description/name column whereas complex look up tables consist of a code column and several informational columns. 
        3. Natural keys don't always work for "look up" tables. Another example of a look up table is one that contains a row for each state, province, or territory in North America.  For example there would be a row for California, a US state, and for Ontario, a Canadian province. The primary goal of this table is to provide an official list of these geographical entities, a list that is reasonably static over time (the last change to it would have been in the late 1990s when the Northwest Territories, a territory of Canada, was split into Nunavut and Northwest Territories). A valid natural key for this table would be the state code, a unique two character code – e.g. CA for California and ON for Ontario.  Unfortunately this approach doesn't work because Canadian government decided to keep the same state code, NW, for the two territories.
        4. Your applications must still support "natural key searches". If you choose to take a surrogate key approach to your database design you mustn't forget that your applications must still support searches on the domain columns that still uniquely identify rows.  For example, your Customer table may have a Customer_POID column used as a surrogate key as well as a Customer_Number column and a Social_Security_Number column.  You would likely need to support searches based on both the customer number and the social security number.  Searching is discussed in detail in Best Practices for Retrieving Objects from a Relational Database.
        5. Don't naturalize surrogate keys. As soon as you display the value of a surrogate key to your end users, or worse yet allow them to work with the value (perhaps to search), you have effectively given the key business meaning. This in effect naturalizes the key and thereby negates some of the advantages of surrogate keys.

        5. What to Do When You Make the "Wrong" Choice

        First of all, don't worry about this: You're only human, and no matter how good you are at database design you're going to make mistakes. The good news is that as I show in The Process of Database Refactoring it is possible, albeit it may require a bit of work, to replace a natural key with a surrogate key (or vice versa). To replace a natural key with a surrogate you would apply the Introduce Surrogate Key refactoring, as you see depicted in Figure 2 to replace the key of the Order table. To replace a surrogate key with a natural key you would apply the Replace Surrogate Key with Natural Key refactoring, as you see in Figure 3 to replace the key of the State table.

        Figure 2. Replacing the natural key of the Order table.

        Figure 3. Replacing the surrogate key within the State table.

        http://www.agiledata.org/essays/keys.html
        http://www.techrepublic.com/blog/10-things/10-tips-for-choosing-between-a-surrogate-and-natural-primary-key/
        http://sqlmag.com/business-intelligence/surrogate-key-vs-natural-key

        Key Differences Between DBMS and RDBMS

        A database is a collection of data that is stored in an organized manner. This data can either be on a computer or on paper. It is, of course, more efficient to store data on a computer, as the computerization of this data makes it easy to retrieve and perform operations on. Today, in the information age, databases of some kind are maintained by all organization, big and small. They are essential in ensuring that the day to day operations of an organization can run smoothly.

        An Introduction to Database Systems

        There are several software-based products that help you build and maintain databases. These software products are known as database management systems (DBMS). In addition to letting you build a database, most DBMS applications will interact with a user, or multiple users. They will also let you perform operations on the data in their repository. DBMS systems are always based on a computer language (which can vary). A user has to type commands in this language if he wants to interact with the data on the system – as well as the software he is using.

        Key Difference between DBMS and RDBMS

        So what is the main difference between DBMS and RDBMS? The key difference is that RDBMS (relational database management system) applications store data in a tabular form, while DBMS applications store data as files. Does that mean there are no tables in a DBMS? There can be, but there will be no “relation” between the tables, like in a RDBMS. In DBMS, data is generally stored in either a hierarchical form or a navigational form. This means that a single data unit will have one parent node and zero, one or more children nodes. It may even be stored in a graph form, which can be seen in the network model.
        In a RDBMS, the tables will have an identifier called primary key. Data values will be stored in the form of tables. The relationships between these data values will be stored in the form of a table as well.  Every value stored in the relational database is accessible. This value can be updated by the system. The data in this system is also physically and logically independent.
        You can say that a RDBMS is an in an extension of a DBMS, even if there are many differences between the two. Most software products in the market today are both DBMS and RDBMS compliant. Essentially, they can maintain databases in a (relational) tabular form as well as a file form, or both. This means that today a RDBMS application is a DBMS application, and vice versa. However, there are still major differences between a relational database system for storing data and a plain database system.

        History of DBMS and RDBMS

        Database management systems first appeared on the scene in 1960 as computers began to grow in power and speed. In the middle of 1960, there were several commercial applications in the market that were capable of producing “navigational” databases. These navigational databases maintained records that could only be processed sequentially, which required a lot of computer resources and time.
        Relational database management systems were first suggested by Edgar Codd in the 1970s. Because navigational databases could not be “searched”, Edgar Codd suggested another model that could be followed to construct a database. This was the relational model that allowed users to “search” it for data. It included the integration of the navigational model, along with a tabular and hierarchical model.

        The Client Server Architecture

        Database management systems like the ones you’ll learn about in this course (at least the pure DBMS applications) do not support the client-server architecture, while relational database management systems do. What is the client-server database model exactly? In a client-server database model, data is stored in a central location on a server. This server can share the data between one or more users, which are referred to as clients. However, this is not a distinction that is relevant today, where a DBMS program is a RDBMS program, and vice versa.

        Ease of Access

        It is generally easier to access data that is stored in a relational database. This is because the data in a relational database follows a mathematical model for categorization. Also, once we open a relational database, each and every element of that database becomes accessible, which is not always the case with a normal database (the data elements may need to be accessed individually).  
        It is also easier to find data in a relational database. You can “query” a relational database in its native language without knowing the value of a key or index.

        Storage Standards

        Relational databases are harder to construct, but they are better structured and more secure. They follow the ACID (atomicity, consistency, isolation and durability) model when storing data. The relational database system will also impose certain regulations and conditions that may not allow you to manipulate data in a way that destabilizes the integrity of the system.
        In a regular database, the data may not be stored following the ACID model. This may introduce inconsistencies in the database. It may even cause the database to become unstable over time or it may put the security of the data at risk.

        You Might Also Like

        Related Posts with Thumbnails

        Pages