UNIT 1

Database: File Processing System Vs DBMS, History, Characteristic-Abstraction levels,

Architecture of a database, Functional components of a DBMS, DBMS Languages -
Database users and DBA.

File Processing System Vs DBMS

1. A database management system coordinates both the physical and the logical
access to the data, whereas a file-processing system coordinates only the physical
access.
2. A database management system is designed to allow flexible access to data (i.e.
queries), whereas a file-processing system is designed to allow predetermined
access to data (i.e. compiled programs).
3. A database management system is designed to coordinate multiple users accessing
the same data at the same time. A file-processing system is usually designed to allow
one or more programs to access different data files at the same time. In a file-
processing system, a file can be accessed by two programs concurrently only if both
programs have read-only access to the file.

4. Redundancy is control in DBMS, but not in file system.

5. Unauthorized access is restricted in DBMS but not in the file system.

6. DBMS provide backup and recovery whereas data lost in file system can't be
recovered.

7. DBMS provide multiple user interfaces. Data is isolated in file system.

1
 DBMS File Processing System
Minimal data redundancy problem
Data Redundancy problem exits
in DBMS
Data Inconsistency does not exist Data Inconsistency exist here
Accessing database is easier Accessing is comparatively difficult
Data is scattered in various files and files
The problem of data isolation is
may be of different format, so data
not found in database
isolation problem exists
Transactions like insert, delete,
In file system, transactions are not
view, updating, etc are possible in
possible
database
Concurrent access and recovery is Concurrent access and recovery is
possible in database not possible
Security of data Security of data is not good
A database manager
A file manager is used to store all
(administrator) stores the
relationships in directories in file systems.
relationship in form of structural
tables

History of Database

1950s and early 1960s:

o Data processing using magnetic tapes for storage
o Tapes provided only sequential access
o Punched cards for input

Late 1960s and 1970s:

o Hard disks allowed direct access to data
o Hierarchical and network data models in widespread use
▪ IBM’s DL/I (Data Language One)
▪ CODAYSL’s DBTG (Data Base Task Group) model
• → the basis of current DBMSs
o Ted Codd defines the relational data model
▪ IBM Research develops System R prototype
▪ UC Berkeley develops Ingres prototype
o Entity-Relationship Model for database
design
1980s
: o Research relational prototypes evolve into commercial systems
2
 • DB2 from IBM is the first DBMS product based on
therelational model

• Oracle and Microsoft SQL Server are the most

prominent commercial DBMS products based on
therelational model

o SQL becomes industrial standard

o Parallel and distributed database systems
o Object-oriented database systems (OODBMS)
• Goal: store object-oriented programming objects in a
database without having to transform them into relational
format

• In the end, OODBMS were not commercially successful due to

high cost of relational to object-oriented transformation and a
sound underlying theory, but they still exist
o Object-relational database systems allow both relational andobject
views of data in the same database

Late 1990s:
o Large decision support and data-mining applications
o Large multi-terabyte data warehouses
o Emergence of Web commerce

Early 2000s:
o XML and XQuery standards
o Automated database administration

Later 2000s:
o Web databases (semi-structured data, XML, complex data types)
o Cloud computing
o Giant data storage systems (Google BigTable, Yahoo PNuts,
Amazon Web Services, …)

3
Characteristics of a Database Stores any kind of Data

A database management system should be able to store any kind of data. It should not be
restricted to the employee name, salary and address. Any kind of data that exists in the real
world can be stored in DBMS because we need to work with all kinds of data that is present
around us.

Support ACID Properties

Any DBMS is able to support ACID (Accuracy, Completeness, Isolation, and Durability)
properties. It is made sure is every DBMS that the real purpose of data should not be lost
while performing transactions like delete, insert an update. Let us take an example; if an
employee name is updated then it should make sure that there is no duplicate data and no
mismatch of student information.

Represents complex relationship between data

Data stored in a database is connected with each other and a relationship is made in
between data. DBMS should be able to represent the complex relationship between data
to make the efficient and accurate use of data.

Backup and recovery

There are many chances of failure of whole database. At that time no one will be able to
get the database back and for sure company will be in a big loss. The only solution is to
take backup of database and whenever it is needed, it can be stored back. All the databases
must have this characteristic.

Structures and described data

A database should not contains only the data but also all the structures and definitions of
the data. This data represent itself that what actions should be taken on it. These
descriptions include the structure, types and format of data and relationship between them.

Data integrity
This is one of the most important characteristics of database management system. Integrity
ensures the quality and reliability of database system. It protects the unauthorized access
of database and makes it more secure. It brings only the consistence and accurate data into
the database.

Concurrent use of database

There are many chances that many users will be accessing the data at the same time. They
may require altering the database system concurrently. At that time, DBMS supports them
to concurrently use the database without any problem.

4
Abstraction levels

A database system is a collection of interrelated data and a set of programs that allow
users to access and modify these data. A major purpose of a database system is to
provide users with an abstract view of the data. That is, the system hides certain details
of how the data are stored and maintained.

Data Abstraction

For the system to be usable, it must retrieve data efficiently. The need for efficiency has
led designers to use complex data structures to represent data in the database. Since many
database-system users are not computer trained, developers hide the complexity from users
through several levels of abstraction, to simplify users’ interactions with the system:

Database Disk

Levels of Abstraction in a DBMS

• Physical level (or Internal View / Schema): The lowest level of abstraction describes
how the data are actually stored. The physical level describes complex low-level data
structures in detail.

• Logical level (or Conceptual View / Schema): The next-higher level of abstraction
describes what data are stored in the database, and what relationships exist among
those data. The logical level thus describes the entire database in terms of a small
number of relatively simple structures. Although implementation of the simple
structures at the logical level may involve complex physical-level structures, the user
of the logical level does not need to be aware of this complexity.

5
• This is referred to as physical data independence.
• View level (or External View / Schema): The highest level of abstraction describes
only part of the entire database. Even though the logical level uses simpler structures,
complexity remains because of the variety of information stored in a large database.
Many users of the database system do not need all this information; instead, they need
to access only a part of the database. The view level of abstraction exists to simplify
their interaction with the system. The system may provide many views for the same
database.
For example, we may describe a record as follows:
type instructor = record

ID : char (5);

name : char (20);

dept name : char (20);

salary : numeric (8,2);

end;

This code defines a new record type called instructor with four fields. Each
field has a name and a type associated with it. A university organization may
have several such record types, including

• department, with fields dept_name, building, and budget

• course, with fields course_id, title, dept_name, and credits
• student with fields ID, name, dept_name and tot_cred

At the physical level, an instructor, department, or student record can be described

as a block of consecutive storage locations.

At the logical level, each such record is described by a type definition, as in the
previous code segment, and the interrelationship of these record types is defined as
well.

Finally, at the view level, computer users see a set of application programs that hide
details of the data types. At the view level, several views of the database are defined, and
a database user sees some or all of these views.

Architecture of a Database
The architecture of a database system is greatly influenced by the underlying computer
system on which the database system runs. Database systems can be centralized, or client-
server, where one server machine executes work on behalf of multiple client machines.

