Data Base Management System
UNIT I
Overview of Database Management System: Introduction, Data and information,
Database, Database Management System, Objectives of DBMS, Evolution of Database
Management Systems, Classification of Database Management System.
UNIT II
File-based System, Drawbacks of File-Based System, Advantages of DBMS, Services of
DBMS, Components and Interfaces of Database Management System, Codd’s Rules.
The Relational Database Model: Integrity Rules, Relational Set Operators,
Relationship within the Relational Database.
UNIT III
Entity-Relationship Model: Introduction, The components of Entity Relationship
Model, Entities, Attributes, Relationships, classification of entity sets, attribute
classification, relationship degree, relationship classification. Normalization, 1NF, 2NF,
3NF and BCNF.
UNIT IV
Structured Query Language: Introduction, Commands in SQL, Data Types in SQL,
Data Definition Language, Selection Operation, Projection Operation, Aggregate
functions, Data Manipulation Language, Table Modification Commands, Table
Truncation, Imposition of Constraints, Join Operation, Set Operation,View, Sub Query,
Embedded SQL,
UNIT V
PL/SQL: Introduction, Structure of PL/SQL, PL/SQL Language Elements, Data Types,
Operators Precedence, Control Structure, Steps to Create a PL/SQL, Program, Iterative
Control, Cursors, Steps to create a Cursors.
UNIT-I
1. Introduce Database Management System by explaining Data, Information and Database.
Ans:
Database Management System (DBMS) is widely being used in many areas like science, business, education, economy, law etc. It covers almost all areas of human development “work” with the constant aid of data. Databases play a crucial role in the management of data in any field.
Data and Information:
Data are raw facts that constitute building blocks of information. Data are the heart of the DBMS. All the data will not convey useful information.
Information is obtained from processed data. Data must be interpreted in order to obtain information. Relevant information is the key for decision-making.
Data can be broadly classified into two types, one is the collection of information needed by the organization and the other is “Metadata” which is the information about the data. Data are the most stable part of an organization’s information system.
Database:
Database is a repository (Storage Area) of data. We can define a database as a well-organized collection of logically related data.
The Database is a well-organized data structure to be easily stored, manipulated and retrieved by users. As related, the data describes the domain of interest to a group of users and that the users can use the data to answer the questions concerning that domain. A database may be of any size and complexity.
The database should contain all the data needed by the organization. As a result, database is a huge volume of data for long-term storage of the data.
2. What is Database Management System? Ans:
A Database Management System (DBMS) consists of collection of interrelated data and a set of programs to manage the data.
Database Management System serves as an interface between the user or application program and the actual database. It is software that is helpful in maintaining and utilizing a database.Characteristics of DBMS:
It is a collection of interrelated and persistent data called Database.
It is a set of application programs used to access, update and manage data called Management System.
DBMS is general-purpose software which is not application specific.
DBMS takes care of storing and accessing data by leaving only application specific tasks to application programs.
DBMS is a complex system that allows a user to do many things to data. DBMS allows user to input, share, edit and manipulate the data.
The DBMS receives all requests that are made by the End-users and processes those requests to fulfill the request’s requirements. While processing the requests, DBMS hides all the complexities behind that from the End-users/application programs. This transparency encourages the users to use DBMS widely.
3. Explain the objectives of DBMS Ans:
The main objectives of Database Management System are Data Availability, Data Integrity, Data Security, and Data Independence.
Data Availability:
Data Availability refers to the fact that the data are made available to wide variety of users in a meaningful format at reasonable cost so that the users can easily access the data.
Data Integrity:
Data Integrity refers to the correctness of the data in the database. It ensures that the data available in the database is a reliable data.
Data Security:
Data Security refers to the fact that only authorized users can access the data. Data Security can be enforced by passwords. If two different users are accessing a
4. Explain the classification of Database Management System (DBMS) Ans:
The Database Management System can be broadly classified into two types. 1. Passive Database Management System
2. Active Database Management System
Passive Database Management System:
Passive Database Management Systems are program-driven.
In passive database management system the users query the current state of database and retrieve the information currently available in the database.
Traditional DBMS are passive in the sense that they are explicitly and synchronously invoked by user or application program initiated operations. Applications send requests for operations to be performed by the DBMS and wait for the DBMS to confirm and return any possible answers.
Active Database Management System:
Active Database Management Systems are data-driven or event-driven systems.
In this system, the users specify to the DBMS what information they need. If theinformation is currently available, the DBMS actively monitors the arrival of the desired information and provides it to the relevant users. Here, the scope of the query includes past, present and future data.
Databases can be classified into different types according to the number of users, location of the database and type of use.
(i) Based on number of users
we can categorize databases into the following types. 1) Personal databases.
2) Workgroup databases. 3) Department databases. 4) Enterprise databases.
5) Internet, Intranet and Extranet databases.
1) Personal databases:
Personal databases are designed to support one user.
2) Workgroup databases:
A workgroup database is designed to support the joint efforts of team(Group of users).
3) Department database:
Department databases are designed to support the various functions (works) and activities of a department.
4) Enterprise database:
Enterprise databases are introduced to support organization wide operations and decision-making.
(ii) Based on location of database,
1. Centralized database 2. Distributed database
1) Centralized Database:
The database that supports data located at a single site is called as centralized databas2).
2) Distributed Databases:
The database that supports data that is distributed across several different sites is called as “Distributed Database”.
1. Operational database 2. Data warehouse
1. Operational Database:
The database that is designed basically to support day-to-day operations of an organization is called as “Operational Databases”. They are also called as Transactional Databases or Productional Databases.
2. Dataware house:
Dataware houses basically focus on storing data used to generate information that is needed to make strategic (planned) decisions. They are used in Decision Support Systems.
5. What is Data Model? Explain various Data Models in brief? (Or) Evolution of Database systems?
Data model is a collection of conceptual tools for describing data, relationship between data and consistency constraints.
Data models help in describing the structure of data at the logical level. Data model describes the structure of the database.
A data model is the set of conceptual constructs available for defining schema. Data Model is a language for describing the data and database. It may consist of
abstract concepts which must be translated by the designer into database objects. There are the following data models to represent the structure of a database.
Hierarchical Model:
Hierarchical Model was developed to manage large amounts of data for complex projects. In this model, the basic logical structure is represented by a top-down tree structure. This structure contains several levels where each level contains several record types in file system.
Network Model:
Network Model was created to represent complex data relationships more effectively. It was created to improve database performance and to set a database standard. In Network Model, the user views the database as a collection of records in 1:M relationships. Network Model allows a record to have more than one parent. In Network Model a relationship is composed of at least two record types.
Student: Table
Stud_id Stud_Name Address
1001 Siva kumar Gudur
1002 Rajesh Nellore
1003 Pooja kavali
Entity Relational Model: Entity Relationship Data Model consists of a collection of
objects called entities and relationships among those entities. ER models are normally represented by using graphical representation called ER diagrams to model database components.
Object-Oriented Model:
In Object-Oriented data model both data and their relationships are contained in a single structure called object. Object-Oriented data model is the base for Object-Oriented Database Management System (OODBMS). An object includes information about relationships between various facts within the object and information about its relationships with other objects. Course: Table Course_id Course_Name C001 Java C002 C++ C003 Ms_Office Stud_Course: Table Stud_id Course_Id 1001 C001 1002 C002 1003 C003
UNIT-II 1. Explain about File-Based System in brief Ans:
In the early age of computer based data management there were no database systems to store and manage user’s data. The file-based system of storing and managing data on computer media was an improvement against the manual system. The approach of file-based system data management was used vastly before the invention of database approach.
In this system, we have collection of application programs that perform services for the end users. Each program defines and manages its own data.
Example:
Consider University database, the University database contains details about student, faculty, courses offered etc. In File-based processing for each database there is separate application program. One group of users may be interested in knowing the courses offered by the University. Another group of users may be interested in knowing the faculty information. For each group, a separate set of file and application programs are written.
2. Explain limitations or drawbacks of File-Based System? Ans:
In the early age of computer based data management there were no database systems to store and manage user’s data.
The file-based system of storing and managing data on computer media was an improvement against the manual system.
The approach of file-based system data management was used vastly before the invention of database approach.
Though File-Based system gives the advantage of using computer technology in maintaining data, still it has some limitations.
Data Dependence:
Data dependence means the application program depends on the data. If some changes must be made in the data, then the application program has to be rewritten. Whenever any change is made to a data file, then all the application programs that access that data file must also be refined.
Excessive Program Maintenance:
The above discussed factors create heavy program maintenance. Structural data dependency leads to frequent changes in application programs as every data change needs change in application program.
Incompatible File Formats:
Because of data dependence, the structure of the file depends on the application programming language. For example, the structure of the file generate by FORTRAN program may be different from the structure of a file generated by ‘C’ program. This incompatibility of files makes it difficult to process them jointly.
Separation and Isolation of Data:
In File-Based approach, data are isolated in separate files. Hence it is difficult to access data. The application programmer must synchronize the processing of two files to ensure that the correct data are extracted. This difficulty will be more if data has to be retrieved from more than two files.
3. Explain about Database Management System and its advantages over File-Based System
Ans:
DBMS is software that provides a set of primitives for defining, accessing and manipulating data. In DBMS approach, the same data are being shared by different application programs; as a result data redundancy is very much minimized. The database approach provides several advantages when compared to file processing systems.
Centralized Data Management:
In DBMS, all files are integrated into one system thus reducing redundancies and making data management more efficient.
Data Independence:
Data Independence means that programs are isolated from changes in the way the data are structured and stored. DBMS provides the interface between the application programs and the data. The application programs need not think about how the data are physically structured and stored. Application programs should work with a logical data model and declarative query language.
Reduced Program Maintenance:
The data that is stored in the database need not be changed frequently. As in a database approach, data is more independent of the application programs we can accommodate these changes very easily.
Data Consistency:
Using DBMS, we can maintain data consistency. Data consistency means maintaining same data with same values even though it is stored in multiple locations. We always try to store data values at least possible no.of times. So we can avoid repetition of storing same data value again and again. Hence we can maintain data consistency.
Improved Data Sharing:
The database approach provides end-users to have better access to more data and better-managed data. This access makes possible for end users to respond quickly for changes in their environment.
Improved Data Security:
When multiple users have the access to data, securing data is more critical. To provide security for data, we spend considerable amount of time, effort and money. DBMS provides better facilities for data privacy and security policies.
Improved Data Quality:
The database approach provides number of tools to improve data quality. Database designers can specify integrity constraints. A constraint is a rule that cannot be violated by database users.
Improved Data Access:
DBMS makes it possible to produce quick answers to the queries posed by end users.
4. What are the Functions or Services of Database Management System?
Ans:
A DBMS performs several important functions that guarantee integrity and consistency of data in the database. Most of these functions are transparent to end-users. There are the following important functions and services provided by a DBMS.Data Storage Management:
It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the operating system to access the physical storage.
Data Manipulation Management:
A DBMS furnishes users with the ability to retrieve, update and delete existing data in the database.
Data Definition Services:
The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mappings in source form.
Data Dictionary/System Catalog Management:
The DBMS provides a data dictionary or system catalog function in which descriptions of data items are stored and which is accessible to users.
Database Communication Interfaces:
The end-user's requests for database access are transmitted to DBMS in the form of communication messages.
Authorization / Security Management:
The DBMS protects the database against unauthorized access, either intentional or accidental. It furnishes mechanism to ensure that only authorized users can access the database.
Backup and Recovery Management:
The DBMS provides mechanisms for backing up data periodically and recovering from different types of failures. This prevents the loss of data,
Concurrency Control Service:
Since DBMSs support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMSs ensure that the database kept in consistent state and that integrity of the data is preserved.
Transaction Management:
A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database. Therefore, a DBMS must provide a mechanism to ensure either that all the updates corresponding to a given transaction are made or that none of them is made.
Database Access and Application Programming Interfaces:
All DBMS provide interface to enable applications to use DBMS services. They provide data access via Structured Query Language (SQL). The DBMS query language contains two components: (a) a Data Definition Language (DDL) and (b) a Data Manipulation Language (DML).
5. Explain about Components and Interfaces of Database Management System (or) Explain about Database System Environment?
Ans:
The database system is an organization of various components that define and control the storage, management and use of data within a database environment.
A Database Management System involves five major components such as Data,Hardware, Software, Procedure and Users.
Software: The software includes the DBMS software, application programs together with
the operating systems. The application programs are written in third-generation programming languages like C, COBOL, FORTRAN, Pascal etc.
Data: A database is a repository of data that is integrated, shared and persistent.
Integrated Data: Data can be considered to be a unification of several distinct files and
any redundancy among those files is eliminated.
Share: A database contains data that can be shared by different users for different
applications simultaneously.
Persistent: Persistent data are one which cannot be removed from the database.
Procedure: Procedures are the rules that govern the design and the use of database.
6. Explain E.F.Codd’s Relational Model rules Ans:
Edgar Frank Codd (E.F.Codd) defined 12 important relational database rules that make a database system as relational database system. He defined these rules to standardize the definition of relational database system. Any database system that obeys/follows all these rules can be called as Relational Database System.
1. Information:
The information in a relational database must be presented logically in the form of rows and columns in the tables.
2. Guaranteed Access:
Every data value in a table must be accessible through the combination of table name and the column.
3. Systematic treatment of Null Values:
The null values must be treated in a systematic way independent of its datatype. A Null value is different from a white space or blank.
4. Dynamic on-line catalog based on the Relational Model:
The database description is represented at the logical level in the same way as ordinary data. The authorized users can access this data as similar to ordinary data.
5. Comprehensive Sub-Language:
Relational database system must support a common and well-defined language for data definition, retrieval, manipulation, and data integrity and transaction management.
6. View Updating:
The views must be designed as updatable views.
7. High Level Insert, Update and Delete:
This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
8. Physical Data Independence:
Application Programs or Users are isolated from the physical storage structure of the database. They must be allowed to access data even the physical storage hardware is changed.
9. Logical Data Independence:
Application Programs or Users are isolated from the logical structure of the database. They must be allowed to access data even the structure of the tables is changed.
10. Integrity Independence:
All relational integrity constraints must be definable in the relational language and stored in the system catalog but not at application level.
11. Distribution Independence:
The end-users and application programs are completely unaware of where the actual database is located.
12. Non-Subversion:
If the system supports low-level data access, there must be no chance to modify the structure of the tables.
7. Explain about Relational Data Model? Ans:
The Relational Model uses a collection of tables to represent both data and the relationships among those data. Tables are logical structures maintained by the database manager. The relational model is a combination of three components such as Structural, Integrity and Manipulative parts.
Structural Part:
The structural part defines the database as a collection of relations.
Integrity Part:
The database integrity is maintained in the relational model using Primary and Foreign keys.
Manipulative Part:
The relational algebra and relational calculus are the tools used to maintain data in the database. The key features of relational data model as follows:
Each row in the table is called tuple, and each column is called attribute. The intersection of row with column will have data value.
In relational model, rows and columns can be in any order.
All rows in a relation are distinct. No two rows can be exactly the same. Relations must have a key where key can be a set of attributes.
For each column, there is a domain. Domain is the set of valid values for an attribute.
Degree of the relation is the number of attributes in the relation. Cardinality of the relation is the number of tuples in the relation.
Table and Relation:
A table to be a relation, it must satisfy the following rules.
Each intersection point of row and column should contain a single value(atomic value).
All values in a column are of same type. Each column has a unique name.
No two rows are identical.
Example:
In the above relation:
Degree of the relation :4 Cardinality of the relation :3
8. What is a Key? Explain different types of Keys? Ans:
Key: A key is an attribute or group of attributes which is used to identify a row in a
relation uniquely. Keys can be broadly classified into the following types: 1. Super Key
STUDENT
SNO SNAME COURSE AGE
100 Kumar B.Sc 20
101 Lavanya B.Com 21
Primary Key: The database designer can choose one candidate key of a relation as
Primary Key.
Example: EMPLOYEE : Emp_ID, Emp_Name, Emp_Age
Here, Super Key is the combination of all the columns of the relation, Candidate Key is Emp_ID and Primary Key is also Emp_ID.
Foreign Key:
A Foreign Key us set of fields or attributes in one relation that is used to refer a tuple in another
relation.
COURSE STUDENT_COURSE
Course_ID Course_Desc SNO SNAME COURSE_ID
C001 B.Sc 100 Kumar C001
C002 B.Com 101 Rajesh C002
C003 B.A 102 Lavanya C003
103 Gopi C002
104 Rajitha C001
Here Course_ID of STUDENT_COURSE table refers the column Course_ID of
COURSE table. Hence the column Course_ID of COURSE table is called Primary Key and
the column Course_ID of STUDENT_COURSE table is called Foreign Key.
9. Explain about Relational Integrity in detail? Ans:
Data Integrity constraints refer to the accuracy and correctness of data in the database. Data integrity provides a mechanism to maintain data consistency for operations like INSERT, UPDATE and DELETE.
There are different types of Integrity Constraints as follows: 1. NULL Integrity
2. Domain Integrity 3. Entity Integrity 4. Referential Integrity
Null Integrity:
NULL implies that the data value is not known. NULL can be used in absence of a value or the value is not known.
Domain Integrity Constraints:
Domains are used in the relational model to define the characteristics of the columns of a table. A domain refers to the set of all possible values that an attribute can take. Domain integrity constraints are used to specify the valid values that a column defined under the domain can take.
We can define the valid values by specifying a list of values or a range of values or an expression that accepts valid values.
Entity Integrity:
Entity Integrity implies that a Primary Key cannot accept null values. The Primary Key of the relation uniquely identifies a row in a relation. Entity integrity means that in order to represent an entity in the database it is necessary to have a complete identification of the entity’s key attributes.
Referential Integrity:
In the relational data model, associations between tables are defined through the use of foreign keys. The referential integrity rule states that a database must not contain any unmatched foreign key values. Referential integrity constraint does not control a foreign key cannot be null. There may be situations in which each entity may not having a matching entity in another entity set. In those situations, foreign key allows NULL values.
10. Explain Relational Algebra Operations in detail with examples? Ans:
Operations in relational algebra can be broadly classified into 2 types. They are, 1. Database Operations
2. Set Operations
1. Database Operations:
There are three main database operations such as SELECTION, PROJECTION and JOIN operations.
(i)SELECTION Operation:
The selection operation works on a single relation R and defines a relation that contains only those tuples of R that satisfy the specified condition(Predicate). Selection operations is the process of row wise filtering.
Syntax:
Predicate(R)
Here R is the relation from which we want to select the rows, Predicate is the condition based on which we want to filter the rows.
Example:
Relation: STUDENT
Query: List SNO, SNAME and GPA of those students
who are having Marks above 80. Expression: MARKS>80 (STUDENT)
Query: List the student details of first four students in the relation.
Expression: SNO ≤ 103 (STUDENT)
(ii) PROJECTION Operation:
The Projection operation works on a single relation R and defines a relation that contains the values of specified attributes. Projection operation is the process of column wise filtering.
Syntax:
∏
a1, a2, a3,….an(R)
Here a1,a2,a3,…..an are attributes and R stands for relation.
Example:
Relation: EMPLOYEE
Query: List the ENAME and SALARY of all the Employees. Expression :
∏ENAME, SALARY(EMPLOYEE)
(iii) JOIN Operation :
Join Operation combines two relations to form a new relation. The tables should be joined based on a common column. The common column should be compatible in terms of domain.
SNO Sname MARKS
100 Kumar 72 101 Rajesh 75 102 Lavanya 82 103 Gopi 80 104 Suresh 85 105 Rajitha 72
EID ENAME GENDER DEPT SALARY
E001 Krishna Male Marketing 10000
E002 Lavanya Female Sales 7500
E003 Naveen Male Purchasing 12000
E004 Ganesh Male Marketing 6900
E
xample:
EMPLOYEE DEPARTMENT
EID ENAME DEPT DEPT MANAGER
345 Harish Finance Finance Harish
346 Satya Sales Sales Satya
347 Ganesh Finance Production Pavan 348 Pavan Production
EMPLOYEE ⋈
DEPARTMENT
EID ENAME DEPT MANAGER
345 Harish Finance Harish
346 Satya Sales Satya
347 Ganesh Kumar Finance Harish 348 Pavan Production Pavan
(b) Outer Join:
By using Outer Join operation, we can read the rows that are having match in other table. In addition to that, unmatched rows from one table or both tables can also be read.
(c)Left Outer Join:
This operation returns matched rows from both the tables and unmatched rows from left relation.
Syntax:
(d) Right Outer Join:
This operation returns matched rows from both the tables and unmatched rows from right relation.
Syntax:
(e) Full Outer Join:
This operation returns matched and unmatched rows from both the tables.
Syntax:
2. Set Operations:
There are the following operations can be performed on relations using set theory. Set operations are performed on two relations R and S and produce a new relation.
(1)UNION Operation:
The Union operation defines a relation that contains all the tuples of R and S by eliminating duplicate tuples. Union operation between two relations R and S is denoted by:
Syntax: R U S.
Example:
CUSTOMER_BGLR CUSTOMER_CHN
CUST_ID CUST_NAME CUST_CITY CUST_ID CUST_NAME CUST_CITY
C001 Anil Nellore C005 Revanth Tirupati
C002 Satesh Gudur C001 Anil Nellore
C003 Vinay Kavali C004 Hari Nellore
C004 Hari Nellore C006 Naveen Chittore
CUSTOMER_BGLR U CUSTOMER_CHN CUST_ID CUST_NAME CUST_CITY
C002 Satesh Gudur C003 Vinay Kavali C004 Hari Nellore C005 Revanth Tirupati C006 Naveen Chittore (2) INTERSECTION Operation:
The Intersection operation defines a relation that contains the set of tuples that are in both R and S. Intersection operation between two relations R and S is denoted by:
Syntax: R ∩ S.
Example:
CUSTOMER_BGLR CUSTOMER_CHN
CUST_ID CUST_NAME CUST_CITY CUST_ID CUST_NAME CUST_CITY
C001 Anil Nellore C005 Revanth Tirupati
C002 Satesh Gudur C001 Anil Nellore
C003 Vinay Kavali C004 Hari Nellore
C004 Hari Nellore C006 Naveen Chittore
CUSTOMER_BGLR U CUSTOMER_CHN CUST_ID CUST_NAME CUST_CITY
C001 Anil Nellore
C004 Hari Nellore
(3) DIFFERENCE Operation:
The Difference operation defines a relation consisting of the tuples that are in relation R but not in S. Difference operation between two relations R and S is denoted by:
Syntax: R - S.
Example:
CUSTOMER_BGLR CUSTOMER_CHN
CUST_ID CUST_NAME CUST_CITY CUST_ID CUST_NAME CUST_CITY
C001 Anil Kumar Nellore C005 Revanth Tirupati
C002 Sathosh Gudur C001 Anil Kumar Nellore
C003 Vinay Kumar Kavali C004 Hari Krishna Nellore
C004 Hari Krishna Nellore C006 Naveen Chittore
CUSTOMER_BGLR - CUSTOMER_CHN CUST_ID CUST_NAME CUST_CITY
C002 Sathosh Gudur
UNIT-III
1. What is Entity-Relationship Model? Explain the building blocks of E-R Model Diagrams
Ans: Entity-Relationship Model:
Entity-Relationship model gives the conceptual model of the world to be represented in the database. ER Model is based on a perception of a real world that consists of collection of basic objects called entities and relationships among those entities.
The main usage of defining ER model is to provide a high-level model for conceptual database design.
This model acts as an intermediary between database design and implementation. A database schema in ER model can be represented pictorially by
Entity-Relationship diagram.
ER diagram is a graphical modeling tool to standardize ER modeling. The modeling can be carried out with the help of graphical representation of entities, attributes and relationships. The basic building blocks of Entity-Relationship diagram are Entity, Attribute and Relationship.
Entity:
An entity is an object that exists and can be uniquely identified from other objects. Eg: PERSON, EMPLOYEE, STUDENT, DEPARTMENT, PRODUCT, STORE
Attributes:
Attributes are properties or characteristics of entity type. Entities are described in a database by a set of attributes.
Eg: STUDENT - sid, sname, course, gender
EMPLOYEE - ENO, ENAME, DEPT, DOJ, SALARY
Relationship:
A relationship is an association of entities where the association includes one entity from each participating entity type. A relationship type is a meaningful association between entity types.
Eg:
ER Diagram:
The ER Diagram is used to represent database schema. We use the following symbols to represent various types of objects.
2. Explain the classification of Entity Sets in ER Modeling Ans:
Entity sets can be broadly classified into the following types. 1. Strong Entity
2. Weak Entity 3. Associative Entity
Strong Entity:
Strong Entity is the entity set whose existence does not depend on other entity. The entity which contains Key is called as “Strong Entity”.
Notation:
Strong Entity is represented using single rectangle symbol.
Example:
Weak Entity:
Weak Entity is one whose existence depends on other entity. The entity which does not contain Key is called “Weak Entity”.
Notation:
Weak Entity is represented using double rectangle symbol.
Example:
In the above example, Customer barrows Loan. Here loan is a weak entity. For every loan, there should be at least one customer. Here the entity loan depends on the entity customer hence LOAN is a weak entity.
Associative Entity:
An associative entity maps two or more entities together by referencing the primary keys of each entity. In effect, it contains number of foreign keys each from one entity. The Primary Key of the associative entity is typically composed of the Foreign Key columns themselves.
Notation:
Associative Entity is represented using a Rectangle which contains a diamond symbol inside it.
3. Explain various types of Attributes Ans:
Attributes are used to describe the properties or characteristics of the entity. These attributes are classified based on value and structure. Based on value, the attributes can be classified into single valued, multi valued, derived and null attribute. Based on structure, the attributes can be classified as simple and composite attributes.
Symbols Used:
Single-Valued Attribute:
Single-Valued Attribute means there is only one value associated with that attribute.
Eg: For each EMPLOYEE, there is only one EID value.
Multi-Valued Attribute:
If more than one value associated with an attribute, then it is called as “Multi-Valued” attribute.
Eg: An EMPLOYEE can have more than one SKILLS.
Derived Attribute:
The value of a derived attribute can be derived from the values of other related attributes or other entity attributes.
Eg: The AGE of an EMPLOYEE can be calculated by another attribute DOB.
Null Value Attribute:
In some cases, a particular entity may not have any applicable value for an attribute. For such situation, a special value called NULL value is created.
Eg: An EMPLOYEE may not have any PHONENO, then it is treated as null.
Simple Attribute:
The attribute that cannot be sub-divided to yield additional attributes is called as a “Simple Attribute”.
Eg: The value of GENDER cannot be sub-divided.
Composite Attribute:
The attribute that can be sub-divided to yield additional attributes is called as a "Composite Attribute".
4. Explain about Relationship Degree Ans:
Relationship Degree refers to the number of associated entities. It describes the total number of entities that participate in a relationship. Based on this degree we can categorize relationships into unary, binary and ternary relationships. Usually the relationships in relational data model are binary relationships.
Unary Relationship:
Unary Relationship is also known as recursive relationship. In this relationship, the no.of associated entities is only one. An entity related to itself is known as recursive relationship.
Eg:
Binary Relationship:
In a Binary Relationship, two entities are involved. In this relationship, we will have two related entities participate in a relationship.
Eg:
Here two entities PROFESSOR and DEPARTMENT are participating in a relationship ‘is assigned’. Each PROFESSOR is assigned to a particular DEPARTMENT.
Ternary Relationship:
In a Ternary relationship, three entities are involved. Ternary relationships are required when Binary relationships are not sufficient to describe the semantics of an association among three entities.
Eg:
Quaternary Relationship:
In a Quaternary Relationship, four entities are involved.
5. What is Relationship? Explain various types Relationships Ans:
A Relationship is an association among one or more entities. This relationship can be broadly classified into one-to-one relation, one-to-many relation, many-to-one relation and many-to-many relation.
One-to-One Relationship:
In One-to-One relationship, an instance of one entity type is related to exactly one instance of another entity type.
Eg: Consider an example that one COLLEGE is under the control of one PRINCIPAL
One-to-Many Relationship:
In One-to-Many relationship, an instance of one entity type is related to more than one instance of another entity type.
Eg: Consider an example that each instance of DEPARTMENT may be related to more
than one EMPLOYEE instances.
Many-to-One Relationship:
In Many-to-One relationship, many instances of one entity type are related to only one instance of another entity.
Eg: Consider an example that many BOOKs are in a LIBRARY
Many-to-Many Relationship:
In Many-to-Many relationship, many instances of one entity type are related to many instances of another entity type.
Eg: Consider an example that an EMPLOYEE may works multiple PROJECTs, similarly a
PROJECT may be involved with multiple EMPLOYEEs.
6. What is Normalization? Explain the need for Normalization in Database design
Normalization is a process of evaluating and correcting table structures for eliminating redundancy and inconsistency of data. It is the process of decomposing the relations with anomalies to produce smaller, well-structured relations.
Normalization is a formal process for deciding which attributes should be together in a relation based on the concept of determination.
Steps in Normalization:
Normalization can be accomplished and understood in stages each of which corresponds to a normal from. A normal form is a state of a relation that results from applying simple rules regarding functional dependencies to that relation.
1. First Normal Form (1NF): Any multi-valued attributes (repeating groups) have been removed, so there is a single value at the intersection of each row and column of the table.
2. Second Normal Form (2NF): Any Partial Functional Dependencies have been removed.
3. Third Normal Form (3NF): Any transitive Dependencies have been removed.
4. Boyce/Codd Normal Form (BCNF): Any remaining anomalies that results from Functional Dependencies have been removed.
7. Explain First, Second, and Third Normal forms with suitable examples
Relational data model views data as part of a table or collection of tables. A relation is said to be in First Normal Form (1NF) if it contains no repeating groups or multi-valued attributes.
A repeating group is a set of columns that store similar information that repeats in the same table. To transform an unnormalized table into First Normal Form, we identify and remove the repeating groups within the table.
In the above table there are two repeating groups ‘Proj_Num’ and ‘Proj_Name’. The attribute Proj_Num is intended to be a primary key but it contains null values. There is another problem in the above table that is there may be more chance of data inconsistency.
To solve the above anomalies, we convert the above relation into First Normal From. For this we eliminate all the repeating groups in the relation by filling the empty cells with appropriate values.
The redundant data in the above table causes the following anomalies:
Update Anomalies:
A Functional Dependency is a constraint between two attributes or two sets of attributes. The keys are identified based on the theory of Functional Dependency.
If there are two attributes A and B, then attribute B is said to be functionally dependent on attribute A if each value in A determines one and only one value in B.
The functional dependency between the attributes A and B can be written as :
The above notations can be read as “A determines B”.
The attribute set on the left-hand side of the arrow is called "Determinant" and the attribute set on right side of the arrow is called "Dependent". Both determinant and dependent can be a single attribute or set of multiple attributes. If the key attribute set contains only one attribute, then it is called "Simple Key", otherwise it is called "Composite Key".
The attributes that are part of the determinant are called as Key attributes and the attributes that are part of the dependent are called as Non-Key attributes.
Example:
Book : Book_ID, Book_Title, Author, Publisher
In the above relation Book, we can identify different functional dependencies as follows :
Second Normal Form:
The theory of Normalization depends on the understanding of functional dependency. In the above example, we can identify all the functional dependencies in the table Project_Master as follows.
The above functional dependencies can be represented as dependent diagrams as follows.
2NF Definition:
A relation is said to be in second normal form if it is in first normal form and every non-key attribute is fully functionally dependent on Primary Key. Thus no non-key attribute is functionally dependent on some part of the Primary Key. So there should be no partial functional dependency exists in the relation.
Converting a relation from first normal form to second normal is done only when it contains a composite primary key, otherwise it will be in 2NF automatically.
A relation that is in First Normal Form will be in Second Normal Form if any one of the following conditions applies:
1. The primary key consists only one attribute. 2. No non-key attribute exists in the relation.
3. Every non-key attribute is functionally dependent on the full set of primary key attribute.
Partial Functional dependency:
It is a functional dependency in which one or more non-key attributes are functionally dependent on some part of the primary key.
In the above example there is a partial functional dependency in the relation as follows.
Here the non-key attributes Emp_Name, Job_Class and Chrg_per_Hour are functionally dependent on some part of the primary key that only on Emp_Num. So the relation is not in Second Normal Form.
Converting to Second Normal Form: Step 1:
Write the determinants then write the original composite primary key.
Step 2:
Then decompose the original relation into smaller relations based on the determinants listed.
PROJECT (Proj_Num, Proj_Name)
EMPLOYEE (Emp_Num, Emp_Name, Job_Class, Chrg_per_Hour) ASSIGNMENT (Proj_Num, Emp_Num, Hours)
Third Normal Form:
A relation is said to be in Third Normal Form, if it is in second normal form and no transitive dependency exists in the relation.
Transitive Dependency:
A transitive dependency in a relation is a functional dependency between two or more non-key attributes.
In the above example there is a transitive dependency between the non-key attributes Job_Class and Chrg_per_Hour. That is the non-key attribute Job_Class determines another non-key attribute Chrg_per_Hour.
Anomalies with Transitive dependencies:
Transitive dependencies cause the following anomalies in database design:
Insertion Anomaly:
If a new Job_Class is defined, it cannot be inserted until an employee has been assigned with that job_class.
Updation Anomaly:
If Chrg_per_Hour is changed for a particular Job_Class it leads the changes in many rows.
Deletion Anomaly:
If an employee leaves the company, we will loose the information about job_class and its chrg_per_hour also.
Converting to Third Normal Form:
Step 1: Write the determinants in transitive dependencies. Consider these determinants
as primary keys for new tables.
Job_Class
Step 2: Identify the dependent attributes for all the determinants identified in step1. Step 3: Remove all the dependent attributes from transitive dependencies and design new tables.
PROJECT (Proj_Num, Proj_Name)
EMPLOYEE (Emp_Num, Emp_Name, Job_Class) JOB (Job_Class, Chrg_per_Hour)
There are no transitive dependencies exist in the above relations. Hence the relations are in Third Normal Form.
8. Explain Higher-Level Normal forms with suitable examples
The relations that are in 3NF satisfy almost all the business rules of an organization. But still there may be some problems with 3NF relations. To eliminate these problems, we use higher normal forms like Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF).
Boyce-Codd Normal Form:
A relation is said to be in Boyce-Codd Normal Form if every determinant in the relation is a candidate key. When a table contains only one candidate key then it is in 3NF and Boyce-Codd Normal Form.
When a relation has more than one candidate key, anomalies may result even though that relation is in 3NF. Database relations are designed so that they have neither partial dependencies nor transitive dependencies because these types of dependencies result in update anomalies. A relation that is in 3NF contains no transitive dependencies in the relation. A transitive dependency is the functional dependency between two or more non-key attributes. If a non-key attribute determines a key-attribute, it does not violate 3NF but it fails to satisfy the Boyce-Codd Normal Form rule that every determinant in
In the above example, the primary key is the composite key consisting of Stud_ID and Major. Thus the non-key attributes Advisor and GPA are functionally dependent on this key.
There is another functional dependency in the relation that is the non-key attribute Advisor determines the key-attribute Major.
Hence the above relation is not in Boyce-Codd Normal Form, because it does not satisfy the condition of BCNF. So the above table design causes the following anomalies.
Updation Anomaly:
If the advisor Charles Babbage is replaced by James Gosling, this change must be made in multiple rows in the table.
Insertion Anomaly:
If a new advisor Kautilya is appointed to advice in major Economics, those details cannot be inserted unless a student selects Economics as major.
Deletion Anomaly:
If we delete the student with Stud_ID 124, we lose the information about the advisor Einstein advises in Physics.
Converting a relation to BCNF:
A relation that is in 3NF can be converted to relations in BCNF using a simple two step process.
Steps 1:
The relation is modified so that the determinant in the relation that is not a candidate key becomes a component of the primary key. The key attribute that is functionally dependent on that determinant becomes a non-key attribute.
The above relation has a partial functional dependency that is Major is functionally dependent on some portion of the primary key. Thus the relation is in 1NF but not in 2NF.
Decompose the relation to eliminate the partial functional dependencies.
The above two relations are in 3NF. Since there is only one candidate key in each relation they are also in BCNF.
Fourth Normal Form:
A relation is said to be in Fourth Normal Form if it is in BCNF and no multi-valued dependencies in the relation. When a relation is in BCNF and contains no multi-valued dependencies then no anomalies will result in that relation.
Multi-Valued Dependency:
Multi-Valued dependency is a type of dependency which exists when there are atleast three attributes (Ex: A, B, C) in a relation and for each value of A there is a well-defined set of values of B and a well-well-defined set of values of C. However the set of values of B is independent to set C and set of values of C is independent to set B.
Example:
OFFERING (Course, Instructor, Textbook)
Constraints:
1. Each Course has a well-defined set of Instructors 2. Each Course has a well-defined set of Textbooks
3. The Textbooks used for a given Course are independent of the Instructor for that Course
UNIT - IV 1. What is SQL and its Advantages?
SQL stands for Structured Query Language that is an ANSI standard computer language for accessing and manipulating database systems.
SQL was the first commercial language introduced for E.F Codd's Relational model. SQL works with many database programs like MS-Access, DB2, Informatica, MySQL,
MS-SQL Server, Oracle etc.
SQL is used to perform all type of data operations in RDBMS. SQL is widely popular because it offers the following Advantages
Allows users to access data in the relational database management systems. Allows users to describe the data.
Allows users to define the data in a database and manipulate that data. Allows embedding within other languages using SQL modules, libraries &
pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views.
2. Explain about the SQL Commands?
Ans :
SQL commands can be categorized into several types based on their functionality in database operations:
1. Data Definition Language (DDL) 2. Data Manipulation Language (DML) 3. Transaction Control Language (TCL) 4. Data Control Language (DCL)
1. Data Definition Language (DDL) - These SQL commands are used for
creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, and TRUNCATE.
2. Data Manipulation Language (DML) - These SQL commands are used for
storing, retrieving, modifying, and deleting data.
These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.
3. Transaction Control Language (TCL) - These SQL commands are used for
managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
4. Data Control Language (DCL) - These SQL commands are used for providing
3. Explain about Data Definition Language commands?
SQL includes commands to create database objects such as tables, indexes, views etc.
These commands are used to create a database object, modify the structure of an existing object, and also to remove the object from the database.
Commands: 1. CREATE 2. ALTER 3. DROP 4. TRUNCATE
1. CREATE: This command is used to define the structure of an object such as table,
index, view etc. in the database.
Syntax: CREATE <Object Type> <Object Name> (Object Definition); Example: CREATE TABLE Student (Sno Number(3),
Sname Varchar2(20), age number(4,2), DOB Date);
2. ALTER: This command is used to modify the structure of an existing object in the
database. This modification may be changing the datatype, constraints of the properties or adding new properties to the existing objects.
Syntax:
ALTER TABLE <Table Name> ADD/MODIFY (Col1 Definition, Col2 Definition, ….…); Example: ALTER TABLE student MODIFY (sname varchar2(25));
ALTER TABLE student ADD (course char(10)); ALTER TABLE student DROP COLUMN DOB;
ALTER TABLE student RENAME COLUMN DOB TO SDOB;
3.DROP: This command is used to remove or erase an object from the database including
data and structure of the object.
Syntax: DROP <Object Type> < Object Name >; Example: DROP TABLE student;
DROP VIEW Stud_View;
4. TRUNCATE: This command is used to delete all the rows of a table by leaving its
structure.
Syntax: TRUNCATE TABLE <Table Name>; Example: TRUNCATE TABLE Student;
TRUNCATE TABLE Employee;
Q. What is Database Schema?
Ans:
In the SQL environment, a schema is a group of database objects such as tables, indexes, views that are related to each other. Usually the schema belongs to single user or application.
Each DBMS supports various data types to define the domain of the columns that makes a table. Datatypes help the designers to choose an appropriate datatype for columns depending on their requirements.
These datatypes support various kinds of data values such as Numeric, Character, Date etc.
Oracle supports the following datatypes to design the database objects with various domains:
1. Char:
Char datatype is used when the user wants to store a fixed-length of characters. It can hold alphanumeric values in the range of 1 Byte to 2000 Bytes.
CHARACTER(n) where n represents the desired length of the character string.
Example : CHAR(10) or CHARACTER(10) 2. Varchar/Varchar2:
Both Varchar and Varchar2 datatypes are used when the user wants to store variable-length of character values. These two datatypes can hold data values of type alphanumeric.
VARCHAR(n) can accept any length of character string up to n characters in length.
Example : VARCHAR(20) 3. Long:
Long datatype is used to store variable-length of alphanumeric values upto 2GB of size. But only one column of a table can have the datatype long.
4. Date:
Date datatype is used to store data values related to date. The format of date is ‘dd-mon-yy’.
5. TimeStamp:
TimeStamp datatype is used to store data values related to date and time.
6. Number:
Number datatype is used to store only numerical values in the form of both integers and real values. The values of these columns may be either positive or negative.
Syntax:NUMBER [(p[,s])]
Where p indicates the precision and s indicates the scale. The scale cannot be larger than the precision.
Example: NUMBER(4,2)
7. Raw:
Raw datatype is used to store binary data with a maximum size of 200 Bytes.
8. LongRaw:
LongRaw datatype is used store raw large binary data values.
9. Blob, Clob:
These types are used to store large binary and character data values.
10. BFile:
Q. Explain about Data Manipulation Language Commands?
Data Manipulation Language (DML) commands are used for data manipulation operations such as insertion, updation or deletion.
Commands: 1. INSERT 2. SELECT 3. UPDATE 4. DELETE INSERT Command:
INSERT command is used to add new data values to the database. While giving data values each value must be separated by a comma symbol. The data values like char, varchar, varchar2, date, long etc must be enclosed within a pair of single quote symbols. Syntax:
1. INSERT INTO <Table Name> VALUES (List of data values in the order of structure defined);
2. INSERT INTO <Table Name> (List of columns for which data are available) VALUES (List of values in the order of column list specified);
Example: Table - STUDENT(Sno,Sname,age,Dob,course)
1. Insert Into Student Values(100,'Kumar',19, '06-Sep-2010', 'B.Sc');
2. Insert Into Student(Sno, Sname,course) Values (102,'Radhika','B.Com'); Inserting Null Values:
NULL represents information that is not available. NULL values can be inserted as similar to other data values.
Example:
Insert Into Student Values(103,'Rajesh',Null,Null, Null); Inserting Table rows with Select Queries:
We can also use data values of existing tables to make/fill other tables by using select queries.
Syntax :
CREATE TABLE <Table Name> AS < Select Query >; Example:
CREATE TABLE Student_Names AS SELECT sname from student;
SELECT Command:
SELECT command is used to retrieve/read the existing data values from the database. The statements that are built by using SELECT command are called as ‘Queries’.
Syntax:
Selecting Unique Values:
While reading values, we can also read only unique values of a column. For this we use DISTINCT clause.
Syntax:
SELECT DISTINCT <Column Name> FROM <Table Name>; Example: Select Distinct Pname from Product;
Select Distinct Course from Student;
Selecting Ordered Data:
When we read data values, they can also be ordered in either ascending or descending order.
Syntax:
SELECT <Column List> FROM <Table List> ORDER BY <Column Name> ASC/DESC; Examples: Select sname from Student Order By sname Asc;
Select sname from Student Order By sname Desc;
Select Eid, Ename From Employee Order By Salary Desc;
Selecting Computed Columns and Column Aliases:
While retrieving multiple columns of a table we can also perform computations on those columns and we can name that result as a column alias.
Example:
Select Pid, PName, Price*QOH from Product;
Select Pid, PName, Price*QOH As Cost from Product;
Select sid, sname, maths+physics+cs As Total from Student_Results;
UPDATE Command:
UPDATE command is used to update/modify the data values of a table. By using a single update command we can update multiple records. Updations on a table can be done based on a condition or even without any condition.
Syntax: UPDATE <Table Name> SET Col1= Val, Col2=Val, . . . Col n=Val; Condition Based Update:
While performing update operations, we can specify a condition based on which we want to update the rows of the table.
Syntax: UPDATE <Table Name> SET Col1=Val, Col2=Val, . . . . WHERE <Condition>; Example:
Update Product Set Stock_Bal=Price*QOH; Update Student Set Avg=Total/3;
Update Employee Set Desi='Clerk' Where EID=102;
Update Employee Set Salary=Salary+(salary*10)/100 Where Desi='Manager'; Update Employee Set Salary=Salary+(Salary*12.5)/100 Where Dept='Sales' And Desi='Accountant’;
DELETE Command:
DELETE command is used to delete/erase the data values (records) of a table. By using a single DELETE command, we can delete multiple records. Deletions on a table can be done based on a condition or even without any condition.
Syntax: DELETE FROM <Table Name>; Condition Based Deletion:
While performing deletion operations, we can specify a condition based on which we want to delete the rows of the table.
Example:
Delete From Product;
Delete From Student Where Course='B.Sc'; Delete From Emp Where job='Clerk';
Delete From Emp Where Sal < 10000;
Delete From Emp Where Sal < 10000 And job='Manager';
Q. Explain about Transaction Control Language commands? Ans:
A Transaction is a logical unit of work that consists multiple database operations like inserting new data, retrieving data, modifying data or deleting data to and from the database.
The statements that are placed between two commit statements can be defined as a ‘Transaction’.
All the changes that are made to the database can be referred as completion of a single transaction. Transaction changes can be made permanent to the database only if they are committed.
Oracle supports the following commands to maintain transaction integrity: Commands:
1. COMMIT 2. SAVEPOINT 3. ROLLBACK
COMMIT:
COMMIT statement is used to complete a transaction successfully. By using this command we can make the changes done by a transaction permanent to the database. Syntax : 1. COMMIT; 2. COMMIT WORK; Example: Commit; Commit Work; SAVEPOINT:
SAVEPOINTs are like markers that are used to divide a lengthy transaction into smaller and meaningful sub-transactions.
Syntax:
SAVEPOINT <Savepoint ID>; Savepoint S1;
ROLLBACK:
ROLLBACK statement is used to cancel all the actions done by the current transaction. By using this command we can cancel either the entire transaction or upto a specified savepoint.
Q. What are the SQL Operators?
An operator is a symbol or word that acts upon operands. Oracle supports various kinds of operators that can be used to build SQL statements with different operations and queries with different condition criteria.
1. Arithmetic Operators 2. Relational Operators 3. Logical Operators 4. Special Operators
Arithmetic Operators:
Arithmetic Operators are used to build SQL statements containing arithmetic expressions like additions, subtractions, multiplications and divisions.
Rule of Precedence:
The rule of precedence is set of rules that establish the order in which computations are completed.
1. Perform operations within the parenthesis 2. Perform power operations
3. Perform multiplication and division 4. Perform addition and subtraction Example: 8 + 2 * 5 = 18 4 + 5 ^ 2 = 29 ( 8 + 2 ) * 5 = 50 4 + 5 ^ 2 * 3 = 79 Relational Operators:
Relational Operators are used to compare two different quantities of values with each other.
Example:
1 0 > 5 5 = 6 5 ! = 8 1 6 < = 1 5 Salary> 1 0 0 0 0
Logical Operators:
Logical Operators are used to combine two relational expressions to make compound relational expressions.
Example: 5>6 And 6<8
Salary < 5000 And Desi=’Clerk’
Avg>60 Or Result=’Pass’ And Course=’B.Sc’
Special Operators:
SQL allows the use of special operators in conjunction with the WHERE clause. These special operators include:
1. BETWEEN 2. IN 3. LIKE 4. IS NULL 5. EXISTS BETWEEN:
BETWEEN operator may be used to check whether an attribute value is within a range of values.
Syntax : BETWEEN <Value 1> AND <Value 2> Example:
Select Empno, EName, Salary From Emp Where Salary Between 5000 And 10000; Select Empno, EName, Salary From Emp Where Salary Not Between 5000 And 10000;
IN:
IN operator is used to check a value against a list of values. It can be used as an alternative for OR operator. The list of values must be of same type where each value must be separated by a comma symbol.
Syntax:
IN (List of Values) Example:
Select * From Student Where Course In('B.Sc','B.Com','B.A');
Select empno,ename,job From Emp Where Job In('Clerk','Salesman');
LIKE:
LIKE operator is used in conjunction with wildcards to find patterns within string attributes. SQL allows us to use the symbols % and _ wildcard characters to make matches.
IS NULL:
SQL allows the use of IS NULL operator to check for a null attribute value. Syntax:
IS NULL Example:
Select * From Emp Where comm Is Null; Select * From Emp Where comm Is Not Null;
EXISTS:
EXISTS command is used to execute a query based on the results of another query. If the sub-query returns atleast a row, then the main query will be executed.
Syntax:
EXISTS ( Sub Query) Example:
Select EName, Job From Emp Where Exists(Select DName From Dept);
Select sno,sname,course from student where exists(Select cdesc from course); Select sno,sname,course from student where
exists(Select cdesc from course where course='M.C.A');
Q. What are the Aggregate Functions in SQL? ANS:
SQL can perform various mathematical summaries such as counting the number of rows that contain a specified condition, finding the maximum or minimum values for some specified attribute, summing the values in a specified column and averaging the values in a specified column.
Aggregate functions enhance the power of SQL statements. An aggregate function takes an entire attribute as its argument and produces result.
Functions: 1. COUNT 2. MAX 3. MIN 4. SUM 5. AVG COUNT( ):
The COUNT function is used to count the number of non-null values of an attribute. COUNT function can also be used in conjunction with the DISTINCT clause.
Syntax: COUNT (Column Name)
Example: Select Count(*) From Employee;
Select Count(Department) From Employee;
Select Count(Distinct Department) From Employee; Select Count(SID) From Student Where Result='Pass';
MAX( ):
The MAX( ) function is used to find the maximum value in a list of values. It accepts any kind of data as input.
Syntax: MAX (Column Name) Example:
Select Max(Salary) From Employee;
Select Max(DOJ) From Employee Where Department='Marketing'; Select Max(EmpName) From Employee;
Select Max(Salary) From Employee Where Department='Sales'
And Designation='Accountant'; Select Max(QOH) From Product;
