Unit-2
• Relational query languages: Relational algebra, Tuple
and domain relational calculus, SQL3, DDL and DML
constructs, Open source and Commercial DBMS
- MYSQL, ORACLE, DB2, SQL server.
• Relational database design: Domain and data
dependency, Armstrong's axioms, Functional
Dependencies, Normal forms, Dependency
preservation, Lossless design.
Domains, Attributes, Tuples and Relations
Domain
• A domain is a set of atomic values
• Eg: Phone number: The set of 10 digit phone
numbers
Attribute
• An attribute Ai is the name of a role played by some
domain D in the relation schema R. D is called the
domain of Ai and is denoted by dom(Ai).
Tuple
• Mapping from attributes to values drawn from the
respective domains of those attributes.
• Tuples are intended to describe some entity (or
relationship between entities) in the mini world.
Example: a tuple for a PERSON entity might be
{ Name –”smith” , Gender --> Male, Age --> 25 }
Relation
• A named set of tuples all of the same form i.e.,
having the same set of attributes.
Relation schema
• A relation schema R, denoted by R(A1, A2, ...,An), is
made up of a relation name R and a list of attributes
A1, A2, ...,An.
• Each attribute Ai is the name of a role played by
some domain D in the relation schema R. D is called
the domain of Ai and is denoted by dom(Ai).
Degree of a relation
• The degree (or arity) of a relation is the number of
attributes n of its relation schema.
• A relation of degree seven, which stores information
about university students, would contain seven
attributes describing each student. as follows:
• STUDENT(Name, Ssn, Home_phone, Address,
Office_phone, Age, Gpa)
Key Constraints and Constraints on NULL Values
• All tuples in a relation must also be distinct.
• No two tuples can have the same combination of
values for all their attributes.
• There are other subsets of attributes of a relation
schema R with the property that no two tuples in any
relation state r of R should have the same
combination of values for these attributes.
Superkey
• A superkey SK specifies a uniqueness constraint that
no two distinct tuples in any state r of R can have the
same value for SK.
• Every relation has at least one default superkey the
set of all its attributes.
Candidate key
• A relation schema may have more than one key. In
this case, each of the keys is called a candidate key.
• For example, the CAR relation has two candidate
keys: License_number and Engine_serial_number
Primary key
• It is common to designate one of the candidate keys
as the primary key of the relation.
• This is the candidate key whose values are used to
identify tuples in the relation
Relational Databases and Relational Database
Schemas
• A Relational database schema S is a set of relation
schemas S = {R1, R2, ..., Rm} and a s et of integrity
constraints
Example of relational database schema:
• COMPANY = {EMPLOYEE, DEPARTMENT,
DEPT_LOCATIONS, PROJECT, WORKS_ON,
DEPENDENT}
Relational Algebra
• Relational algebra is the basic set of operations for
the relational model
• Enable a user to specify basic retrieval requests as
relational algebra expressions.
• Formal foundation for relational model operations
Unary Relational Operations: SELECT and PROJECT
1) The SELECT Operation
• The select operation denoted by sigma is used to
select a subset of the tuples from a relation
• The selection condition acts as a filter
• SELECT operation to restrict the tuples in a relation to
only those tuples that satisfy the condition.
• The select operator is denoted by <selection
condition>(R)
Eg: 1. Select the EMPLOYEE tuples whose department
number is 4.
Eg: 2. Select the employee tuples whose salary is
greater than $30,000.
2) The PROJECT Operation
• The PROJECT operation denoted by selects certain columns
from the table and discards the other columns Used when we
are interested in only certain attributes of a relation
Eg: To
Relational Algebra Operations from Set Theory
The UNION, INTERSECTION, and MINUS Operations
• UNION: The result of this operation, denoted by RUS, is a
relation that includes all tuples that are either in R or in S or in
both R and S. Duplicate tuples are eliminated.
• INTERSECTION: The result of this operation is a relation that
includes all tuples that are in both R and S.
• SET DIFFERENCE (or MINUS): The result of this operation,
denoted by R-S, is a relation that includes all tuples that are in
R but not in S.
SQL Commands are mainly categorized into five categories:
• DDL – Data Definition Language
• DQL – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL – Transaction Control Language
Data Definition Language (DDL)
• Data Definition Language actually consists of the SQL
commands that can be used defining, altering and deleting
database structures such as tables, indexes, and schemas.
• It simply deals with descriptions of the database schema and
is used to create and modify the structure of database
objects in the database
Data Manipulation Language (DML)
• The SQL commands that deal with the manipulation of data
present in the database belong to DML or Data Manipulation
Language
• This includes most of the SQL statements.
• It is the component of the SQL statement that controls access
to data and to the database.
Commercial DBMS
MySQL
• most popular relational database systems.
• MySQL is now owned by Oracle Corporation
Pros of MySQL
• Free installation. The community edition of MySQL is free to
download.
• Simple syntax and mild complexity. MySQL’s structure and
style are very plain. Developers even consider MySQL a
database with a human-like language.
Cons of MySQL
• Partial open-source support. Although MySQL has an open-
source part, it’s mostly under Oracle’s license.
• MySQL provides no support for some standard SQL features.
Oracle
• Oracle is a relational database management system created
and run by the Oracle Corporation.
Pros of Oracle
• Oracle ensures decent customer support and provides
comprehensive tech documentation across multiple
resources.
Db2
• Db2 is a family of database management system (DBMS)
products from IBM that serve several different operating
system (OS) platforms.
• A Db2 database is a group of data treated collectively as a
unit.
• A database administrator (DBA) uses Db2, which is DBMS or
database server, to create and use Db2 databases.
Advantages of Db2
• Hybrid transaction analytical processing, or HTAP,
performance is accelerated by the Db2
Disadvantages of Db2
• The relational model requires a rigid schema that does not
necessarily fit with some types of modern development.
SQL server
• SQL Server is a relational database management system
(RDBMS) developed and marketed by Microsoft.
• SQL Server is built on top of SQL, a standard programming
language for interacting with relational databases.
SQL Server Editions
• SQL Server Developer Edition is intended for database
development and testing purposes.
• SQL Server Express Edition is suitable for small databases
with a storage capacity of up to 10 GB.
Domain relational calculus
• Relational calculus is a non-procedural query language in
database management systems
• Guides users on what data is needed without specifying how
to obtain it.
Tuple Relational Calculus (TRC)
• Tuple Relational Calculus in DBMS uses a tuple variable (t)
that goes to each row of the table and checks if the predicate
is true or false for the given row.
• Depending on the given predicate condition, it returns the
row or part of the row.
Domain Relational Calculus (DRC)
• Domain Relational Calculus uses domain Variables to get the
column values required from the database based on the
predicate expression or condition.