6
Database systems can also be designed to exploit parallel computer architectures. Distributed
databases span multiple geographically separated machines.

A database system is partitioned into modules that deal with each of the
responsibilities of the overall system. The functional components of a database
system can be broadly divided into the storage manager and the query processor
components. The storage manager is important because databases typically require a
large amount of storage space. The query processor is important because it helps the
database system simplify and facilitate access to data.

7
Query Processor:
The query processor components include
· DDL interpreter, which interprets DDL statements and records the definitions in the
data dictionary.
· DML compiler, which translates DML statements in a query language into an
evaluation plan consisting of low-level instructions that the query evaluation engine
understands.
A query can usually be translated into any of a number of alternative evaluation plans
that all give the same result. The DML compiler also performs query optimization,
that is, it picks the lowest cost evaluation plan from among the alternatives.
Query evaluation engine, which executes low-level instructions generated by the
DML compiler.

Storage Manager:

A storage manager is a program module that provides the interface between the low level
data stored in the database and the application programs and queries submitted to the
system. The storage manager is responsible for the interaction with the file manager.

8
Transaction Manager:
A transaction is a collection of operations that performs a single logical function in
a database application. Each transaction is a unit of both atomicity and consistency.
Thus, we require that transactions do not violate any database-consistency constraints.

Functional components of a DBMS

1. File Manager manages the allocation space on disk storage and the data
structures used to represent info stored on other media. In most applications
(99.9%) the file is the central element. All applications are designed with
the specific goal: generation and use of information. A typical file system
layered architecture is the following.
User
Progra
m

Sequential Indexed Random Lists

Logical
I/O
Basic File System Structure
Device Drivers (Disk,tape,etc)

Controllers
Actual
Devic
e

2. Buffer Manager among other tasks, it transfers blocks between disk (or
other devices) and Main Memory (MM). A DMA (Direct Memory
Access) is a form of I/O that controls the exchange of blocks between
MM and a device. When a processor receives a request for a transfer of a
block, it sends it to the DMA which transfers the block uninterrupted.

3. Query Parser translates statements in a query language, whether

embedded or not, into a lower level language. (See RL language example
from CPS510). This parser is also a strategy selector: i.e., finding the best
and most efficient way (faster?) of executing the query.

4. Authorization and Integrity Manager checks for the authority of the

users to access and modify info, as well as integrity constraints (keys,
etc).

5. Recovery Manager ensures that the database is and remains in a consistent

(sound) state after any kind of failure.

6. Concurrency Controller enforces Mutual Exclusion by ensuring that

9
concurrent interactions with the data base proceed without conflict
(deadlocks, etc).

10
Components of a Database

User: - Users are the one who really uses the database. Users can be administrator,
developer or the end users.

Data or Database: - As we discussed already, data is one of the important factor of

database. A very huge amount of data will be stored in the database and it forms the
main source for all other components to interact with each other. There are two types
of data. One is user data. It contains the data which is responsible for the database,
i.e.; based on the requirement, the data will be stored in the various tables of the
database in the form of rows and columns. Another data is Metadata. It is known as
‘data about data’, i.e.; it stores the information like how many tables, their names,
how many columns and their names, primary keys, foreign keys etc. basically these
metadata will have information about each tables and their constraints in the
database.

DBMS: - This is the software helps the user to interact with the database. It allows
the users to insert, delete, update or retrieve the data. All these operations are handled
by query languages like MySQL, Oracle etc.

Database Application: - It the application program which helps the users to interact
with the database by means of query languages. Database application will not have
any idea about the underlying DBMS.

DBMS Languages

To read data, update and store information in DBMS, some languages are used. Database
languages in DBMS are given as below.

• DDL – Data Definition Language

• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL – Transaction Control Language

1. Data Definition Language (DDL)

11
DDL stands for data definition language and used to define database patterns or
structures. DDL is a syntax which is same as syntax of computer programming language
for defining patterns of database.

Few examples of it are:

• CREATE – used to create objects in database

• ALTER – alter the pattern of database
• DROP – helps in detecting objects
• TRUNCATE – erase all records from table
• COMMENT – adding of comments to data dictionary
• RENAME – useful in renaming an object

CREATE statement or command is used to create a new database. In structured query

language the create command creates an object in a relational database management
system. The commonly used create command is as follows

• CREATE TABLE [name of table] ( [ definitions of column ]) [parameters of table]

DROP statement destroys or deletes database or table. In structured query language, it also
deletes an object from relational database management system. Typically used DROP
statement is

• DROP type of object name of object

ALTER statement enhance the object of database. In structured query language it

modifies the properties of database object. The ALTER statement is

• ALTER type of object name of object

RENAME statement is used to rename a database. It’s statement is as follows

• RENAME TABLE old name of table to new name of table.

2. Data manipulation language (DML)

It has statements which are used to manage the data within the pattern of objects. Some of
the samples of the statements are as follows:

• SELECT – useful in holding data from a database

• INSERT – helps in inserting data in to a table

12
 • UPDATE – used in updating the data
• DELETE – do the function of deleting the records
• MERGE – this do the UPSERT operation i.e. insert or update operation
• CALL – this calls a structured query language or a java subprogram
• EXPLAIN PLAN – has the parameter of explaining data
• LOCK TABLE – this ha the function of controlling concurrency

These syntax elements are similar to the syntax elements used in computer programming
language. Performing the operation of reading of queries is also a component of data
manipulation language. Other forms of data manipulation languages (DML) are used by
IMS, CODASYL databases.

DML also include the structured query language (SQL) data modifying statements, they
modify the saved data but not the pattern of objects. The initial word of the DML
statements has functional capability.

The query statement SELECT is grouped with data statements of structured query
language (SQL). In practice there is no such difference and it is viewed to be a portion of
DML.

Data manipulation languages contribute to have distinct relishes between database sellers.
They are divided as:

• Procedural programming
• Declarative programming

Initially data manipulation languages were only used in computer programs, but with the
coming of structured query languages it is also used in the database executors.

3. Data Control Language (DCL)

Data Control Language (DCL) is syntax similar to the programming language, which
was used to retrieve the stored or saved data. Examples of the commands in the data
control language (DCL) are:

• GRANT – this permits particular users to perform particular tasks

• REVOKE – it blocks the previously granted untrue permissions

The operations which has the authorization of REVOKE are CONNECT, INSERT, USAGE,
EXECUTE, DELETE, UPDATE and SELECT.

13
The execution of DCL is transactional; it also has the parameter of rolling back. But
the execution of data control language in oracle database does not have the feature of
rolling back.

4. Transaction Control Language (TCL)

Transaction Control Language (TCL) has commands which are used to manage the
transactions or the conduct of a database. They manage the changes made by data
manipulation language statements and also group up the statements in o logical
management.

Some examples of it are:

• COMMIT – use to save work

• SAVE POINT – helps in identifying a point in the transaction, can be rolled
back to the identified point
• ROLL BACK – has the feature of restoring the database to the genuine point,
since from the last COMMIT
• SET TRANSACTION – have parameter of changing settings like isolation level
androll back point

COMMIT command permanently save the transaction in to database.

• It’s syntax is: Commit;

ROLL BACK command uses the save point command to jump to save point in transaction.

