Database Technology

DBMS is a collection of programs that enables users to create and maintain a Database.

Basic features of DBMS are :

1. Control Redundancy
2. Authorization
3. Efficient query processing
4. Backup and Recovery
5. Multiple UI
6. Complex Relationships
7. Integrity Constraints

Three Level Database Schema Architecture :

1. Internal level schema (describes physical storage)
2. Conceptual level schema (describes database structure)
3. External level schema (describes user views)

Data Independence :
Data independence is the ability of to make changes to data characteristics without have to make changes to the programs that access the data.
It's important because of the savings in time and potential errors caused by reducing modifications to data access software.

1. Logical Data Independence - ability to change conceptual schema without having to change the physical schema.
2. Physical Data Independence - ability to change physical schema without having to change the internal schema.

Database Language :

1. DDL - Data Definition Language (CREATE  ALTER  DROP  TRUNCATE)
2. DML - Data Manipulation Language (SELECT  INSERT UPDATE  DELETE)
3. DCL - Data Control Language (GRANTE  REVOKE)
4. TCL - Transaction Control Language (COMMIT ROLLBACK)

Database Design Techniques :

1. Top - Down Approach - start with a general idea of what is needed for the system and then ask the end-users what data they need to store.   For example, an automobile manufacturer must follow a top-down approach to meet the overall specifications for the car.  If a car has the requirement that it cost less than 15,000 dollars, gets 25 miles per gallon, and seating five people.   In order to meet these requirements the designers must start by creating a specification document and then drilling down to meet these requirements. (Entity-Relationship Model)
2. Bottom - Up Approach - The bottom-up approach begins with the specific details and moves up to the general idea.For Example, statistical analysis, analysts are taught to take a sample from a small population and then infer the results to the overall population.(Normalization)

 Entity : An Entity represents a real world object that has independent existence. Attributes/Properties defines an Entity.If Entity physically exist then it is tangible or if it exist logically then it is called intangible entity.

Attribute Types :

1. Single valued Attributes : An attribute, that has a single value for a particular entity. For example, age of a employee entity.
2.  Multi valued Attributes : An attributes that may have multiple values for the same entity. For example colors of a car entity.
3.  Compound /Composite Attribute : Attribute can be subdivided into two or more other Attribute. For Example, Name can be divided into First name, Middle name and Last name.
4.  Simple/Atomic Attributes : The attributes which cannot be divided into smaller subparts are called simple or atomic attributes. For example, age of employee entity
5.  Stored Attribute : An attribute, which cannot be derived from other attribute, is known as stored attribute. For example, Birth Date of employee.
6.  Derived Attribute : Attributes derived from other stored attribute. For example age from Date of Birth and Today’s date.
7.  Complex Attributes : If an attribute of an entity, is built using composite and multivalued attributes, then these attributes are called complex attributes. For example, a person can have more than one residence and each residence can have multiple phones, an addressphone for a person entity can be specified as – {Addressphone (phone {(Area Code, Phone Number)}, Address(Sector Address (Sector Number, House Number), City, State, Pin))}
   Here {} are used to enclose multivalued attributes and () are used to enclose composite attributes with comma separating individual attributes.
8.  Key Attribute : represents primary key. (main characteristics of an entity). It is an attribute, that has distinct value for each entity/element in an entity set. For example, Roll number in a Student Entity Type.
9.  Non Key Attributes : These are attributes other than candidate key attributes in a table. For example Firstname is a non key attribute as it does not represent the main characteristics of the entity.
10.  Required Attribute : A required attribute is an attribute that must have a data value. These attributes are required because they describe what is important in the entity. For example, In a STUDENT entity, firstname and lastname is a required attribute.
11.  Optional Attribute/Null Value Attribute – An optional attribute may not have a value in it and can be left blank. For example, In a STUDENT entity, Middlename or email address is an optional attribute. as some students may not have middlename or email address

Attribute domain : it is set of all valid values that the attribute can have. If a composite attribute combine with n number of simple attributes with domain D1,D2,.....,Dn then its domain will be D1*D2*.......*Dn  

Association : it shows inter dependencies .
Association between Entities are called Relationships.
Relationship type : it relates two or more entities and defines a relationship set. Degree of Relationship type is number of entities participating.
Relationship instance : Any given picture of Relationship type is called Relationship instance.
Constraints : Constraints limit the set of possible combination of Entities that can participate in relationship type.
Cardinality ratio - max. number of relationship instance that an entity can participate .( min. number of relationship instances that each entity can participate is min. cardinality constraint)
1:1 - Employee table  to Salary table (one to one)
1:N - Department table to Employee table (one to many)
N:1 - Employee table to Department table (many to one) 
N:M - Employee table to Project table (many to many)

(E - Entity Set and R - Relationship set)
Participant constraint - These constraints specifies max and min number of relationship instances that each entity can or must participate in. Total if Every entity in E participate in at least one relationship in R (min cardinality is more then zero) and Partial if only some entity in E participate in at least one relationship in R (three could be some entity which will not participate in the relationship in partial ).

Identifying Relationships :Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity’s primary key.

