Category: DBMS

Find all employees who live in the city where the company for which they work is located

Q. Consider the following employee database- Employee (Emp_name, Street, City) Works (Emp_name, Company_name, Salary) Company (Company_name, City) Manages (Emp_name, manager_name) Write expressions in SQL for the following queries- (i) Find all employees who live in the city where the company for which they work is located. (ii) Find all employees who live in the same […]

Introduction to Concurrency control

Concurrency control is the procedure in DBMS for managing simultaneous operations without conflicting with each another. Concurrent access is quite easy if all users are just reading data. There is no way they can interfere with one another. Though for any practical database, would have a mix of reading and WRITE operations and hence the […]

Oracle cursor

What is Cursor ? Cursor is a pointer in memory area called context area. What is context area ? Context area is a memory area inside the Process Global Area (PGA) which helps Oracle server in processing an SQL statement by holding the important information about the statement. This information include: Rows returned by a […]

Trigger

What is Trigger ? A trigger is defined as an action taken by database when some database related events occur. Triggers are implicitly fired by ORACLE when triggering event occur,no matter which user is using or used by which application. The code within the trigger called the trigger body. Trigger fires in following operations DML […]

SQL Functions

Five Important aggregate functions are SUM, AVG, MIN, MAX and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows. SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric. AVG () […]

SQL Join

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are: 1. INNER JOIN Select records that have matching values in both tables. 2. LEFT JOIN Select records from the first (left-most) table with matching right table records. […]

RDBMS

A DBMS based on relational model is called relational database management system or RDBMS.RDBMS uses relational structures to store data. A relation is also called a table.A relation contain tuples (rows) and attributes (columns). STUDENT Roll_no Name City 10001 Anurag Burhanpur 10002 Yash Khandwa 10003 Praful Nepa nagar 10004 Harshal Khanknar MARKS Roll_no Marks 10001 […]

OODBMS vs RDBMS

OODBMS RDBMS Stores data and methods Stores only data Main objective data encapsulation and data independence Main objective data independence Classes can be reorganized without affecting the mode of using them. Data can be reorganized without affecting the mode of using them. here objects are active Here data are passive Here structure of data is […]

Serializability

What is Serializability ? Serializability is the concurrency scheme.  Serializability ensures that a schedule for executing concurrent transactions is equivalent to one that executes the transactions serially in some order.  It assumes that all accesses to the database are done using read and write operations. Let’s take a simple example: In this SQL query, two […]

Schedules

What are Schedules ? Schedules are sequences that indicate the chronological order in which instructions of concurrent transactions are executed.A schedule must preserve the order in which the instructions appear in each individual transaction. Types of schedule Serial schedule Non serial schedule Example of serial schedule Transaction2 starts after completion of transaction1. Transaction1 Transaction2 Read(X); […]

Transaction processing concepts

A transaction is a unit of program execution that accesses and  possibly updates one or more data items in  the database. For example, Money transfer from an account to another account.Transaction to transfer Rs 1000 from account A to account B: Read(A) A := A – 500 Write(A) Read(B) B := B + 500 Write(B) […]

Functional Dependency

A Functional dependency is a relationship between attributes. In functional dependency we can obtain the value of another attribute from given attribute. For example,If we know the value of student roll number, we can obtain student address, marks etc. By this, we say that student address and marks is functionally dependent on student roll number. Types […]

Normalization

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.  It divides larger tables to smaller tables and link them using relationships. Purpose of Normalization, Eliminating redundant (useless) data. Ensuring data dependencies make sense i.e data is logically stored. Problems without Normalization Insertion, deletion, updation of […]

Twelve rules of CODD

There are twelve rules formulated by E.F. Codd for RDBMS in 1970. If an RDBMS satisfies all these twelve rules, then only full benefits of the relational database can bring out results.  Twelve rules of Codd Information Representation Guaranteed Access Systematic Treatment of Null Values  Database Description Rule Comprehensive Data Sub-Language View Updating High-Level Update, Insert, […]

Schema

Design of a database is called the schema. Types of Schema Physical schema Logical schema View schema. 1. Physical schema The design of a database at physical level is called physical schema, how the data stored in blocks of storage is described at this level. This is the lowest level of data abstraction. It describes […]

DBMS Keys

Keys Keys are very important part of Relational database.They are used to establish and identify relation between tables. They also ensure that each record within a table can be uniquely identified by combination of one or more fields within a table. Different keys Super Key :Super Key is defined as a set of attributes within a […]

Database Relation

A database relation is a predefined row/column format for storing information in a relational database. Relations are equivalent to tables.Also Known As: Table Characterstics of relation: 1. No Duplicate Tuples: A relation cannot contain two or more tuples which have the same values for all the attributes. i.e., In any relation, every row is unique.2. Tuples […]

Attribute

In general, an attribute is a characteristic. It is the name of the column.An attribute gives the characteristics of the entity. In a database management system (DBMS), an attribute refers to a database component, such a table. It also may refer to a database field.Attributes describe the instances in the row of a database. For […]

Relationship among entities

