Introduction to RDBMS

Database Management System

is a software package where you maintain and control the information to run the business and to do the day to day operations in an organized and user friendly method. In the DBMS we can back up the data, implement the security so that certain information can be modified or viewed by certain people working in the organization. In this section we learn the main concepts which we must follow while working in DBMS.

In simple words, a Database Management System is a Software program which makes the task easier, the task being adding, deleting, modifying and maintain the information. Here are some database terms and concepts.

1. Data
2. Databases
3. Entity, Attribute and Data
4. Entity Relationships
5. Data Models

Data Data is information about some thing. Data gets differed from business to business,FOR EXAMPLE:- For a bus transportation company BUS, DRIVERS, CITY, SEATS OCCUPIED are data, for a computer hardware company MONITOR, CPU, HARD DISK are data.

Databases Database is an area where you save the data needed to run the business. Database provides the following features like Sharing Data,enforcing Business rules,Data security which can be applied centrally

Entity, Attribute and Data An Entity is an object which has some attributes, FOR EXAMPLE:- A employee in an organization has a name and a particular Job.Here EMPLOYEE is an ENTITY, NAME and JOB are ATTRIBUTES. Entity - EMPLOYEE Attribute - EMP_NAME and JOB Data - Name of Employee like (Mike Modano) , his Job is like (Marketing).

Entity Relationships Database contains many Entities (Tables), we can relate an entity to another,FOR EXAMPLE:- In an organization we have many departments and many employees working , here we have two entities one is EMPLOYEE and the other is DEPARTMENT. So how do we relate these two entities?We can RELATE these two entities as:- In one department there can be many employees.

The relationship between two different entities can be classified as:

1. ONE to ONE example PERSON - PASSPORT (one individual can have only one passport)
2. ONE to MANY example DEPARTMENT - EMPLOYEE (In one department there can be many employees working)
3. MANY to MANY example DOCTOR - PATIENT (A doctor can have many patients and a patient can have many doctors)

Concepts of RDBMS

To understand the basic concepts of RDBMS,the following information is provided to you.

Table is the place where you can save all your data in the relational model(RDBMS). Data is organized in terms of Rows and columns in a table. For Example, see the information we have here in employee table.

In the above example,we have 6 employee records. 2 employees for each department.Take a look at it and see how many times we have the same information ie duplicate information. The departments are occuring twice and so more memory space is occupied. To calculate this,

For 6 records we wasted 26 bytes, Imagine having 5000 employees working in the company! Then we are wasting 28 * 5000 bytes = 140000bytes,which is around 13 MB of memory.

Advantages of the new system(Relational model)

• If you split the above shown data into two different tables then it becomes easy to maintain the data
  For example:- If a particular department moved from Dallas to Atlanta,we need to change the city table for deptno 10 in the new system where as in the previous system we have to update all the employee records whose department is deptno 10.
• removes the duplicate information (Data Redundancy) instead of duplicating the Department name and Location for every employee record as seen in the previous system,You can see Department name and Location only once in the Department table based on the common column in the new system
• Reduces the Memory space in the database

We have the same information now as before but the difference is we have two tables instead of one table. To relate the two tables, we need a common column between the tables. In the above example, there is a common column (DEPTNO).

Codd's rules

Primary keys

Constraints

When you bind a business rule to a column in the table then those rules are called the Constraints. Constraints are defined while creating the table. Say for example, you cannot have an employee who does not have a name, then employee name column in employee table should be a NOT NULL column. The NOT NULL is a constraint.

The following table shows the constraint types and short descriptions.

NOT NULL

If we have a business rule saying that all customers should have a name, we cannot have any customer with out a name. So to implement that business rule we can create customer table and specify customer name column as NOT NULL (constraint)

Example

#-- Install Oracle on your machine
CREATE TABLE employee (empno NUMBER(4) PRIMARY KEY, ename VARCHAR(4) NOT NULL);

CHECK

Check constraint is used where we define a condition on a column. Check constraint consists of the keyword

col_name datatype CHECK (col_name in(value1, value2))

Example If you have a business rule saying that all employees in the organization should get atleast $500 then we can use CHECK constraint while creating table.

#-- Install Oracle on your machine
CREATE TABLE employee ( empno NUMBER(4) PRIMARY KEY, ename VARCHAR(4) NOT NULL, 
salary NUMBER(7,2) CHECK (salary > 500) );

DEFAULT

While inserting a row into a table without giving values for every column, SQL must insert a default value to fill in the excluded columns, or the command will be rejected. The most common default value is NULL. This can be used with columns not defined with a NOT NULL.

Default value assigned to a column while creating the table using CREATE TABLE operation.

Example

#-- Install Oracle on your machine
CREATE TABLE item (item_id NUMBER(4) PRIMARY KEY, item_name VARCHAR(15),
item_desc VARCHAR(100), qoh NUMBER(4) DEFAULT 100)

Assigning a default value 0 for numeric columns makes the computation.

PRIMARY KEY

Primary Key in a table is a unique identifier of a row. For example,if you are maintaning the customer profiles, you should assign particular number to each one. So customer_number should be defined as a Primary key in Customer table.

REFERENCES

is a Foreign key. A foreign key column value refers a column in another table to check whether the value exists or not.

UNIQUE

The values entered into a column are unique ie no duplicate values exists.This constraint ensures business that there is no duplicates allowed.

Referential integrity

Normalization

SQL

Hierarchial Database Management System

Based on the concept of hierarchy, entities and relationships are modelled in the form of a Tree.

ORGANIZATION -----> DEPARTMENTS -------> EMPLOYEES

This type of model can represent ONE to ONE and ONE to MANY relationships naturally, but not MANY to MANY relationships.

Network Database Management System

This model is an Extension of Hierarchial Model. Here,

• Child can be related to more than one parent.
• Subordinate entities can be associated with superior entities.

• Cannot model MANY to MANY
• Complex Navigational Paths to get the information.
• Relationships are physical Pointers.

Introduction

RDBMS Concepts

For every programmer who is getting into the IT world, its important to understand the business and the data which gets generated and reused in the business.

Business, now a days generate lots of information through the activities they perform in daily operations. Technology group should gather and store the data / information in a right way. IT programmers should know how to get the data from the databases in an easier and optimized way.

More than 75% of business data gets stored in the Relational database managemet systems. So, its imperative for resources to understand the concepts of RDBMS. No matter what RDBMS you are going to use, the concepts are same across the board.

We follow a unique methodology to teach our students. While explaining a simple concept or a complex concept, we depend on the scenarios. We explain the scenario first, then we go ahead and solve the problem with a concept what we teach. As you get exposure to more business scenarios, you will be able to relate to things better in the actual implementation.

About Insturctor:

Rajesh Kumar V.S, is the course creator. He has a rich experience in Databases, Data Modeling and implementing data warehousing projects. His expertise is to understand the business and model the information in a right way for easy and faster access. He is well versed with different RDBMS in the industry.

Expectation from you

Data Part-1

Data Part-2

Entities

Visualizing Retail Business

Databases

Types of Databases

Relational DBMS

RDBMS Products in the market

Three types of Relationships

Entities and Relationships-Example

Visualizing Data

Tables in RDBMS

Primary Key,Foreign Key

EF Codd's Rules

What is a transaction?

ACID property

Conceptual Model

Sample-Conceptual Model

Representing relationship in Logical and Physical Model

Sample-Logical Model

Sample-Questions from Business

Next Steps