For example , Book_id PK in Book table is also included in Chapter table as PK.
Another example, A company may store the information of dependents (Parents, Children, Spouse) of an Employee. But the dependents don’t have existence without the employee. So Dependent will be weak entity type and Employee will be Identifying Entity type for Dependent.
A weak entity type is represented by a double rectangle. The participation of weak entity type is always total. The relationship between weak entity type and its identifying strong entity type is called identifying relationship and it is represented by double diamond.

Recursive Relationship :A relationship between two entities of similar entity type is called a recursive relationship. For Example, Reports to relationship in a Employee table having CEO , Managers , Assistant  .

 

Entity-Relationship Model :

**Derived attribute is represented with dotted attribute symbol.
* Multivalued attribute is a double line and composite attribute is single line border.
* Relationship type is represented by a diamond and connecting the entities with lines.
* Total participation is shown by double horizontal line in ER diagram.

An Entity class is said to be a subclass of Entity A if there exist a "is a " relationship with A.
For Example, Car is a vehicle.
Specialization : The process of creating subclass from a Entity is called Specialization.
Generalization : The process of clubbing two or more Entity classes under a common superclass is called Generalization.
Predicate defined subclass : In a predicate-defined (or condition-defined) subclass, the subclass membership of an entity can be determined from its attribute value(s) in the superclass

Then a specialization group must be identified as either disjoint or overlapping (exist both subclass at same time), symbolized by ⓓ or ⓞ specialization nodes.
It may also be either a total specialization (double line connecting the superclass) or a partial specialization (single line connecting the superclass).

To maintain consistency with inheritance, insertions and deletions of entities must follow certain rules, including:

• Deletion from a superclass implies deletion from all subclasses.
• Insertion into a subclass implies insertion into all superclasses.
• Insertion into a superclass with an automatically-defined specialization must possibly insert into the subclass(es).
• Insertion into a superclass with a total specialization implies insertion into at least one subclass. (Exactly one if the specialization is also disjoint).

Union Type : A union type is also called category.
For Example , A individual (A) ,a Family(B) ,or a  or a Company(C) can be a Account Holder in Bank.

This union type is partial, shown with a single line, meaning an entity may be a member of type A without also having type D. If the union type were total, shown with a double line between D and the Ⓤ, every entity in one of the superclasses would necessarily also be in D.

 Relational Model :

The standard model for most of the transactional database today.
Three basic components :

1. Set of domain and set of relations
2. Operations on relations
3. Integrity rules

• A relational database is a collection of relations.
• A relation resembles a table or a flat file of records
• Each row is called tuple and each column is called attribute in relation
• Relational schema is denoted by R(A1,A2,.....,An) where Ai is a attribute
• Domain of A is denoted as dom(Ai)
• The Cardinality of relation is number of tuples in the relation
• Degree of relation is number of attributes in the schema.
• A relation can be defined as

            r(R) ⊂ ( dom(A1) x dom(A2) x ......x dom(An) ) where x is Cartesian product over sets.

 Relational Constraints :

1. Domain constraint- specifies attribute value is atomic and in dom(A)
2. Key constraint
3. Entity Integrity constraint
4. Relational Integrity constraint
5. Semantic Integrity constraint

Relational Algebra Relational Calculus :

Relational Algebra is a procedural language .It specifies the operations to be performed on existing relations to derive a result relation.

1. Selection (σ) Operation is unary and commutative and applies on single tuple.${\displaystyle \sigma _{\varphi }(R)}$A selection is written as ${\displaystyle \sigma _{a\theta b}(R)}$ or ${\displaystyle \sigma _{a\theta v}(R)}$ where:
   
   • a and b are attribute names
   • θ is a binary operation in the set ${\displaystyle \{\;<,\leq ,=,\neq ,\geq ,\;>\}}$
   • v is a value constant
   • R is a relation where ${\displaystyle \varphi }$ is a propositional formula that consists of atoms as allowed in the normal selection and the logical operators ${\displaystyle \land }$ (and), ${\displaystyle \lor }$ (or) and ${\displaystyle \lnot }$ (negation).
2. Projection (π) is a unary operation written as where is a set of attribute names. written as ${\displaystyle \Pi _{a_{1},\ldots ,a_{n}}(R)}$ where ${\displaystyle a_{1},\ldots ,a_{n}}$ is a set of attribute names
3. Rename (ρ) unary operator as ${\displaystyle \rho _{a/b}(R)}$ where the result is identical to R except that the b attribute in all tuples is renamed to an a attribute

Joins :

1. Cartesian join -  combines two relations to form a new relation
2. Theta join - The θ-join is a binary operator that is written as or where a and b are attribute names, θ is a binary relational operator in the set {<, ≤, =, >, ≥}, v is a value constant, and R and S are relations. The result of this operation consists of all combinations of tuples in R and S that satisfy θ.(if only = is used then it is equijoin)

Transform ER model to Relational model :

1. Entity represents a Row in a table and Attribute represents a Column
2. multi-valued attribute is represented in a separate table 
3. derived attribute are not stored in table
4. arrow in relation is 1 to 1 and simple line or line with */N represents many to many
5. min and max cardinality is represented as ( 1, 10 )
6. total participation is represented as double line.