Computer Number system Note for IBPS PO

IBPS SO IT Officer Notes for DBMS Information Rule

Jan 31 • IBPS Specialist Officer • 351 Views • No Comments on IBPS SO IT Officer Notes for DBMS Information Rule

IBPS SO IT Officer Notes for DBMS Information Rule

Rule 1: Information Rule

The data stored in a database, to be user data or metadata, must be a value of some table cell. Everything is in a database must be stored in a table format.

Rule 2: Guaranteed Access Rule

Every single data value is guaranteed to be accessible logically with a combination of table-name, primary-key and attribute-name. pointers, can be used to access data.

Rule 3: Systematic Treatment of NULL Values

The NULL values in a database must be given in a systematic and in uniform way. This is a very important rule because a NULL can be interpreted as one the following − data is missing or data is not known.

Rule 4: Active Online Catalog

The structure description of the entire database must be stored in an online catalog, known as data dictionary, which can be accessed by authorized users.

Rule 5: Comprehensive Data Sub-language Rule

A database can only be accessed using a language having linear syntax that support data definition, data manipulation and data transaction management operations. This language can be used directly or by means of some application. If the database allows access of data without any help of this language, then it considered as a violation.

Rule 6: View Updating Rule

All the views of a database, can theoretically be updated, must also be updatable by the system.

Join Best Exam Preparation group on Telegram

Rule 7: High-Level Insert, Update, and Delete Rule

A database support high-level insertion, updation, and deletion. This must not be limited to a single row, that is, it must also support union, intersection and minus operations to data records.
Rule 8: Physical Data Independence

The data stored in a database must be independent to the applications that access the database. Any change in the physical structure of a database  does not have any impact on how the data is to be accessed by external applications.

Rule 9: Logical Data Independence

The logical data in a database independent of its user’s view . Any change in logical data  not affect the applications. For example, if two tables are merged or one is split into two different tables, there should be no impact or change on the user application.

Rule 10: Integrity Independence

A database should be independent of the application that uses it. All its integrity constraints can be independently modified without the need of any change in the application. This rule makes a database independent to the front-end application and its interface.

Rule 11: Distribution Independence

The end-user must not be able to see that  data which is distributed over various locations. Users should always get the impression that the data is located at one site only. This rule has been regarded as the foundation of distributed database systems.

Rule 12: Non-Subversion Rule

If a system has an interface to provides access to low-level records, and interface must not be able to destabilize the system and bypass security and integrity constraints.

Relational data model is the primary data model,  used  around the world for data storage and processing.

Concepts

Tables − A table has rows and columns, where rows represents records and columns represented the attributes.

Tuple − A single row of a table, which contains a single record for that relation is called a tuple.

Relation instance − A finite set of tuples in the relational database system represents relational instance. Relation instances do not have duplicate tuples.

Relation schema − A relation schema describes the relation name, attributes, and their names.

Relation key − Each row has one or more attributes known as relation key, which identify the row in the table uniquely.

Attribute domain − Every attribute has some predefined value scope, known as attribute domain.

Constraints

Every relation has some conditions  hold for it  a valid relation. These conditions are called Relational Integrity Constraints.

  • Key constraints
  • Domain constraints
  • Referential integrity constraints

Key Constraints

There must be at least one minimal subset of attributes in the relation, identify a tuple uniquely. If there are more than one minimal subsets,are called candidate keys.

Key constraints have−

  • in a relation with a key attribute, no two tuples can have identical values for key attributes.
  • a key attribute cannot have NULL values.

Key constraints are also referred  as Entity Constraints.

Domain Constraints

Attributes have specific values. Every attribute is bound to have a specific range of values. For example, age cannot be less than zero and phone numbers cannot contain a digit outside 0-9.

Referential integrity Constraints

Referential integrity constraint states that if a relation refers to a key attribute of a different or same relation, then that key element must exist.

Relational Algebra

Relational algebra is a procedural query language, which takes relations as input and relations as output. It uses operators to perform queries. An operator can be either unary or binary.

Select Operation (σ)

It selects tuples that satisfy the given predicate from a relation.

Notation − σp(r)

Where σ stands for selection predicate and r stands for relation.

Project Operation (∏)

It projects column(s) that satisfy a given predicate.

Notation − ∏A1, A2, An (r)

Best Books for Competitive exam preparation.

Union Operation (∪)

It performs binary union between two given relations.

r ∪ s = { t | t ∈ r or t ∈ s}

Notation − r U s

Set Difference (−)

The result of set difference operation is tuples, which are present in one relation but  not in the second relation.

Notation − r − s

Cartesian Product (Χ)

Combines information of two different relations into one.

Notation − r Χ s

Rename Operation (ρ)

The results of relational algebra are also relations but without any name. The rename operation allows us to rename the output relation. ‘rename’ operation is denoted with rho ρ.