• It’ s syntax is: rollback to name-save point;

SAVE POINT command is used to save a transaction temporarily.

• It’s syntax is: Save point name-save point;

Database Users

Database administrators – DBA is responsible for authorizing access to the database, for
coordinating and monitoring its use, and acquiring software and hardware resources as
needed.

Database designers – identify data to be stored in the database and choosing appropriate
structures to represent and store the data. Most of these functions are done before the
database is implemented and populated with the data. It is the responsibility of the database
designers to communicate with all prospective users to understand their requirements and

14
come up with a design that meets these requirements. Database designers interact with
all

15
potential users and develop views of the database that meet the data and processing
requirements of these groups. The final database must support the requirements of all user
groups.

End Users

▪ Casual End Users – occasionally access, may need different information each time.
Use query language to specify requests.
▪ Naïve or parametric end users – main job is to query and update the database using
standard queries and updates. These canned transactions have been carefully
programmed and tested. Examples?
▪ Sophisticated end users – engineers, scientists, analysts who implement applications to
meet their requirements.
▪ Stand alone users – maintain personal databases using ready made packages.

DBA

A database administrator’s (DBA) primary job is to ensure that data is available, protected
from loss and corruption, and easily accessible as needed. Below are some of the chief
responsibilities that make up the day-to-day work of a DBA. DSP deliver an outsourced
DBA service in the UK, providing Oracle Support and SQL Server Support; whilst mindset
and toolset may be different, whether a database resides on-premise or in a Public / Private
Cloud, the role of the DBA is not that different.

1. Software installation and Maintenance

A DBA often collaborates on the initial installation and configuration of a new Oracle,
SQL Server etc database. The system administrator sets up hardware and deploys the
operating system for the database server, then the DBA installs the database software and
configures it for use. As updates and patches are required, the DBA handles this on-going
maintenance. And if a new server is needed, the DBA handles the transfer of data from the
existing system to the new platform.

16
2. Data Extraction, Transformation, and Loading

Known as ETL, data extraction, transformation, and loading refers to efficiently importing
large volumes of data that have been extracted from multiple systems into a data
warehouse environment. This external data is cleaned up and transformed to fit the desired
format so that it can be imported into a central repository.

3. Specialized Data Handling

Today’s databases can be massive and may contain unstructured data types such as images,
documents, or sound and video files. Managing a very large database (VLDB) may require
higher-level skills and additional monitoring and tuning to maintain efficiency.

4. Database Backup and Recovery

DBAs create backup and recovery plans and procedures based on industry best practices,
then make sure that the necessary steps are followed. Backups cost time and money, so the
DBA may have to persuade management to take necessary precautions to preserve data.

System admins or other personnel may actually create the backups, but it is the DBA’s
responsibility to make sure that everything is done on schedule.

In the case of a server failure or other form of data loss, the DBA will use existing backups
to restore lost information to the system. Different types of failures may require different
recovery strategies, and the DBA must be prepared for any eventuality. With technology
change, it is becoming ever more typical for a DBA to backup databases to the cloud,
Oracle Cloud for Oracle Databases and MS Azure for SQL Server.

5. Security

A DBA needs to know potential weaknesses of the database software and the company’s
overall system and work to minimize risks. No system is one hundred per cent immune to
attacks, but implementing best practices can minimize [Link] the case of a security breach or
irregularity, the DBA can consult audit logs to see who has done what to the data. Audit
trails are also important when working with regulated data.

17
6. Authentication

Setting up employee access is an important aspect of database security. DBAs control who
has access and what type of access they are allowed. For instance, a user may have
permission to see only certain pieces of information, or they may be denied the ability to
make changes to the system.

7. Capacity Planning

The DBA needs to know how large the database currently is and how fast it is growing in
order to make predictions about future needs. Storage refers to how much room the
database takes up in server and backup space. Capacity refers to usage level. If the
company is growing quickly and adding many new users, the DBA will have to create the
capacity to handle the extra workload.

8. Performance Monitoring

Monitoring databases for performance issues is part of the on-going system maintenance
a DBA performs. If some part of the system is slowing down processing, the DBA may
need to make configuration changes to the software or add additional hardware capacity.
Many types of monitoring tools are available, and part of the DBA’s job is to understand
what they need to track to improve the system. 3rd party organizations can be ideal for
outsourcing this aspect, but make sure they offer modern DBA support.

9. Database Tuning

Performance monitoring shows where the database should be tweaked to operate as

efficiently as possible. The physical configuration, the way the database is indexed, and
how queries are handled can all have a dramatic effect on database performance. With
effective monitoring, it is possible to proactively tune a system based on application and
usage instead of waiting until a problem develops.

18
10. Troubleshooting

DBAs are on call for troubleshooting in case of any problems. Whether they need to
quickly restore lost data or correct an issue to minimise damage, a DBA needs to quickly
understand and respond to problems when they occur.

19
Database Design and ER Diagram:

The database design process can be divided into six steps. The ER model is most relevant to
the first three steps:

(i) Requirements Analysis: The very first step in designing a database application is
to understand what data is to be stored in the database, what applications must be
built on top of it, and what operations are most frequent and subject to performance
requirements. In other words, we must find out what the users want from the
database.

(ii) Conceptual Database Design: The information gathered in the requirements

analysis step is used to develop a high-level description of the data to be stored in
the database, along with the constraints that are known to hold over this data. This
step is often carried out using the ER model, or a similar high-level data model, and
is discussed in the rest of this chapter.

(iii) Logical Database Design: We must choose a DBMS to implement our database
design, and convert the conceptual database design into a database schema in the
data model of the chosen DBMS. We will only consider relational DBMS’s, and
therefore, the task in the logical design step is to convert an ER schema into a
relational databaseschema.
Beyond the ER Model

ER modeling is sometimes regarded as a complete approach to designing a logical database

schema. This is incorrect because the ER diagram is just an approximate description of the data,
constructed through a very subjective evaluation of the information collected during
requirements analysis. The remaining three steps of database design are briefly described
below:

(iv) Schema Refinement: The fourth step in database design is to analyze the collection
of relations in our relational database schema to identify potential problems, and to
refine it. In contrast to the requirements analysis and conceptual design steps, which
are essentially subjective, schema refinement can be guided by some elegant and
powerful theory.

(v) Physical Database Design: In this step we must consider typical expected
workloads that our database must support and further refine the database design to
ensure that it meets desired performance criteria. This step may simply involve
building indexes on some tables and clustering some tables, or it may involve a
substantial redesign of parts of the database schema obtained from the earlier design
steps.

(vi) Security Design: In this step, we identify different user groups and different roles
played by various users (e.g., the development team for a product, the customer
20
 support representatives, and the product manager). For each role and user group, we
must identify the parts of the database that they must be able to access and the parts
of the database that they should not be allowed to access, and take steps to ensure
that they can access only the necessary parts.

Entities, Attributes & Entity Sets:

An entity is an object that exists and is distinguishable from other objects.

– Example: student, department, employee and branch

Entities have attributes, which defines the property of an entity

– Example: student has names and roll no.

