Computer Science & IT – B.Tech, BCA, BBA, MCA, M.Tech Best Online Coaching Institute

JOINS and Types of Joins

INTRODUCTION TO JOINS

A join clause is used in relational database system to combine rows from two or more tables based on a common field between them. It is performed by using ‘where’ or ‘on’ clause in which specify the column name of both table and the condition is that data type should be same in both tables which combines the specified rows of tables.

Consider the following two tables Employees and Orders…

Data Types in ORACLE

Data Types in ORACLE The data type is used to create an object such as table, procedure and package and specify a data type for every column. It has specific storage format, constraints and a specific range of value. An oracle database has a built in data type as well as user defined data type Here are various data types used in ORACLE: Character Data type Numeric Data type Date Data type LOB Data type RAW and LONG RAW Data type ROWID and UROWID Data type Character Data type The character data type is used to store character when create the database. It includes national character set or database character set. It is less restrictive and free form text as compare than other data type. For example character can store all alphanumeric values. Types of Character data Type: Here are various data types used in character...

CONSTRAINT in Oracle

Definition of Constraint A constraint is defined in integrity constraint to specify rules for the data in a database.  If there is any violation between the constraint and the data action, the action is aborted by the constraint. It can be specified when the table is created using the CREATE TABLE statement or after the table is created using the ALTER TABLE statement. It can be specify in two ways: Column level constraint Table level constraint Column level constraint It refers to a single column in the table and do not specify the column name. It is also known as inline specification. Table level constraint It refers to one or more column in the table and specifies the names of the column to which they apply. It is also known as outline specification. Types of Constraints Oracle Database creates six types of constraints...

Multimedia Database

Introduction to Multimedia Database It is a collection of related multimedia data type such as text, images, audio and video and file types such as .txt for text, .jpg for images, .mp3 for audio, .swf for video. It facilitate for creation, storage, access, query and control of a multimedia database. It contains number of features and provides information such as engineering, science, medicine and social science. It have large amount of data that provide consistency, concurrency, integrity, security and availability of data. From a user point of view it provides functionalities for easy manipulation, query and retrieval of highly relevant information from large collection of stored data. It manage large amount of multimedia data used in software application. A Multimedia database has to provide some new and enhanced functionalities and features. Types of Multimedia Data Types The multimedia data types are categorized into three categories. Static...

Schema, Subschema and instance

Schema Schema is used to describe logical database and tell how the data is organized and how relation among them is associated. In other words it is the physical arrangement of the data as it appears in the DBMS. A database schema is plan for a database system and describes the relationship between the entity and attribute. It includes the definition of the database name, the record type and the component that make up the records. It is sometimes called as overall model of the data a conceptual model or conceptual schema. A database schema is a meta-data that means data about data and it describes the relations in a database. It is simply say as the “layout” of a database or the blueprint that describes the way data is organized into tables. The language used to describe the schema is Structured Query Language and CREATE statements is used...

Database Architecture

Introduction to Database Architecture Database architecture can be centralized, decentralized and hierarchical. The DBMS architecture divides the whole system as single tier, multi tier and n-tier architecture which can be independently modified, altered, changed or replaced. Types of Database Architecture Single -Tier Two-Tier N-Tier SINGLE TIER The single tier architecture is also called as 1-tier architecture. In 1-tier architecture user work as a server and maintain this changes itself. It does not provide support for end users and use for database designer and programmer. An example of single tier architecture is file server architecture. In this architecture a database is stored on single machine in shared location. Advantage of 1-tier architecture It is relevant to small number of users Disadvantage of 1-tier architecture It works only small amount of data Two-tier architecture The two-tier architecture is like a client-server application. The...

Relational Algebra in Database Management System

