SQL

Data Base: A database is the place of storage of the data in the form of tables. Data means information which is very useful. A database is also collection is also collection of 1 or more tables.

Table - a table is a collection of rows and columns.





 CELL


 CELL

 
A cell is an intersection of row and a column
A column is also called as a field/attribute
A row is also called as a record/tuple
A table is also called as an entity/relation.

Note:
  • If we install any of the database related software(s) - we can create our own database, we can create our own tables and we can store the data inside it.
  • When we install any database software(s) - a part of hard disk will be designated/reserved to perform database related activities.
  • A database can also contain other database objects like views, indexes, stored procedures, functions, triggers etc, apart from tables.
Some of the database software(s) we have are, Oracle, SQL Server, DB2, Sybase, Informix, MySQL, MS-Access, Foxbase, ForPro.

Among the above database software - some of them are DBMS and some of them are RDBMS.

The software which is widely used today is Oracle. The different versions of Oracle starting from the earliest to the latest are - Oracle2, Oracle3, Oracle4, Oracle5, Oracle6, Oracle7, Oracle8i, Oracle9i, Oracle10g and the latest hit the market is Oracle11g. Here, i stands for Internet and g stands for Grid/Grid computing.

Relationships:

A relationship is the association between any two tables which preserves data integrity.

Master(Parent)
Dept No
Dept Name
 10
Accounting
 20
Research
 30
Sales
Relationships

Details(Child)
Emp No
Emp Name
Dept No
Salary
101
A
10
12000
102
B
10
15000
103
C
20
20000

There is the relationship between the above 2 tables.
  • Relationship helps to prevent the incorrect data in the child tables.
  • Once the relationship is created, one table becomes master(or parent) and the other one becomes child(or detail).
  • Whatever we have inserted into child should be present in the master, else the record will be rejected the child.
  • The master table contains the master data which will not change frequently.
  • The child table contains the transaction data which will changes quite often.
DBMS & RDBMS:
  • DBMS - stands for Data Base Management System
DBMS is a database software which allows us to store data in the form of tables.
  • RDBMS - stands for Relational DBMS
RDBMS is also a database software which has facility to handle more data volume, good performance, enhanced security features etc when compared against DBMS.
Any DBMS to qualify as a RDBMS should support the Codd rules/Codd laws.

Ex for DBMS - FoxPro, FoxBase,Dbase
Ex for RDBMS - Oracle, Sybase, DB2, Teradata, SQL Server, MySQL.

Constraints:
A constraint is a condition which restricts the invalid data in the table.
A constraint can be provided for a column of a table.

Types of Constraints:
  • NOT NULL
  • UNIQUE
  • Primary Key
  • Foreign Key
  • Check
NULL:
  •  NULL is nothing, it is neither zero nor blank space
  •  It will not occupy any space in the memory
  •  Two nulls are never same in Oracle
  •  NULL represents unknown value
  • Any arithmetic operation we perform on NULL will result in NULL itself.
NOT NULL:
  • NOT NULL will ensure atleast some value should be present in the column
UNIQUE:
  • It will not allow any duplicates in a column
  • UNIQUE column can take only one NULL.
Primary Key:
  • It is the combination of NOT NULL and UNIQUE
  • Only one Primary Key is allowed in one table
  • Primary Key identifies a record uniquely in a table
  • Creation of Primary Key is not mandatory, but it is highly recommended to create
Foreign Key:
  • Foreign Key creates relationship between any two tables
  • FK is also called as referential integrity constraint
  • FK is created on the child table
  • FK can take both NULL and duplicate values
  • To create FK, the Master table should have PK defined on the common column of the master table
  • We can have more than one FK in a given table
CHECK:
  • It is used to provide additional validations as per the customer requirements.
Ex: sal > 0
       empnum should start with 1
       commission should be between 1000 & 5000

NEXT POSTS:

Comments

Popular posts from this blog

Prime, Fibonacci and Factorial number with example in java

JNDI configuration for Tomcat 9 with Oracle

SessionFactory creation failed.java.lang.NoSuchFieldError: namingStrategy