– There are different types of attributes which are categorized as follows:
i) Simple Attributes: having atomic or indivisible values. For e.g.
Dept–a string, PhoneNumber–an eight digit number.

ii) Composite Attributes: having several components in the value. For

e.g.: Qualification with components (DegreeName, Year,
UniversityName).

iii) Derived Attributes: Attribute value is dependent on some other

attribute. For e.g.:Age depends on DateOfBirth. So age is a derived
attribute.

iv) Single-valued Attributes: having only one value rather than a set of
values.
For e.g. PlaceOfBirth–single string value.

v) Multi-valued Attributes: having a set of values rather than a single

value.
For

e.g., CoursesEnrolledattribute for student, EmailAddress attribute

for student, PreviousDegree attribute for student.

21
 Diagrammatic representation of an Entity and different types of attributes:
i) entity -rectangle attribute – ellipse connected to rectangle
ii) multi-valued attribute – double ellipse
iii) composite attribute – ellipse connected to ellipse
iv) derived attribute – dashed ellipse

Domains of Attributes

Each attribute takes values from a set called its domain.

For example,

studentAge – {17,18, …, 55}

HomeAddress–character strings of length 35.

Domain of composite attributes –cross product of domains of component

attributes. Domain of multi-valued attributes –set of subsets of values from the

basic domain

An entity set is a set of entities of the same type that share the same properties.

– Example: set of all students, employees etc.

Relationships and Relationship sets

When two or more entities are associated with each other, we have an instance of a
Relationship.
E.g.: student Ramesh enrolls in Discrete Mathematics course
Relationship enrolls has Student and Course as the participating entity sets.

Degree of a relationship
Degree: The number of participating entities.
• Degree 2: A relationship having 2 entities attached, it is called
binaryrelationship.
• Degree 3: A relationship having 3 entities attached, it is called
ternaryrelationship
• Degree n: A relationship having 2 entities attached, it is called n-
aryrelationship
• Binary relationships are very common and widely used.

Diagrammatic Notation for Relationships

Relationship is represented using a diamond shaped box. Rectangle of each

participating entity is connected by a line to this diamond. Name of the
relationship is written in the box

22
 Binary Relationships and Cardinality Ratio

Cardinality Ratios
• One-to-One: An E1 entity may be associated with at most one E2 entity and
similarly an E2 entity may be associated with at most one E1 entity.

• One-to-Many & Many-to-One: An E1 entity may be associated with many E2

entities whereas an E2 entity may be associated with at most one E1 entity.

• Many-to-Many: Many E1 entities may be associated with a single E2 entity

and a single E1 entity may be associated with many E2 entities

23
 Participation Constraints
An entity set may participate in a relation either totally or partially.
• Total participation: Every entity in the set is involved in some association (or
tuple) of the relationship.
• Partial participation: Not all entities in the set are involved in association (or
tuples) of the relationship.

Attributes for Relationship Types

Relationship types can also have attributes.

Grade gives the letter grade (S,A,B, etc.) earned by the student for a course. It is
neither an attribute of student nor that of course.

Design Issues of ER model:

The notions of an entity set and a relationship set are not precise, and it is possible
to define a set of entities and the relationships among them in a number of different ways.
Use of Entity Sets versus Attributes
Consider the entity set instructor with the additional attribute phone number It can
easily be argued that a phone is an entity in its own right with attributes phone number
and location; the location may be the office or home where the phone is located, with
mobile (cell) phones perhaps represented by the value “mobile.” If we take this point of
view, we do not add the attribute phone number to the instructor. Rather, we create:
• A phone entity set with attributes phone number and location.
• A relationship set inst phone, denoting the association between instructors and
the phones that they have.

Use of Entity Sets versus Relationship Sets

24
 It is not always clear whether an object is best expressed by an entity set or a relationship
set.

we used the takes relationship set to model the situation where a student takes a (section of a)
course. An alternative is to imagine that there is a course-registration record for each course
that each student takes. Then, we have an entity set to represent the course-registration
record. Let us call that entity set registration. Each registration entity is related to exactly one
student and to exactly one section, so we have two relationship sets, one to relate
courseregistration records to students and one to relate course-registration records to sections.
we show the entity sets section and studentfrom with the takes relationship set replaced by
one entity set and two relationship sets:

• registration, the entity set representing course-registration records.

• section reg, the relationship set relating registration and course.

• student reg, the relationship set relating registration andstudent.

Note that we use double lines to indicate total participation by registration entities.

One possible guideline in determining whether to use an entity set or a relationship

set is to designate a relationship set to describe an action that occurs between entities. This
approach can also be useful in deciding whether certain attributes may be more appropriately
expressed as relationships

Binary vs. n-ary relationship sets

Relationships in databases are often binary. Some relationships that appear to be

non- binary could actually be better represented by several binary relationships

25
It is always possible to replace a non-binary relationship set by a number of distinct binary
relationship sets. For example, consider a ternary relationship R associated with three entity
sets A, B and C. We can replace the relationship set R by an entity set E and create three
relationship sets as:

• RA, relating E and A

• RB, relating E and B

• RC, relating E and C

If the relationship set R had any attributes, these are assigned to entity set E. A special
identifying attribute is created for E

Placement of Relationship Attributes

The cardinality ratio of a relationship can affect the placement of relationship attributes:

• One-to-Many: Attributes of 1:M relationship set can be repositioned to only the entity set
on the many side of the relationship

• One-to-One: The relationship attribute can be associated with either one of the
participating entities

• Many-to-Many: Here, the relationship attributes cannot be represented to the entity sets;
rather they will be represented by the entity set to be created for the relationship set.

ER Diagram Notations

An E-R diagram consists of the following major components:

26
• Rectangles divided into two parts represent entity sets. The first part, which in this
textbook is shaded blue, contains the name of the entity set. The second part contains
the names of all the attributes of the entity set.

• Diamonds represent relationship sets.

• Undivided rectangles represent the attributes of a relationship set. Attributes

that are part of the primary key are underlined.

• Lines link entity sets to relationship sets.

• Dashed lines link attributes of a relationship set to the relationship set.

• Double lines indicate total participation of an entity in a relationshipset.

• Double diamonds represent identifying relationship sets linked to weak entitysets

Weak Entity Set-

Consider a section entity, which is uniquely identified by a course identifier,

semester, year, and section identifier. Clearly, section entities are related to course
entities. Suppose we create a relationship set sec course between entity sets section and
course. Now, observe that the information in sec course is redundant, since section
already has an attribute course id, which identifies the course with which the section is
related. One

27
option to deal with this redundancy is to get rid of the relationship sec course;
however, by doing so the relationship between section and course becomes
implicit in an attribute, which is not desirable.

The notion of weak entity set formalizes the above intuition. An entity
set that does not have sufficient attributes to form a primary key is termed a
weak entity set.
strong entity set An entity set that has a primary key is termed a strong entity
set.

For a weak entity set to be meaningful, it must be associated with

another entity set, called the identifying or owner entity set. Every weak entity
must be associated with an identifying entity; that is, the weak entity set is said
to be existence dependent on the identifying entity set. The identifying entity
set is said to own the weak entity set that it identifies. The relationship
associating the weak entity set with the identifying entity set is called the
identifying relationship