Introduction to Relational Algebra  Relational algebra is first described by E.F.Codd and it is a procedural query language. It takes instances of relations as input and yields instances of relations as output. It uses operators to be performed on existing relations to derive result relation. It is similar to normal algebra but the difference is that it uses values instead of numbers and not used as a query language in actual database. It is used to execute query and optimization in relational database. The operators of relational algebra are not same as SQL operators even though they have the same name. Relational Algebra Operators Union Difference Intersection Select Project Rename Cartesian Product Division     Union, Intersection, Difference For union, intersection, difference these operation takes two input relation and that must be union compatible. The term union compatible means same number of fields and corresponding fields have same type. Union (∪) The union of two relations A and...

KEYS in Database Management System

Introduction to keys It is a part of database management system or relational database management system and important part for creating the structure of a table. They are used to establish and identify relation between tables and help how to create integrity on tables. There are various type of keys used in a database. Super Keys Candidate Keys Primary Keys Foreign Keys Alternate keys Simple Keys Compound Keys Composite Keys Let us explain these keys with the help of an example. Consider a relation Employee, having column ID, Emp-no, Name, Designation and City. Super Keys A Set of attributes is called a super key, if it recognizes the tuple uniquely. Every relation has at least one default super key. A super key has redundant attributes. For example: ID + Name + City is a super key. Candidate Keys A candidate keys has more than one attributes in a relation such that they identify a tuple uniquely. It is...

Responsibility of Database Administrator

Responsibility of Database Administrator The database administrator is also known as DBA. DBA is a person or group of person who is responsible to define the structure such as type of fields and range of values that can be stored in fields. The main role of DBA is in backup and recovery. The term ‘backup’ means copying the data to some other folder such as in pen-drive, tape. The term ‘recovery means recover the data when data has lost for some reason. The reason could be anyone such as power failure, instance failure and media failure. The power failure and instance failure is managed by operating system itself in which OS just restart the database and nothing else there is no role of DBA in there. The major role of DBA is in media failure.  DBA is decides which data is to be backup and when (amount...

Normalization in Database Management System

INTRODUCTION This process was developed by E.F.Codd. Normalization is basically used to improve performance by reducing redundant information from the database. In other words normalization is used to reduce the table in normal forms. But the problem occur in normalization is anomalies. ANOMALIES There are various types of anomalies in database. Deletion Anomaly Insertion Anomaly Updation Anomaly Consider the Student Relation. The attribute of the Student relation are SID, Fee, Activity Deletion Anomaly Suppose that each student activity has a fixed fee that is same for all students. If we delete the row for the student table sid (100), we lose a whole tuple related to that row. Insertion Anomaly Suppose we want to enter information in Student Table and we have information only sid and activity we do not know how much fees is to be store in that...

Data Modeling using E-R model

INTRODUCTION Data Model is an abstraction process that hides unnecessary detail from the users for example if you purchase a car then dealer of the car specifies only car information such as mileage not specify other detail such as how its engine works. Data modeling is used in entities and their relationship in the database. The model is used to represent a set of constraint and maintain the data integrity. There are various models to represent the database. Data Model can be classified as: File based Data model Traditional Data Model Semantic Data Model File Based Data Model These models use records to store the entities in a file and directories of various kinds to represent relationship between objects. There are no accepted standard for these models. Traditional Data Models Relational model Hierarchical model Network model Relational Model The relational...

Fundamental of SQL

SQL stands for Structured Query Language it is basically used in Oracle Database. It is an advanced version of relational database management System and it is based on object relational database management system. It provides small and simple commands which help us to save time and reduce the effort of programming to perform complex queries. Two types of SQL are used in Database Interactive SQL Embedded SQL In Interactive SQL we just enter a command; it is executed and get the output. In Embedded SQL it consists of SQL commands put in another programming language such as FORTRAN, PASCAL, COBOL and C. It can make this program powerful and efficient.   Difference between SQL and Programming Language   SQL PROGRAMMING LANGUAGE It is non-procedural language It is procedural language It is a set of commands It is sequence of operation one after the another. In SQL it is simply tell what you want. In PL in which you...