Notation − ρ x (E)

Relational Calculus

Relational Calculus is a non-procedural query language, it tells what to do but never explains how to do it.

Keys

Key is defined a the combination of attributes that is used to identify record of the database table. It is also used to arrange the records.

 

  • Superkey − A set of attributes  that collectively identifies an entity in an entity set.
  • Candidate Key − Minimal superkey is called a candidate key. An entity set may have more than one candidate key.
  • Primary Key − uniquely identify the entity set called primary key.

 

  • Foreign key– whose value is derived from the primary key of another table. Relationship held between two tables with the help of foreign key.

Normalization

Technique of organising data  in database. It eliminate data redundancy and perform insertion,deletion and updation anomalies.

  1. Eliminate useless data
  2. Data is stored logically.

Update anomaly : update address which occur twice or more times in a table. Update row and column.

Insertion anomaly : adding new address of any entity in a table.

Deletion anomaly : delete a record.

First normal form :each set   of column have a unique value. Table have rows and row have a primary key.

Atomicity means attributes must contain  single value.

Second normal form :

  1. table must meet the criteria of 1st normal form.
  2. If the primary key is a composite of attributes than non key attributes must depend on whole key.

Third normal form

  1. Table meet the criteria of 2nd normal form
  2. Each non key attribute in a row does not depend on the entry in another key column.

Boyce codd normal form: A relation is in BCNF if and only if every determinant is a candidate key.

Fourth  normal form :

  1. table meet the criteria of 3rd normal form.
  2. Non key attributes depend on the key column exclusive of other non key columns are eliminated

Fifth normal norm :

  1. table meet the criteria of 4th normal form.
  2. Table consist of a key attributes and a non-key attributes only.

Join is a  the combination of a Cartesian product followed by a selection process. A Join operation combine two tuples from different relations.

We  describe here various join types .

Theta (θ) Join

Theta join combines tuples from different relations provided the theta condition they satisfy. The join condition is denoted by the θ.

Notation   R1 ⋈θ R2
Equijoin

When Theta join uses only equality comparison operator, it is said to be equijoin.

Natural Join (⋈)

Natural join does not use any comparison operator. We can perform a Natural Join only if there is at least one common attribute  exists between two relations.

Outer Joins

There are three kinds of outer joins − left outer join, right outer join, and full outer join.

Left Outer Join(R S)

All the tuples from the Left relation, R, are included in the resulting relation.

Right Outer Join: ( R S )

All the tuples from the Right relation, S, are included in the resulting relation.

Full Outer Join: ( R S)

All the tuples from both participating relations are included in the resulting relation. If there are no matching tuples for both relations, their respective unmatched attributes are made NULL.

Redundant Array of Independent Disks

Redundant Array of Independent Disks, is a technology to connect multiple secondary storage devices and use them as a single storage media.

RAID 0

In this level, a striped array of disks is broken down into blocks and the blocks are distributed among disks. Each disk receives a block of data to write/read in parallel.

RAID 1

RAID 1 uses mirroring techniques. data is sent to a RAID controller, it sends a copy of data to all the disks in the array. RAID level 1 is also called mirroring and provides hundred percent redundancy in case of a failure.

RAID 2

RAID 2 uses Error Correction Code using Hamming distance for its data, striped on different disks.

RAID 3

RAID 3 stripes the data onto multiple disks. The parity bit generated for data word is stored on a different disk

RAID 4

In this level, an entire block of data is written onto data disks and then the parity is generated and stored on a different disk.

RAID 5

RAID 5 writes whole data blocks onto different disks, but the parity bits generated for data block stripe are distributed among all the data disks rather than storing them on a different dedicated disk.

RAID 6

RAID 6 is an extension of level 5. In this level, two independent parities are generated and stored in distributed fashion among multiple disks.

ACID Properties

A transaction is a very small unit of a program and  contain several low level tasks. A transaction in a database system  maintain Atomicity, Consistency, Isolation, and Durability − known as ACID properties .

Atomicity − This property states that a transaction must be treated as an atomic unit,.

Consistency − The database must remain in a consistent state after any transaction. No transaction have any adverse effect on the data residing in the database.

Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts.

Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel.

File Organization

File Organization defines how file records are mapped onto disk blocks.

Heap File Organization

In Heap File Organization, the Operating System allocates memory area to file without any further accounting details.

Sequential File Organization In sequential file  records are placed sequential order based on the unique key field or search key.

Hash File Organization

Hash File Organization uses Hash function computation on some fields of the records. hash function determines the location of disk block where the records are to be placed.

Clustered File Organization

In this mechanism, related records from one or more relations are kept in the same disk block.

Join us on Telegram and Facebook for BANK exam is also asked in other government exam like BANK IBPS SO RRB SSC. This note has been prepared by Supriya Kundu is of one of best teacher in this field.If any question please ask in below.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

« »