DBMS Basic Concept

A)DATABASE MANAGEMENT SYSTEM(DBMS)
The software used for the management, maintenance and retrieval of the data stored in a database is called DBMS.
Database management systems frees the programmer from the need to worry about the organization and location of data. The primary goal of a DBMS is to provide an environment which is both convenient and efficient to use in retrieving and storing information.

B) A DATABASE STORAGE HIERARCHY
1) DATABASE
A collection of data files integrated and organised into a simple comprehensive file system, which is arranged to minimize duplication of data and to provide conenient access to information within that system to satisfy a wide variety of user needs.

2) DATA FILES
A file is a number of related records that are treated as a unit.
eg:- A collection of all employee records for one company would be an employee file.

3) RECORDS
A collection of related items of data that are treated as a unit.
eg:- An employee record would be collection of all fields of one employee.
->Record is sometimes referred as tuple.

4) FIELD
Individual element of data is called Field.
eg:- Bank cheque consist of following field cheque no, date, payee, numeric amt, signature, bank,etc.
->Field is sometimes referred as Data item.


C) Basic DBMS TERMS
1) RELATION
A relationship is an association among several entities
eg:- A cusst_Acct relationship associates a customer with each account that she or he has.

2) REDUNDANCY
If same piece of information is stored in database for number of times the database is said to be redundant. We should check our database should not be redundant as it wastes make our disk space, reduced efficiency of database, require more processing time, and their are chances of Inconsistency due to it in our database.
eg:-If we have to tables emp_details (contains details of employee) and Payroll(contains Payment details to employee), than if we include details of employee in payroll table, than it is said to be redundancy as same piece of information is repeated.

3) INCONSISTENCY
Inconsistency is various copies of the same data may no longer agree. Inconsistency occurs due to redundancy, so redundancy should be reduced . Though we cannot elimnates the redundancy , but we can reduced it upto certain level.
eg:- If we have details of employee stored in emp_details and payroll table than while updating information we should check that both tables are updated or not, if we update the address of one employee in emp_details and same details is not updated in payroll table, than database is said to be in inconsistent state.


4) PROPAGATING UPDATES
Propagating updates ensures users that any change made to record of one files, automatically made to other files. This process is known as Propagating updates. Where the term "Updates" is used to cover all the operations of creation, deletion and modification.
-we can avoid inconsistency by using propagating update technique.

5) INSTANCES
The collection of information stored in the database at a particular moment in time is called an instances of the database.

6) SCHEMES
The overall design of the database is called the database schemes. Schemes are changed infrequently, if at all.

7) USERS
There are four different types of database system users.

- Application programmers:- A person who prepares application program are called application programmer. Application programs operates on the data in all the usual ways: retrieving information, creating new information, deleting or changing existing information.

- SOPHISTICATED USERS:- Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. Each such query is submitted to a query processor whose function is to take a DML statement and break it down into instructions that the database manager understands.

- SPECIALIZED USERS:-Some sophisticated users write specialized database application that do not fit into the traditional data processing framework. Among these application are computer-aided design systems, knowledge-base and expert systems, systems that store data with complex data types
eg:-For Graphics and Audio data.

- END USERS:-Unsophisticated users interact with the system by invoking one of the permanent application programs that have been written previously.Thus they are persons who uses the information generated by a computer based system. Retrival is the most common function for this class of user.


D) KEYS concept in DBMS
A Key is a single attribute or combination of two or more attributes of an
entity set that is used to identify one or more instances of the set.

1) PRIMARY KEY:-A primary key is a field that uniquely identifies each record in a table. As it uniquely identify each entity, it cannot contain null value and duplicate value.
eg:-Consider the customer table, which has field :customer_number, customer_socialsecurity_number, and customer_address.here customer_number of each entity in customer table is distinct so customer-number can be a primary key of customer-table.

2) SUPER KEY :- If we add additional attributes to a primary key, the resulting combination would still uniquely identify an instance of the entity set. Such augmented keys are called superkey.
A primary key is therefore a minimum superkey.

3) CANDIDATE KEY:-A nominee's for primary key field are know as candidate key.
eg:-From above example of customer table, customer_socialsecurity_number is candidate key as it has all characteristics of primary key.

4) ALTERNATE KEY:-A candidate key that is not the primary key is called an Alternate key.
eg:- In above example, customer_socialsecurity_number is a candidate key but not a primary key so it can be considered as alternate key.