had a primary key. However, conceptually, a section is still dependent

on a course for its existence, which is made explicit by making it a weak entity
set.

In E-R diagrams, a weak entity set is depicted via a rectangle, like a

strong entity set, but there are two main differences:

• The discriminator of a weak entity is underlined with a dashed,

rather than a solid, line.

• The relationship set connecting the weak entity set to the

identifying strong entity set is depicted by a double diamond.

ER Diagram Notations
An E-R diagram consists of the following major components:

28
Rectangle – It represents entity in the ER Model.
Ellipse – It represents attribute in the ER Model.
Diamond – It represents relationship between entity and attribute.
Line – It links attribute(s) to entity set(s) and entity set(s) to relationship set(s).
Doubles Ellipses – It represents multivalued attributes.
Dashed Ellipses – It denotes derived attributes.
Double lines – It indicates total participation of an entity in a relationship set.
Double Rectangle – It represents weak entity set.
Double Diamonds – It represents weak relationships.
Multiple ellipses connected to single ellipse using lines – It represents composite attribute
Ellipse with line inside it – It represents single values attributes

29
Extended Entity-Relationship (EE-R) Model

EER is a high-level data model that incorporates the extensions to the original ER model.
Enhanced ERD are high level models that represent the requirements and complexities of
complex database.

In addition to ER model concepts EE-R includes −

• Subclasses and Super classes.

• Specialization and Generalization.

• Category or union type.

• Aggregation.
These concepts are used to create EE-R diagrams.

Subclasses and Super class

Super class is an entity that can be divided into further subtype.

For example − consider Shape super class.

Super class shape has sub groups: Triangle, Square and Circle.

Sub classes are the group of entities with some unique attributes. Sub class inherits the
properties and attributes from super class.

Specialization and Generalization

Generalization is a process of generalizing an entity which contains generalized attributes or

properties of generalized entities.

30
It is a Bottom up process i.e. consider we have 3 sub entities Car, Truck and Motorcycle. Now
these three entities can be generalized into one super class named as Vehicle.
Specialization is a process of identifying subsets of an entity that share some different
characteristic. It is a top down approach in which one entity is broken down into low level entity.

In above example Vehicle entity can be a Car, Truck or Motorcycle.

Category or Union

Relationship of one super or sub class with more than one super class.

Owner is the subset of two super class: Vehicle and House.

Aggregation

Represents relationship between a whole object and its component.

31
Consider a ternary relationship Works_On between Employee, Branch and Manager. Now the best way to
model this situation is to use aggregation, So, the relationship-set, Works_On is a higher level entity-set.
Such an entity-set is treated in the same manner as any other entity-set. We can create a binary relationship,
Manager, between Works_On and Manager to represent who manages what tasks.

32
 UNIT-2
Constraints/key constraints: -

Entity integrity constraints / key constraints specify the condition and restricts the data
that can be stored, only in one table

Definitions:
Key constraint: a key constraint is a statement that a certain minimal field of a relation has
a unique identifier for all tuples. Actually, key constraint is the general term, the term
candidate key is used for satisfying the constraints according to a key constraint.
Candidate key: A set of fields that uniquely identifies a tuple according to a key constraint
is called a candidate key for the relation
Super key: A super key is a super set of a candidate key. A super key is a set
of fields that each contains a candidate key

Specifying key constraint in sql:

In sql , we can declare that columns of a table from a candidate key using two statements.

 Unique key
 Primary key
Example:
Sql>create table student(sid char(10) primary key ,name

varchar(20), login char(10) UNIQUE, age int ,GPA

float);

UNIQUE Key:- The purpose of a UNIQUE Key is to ensure that the

information in the column is UNIQUE,i.e

✓ The data held across the column must be UNIQUE.

✓ Any column can be left blank eith NULL value.

PRIMARY Key: - The purpose of a PRIMARY Key is to ensure that information in

the column is UNIQUE and must be compulsory entered

✓ The data held across the column must be UNIQUE.

✓ Even one column also can’t be left blank.

 Referential Integrity constraints/foreign key constraints: -

Referential Integrity constraints checks the conditions to satisfied in more than one
relation (Two or more tables) connected with some relationship.
If a relation supplies includes a foreign key (SUPP No) matching a Primary
key([Link] No) in some other relation(Supplier), then every value of the
foreign key in that relation(Supplies) must be equal to the primary key of
relation(Supplier).
33
Specifying foreign key constraints in sql:
In SQL, we can declare that columns of a table form a referential integrity
constraint using Foreign key.
Foreign keys represent relationship between tables. A foreign key is a column
whose values are derived from the primary key or UNIQUE Key of some other
table.

The table in which the foreign key is defined is called a foreign table or detail table.
The table that defines the primary key and is referenced by the foreign key is called
the primary or Master Table.

Let us create the new table “Enrolled” connected with the previous example Student table.

Sql>create table enrolled (sid varchar(10), cid varchar(10) primary key,

grade varchar(10), foreign key (sid) references students);

Introduction to relational model:

The relational model was introduced by [Link] in [Link] relational

model represents data in the form of two dimensional tables. The organization of data
into relational tables is known as the logical view of the database.

Characteristics of Relational Model:

• A relational table eliminates all parent child relationships or instead

represented all data in the database as sample row/column tables of data
values
• A relation as similar to a table with rows/columns of datavalues
• Each table as an independent entry and there as no physical relationships betweentables
• Relational model of data management is based on set theory
• The user interface used with relational models as non procedural because
only what needs to be done as specified and not how it has to be done

Fundamental concepts of relations:

Relation: - A Relation can be thought of as a set of records in the form of two-

dimensional table containing rows and columns of data

A relation consists of two things: a relation schema and instance relation.

34
 Relation schema: The relation schema contains the basic information of a table. This information
includes the name of the table, the names of the columns and the data types associated with
each column

Relation instance:
An instance of a relation is a set of types in which each tuple has the same no. of
fields as the relation schema
Relational database schema: A relational database schema is a collection of
relation schemas, describing one or more relations
Relation cardinality: The Relation cardinality is the no. of tuples in the elation
Relation degree: The Relation degree as the [Link] columns in the relation
Tuples/records: The rows of the table as also known as records or tuples
Field/attributes: The columns of the table as also known as fields/attributes

Tabular Representation of Various ER Schemas

The relational model is today the primary data model for commercial data
processing applications. It attained its primary position because of its simplicity,
which eases the job of the programmer, compared to earlier data models such as
the network model or the hierarchical model.

Structure of Relational Databases:

A relational database consists of a collection of tables, each of which is assigned
a unique name. For example, consider the instructor table of Figure:1.5, which
stores information about instructors. The table has four column headers: ID,
name, dept name, and salary. Each row of this table records information about

35
an instructor, consisting of the instructor’s ID, name, dept name, and salary.
Database Schema
When we talk about a database, we must differentiate between the database
schema, which is the logical design of the database, and the database instance,
which is a snapshot of the data in the database at a given instant in time. The
concept of a relation corresponds to the programming- language notion of a
variable, while the concept of a relation schema corresponds to the
programming-language notion of type definition.

