Skip to main content

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

Post a Comment

Popular posts from this blog

Multithreading in java with example

Multithreading  is one of the most important concept in core java. In this article we will learn what is multithreading? , what is the use of it? and What is the use of Synchronization and when to use it?  with detailed examples. At a time, two or more threads are accessing the same object is called as Multithreading  in Java .  First, we will create two threads for two objects. It is also possible to run two or more threads on a single class object. In this case, there is a possibility to get unreliable results. If the two threads are perform same task, then they need same object to be executed each time. For your better understanding, take an example of any reservations like, railway, movie ticket booking,etc. Let us think only one berth is available in a train and two passengers are asking for that berth. The first person has sent a request to allocate that ticket/berth to him. At the same time, the second person also sent a request to allocate that ...
1 comment
Read more

Git installation for AngularJS 2 in Windows 10

Download Git latest version from https://git-scm.com/downloads or you click on the below link to download directly for windows https://git-scm.com/download/win . Once download completes, click on executable file to start installation process and choose Yes to allow the software installation in windows 10. Click on Next button to continue further installation. Browse the isntallation directory and click on Next button to continue. Select the list of components which you want to be installed and click on Next button to proced further installation. Type the shortcut name for Start menu and click on Next button. Select how you want to use the Git and click on Next button. For Windows no need to change anything, let it be the default one. Choose the Use the OpenSSL library and click on Next button. Select how should Git treat line ending in text files and click on Next button. Select which terminal emulator to use with Git and click on Next button. Configure extr...
Read more

JNDI configuration for Tomcat 9 with Oracle

In this article, I am going to place the required source code to get data from the table by using the JNDI configuration. Below are the environment details that I have configured currently. Windows - 7 Oracle - 10g Tomcat - 9 JDK - 8 Eclipse Oxygen Ojdbc6 jar required First, we need to create the Dynamic Web Project. If you don't know how to do <Click Here>. I have faced a lot of issues before getting the output like 405, No driver class to load, etc. I am using JSP & Servlets in the current explanation. Before started writing the application logic, we need to do the below configuration in the installed tomcat directory. Place OJDBC6.jar in the Tomcat LIB directory. Add this Resource under <GlobalNamingResources> in Server.xml file which is present under the conf directory in Tomcat. < Resource name = "jdbc/myoracle" global= "jdbc/myoracle" auth = "Container" type= "javax.sql.DataSource" driverClass...
Post a Comment
Read more