Data:

Data is a rawfact which describes attributes of an entity.

Meta Data

The details about the data is called meta data

Database:

Database is a place or media where data will be stored in systematic and organised manner.
The basic operations performed in database are:

  1. Create
  2. Read
  3. Update
  4. Delete

Database Management System(DBMS):

DBMS is a software used to maintain and manage database.
It provides two important factors.

  1. Security
  2. Autherisation

We use Query language to communicate with DBMS.

Relational Database Management System(RDMS):

RDBMS is a type of DBMS software where data will be stored in the form of tables.
Even RDBMS provides two factors.

  1. Security
  2. Autherisation

We use Structured Query Language(SQL) to communicate RDBMS.
SQL is case-insensitive.

Relational Model:

It is a concept which was designed by a data scientist whose name is E.F.Codd.
It is designed to store the data and whatever the data we store it will be in the form of tables.
A DBMS software which follows a Relational Model becomes Relational Database Management System. Hence in RDBMS the data will be stored in the form of tables.

Table: Table is logical organisation of data.
It consists of Rows and Columns.

Columns: Columns are also reffered as attributes or fields.
It is used to determine one property of all the entities.

Rows: Rows is also reffered as records.
It is used to determine all the properties of one entity.

Cell: It is the smallest unit in the table where data will be stored.
It is generated by the intersection of rows and columns.

Rules of E.F.Codd

  1. The data stored in the cell must be a single value data.
  2. In RDBMS we store everything in the form of tables including meta data.
  3. According to E.F.Codd we can store data in multiple tables. If needed we can establish connection between two tables using key attributes.
  4. We can validate the data entered into the table in two steps.
    1. By Assigning Datatypes
    2. By Assigning Constraints
Table

Note: Datatypes are mandatory whereas constraints are optional.

Types of Datatypes:

  1. char
  2. varchar/varchar2
  3. Number
  4. Date
  5. Large Object
    • Character Large Object
    • Binary Large Object

Char: Char datatype can accept characters such as 'A-Z', 'a-z', '0-9', special characters(#, !, @, $, %, &)

Syntax: CHAR(size)
size:size is used to determine the number of characters we can store.
Whenever we specify char datatype we have to mention size for it.
The maximum size we can store is 2000.

For Example

char

char is a type of fixed length memory allocation.
Hence in char datatype there will be wastage of memory. To overcome this issue they came up with 'varchar'

Varchar: varchar datatype can accept characters such as 'A-Z', 'a-z', '0-9', special characters(#, !, @, $, %, &)

Syntax: VARCHAR(size)
Whenever we specify varchar we need specify size for it.
The maximum size we can store is 2000.

For Example

varchar

varchar is a type of variable length memory allocation.
There is no wastage of memory in varchar.

Varchar2: It is an updated version of varchar.
The size is updated from 2000 to 4000.
Syntax: VARCHAR2(size)
The maximum size we can store is 4000.
Varchar will automatically updated to varchar2.

Number: It is a datatype which is used to store only numerical values(digits).
It can accept two arguments: Precision, scale.
Syntax: NUMBER(precision, scale)

Precision: Precision is used to determine the number of digits we can store in numerical place.
Scale: Scale is used to determine the number of digits we store in decimal place with in the precision.
The maximum precision we can store is 38.
The maximum scale we can store is 127.

number

Date: This datatype is used to store data.
The two oracle specified date formats are

  1. 'DD-MON-YY' -> '07-FEb-98'
  2. 'DD-MON-YYYY' -> '07-FEb-1998'
Syntax: DATE
Date should always enclosed with in single qoutes

Large Object:
i. Character Large Object(CLOB): CLOB is used to store characters upto 4GB of size.
Syntax: CLOB

i. Binary Large Object(BLOB): BLOB is used to store binary numbers of images, videos, files, etc., upto 4GB of size.
Syntax: BLOB


Constraints

Constraints are the conditions which are assigned to a particular column to validate the data.

Types of Constraints:

  1. UNIQUE
  2. NOT NULL
  3. CHECK
  4. PRIMARY KEY
  5. FOREIGN KEY

UNIQUE: UNIQUE is a constraint which is assign to a particular column which cannot accept repeated or duplicate values.
NOT NULL: NOT NULL is a constraint which is assign to a particular column which are mandatory or which cannot accept null.

  • NULL: NULL is used to determine empty cell. Any operation perform with NULL the result will be NULL.
CHECK: CHECK is a constraint which is assign to a particular column for extra validations.
CHECK: CHECK is a constraint which is assign to a particular column for extra validations.
It is assign with a condition, if the condition is true the value gets accepted else rejected.
PRIMARY KEY: Primary Key is a constraint which is used to assign to a columns to identify a record uniquely from the data.
Characterstics of PRIMARY KEY:
  1. We can have only one primary key in table.
  2. It cannot accept repeated or duplicate values.
  3. It cannot accept NULL.
  4. It is combination of UNIQUE and NOT NULL
  5. Primary Key is not mandatory but recommended to have one in table.

FOREIGN KEY: We use the Foreign Key to establish connection between two different tables..
Characterstics of PRIMARY KEY:
  1. We can have 'n' number of Foreign key in table.
  2. It can accept repeated values.
  3. It can accept NULL.
  4. It is not a combination of UNIQUE and NOT NULL
  5. It is present in child table but actually belongs to parent table.
  6. It is also reffered as 'REFERENTIAL INTEGRITY CONSTRAINT'
Only Primary Key can travel to another table, when it travels it becomes foreign key.

Table