Schema Diagrams

A database schema, along with primary key and foreign key dependencies, can be depicted by

schema diagrams. Figure 1.12 shows the schema diagram for our university organization.

36
Schema diagram for the university database.

Referential integrity constraints other than foreign key constraints are not shown explicitly in schema diagrams. We
will study a different diagrammatic representation called the entity- relationship diagram.

37
Views

Introduction to views

• The dynamic result of one or more relational operations operating on the base relations
to produce another relation is called view. A view is a virtual relation that does not
necessarily exist in the database. But can be produced upon request by a particular user
at the time of request.
• A view is object that gives the user a logical view of data from an underlying table or
tables. You can restrict what users can view by allowing them to see only a few columns
from a table.

Purpose of views
• The view mechanism is desirable for several reasons.
• It simplifies queries.
• It can be queried as a base table.
• It provides a powerful and flexible security mechanism by hiding parts of the
database from certain users.
• It permits users to access data in a way that is customized to their needs, so that the
same data can be seen by different users in different ways at the same time.

Updating views

• All updates to a base relation should be immediately reflected in all views that a
single base relation and containing either the primary key or a candidate key of the
base relation.
• Updates are not allowed through views involving multiple base relations.
• Updates are not allowed through views involving aggregation or grouping operations.
Creating

views

Syntax:

CREATE VIEW viewname as

SELECT columnname,cloumnname

FROM tablenmae

WHERE columnname=expression list;

38
Examples: create view on book table which contains two fields title, and author

name. SQL> create view V_book as select title, author_name from book;

View created.

SQL>select * from V_book;

Output:

Title Author_name

Oracle Arora

DBMS Basu

DOS Sinha

ADBMS Basu

Unix Kapoor

39
Selecting Data from a view

Example: Display all the title of book written by author

‘Basu’. SQL> select title from V_Book Where

author_name=’Basu’; Output:

Title

DBMS

ADBMS

Updatable Views

Views can also be used for data manipulation i.e., the user can perform Insert, Update and
the Delete operations on the view. The views on which data manipulation can be done are
called Updatable views, views that do not allow data manipulation are called Read only
Views. When you give a view name in the update, insert or delete statement, the
modification to the data will be passed to the underlying table.

For the view to be updatable, it should meet following criteria:

• The view must be created on a single table.

• The primary key column of the table should be included in the view.
• Aggregate functions cannot be used in the select statement.
• The select statement used for creating a view should not included Distinct,
Group by or Having clause.
• The select statement used for creating a view should not include sub queries.
• It must not use constant, string or values expression like total/5.
Destroying/Altering Tables and

Views Altering Table

The definition of the table is changed using ALTER TABLE statement. The ALTER TABLE
is used to add, delete or modify columns in an existing table explained below:

1) ALTER TABLE…..ADD…..
This is used to add some extra columns into an existing table. The generalized
format is given below.

40
ALTER TABLE relation_name

ADD(new field1 datatype(size),

new field2 datatype(size)’………

new fieldn datatype(size));

41
 Example:

ADD customer phone and fax number in the customer

relation. SQL> ALTER TABLE Customer

ADD(cust_ph_no varchar(15),cust_fax_no

varchar(15)); Table created.

2) ALTER TABLE …..MODIFY

This form is used to change the width as well as data type of existing
relations. the generalized syntax of this from is shown below.
ALTER TABLE relation_name MODIFY(field1 new data type(size),
field2 new data type(size), ....... fieldn new data type(size));

➢ Example:
Modify the data type of the publication year as numeric data
type. SQL> ALTER TABLE Book
MODIFY(pub_year
umber(4)); Table created.
Restrictions of the Alter Table
Using the alter table clause you perform the following tasks:
Change the name of the table.
Change the name of the
column. Drop a column.
Decrease the size of a column if table data exists.
3) ALETR TABLE……DELETE
To delete a column in a table , use the following syntax:

ALTER TABLE table_name

DROP COLUMN column_name

➢ Example: Drop customer fax number from the customer

table. SQL> ALTER TABLE customer

DROP COLUMN cust_fax_no;

DELETING TABLE

The tables are deleted permanently from the database using DROP TABLE
command. We remove all the data from the table using TRUNCATE TABLE
command. It is explained below:

1) DROP TABLE

42
 This command is used to delete a table. The generalized syntax if this form
is given below:
DROP TABLE relation_name
➢ Example: write the command for deleting special_customer relation.
SQL DROP TABLE
Special_customer; TABLR
dropped.

2) Truncate a table
Truncating a table is removing all records from the table. The structure of the
table stays intact. The SQL language has a DELETE statement which can be used
to remove one or more (or all) rows from a table. Truncation releases storage
space occupied by the table, but deletion does not. The syntax:
TRUNCATE TABLE table_name;
➢ Example:
SQL> TRUNCATE TABLE student;
Deleting view
A view can be dropped by using the DROP VIEW command.
Syntax:
DROP VIEW viewname;
➢ Example:
DROP VIEW V_Book;

Triggers.

A trigger is a procedure that is automatically invoked by the DBMS in response to

specified changes to the database, and is typically specified by the DBA. A database that has
a set of associated triggers is called an active database. A trigger description contains three
parts:

Event: A change to the database that activates the

trigger. Condition: A query or test that is run when the
trigger is activated.

Action: A procedure that is executed when the trigger is activated and its con-
dition is true.

A trigger action can examine the answers to the query in the condition part of the trigger,
43
refer to old and new values of tuples modified by the statement activating the trigger, execute
new queries, and make changes to the database.

44
 Examples of Triggers in SQL

The examples shown in Figure 5.19, written using Oracle 7 Server syntax for defining
triggers, illustrate the basic concepts behind triggers. (The SQL:1999 syntax for these
triggers is similar; we will see an example using SQL:1999 syntax shortly.) The trigger
called init count initializes a counter variable before every execution of an INSERT
statement that adds tuples to the Students relation. The trigger called incr count increments
the counter for each inserted tuple that satisfies the condition age < 18.

CREATE TRIGGER init count BEFORE INSERT ON Students /* Event

*/DECLARE
count INTEGER;

BEGIN /*action*/

Count:=0;

END

CREATE TRIGGER incr count AFTER INSERT ON Students /* Event

*/ WHEN ([Link] < 18) /* Condition; ‘new’ is just-
insertedtuple */ FOR EACH ROW
BEGIN /* Action; a procedure in Oracle’s
PL/SQLsyntax */
count := count + 1;
END

(identifying the modified table, Students, and the kind of modifying statement, an INSERT),
and the third field is the number of inserted Students tuples with age < 18. (The trigger in
Figure 5.19 only computes the count; an additional trigger is required to insert the appropriate
tuple into the statistics table.)

CREATE TRIGGER set count AFTER INSERT ON Students /* Event */

REFERENCING NEW TABLE AS
InsertedTuplesFOR EACH STATEMENT
INSERT /* Action */
INTO StatisticsTable(ModifiedTable, ModificationType, Count)
SELECT
‘Students’,
‘Insert’,COUNT *
FROM
InsertedTuples I
[Link] < 18
BEGIN
count := 0;
45
END

