Download Database Management System – Complete Lecture Notes | SQL, ER, Normalization | 122 Pages and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
UNIT-II
RDBMS (Relational Database Management System)
RDBMS stands for Relational Database Management System.
All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access are based on RDBMS.
It is called Relational Database Management System (RDBMS) because it is based on the relational model introduced by E.F. Codd.
How it works
Data is represented in terms of tuples (rows) in RDBMS.
A relational database is the most commonly used database. It contains several tables, and each table has its primary key.
Due to a collection of an organized set of tables, data can be accessed easily in RDBMS.
Table/Relation
Everything in a relational database is stored in the form of relations. The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data. Each table represents some real-world objects such as person, place, or event about which information is collected. The organized collection of data into a relational table is known as the logical view of the database.
Properties of a Relation:
o Each relation has a unique name by which it is identified in the database. o Relation does not contain duplicate tuples. o The tuples of a relation have no specific order. o All attributes in a relation are atomic, i.e., each cell of a relation contains exactly one value.
A table is the simplest example of data stored in RDBMS.
Example
ID Name AGE COURSE
1 Ajeet 24 B.Tech
2 aryan 20 C.A
3 Mahesh 21 BCA
4 Ratan 22 MCA
5 Vimal 26 BSC
row or record
A row of a table is also called a record or tuple. It contains the specific information of each entry in the table. It is a horizontal entity in the table. For example, The above table contains 5 records.
Properties of a row:
o No two tuples are identical to each other in all their entries. o All tuples of the relation have the same format and the same number of entries. o The order of the tuple is irrelevant. They are identified by their content, not by their position.
Let's see one record/row in the table.
ID Name AGE COURSE
1 Ajeet 24 B.Tech
Column/attribute
A column is a vertical entity in the table which contains all information associated with a specific field in a table. For example, "name" is a column in the above table which contains all information about a student's name.
Properties of an Attribute:
o Every attribute of a relation must have a name. o Null values are permitted for the attributes. o Default values can be specified for an attribute automatically inserted if no other value is specified for an attribute. o Attributes that uniquely identify each tuple of a relation are the primary key.
Name
Ajeet
Aryan
Mahesh
Ratan
Vimal
Data item/Cells
The smallest unit of data in the table is the individual data item. It is stored at the intersection of tuples and attributes.
2 aryan 20 C.A
3 Mahesh 21 BCA
4 Ratan 22 MCA
5 Vimal 26 BSC
Domain:
The domain refers to the possible values each attribute can contain. It can be specified using standard data types such as integers, floating numbers, etc. For example , An attribute entitled Marital_Status may be limited to married or unmarried values.
NULL Values
The NULL value of the table specifies that the field has been left blank during record creation. It is different from the value filled with zero or a field that contains space.
Data Integrity
There are the following categories of data integrity exist with each RDBMS:
Entity integrity : It specifies that there should be no duplicate rows in a table.
Keys in DBMS
Here are some reasons for using sql key in the DBMS system.
- Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records. Moreover, the records could be duplicated. Keys in RDBMS ensure that you can uniquely identify a table record despite these challenges.
- Allows you to establish a relationship between and identify the relation between tables
- Help you to enforce identity and integrity in the relationship.
Types of Keys in DBMS (Database Management System)
There are mainly Eight different types of Keys in DBMS and each key has it’s different functionality:
- Super Key
- Primary Key
- Candidate Key
- Alternate Key
- Foreign Key
- Compound Key
- Composite Key
- Surrogate Key
Let’s look at each of the keys in DBMS with example:
- Super Key – A super key is a group of single or multiple keys which identifies rows in a table.
- Primary Key – is a column or group of columns in a table that uniquely identify every row in that table.
- Candidate Key – is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes.
- Alternate Key – is a column or group of columns in a table that uniquely identify every row in that table.
- Foreign Key – is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity.
- Compound Key – has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database.
- Composite Key – is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individual uniqueness is not guaranteed.
- Surrogate Key – An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key.
Super key
A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.
Example:
EmpSSN EmpNum Empname
9812345098 AB05 Shown
9876512345 AB06 Roslyn
199937890 AB07 James
In the above-given example, EmpSSN and EmpNum name are superkeys.
Primary Key
PRIMARY KEY in DBMS is a column or group of columns in a table that uniquely identify
every row in that table. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. A table cannot have more than one primary key.
Rules for defining Primary key:
- Two rows can’t have the same primary key value
- It must for every row to have a primary key value.
- The primary key field cannot be null.
StudID Roll No First Name LastName Email
1 11 Tom Price [email protected]
2 12 Nick Wright [email protected]
3 13 Dana Natan [email protected]
Candidate Key in DBMS
Foreign key
FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of
another table. Example:
DeptCode DeptName
001 Science
002 English
005 Computer
Teacher ID Fname Lname
B002 David Warner
B017 Sara Joseph
B009 Mike Brunton
In this key in dbms example, we have two table, teach and department in a school. However, there is no way to see which search work in which department.
In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.
Teacher ID DeptCode Fname Lname
B002 002 David Warner
B017 002 Sara Joseph
B009 001 Mike Brunton
This concept is also known as Referential Integrity
COMPOUND KEY It has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database. However, when combined with
the other column or columns the combination of composite keys become unique. The purpose of the compound key in database is to uniquely identify each record in the table. Example:
OrderNo PorductID Product Name Quantity
B005 JAP102459 Mouse 5
B005 DKT321573 USB 10
B005 OMG446789 LCD Monitor 20
B004 DKT321573 USB 15
B002 OMG446789 Laser Printer 3
In this example, OrderNo and ProductID can’t be a primary key as it does not uniquely identify a record. However, a compound key of Order ID and Product ID could be used as it uniquely identified each record.
COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table. The difference between compound and the composite key is that any part of the compound key
can be a foreign key, but the composite key may or maybe not a part of the foreign key.
Referential Integrity constraint
A referential integrity constraint is also known as foreign key constraint. A foreign key is a key whose values are derived from the Primary key of another table.
The table from which the values are derived is known as Master or Referenced Table and the Table in which values are inserted accordingly is known as Child or Referencing Table, In other words, we can say that the table containing the foreign key is called the child table , and the table containing the Primary key/candidate key is called the referenced or parent table. When we talk about the database relational model, the candidate key can be defined as a set of attribute which can have zero or more attributes.
o It is denoted by sigma (σ).
- Notation: σ p(r)
Where:
σ is used for selection prediction r is used for relation p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation
BRANCH_NAME LOAN_NO AMOUNT
Downtown L-17 1000
Redwood L-23 2000
Perryride L-15 1500
Downtown L-14 1500
Mianus L-13 500
Roundhill L-11 900
Perryride L-16 1300
Input:
- σ BRANCH_NAME="perryride" (LOAN)
Output:
BRANCH_NAME LOAN_NO AMOUNT
Perryride L-15 1500
Perryride L-16 1300
- Project Operation:
o This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table. o It is denoted by ∏.
- Notation: ∏ A1, A2, An (r)
Where
A1 , A2 , A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION
NAME STREET CITY
Jones Main Harrison
Smith North Rye
Hays Main Harrison
Curry North Rye
Johnson Alma Brooklyn
Brooks Senator Brooklyn
Input:
1. ∏ NAME, CITY (CUSTOMER)
Output:
NAME CITY
Jones Harrison
Smith Rye
Hays Harrison
CUSTOMER_NAME LOAN_NO
Jones L- 17
Smith L- 23
Hayes L- 15
Jackson L- 14
Curry L- 93
Smith L- 11
Williams L- 17
Input:
1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
Johnson
Smith
Hayes
Turner
Jones
Lindsay
Jackson
Curry
Williams
Mayes
- Set Intersection:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S. o It is denoted by intersection ∩.
- Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
Smith
Jones
- Set Difference:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S. o It is denoted by intersection minus (-).
- Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
Output:
EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME
1 Smith A A Marketing
1 Smith A B Sales
1 Smith A C Legal
2 Harry C A Marketing
2 Harry C B Sales
2 Harry C C Legal
3 John B A Marketing
3 John B B Sales
3 John B C Legal
- Rename Operation:
The rename operation is used to rename the output relation. It is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to STUDENT1.
- ρ(STUDENT1, STUDENT)
SQL
o SQL stands for Structured Query Language. It is used for storing and managing data in relational database management system (RDMS). o It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language. o SQL allows users to query the database in a number of ways, using English-like statements.
Rules:
SQL follows the following rules:
o Structure query language is not case sensitive. Generally, keywords of SQL are written in uppercase. o Statements of SQL are dependent on text lines. We can use a single SQL statement on one or multiple text line.
o Using the SQL statements, you can perform most of the actions in a database. o SQL depends on tuple relational calculus and relational algebra.
SQL process:
o When an SQL command is executing for any RDBMS, then the system figure out the best way to carry out the request and the SQL engine determines that how to interpret the task. o In the process, various components are included. These components can be optimization Engine, Query engine, Query dispatcher, classic, etc. o All the non-SQL queries are handled by the classic query engine, but SQL query engine won't handle logical files.
Characteristics of SQL
o SQL is easy to learn. o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database. o SQL is used to describe the data. o SQL is used to define the data in the database and manipulate it when needed. o SQL is used to create and drop the database and table. o SQL is used to create a view, stored procedure, function in a database. o SQL allows users to set permissions on tables, procedures, and views.
- Binary Datatypes
There are Three types of binary Datatypes which are given below:
Data Type Description
binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.
varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.
image It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.
- Approximate Numeric Datatype :
The subtypes are given below:
Data type From To Description
float -1.79E + 308 1.79E + 308 It is used to specify a floating-point value e.g. 6.2, 2.9 etc.
real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number
- Exact Numeric Datatype
The subtypes are given below:
Data type Description
int It is used to specify an integer value.
smallint It is used to specify small integer value.
bit It has the number of bits to store.
decimal It specifies a numeric value that can have a decimal number.
numeric It is used to specify a numeric value.
- Character String Datatype
The subtypes are given below:
Play Video (^) x
Data
type
Description
char It has a maximum length of 8000 characters. It contains Fixed-length non-unicode characters.
varchar It has a maximum length of 8000 characters. It contains variable-length non-unicode characters.
text It has a maximum length of 2,147,483,647 characters. It contains variable-length non-unicode characters.
- Date and time Datatypes
The subtypes are given below:
Datatype Description
date It is used to store the year, month, and days value.
time It is used to store the hour, minute, and second values.
timestamp It stores the year, month, day, hour, minute, and the second value.
SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data. o SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
