RDBMS and Relational Model

 RDBMS

CRUD - create, read, update and delete
Data - any information which can help you to make a decision or can help to make a choice. Data is of different type (Notes, Phone number, to-do list, pics etc.)

Some of the application to read a data:
Plain text reader, excel, access and other applications.

Drawbacks of Excel which database overcome:

  • Inefficiency to access data - If need to access a data to specific id then as need to iterate for informaiton, hence timecomplexity is O(n)
  • Data Integrity - Columns can have any kind of data even numeric column can have string data which is not right.
  • Concurrency - Allowing multiple users to read data at a time form same file. Which should not interfere with requirements, and don't manipulate the data.
  • Data Security - Only authorized users should be able to access the data. Hence we have management and rules for this security enforcement.

Scenario to understand need of database:

If dats is present in excel sheet then steps to find psp of students in excel as a programmer will be.
  1. Open/Read file
  2. Iterate fields and values for information
  3. extract psp data from relevent locations.
  4. calculate it for result.
TIme complexity for this task is: O(N). As we have to read each character of file for information identification and then to extract it for information computation.


So file system has drawbacks as we need to read entire file for information gathering and then can work on real task. Even a developer unintentionally can manupulate data which will lead to errors.

Drawbacks:

  • Inefficient - As least T.C = O(n) and it can increase as well.
  • Data integrity - we can have wrong data in wrong location or column as we don't restrict user to provide only specific type of data.

    • Data is inaccurate
    • We got text in numeric column. PSP column must have numeric data but received text data at row 4. As excel don't restrict user at time of uploading data. As we can't hard bound this rule to have only revelent data in revelent column.
  • Concurrency - Simultaneous access of file. In short case when multiple people want to access same file at same time and want to modify data for their usage. 
    • As normal files don't have this rule to allow which user can write the data and who can overwrite it hence it is causing concurrency issue.
  • Data security issue: As anyone can access data and can modify it.

Database:

Example based understanding.

As you can see from picture each catagory has certain rules to define nature of data which can reside in them.
  • Army Base - only troops and vehicles or any other equipment which can work on ground and can use for war can stay in this category.
  • Air Base - All things which can fly and can be used for war come under this category and those things can't go too much far away from ground or country region due to fuel issue.
  • Navel base - All things which can swim and can be used for war or can carry things for war by using water as travel medium will come under this category. 
So as you can see it has certain rules which will force users to put only relevent information to relevent location.

So all four drawbacks of file system is taken care by database so database is a management system which will help you to have rules implemented for data storage and usage by any user.

This mangement system is know an Database Management System( DBMS).

DBMS:

  • A software system that allows to efficiently manage a database.
  • DBMS allow operations:
    • Create
    • Read
    • Update
    • Delete 
  • Rule enforced by DBMS:
    • Data integrity
    • Security
    • Concurrency




Types of Databases:

  • Relational Database - 
    • Data is stored in form of tables
    • Tables have relationship amoung them to provide relevent data for information. In short inter-related tables are used for data storage.
  • Non -Relational Database -
    • Son't store data in form of table.
    • Store data in form of documents, key-value pairs, graphs etc.

Properties of RDBMS:

  • Relational databases represent database as a collection of tables with each table storing information about something.
  • Every row is unique. As you can see in above images each record is unique in a table. Reason if we have duplicate records then we will have ambiguity to update or to fetch data from table. As it will cause issue to uniquely identify the record in table for information manupulation or read. columns can have duplicate but entire record as a whole should not be duplicate.
  • A column should have all values of same data type. As you can see in above attached tables screen shots.
    • It can help us to hardbound columns to store specific data type data in a column.
  • All values/cell should be atomic in nature. As you can see in below snapshot a cell can't have two values as it will lead to ambiguity.
  • The sequence of column is not guaranteed by RDBMS. MySQL do preserve column order. You can mention any column name in any sequence and can put information in a table.
    • insert into table column(a,b,c,d) values(e,r,f,g);
    • insert into table column(b,a,c,d) values(e,r,f,g);
  • The sequence of row is not guarenteed. It means even after inserting records in a database in specific order row can suffle there positions in database. MySQL so preserve row order. 

MySQL preserve both column order and row order. But for RDBMS it is not manditory to preserve order or row and column.

  • Name of every column has to be unique.
    • in order to avoid ambiguity we need to have unique names of each columns and need to have unique record as well.

Keys:

Unique identifier for any record in a table.

Types of Keys:

  • Super Keys 
  • Candidate Keys
  • Primary Keys
  • Composite Keys
  • Foreign Keys
Super Keys:
It is a key which can help user to uniquely identify row in a table.
It uses a column / combination of columns to uniquely identify a row.
In case of super key redundant columns are allowed.

As you can see if you have id then all other columns are redundant.

Comments

Post a Comment

Popular posts from this blog

Aggregate Queries and Views

 Aggregate Queries and Views Aggregate Functions It provide consolidated result it means work on records and then will provide summary of it or the consolidated result of it. It is basically used to find the facts and figures of table. Means max and other similar type of functions which are consolidate in nature. count - it take count of only not null values and will ignore the null values. max min avg sum notes : Distinct is not an aggregate function as it don't combine the records it just help to remove the duplicate records. All aggregate functions by default work on integers or anything which can be converted into countable information. count: Pseudo code: table = [] count = 0 for row in table:     if row[b_id]:          count++ print(count) Note: You cannot use aggregate functions in nested loops. Group by: Trying to make some groups in a data by using some criteria or conditions. It use the column to form the group and take action on that gr...
Read more

Schema Design

Image
 Schema Design Schema Blue print of out database of to define how are we going to create and use our tables in a database. It gives information about: Structure of Database Tables in Database Columns in a Table Primary Key Foreign Key Index Pictorial Representation How to approach a schema design?  Requirement: Scaler will have multiple batches For each batch, we need to store the name, start month and current instructor Each batch of  Scaler will have multiple students. Each batch has multiple classes. For each class, store the name, date and time, instructor of the class. For evey student, we store their name, graduation year, university name, email, phone no. Every student has a buddy, who is also a student A student may move from one batch to another For each batch a student moves to, the date of starting is stored. Every student has a mentor. For every mentor, we store their name and current company name. Store information about all mentor sessions ( time, duration, ...
Read more

Transactions in SQL

Image
 Transactions Now as you can see that in example A is a person who has 1000 rupees in his account and he want to transfer 500 rupees to B who has account balance as 5000. So now this information is stored in a table. Steps to make is possible programatically: Get account balance of A. - > DB call. Check, if A >= amount. - > logical call not a DB call. Debit Rs. 500 from A and updte table. - > DB call Update the balance of B and update the table. - > DB call These 4 steps mechanism complete 1 transaction between two people. Take a scenario: Multiple person are trying to send amount to same accont. Like how it happens at the time of shopping when multiple person comes to purchase goods from a shop then they pay money from their account to shop keeper account. Now suppose a developer has created a method for transaction to transfer amount from one account to another. Scenario: Multiple person are making transaction and transfering amount to same person say is as shopkee...
Read more