46
 SQL: Overview, The Form of Basic SQL Query -UNION, INTERSECT, and
EXCEPT– join operations: equi join and non equi join-Nested queries - correlated and
uncorrelated- Aggregate Functions-Null values, GROUPBY- HAVING Clause.

THE FORM OF A BASIC SQL QUERY

This section presents the syntax of a simple SQL query and explains its meaning through a
conceptual evaluation strategy. A conceptual evaluation strategy is a way to evaluate the
query that is intended to be easy to understand, rather than efficient. A DBMS would
typically execute a query in a different and more efficient way.

Sid sname rating age sid bid day

22 Dustin 7 45.0 22 101 10/10/98
29 Brutus 1 33.0 22 102 10/10/98
31 Lubber 8 55.5 22 103 10/8/98
32 Andy 8 25.5 22 104 10/7/98
58 Rusty 10 35.0 31 102 11/10/98
64 Horatio 7 35.0 31 103 11/6/98
71 Zorba 10 16.0 31 104 11/12/98
74 Horatio 9 35.0 64 101 9/5/98
85 Art 3 25.5 64 102 9/8/98
95 Bob 3 63.5 74 103 9/8/98

Figure 5.1An Instance S 3 of Sailors Figure 5.2 An Instance R2 of Reserves

bid bname color

101 Interlake blue

102 Interlake red

103 Clipper green

104 Marine red

(Q1) Find the names and ages of all sailors.

SELECT DISTINCT [Link], [Link] FROM Sailors S

The answer to this query with and without the keyword DISTINCT on instance S3 of Sailors
is shown in Figures 5.4 and 5.5. The only difference is that the tuple for Horatio appears
twice if DISTINCT is omitted; this is because there are two sailors called Horatio and age
35.
47
(Q2) Find all sailors with a rating above 7.

SELECT [Link], [Link], [Link], [Link] FROM Sailors AS S WHERE [Link] > 7
(Q3) Find the sids of sailors who have reserved a red boat.

SELECT [Link] FROM Boats B, Reserves R WHERE [Link] = [Link] AND [Link] = ‘red’

(Q4) Find the names of sailors who have reserved a red boat.

SELECT [Link] FROM Sailors S, Reserves R, Boats B WHERE [Link] = [Link] AND
[Link] = [Link] AND [Link] = ‘red’

(Q5) Find the colors of boats reserved by Lubber.

SELECT [Link] FROM Sailors S, Reserves R, Boats B WHERE [Link] = [Link]

AND [Link] = [Link] AND [Link] = ‘Lubber’

(Q6) Find the names of sailors who have reserved at least one boat.

SELECT [Link] FROM Sailors S, Reserves R WHERE [Link] = [Link]

Expressions and Strings in the SELECT Command

SQL supports a more general version of the select-list than just a list of columns. Each item
in a select-list can be of the form expression AS column name, where expression is any
arithmetic or string expression over column names (possibly prefixed by range variables)
and constants.

(Q7) Compute increments for the ratings of persons who have sailed two
differentboats on the same day.

SELECT [Link], [Link]+1 AS rating FROM Sailors S, Reserves R1, Reserves

R2 WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link] AND
[Link] <> [Link]
Also, each item in a qualification can be as general as expression1 = expression2.

SELECT [Link] AS name1, [Link] AS name2 FROM Sailors S1,Sailors

S2 WHERE 2*[Link] = [Link]-1.

(Q8) Find the ages of sailors whose name begins and ends with B and has at
least three characters.
48
 SELECT [Link] FROM Sailors S WHERE [Link] LIKE ‘B %B’

The only such sailor is Bob, and his age is 63.5.

UNION, INTERSECT, AND EXCEPT

SQL provides three set-manipulation constructs that extend the basic query form pre- sented
earlier. Since the answer to a query is a multiset of rows, it is natural to consider the use of
operations such as union, intersection, and difference. SQL supports these operations under

the names UNION, INTERSECT, and EXCEPT.4 SQL also provides other set operations:
IN (to
check if an element is in a given set),op ANY,op ALL(tocom-pare a value with the elements
in a given set, using comparison operator op), and EXISTS (to check if a set is empty). IN
and EXISTS can be prefixed by NOT, with the obvious modification to their meaning. We
cover UNION, INTERSECT, and EXCEPT in this section. Consider the following query:

(Q1) Find the names of sailors who have reserved both a red and a green boat.

SELECT [Link] FROM Sailors S, Reserves R1, Boats B1, Reserves R2,
Boats B2 WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link]
AND [Link]
= [Link] AND [Link]=‘red’ AND [Link] = ‘green’

(Q2) Find the sids of all sailors who have reserved red boats but not green boats.

SELECT [Link] FROM Sailors S, Reserves R, Boats B

WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = ‘red’
EXCEPT SELECT [Link] FROM Sailors S2, Reserves R2, Boats B2
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] =
‘green’

Joins
The join operation is one of the most useful operations in relational algebra and is the most
commonly used way to combine information from two or more relations. Althougha join
can be defined as a cross-product followed by selections and projections, joins
49
 arise much more frequently in practice than plain [Link] have received a lot
of attention, and there are several variants of the join operation.

Condition Joins
The most general version of the join operation accepts a join condition c and a pair of
relation instances as arguments, and returns a relation instance. The join condition is
identical to a selection condition in form. The operation is defined as follows:

R 𝖺⊳c S = σc(R × S)

Thus 𝖺⊳ is defined to be a cross-product followed by a selection. Note that the condition

c can (and typically does) refer to attributes of both R and S.

(sid) sname rating age (sid) bid day

22 Dustin 7 45.0 58 103 11/12/96

31 Lubbe r 8 55.5 58 103 11/12/96

Figure 4.12 S1 𝖺⊳[Link]<[Link] R1

Equijoin
A common special case of the join operation R 𝖺⊳ S is when the join condition con- sists
solely of equalities (connected by 𝖠) of the form R.name1 = S.name2, that is, equalities
between two fields in R and S. In this case, obviously, there is some redun- dancy in
retaining both attributes in the result.

Natural Join
A further special case of the join operation R 𝖺⊳ S is an equijoin in which equalities are
specified on all fields having the same name in R and S. In this case, we can simply omit
the join condition; the default is that the join condition is a collection of equalities on all
common fields.
Non Equi Join

50
The SQL NON EQUI JOIN uses comparison operator instead of the equal sign like >, <, >=,
<= along with conditions.

SELECT *

FROM table_name1, table_name2

WHERE table_name1.column [> | < | >= | <= ] table_name2.column;

NESTED QUERIES

A nested query is a querythat has another query embedded within it; the embedded
query is called a subquery.

(Q1) Find the names of sailors who have reserved boat 103.

SELECT [Link]
FROM Sailors S
WHERE [Link] IN ( SELECT [Link]
FROM Reserves R
WHERE [Link] = 103 )
(Q2) Find the names of sailors who have reserved a red boat.