5) COMPOSITE KEY:- Creating more than one primary key are jointly known as composite key.
eg:-In above example, if customer_number and customer_socialsecurity_number are made primary key than they will be jointly known as composite key.

6) FOREIGN KEY:- Foreign key is a primary key of master table, which is reference in the current table, so it is known as foreign key in the current table. A foreign key is one or more columns whose value must exist in the primary key of another table.
eg:-Consider two tables emp(contains employees description) and emp_edu(contains details of employee's education), so emp_id which is primary key in emp table will be referred as foreign key in emp_edu table.

E) NORMALIZATION
It is important to understand the concept of normalization before switch on to the creation of table and its manipulation.

- NORMALIZATION is the process of grouping data into logical related groups.
- Normalization is the process of reducing the redundancy of data in a relational database.
- A database that is not normalized may include data that is contained in one or more different tables for no apparent reason. This could be bad for security reasons, disks space usage, speed of queries, efficiency of database updates, and may be most importantly, data integrity. A database before normalization is one that has not been broken down logically into smaller, more manageable tables.

BENEFITS OF NORMALIZATION
Normalization provides numerous benefits to a database.

some of the major benefits include

• Wider the overall database organization.
• The reduction of redundant data.
• Data Inconsistency can be avoided.
• A much more flexible database design.
• A better handle on database security.

INTRODUCTION to DDL

A database scheme is specified by a set of definition which are expressed by a special language called a Data Definition Language. The result of compilation of DDL statement is a set of tables which are stored in a special file called Data Dictionary.
-> DDL defines the structure of data.
-> DDL statements in SQL language are responsible for creating or modifying database structures such as tables, views, and indexes.


Let us now understand DDL statements and how it works on Oracle database.

TABLE
(A table consist of Rows and Columns. Table is a collection of related records that are treated as a unit. )

Convention for Naming a Table in Oracle
-> Each table owned by a Oracle account must have a unique name.
-> A table name cannot exceed 30 characters in length.
-> A table name must begin with an alphabetic character.
-> A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore).
-> A table name cannot be an SQL reserved word.
-> You can use uppercase and lowercase characters in naming tables; oracle is not case sensitive as long as table or column names are not enclosed in double quotes.
-> A table name should be descriptive.

Convention for Naming Column in Oracle.
-> Within a single table, a column name must be unique. However, you may use the same column name in different tables.
-> Like other database objects, a column name can be upto 30 characters long.
-> The column name must begin with an alphabetic character.
-> The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _(underscore). The name cannot include spaces.
-> A column name cannot be an SQL reserved word.
-> As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
-> An Oracle table may have upto 254 columns.

Commands Related To Table

1) Syntax to CREATE Table
CREATE TABLE
(
[column level constraint],
:
:
,
[Table level constraint]
);
Here,
-> Table_name is the name for the table.
-> Column_name1 through Column_nameN are valid column names.
-> Datatypes is a valid Oracle datatype specification.
-> Constraint_name is a optional, but it should be used in order to avoid invalid information to be entered in the database.
NOTE:-you can assign default value for field by using the keyword default and specifying default value for that field.
eg:-city varchar2(25) default 'AHMEDABAD'
(Though SQL is not case sensitive, but contents of table are case sensitive so it is good practise to write data always in uppercase).
Tip - CHAR, VARCHAR AND DATE kind of data should be written in single quota.

example
sql> create table emp
(
emp_no number (6) primary key,
emp_name varchar2(35),
emp_address varchar2(45),
emp_city varchar2(30),
emp_state varchar2(30),
emp_age number(2),
emp_sex char,
emp_department varchar2(30)
);
Table created.

2) Describe Table
-> This command will describe the table.
Syntax for describing table
sql> desc ;
or
sql> describe ;
example
sql> desc emp;


3) ALTER Table
-> Alter command is used to make modification after table has created.
-> Modification like to add column, to drop constraint like primary key,etc., However you cannot delete the column nor you can decrease the width of column.
Syntax to ALTER Table definition
ALTER TABLE
[ADD MODIFY DROP]
( ,
:
:
);

examples

-> To Add column in a table.
sql> alter table emp
add
(emp_phno number(10));

-> To Modify Table
sql> alter table emp
modify
(
emp_city varchar2(35),
emp_state varchar2(35)
);

-> To Drop Table's Primary key and other constraints.
general syntax
ALTER TABLE
[DROP ENABLE DISABLE]
CONSTRAINT ;

