rdms

Reltaional DBMS



WHAT IS A DATBASE SYSTEM ?

A Database is a collection of related data. By data we mean known facts that can be recorded and that have implicit meaning .

Few properties of database
1 . a database is a logically coherent collection of data with some inherent meaning. A random assortment of data can be referred as a database .
2. A Database is designed , built , and populated with data for specific purpose.

WHY DATABASE ?
The main characteristics of the database approach versus the file application

1 . self-describing nature of a database system .
2. Insulation between programs and data,and data abstraction.
3. Support of multiple views of the data .
4. Sharing of data and multiuser transaction processing.

DATA MODELS
A Data Model is a collection  of concepts that can be used to describe the structure of a database - provides necessary means of abstraction .

SCHEMAS
The description of a database is called the database schema ,which is specified during database design and not expected to change frequently. (mainly ddl commands deal with schema)

Instances
snapshot of a database at a particular moment is known as a database instance .
(dml commands deals with instances mainly )

NOTE :: database schema is known as intension and database state or instance is known as extension.


DATABASE ARCHITECTURE AND DATA INDEPENDENCIES

3-Tier Architecture

<><><><><><><><><><><><><><><><><><>

Data Independence
1. Logical Data Independence is the capacity to change the conceptual schema without having to change external schema or application program .

2. Physical Data Independence is the capability to change the internal schema without having to change the conceptual schema.

Database Languages
ddl,dml,tcl

DBMS INTERFACE

1. Menu Based Interface for Web Clients or Browsing - these interface present the user with lists of options(called menus) that lead the user through the formulation of a request.
2. Form Based Interface - A form-based interface displays a form to each user. User can fill out all the form entries.
3. Graphical User Interface -  A GUI typically displays a schema to the user in diagrammatic form. The user then can specify a query by manipulating the diagram .
4. Natural Language Interface -  These interface accepts request written in english or some other language and attempt to understand them.
5. Speech Input Output - Limited use of speech as an input query and speech as a result of some question or result of a request is becoming common.
6. Interface for Parametric Use - Parametric users, such as bank tellers has small set of operation that they must perform repeatedly.
7. Interface for DBA - Most database system contain privileged commands that can be used only by dba staff.





Relation Databases -
-The relational model was first introduced by Ted Codd in 1970
-simple and based on mathematics
-based on concept of mathematical relation-which looks somewhat like a table of values , and theoritically based on set theory and first-order predicate logic.

Database Models - A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized, and manipulated.

Relational Models - The relational database model represents the database as a collection of relations, informally each relation resembles a table of values .

Relational Database Paradigm 
Ordering of tuples in a relation - A relation is defined by a set of tuples . It is not necessary that the tuples are ordered or not.

Ordering of values in an attributes - A relation consist of tuples and there is no such ordering of attributes are done .However the value should be along with the attribute and does not violate the rule of semantics .
Values and Nulls in tuples - A attribute consist of values and they are atomic , i.e the value stored in a tuple is considered as one. There is another value which is found in tuples known as NULL , which means that either the data is unknown or not present in the tuples. 
Interpretation of a Relation - The relation schema can be interpreted as a declaration or type of assertion.

Keys :
There are mainly four types of keys to maintain some constraints as well as the integrity of a relation .

Super Key : A set of attribute which can identify all the tuples uniquely . Let R( a1,a2,a3,...an) is a relation having a1, a2...an as attributes . A set of attribute say (a1 , a2,a3... am) where m<=n , is the superkey if these set can uniquely identify all the tuples .

Candidate key : minimal super key is known as candidate key. 
Primary key : Its the dba choice to select one of the candidate keys as a primary key. There is one more constraint with the primary key i.e they must be unique and not null.

Foreign Key :   To maintain the referential integrity between two different relation in a database we use this key . Foreign key are basically a set of attributes which are common in both the relation and they are used to identify a tuple uniquely. The value stored in a foreign key must be present in the reference table's attribute to whom it is reffered..

Domain - The values stored in an attribute are known as its domain . for example : let class be a table having department as one of its attribute then the collection of values stored in the department attribute only is known as domain.

RELATION OPERATOR :
Select :: select all tuples that satisfy the selection condition from a relation R .
Project :: Produces a new relation with only some of the attributes of R , and removes duplicate tuples .
Equijoin ;; Produces all combination of tuples from R1 and R2 that satisfy the join condition.
Natural Join :: Same as EQUIJOIN excpet that the join attributes of R2 are not included in the resulting relation; if the join attributes have the same name , they do not have to be specified at all.
Union :: Produces a relation that includes all the tuples in R1 or R2 or both R1 and R2., R1 and R2 must be union compatible.
Intersection :: Produces a relation that includes all tuples in both R1 and R2 ; R1 and R2 must be union compatible .
Set Difference :: Produces a relation that includes all the tuples in R1 that are not in R2 ; R1 and R2 must be union compatible.
Cartesian Product :: Produces a relation that has the attributes of R1 and R2 and includes as tuples all possible combination of tuples from R1 and R2.


INTEGRITY

To achieve the consistency and to avoid redundancy we mainly come across integrity .
Integrity is of two types (i) entity integrity (ii) referential integrity

Entity Integrity ; Various constraints which are applied in a relation like primary key ,  unique etc applied to an attribute are known as entity integrity .

Referential Integrity : Constraints applied between two or more relation are know as referential integrity. eg .  foreign key .



NORMALIZATION

First Normal Formal - it was defined to disallow multivalued attributes , composite attributes , and their combinations . It states that the domain of an attribute must include only atomic(simple,indivisible) values and that the value of any attribute must be single value from the domain of the attribute .

Second Normal Form -  it is based on the concept of full functional dependency. A functional dependency X->Y is a  full functional dependency if removal or any attribute A from X means that the dependency does not hold any more,

Third Normal Form - it is based on the concept of transitive dependency. X-> Y and Y->Z , if X->Z is present then we say that there exist a transitive dependency.

BCNF - A relation schema R is in BCNF if whenever a nontrivial function dependency X->A holds in R , then X is a superkey of R.