SELECT
[Link]
FROM Sailors S
WHERE [Link] IN ( SELECT [Link]
FROM Reserves R
WHERE [Link] IN ( SELECT [Link]
FROM Boats B
WHERE [Link] = ‘red’ )

(Q3) Find the names of sailors who have not reserved a red boat.
SELECT [Link]
FROM Sailors S
WHERE [Link] NOT IN ( SELECT [Link]
FROM Reserves R
WHERE [Link] IN ( SELECT [Link]
FROM Boats B
WHERE [Link] = ‘red’ )
51
Correlated Nested Queries

In the nested queries that we have seen thus far, the inner subquery has been completely
independent of the outer query:

(Q1) Find the names of sailors who have reserved boat number 103.
SELECT [Link]
FROM Sailors S
WHERE EXISTS (
SELECT *FROM
Reserves
WHERE [Link] = 103
AND [Link] = [Link] )

Set-Comparison Operators

(Q1) Find sailors whose rating is better than some sailor called Horatio.

SELECT [Link]
FROM Sailors S
WHERE [Link] > ANY ( SELECT [Link]
FROM Sailors S2
WHERE [Link] = ‘Horatio’ )

(Q2) Find the sailors with the highest rating .

SELECT [Link]
FROM Sailors S
WHERE [Link] >= ALL ( SELECT
[Link] FROM Sailors S2 )

More Examples of Nested Queries

(Q1) Find the names of sailors who have reserved both a red and a green boat.

SELECT [Link]
FROM Sailors S, Reserves R, Boats B
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] =
‘red’AND [Link] IN ( SELECT [Link]
FROM Sailors S2, Boats B2, Reserves R2
WHERE [Link] = [Link] AND [Link] =
[Link]
AND [Link] = ‘green’ )

52
Noncorrelated
There are two kind of subquery in SQL one is called non-correlated and other is called
correlated subquery. In non correlated subquery, inner query doesn't depend on outer
query and can run as stand alone [Link] used along-with IN or NOT IN sql clause
is good examples of Noncorrelated subquery in SQL. Let's a noncorrelated subquery
example to understand it better
NonCorrelated subquery are used along-with IN and NOT IN clause. here is an example
of subquery with IN clause in SQL.

SQL query: Find all stocks from United States and India

mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FRO

AGGREGATE OPERATORS

We now consider a powerful class of constructs for computing aggregate values such as
MIN
and SUM.

1. COUNT ([DISTINCT] A): The number of (unique) values in the A column.

2. SUM ([DISTINCT] A): The sum of all (unique) values in the A column.
3. AVG ([DISTINCT] A): The average of all (unique) values in the A column.
4. MAX (A): The maximum value in the A column.
5. MIN (A): The minimum value in the A column.

(Q1) Find the average age of all sailors.

53
SELECT AVG ([Link])
FROM Sailors S

54
(Q2) Find the average age of sailors with a rating of 10.

SELECT AVG ([Link])

FROM Sailors S
WHERE [Link] = 10

SELECT [Link], MAX ([Link])

FROM Sailors S

Q3) Count the number of sailors.

SELECT COUNT (*)
FROM Sailors S

NULL VALUES

we have assumed that column values in a row are always known. In practice column values
can be unknown. For example, when a sailor, say Dan, joins a yacht club, he may not yet
have a rating assigned. Since the definition for the Sailors table has a rating column, what
row should we insert for Dan? What is needed here is a special value that denotes unknown.

SQL provides a special column value called null to use in such situations. We use null
when the column value is either unknown or inapplicable. Using our Sailor table definition,
we might enter the row 〈 98, Dan, null, 39 〉 to represent Dan. The presence of null values
complicates many issues, and we consider the impact of null values on SQL in this section.
Comparisons Using Null Values

Consider a comparison such as rating = 8. If this is applied to the row for Dan, is this
condition true or false? Since Dan’s rating is unknown, it is reasonable to say that this
comparison should evaluate to the value unknown.

SQL also provides a special comparison operator IS NULL to test whether a column value
is null; for example, we can say rating IS NULL, which would evaluate to true on the row
representing Dan. We can also say rating IS NOT NULL, which would evaluate to false on
the row for Dan.

55
 Logical Connectives AND, OR, and NOT

Now, what about boolean expressions such as rating = 8 OR age < 40 and rating = 8
AND age < 40? Considering the row for Dan again, because age < 40, the first expression
evaluates to true regardless of the value of rating, but what about the second?We can only
say unknown.

The GROUP BY and HAVING Clauses

we want to apply aggregate operations to each of a number of groups of rows in a

relation, where the number of groups depends on the relation instance (i.e., is not
known in advance).
(Q31) Find the age of the youngest sailor for each rating level.

SELECT MIN ([Link])

FROM Sailors S
WHERE [Link] = i

Q32) Find the age of the youngest sailor who is eligible to vote (i.e., is at least 18
years old) for each rating level with at least two such sailors.

SELECT [Link], MIN ([Link]) AS minageGROUP BY

[Link] COUNT (*) > 1

More Examples of Aggregate Queries

Q3) For each red boat, find the number of reservations for this boat.

SELECT [Link], COUNT (*) AS sailorcount FROM Boats B, Reserves R

WHERE [Link] = [Link] AND [Link] = ‘red’ GROUP BY [Link]

SELECT [Link], COUNT (*) AS sailorcount FROM Boats B, Reserves R

WHERE [Link] = [Link] GROUP BY [Link] HAVING [Link] = ‘red’

(Q4) Find the average age of sailors for each rating level that has at least two sailors.

SELECT [Link], AVG ([Link]) AS avgage

FROM Sailors S
GROUP BY [Link]
HAVING COUNT (*) >
1

56
(Q5) Find the average age of sailors who are of voting age (i.e., at least 18 years old) for

57
each rating level that has at least two sailors.

SELECT [Link], AVG ( [Link] ) AS avgage

FROM Sailors S
WHERE S. age >= 18
GROUP BY [Link]
HAVING 1 < ( SELECT COUNT (*)

FROM Sailors S2 WHERE [Link] = [Link]

(Q6) Find the average age of sailors who are of voting age (i.e., at least 18 years
old) for each rating level that has at least two such sailors.

SELECT [Link], AVG ( [Link] ) AS avgage

FROM Sailors S
WHERE S. age > 18
GROUP BY [Link]

HAVING 1 < ( SELECT COUNT (*)

FROM Sailors S2
WHERE [Link] = [Link] AND [Link] >= 18 )

The above formulation of the query reflects the fact that it is a variant of Q35. The answer
to Q36 on instance S3 is shown in Figure 5.16. It differs from the answer to Q35 in that
there is no tuple for rating 10, since there is only one tuple with rating 10 and age
≥ 18.

SELECT [Link], AVG ( [Link] ) AS avgage

FROM Sailors S
WHERE S. age > 18
GROUP BY [Link]
HAVING COUNT (*) >
1

This formulation of Q36 takes advantage of the fact that the WHERE clause is applied
before grouping is done; thus, only sailors with age > 18 are left when grouping is done.
It is instructive to consider yet another way of writing this query:
SELECT [Link], [Link]
FROM ( SELECT [Link], AVG ( [Link] ) AS
avgage, COUNT (*)
AS ratingcount
FROM Sailors S WHERE S. age > 18 GROUP BY [Link] ) AS
TempWHERE [Link] > 1

58