sql> alter table emp
drop primary key;

After successfully completion of above command you get the message table altered.


4) Command to Delete contents of Table
TRUNCATE
-> Truncate command will delete all the table contents by removing all the rows(records) from the table.
-> After the implementation of this command, only the structure of the table remains.
Syntax for Truncate command
sql> TRUNCATE TABLE ;
example
sql> truncate table emp;
-> It will delete all the rows of the emp table and only structure of the table remains.

5) Command to Delete Table
DROP
-> Drop command will delete the table contents and its structure.
Syntax for Drop command
sql> DROP TABLE ;
example
sql> drop table emp;
-> It will delete all the rows and the structure of emp table.

6) Command to Rename Table
-> Rename command will rename the table name.
Syntax for Rename command
Sql > RENAME TO
example
sql> Rename employee to emp;
-> Now the table name is renamed and you can manipulate it using name emp.

INTEGRITY CONSTRAINTS
An Integrity constraint is a trick used by oracle to prevent invalid data entry into the table. It is only a rules applied to restrict user to enter particular value. It prevent this by defining it at column-level or table-level constraint. The main difference between both is that column-level constraint is defined at the column level and cannot reference by any other columns in the table. A table-level constraint is defined at the table level and can reference to any of the table's columns.
NOTE:-not null and check constraint cannot be defined as table-level constraint.
Integrity constraint are categorized into three types they are as under
1) Domain Integrity Constraints
2) Entity Integrity Constraints
3) Referential Integrity Constraint.

I) DOMAIN INTEGRITY CONSTRAINTS
-> Domain integrity constraint Prevent invalid data entry by enforcing rules like NOT NULL and CHECK on the column.

NOT NULL
->By default, every column can contain null value. But, If we want to restrict the user to enter the value at any cost we should put not null constraint.
->In other words, not null field should always contains value.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25)
);
->here empno is column-level constraint and after implementation of the above command while user want to enter the value, he must have to enter empno. ntnl is the constraint name.

CHECK
-> check constraint is used to match the enter data with predifined criteria during designing of table.
-> As it check for predifined criteria it stops the invalid user to do mischief with the database.
-> Thus it helps in making database consistent by feeding reliable information.
example
create table emp
(
empno number(4) constraint ntnl not null,
ename varchar2(25),
job varchar2(25) constraint check(job in('clerk','manager'))
);
->here check constraint will look for job in clerk or manager and if enduser try's to enter job for another category an error code for it will be generated by sql.

II) ENTITY INTEGRITY CONSTRAINTS
-> Entity integrity constraint Prevent invalid data entry by enforcing rules like UNIQUE and PRIMARY KEY on the column.

UNIQUE
-> Unique constraint allowed null values, but it does not allowed duplicate values.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4),
ename varchar2(25),
job varchar2(25),
constraint unino unique(empno)
);
->here unino is constraint name for table-level constraint definition and constraint unique is applied to empno, so after execution of above command user can enter only unique value or may not enter the value.

PRIMARY KEY
-> A primary key is a field that uniquely identifies each records in a table.
-> It should neither be duplicate nor null.
-> It may be composite upto 16 columns.
example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);
->here Primary key is created for empno so it will alone identifies each records in a table. pkno is a constraint name for column-level definition of constraint.


III) REFRENTIAL INTEGRITY CONSTRAINTS
-> Often, we wish to ensure that a value that appers in one relation for a given set of attributes also appears for a certain set of attributes in another realtion. This condition is called referential integrity.
-> To implement this, we should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent entry.
->foreign key is a column or combination of column which refers to primary key of primary table.

example
create table emp
(
empno number(4) constraint pkno primary key ,
ename varchar2(25),
job varchar2(25)
);


create table education_detail
(
empno number(4) ,
degree varchar2(30),
foreign key (empno) references emp(empno)
);

-> here emp table contains the details of the employee while education_detail table consist of their achived degree's by their no. one-to-many relationship is formed here. As one employee have many degree's and even few employee have no degree's.

ON CASCADE DELETE
-> It is special facilty through which we can ensure that the row deleted in the master table will automatically deletes the row in the reference table.
for example if we have records of employee details than after deleting any one of their record will automatically deletes the corresponding entry in the reference table.
example
create table education_detail
(
empno number(4) references emp(empno) on-delete-cascade,
degree varchar2(30)
);