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.
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:
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:
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.
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
|
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 - stands for Data Base Management System
- RDBMS - stands for Relational 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 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 will ensure atleast some value should be present in the column
- It will not allow any duplicates in a column
- UNIQUE column can take only one NULL.
- 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 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
- It is used to provide additional validations as per the customer requirements.
empnum should start with 1
commission should be between 1000 & 5000
NEXT POSTS:
Comments
Post a Comment