Relationship A relationship is represented by diamond shape in ER diagram, it shows the relationship among entities. There are four types of relationships: One to One One to Many Many to One Many to Many 1. One to One Relationship: When a single instance of an entity is associated with a single instance of another […]

Entity and Attribute

Entity An entity is an object or component of data. An entity is represented as rectangle in an ER diagram. For example: Let we have two entities Student and College and these two entities have many to one relationship as many students study in a single college. Attribute An attribute describes the property of an […]

Domain

A domain is defined as the set of all unique values permitted for an attribute. The domain of a database attribute is the set of all allowable values for that attribute. Example: A field for gender may have the domain {male, female, unknown} where those three values are the only permitted entries in that column. […]

SQL | DDL, DML, DCL Commands

Structured Query Language(SQL) is the database language which is used to perform certain operations on the existing database and also this language is used to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the required tasks. These some SQL commands are categories as discussed below: DDL(Data Definition Language) […]

Advantages and Disadvantages of DBMS

Advantages of DBMS DBMS offers a variety of techniques to store & retrieve data DBMS serves as an efficient handler to balance the needs of multiple applications using the same data Uniform administration procedures for data Application programmers never exposed to details of data representation and storage. A DBMS uses various powerful functions to store […]

Introduction to Database

Before knowing database one must know about data. Data In simple words data can be facts related to any object in consideration. For example your name, age, height, weigh, etc are some data related to you. A picture , image , file , pdf etc can also be considered data. So data is everything which […]

Introduction to DBMS

DBMS stands for Database Management System. DBMS means database with management system. That means a system used to manage database. Database Management System is a set of programs to store, retrieve and manage data from database. Examples of DBMS MySQL PostgreSQL Microsoft Access SQL Server Oracle What are the components of DBMS ? Data Software […]

RGPV DBMS Explain the concepts of generalization and aggregation with appropriate examples

RGPV 2019Q. Explain the concepts of generalization and aggregation with appropriate examples ? Ans. Generalization: It is a bottom-up approach in which two lower level entities combine to form higher entity. In generalization, the higher level entity can also combine with other lower level entity to make further higher level entity. Generalization proceeds from the […]

RGPV solved Database approach vs Traditional file accessing approach

RGPV 2019Q. Differentiate between Database approach vs Traditional file accessing approach ? Ans.  Database approach Traditional file accessing approach All Application shares a pool of related and integrated data. Use separate data file for each application Minimal data redundancy – Separate data files are integrated in to a single, logical structure. Data redundancy – independent […]

Entity

ENTITY AND ATTRIBUTE ENTITY An entity is an object or component of data. An entity is represented as rectangle in an ER diagram. For example: Let we have two entities Student and College and these two entities have many to one relationship as many students study in a single college.  ATTRIBUTE An attribute describes the […]

SQL Functions

SQL FUNCTIONS Five Important aggregate functions are SUM, AVG, MIN, MAX and COUNT. They are called aggregate functions because they summarize the results of a query, rather than listing all of the rows. SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric. […]

Check Constraint

CHECK CONSTRAINT The CHECK constraint is used to limit the value range that can be placed in a column. The  CHECK constraint allows only certain values for a column. How to create a CHECK constraint? Check constraint can be created in this way: CREATE TABLE Student( RollNumber INT NOT NULL, Name varchar(255),  Age INT, CHECK […]

Primary and Foreign key

PRIMARY KEY AND FOREIGN KEY Primary Key: A primary key is a field in a table which uniquely identifies each row in a table.  Primary keys must contain unique values.  A primary key column cannot have NULL values.  A table can have only one primary key, which may consist of single or multiple fields. How […]

SQL join

SQL JOIN A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are: INNER JOIN: Select records that have matching values in both tables. LEFT JOIN: Select records from the first (left-most) table with matching right table records. RIGHT JOIN: Select […]

DDLDMLDCL

SQL | DDL, DML, DCL COMMANDS Structured Query Language(SQL) is the database language which is used to perform certain operations on the existing database and also this language is used to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the required tasks. These some SQL commands are categories […]

Disadvantages of file system data management

DISADVANTAGES OF FILE SYSTEM DATA MANAGEMENT Data redundancy and inconsistency. Integrity Problems. Security Problems Difficulty in accessing data. Data isolation Limited Data Sharing Atomicity Problems 1) Data redundancy and inconsistency: Data redundancy means duplication of data and inconsistency means that the duplicated values are different. 2) Integrity problems: Data integrity means that the data values […]

History of DBMS

HISTORY OF DBMS In the 1960’s the first DBMS was developed at IBM and was originally called IMS, written for the Apollo program. In the 1970s, Edgar Codd thought of a way to make things a bit easier. He wrote a paper entitled, A Relational Model of Data for Large Shared Data Banks, in which […]

Data types

Primitive datatypes: Data types that are defined by system are called primitive data types. For example: int, float, char, double, bool, etc. The number of bits allocated for each primitive data type depends on the programming languages, the compiler and the operating system. For example:For example, “int” may take 2 bytes for 16 bits system. […]