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

how to count the page views by using JSP

To count no.of page's are viewed by user in your application is a challenge in real time application. In this page we will see how to count the page views when user has viewed the page.           Here, we will do one example how many times user has visited each page in our application. In this post we will come to know, how to count the total application page views as well as individually each pages count.           Let us assume my web application name is "Page Counter". For creating the web application in eclipse IDE, Click here . This Page Counter contains three pages, assume that are the different pages in an application. But, there is the link between each other files for navigating from one page to another. Also it will show the total pages view count and individual page view count in all the pages. Let us see, below is my first jsp file,           You can declare the below content inside the < body > tag. Rest of the content in every page will b
Read more

Exception in thread "main" java.lang.NoClassDefFoundError: javax/transaction/SystemException

Exception in thread "main" java.lang.NoClassDefFoundError: javax/transaction/SystemException error we got in Hibernate 4.3 to work with my MySQL database. We are using hibernate code generation tool in Eclipse and we are able to connect to the database using the Hibernate configuration file. When we try to run code in my Main class application is connecting to the database, but transaction is failing and we got the following error: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Exception in thread "main" java . lang . NoClassDefFoundError : javax / transaction / SystemException at java . lang . Class . forName0 ( Native Method ) at java . lang . Class . forName ( Unknown Source ) at org . jboss . logging . Logger . getMessageLogger ( Logger . java : 2248 ) at org . jboss . logging . Logger . getMessageLogger ( Logger . java : 2214 ) at org . hibernate . cfg . Configuration .< clinit >( Configuration . java : 184 ) at be . comp . permanenti
Read more

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 ticket/berth to him
Read more