Database Management Systems (DBMS)
Relational Query
Languages
P Outline
Looping
• Structure of Relational Databases
• Key
• Relational Algebra
• Fundamental Operators and Syntax
§ Selection
§ Projection
§ Cross Product OR Cartesian Product
§ Joins
§ Set Operators
§ Division
§ Rename
§ Aggregate Functions
• Open Source and Commercial DBMS
Section - 1
Structure of Relational Databases
Columns (5) Table (Relation): A database object that holds a
collection of data for a specific topic.
Student Table consist of rows and columns.
Attributes:
RollNo Name Branch Semester SPI
Title of column Column (Attribute): The vertical component of a
101 Raju CE 3 8
table. A column has a name and a particular data
Cardinality = No of tuples (7)
102 Mitesh CI 3 7 type; e.g. varchar, decimal, integer, datetime etc.
Rows or 103 Mayur CE 3 6
Tuples or Record (Tuple): The horizontal component of a
Records (7) 104 Nilesh EE 3 9
table, consisting of a sequence of values, one for
105 Hitesh CI 3 7 each column of the table. It is also known as row.
106 Tarun ME 3 8
107 Suresh CE 3 9 A database consists of a collection of tables
(relations), each having a unique name.
Degree = No of columns (5)
Domain is a set of all possible unique values for a specific column.
Domain of Branch attribute is (CE, CI, ME, EE)
4
Section - 2
Super Key
} A super key is a set of one or more attributes whose values uniquely identifies each record
within a relation (table).
Super Key Super Key Super Key
EnrollNo (RollNo, Branch, Sem) (SPI, Name, BL)
EnrollNo RollNo Branch Sem SPI Name BL
190540107001 101 CE 3 8 Raju 0
190540107002 102 CE 3 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
190540106002 102 CI 3 9 Nilesh 0
180540107001 101 CE 5 7 Hitesh 1
180540106001 101 CI 5 8 Tarun 0
180540106002 102 CI 5 9 Suresh 0
6
Candidate Key
} A candidate key is a subset of a super key.
} A candidate key is a single attribute or the least combination of attributes that uniquely
identifies each record in the table.
} A candidate key is a super key for which no proper subset is a super key.
} Every candidate key is a super key but every super key is not a candidate key.
Candidate Key Candidate Key
EnrollNo (RollNo, Branch, Sem)
EnrollNo RollNo Branch Sem SPI Name BL
190540107001 101 CE 3 8 Raju 0
180540107002 101 CE 5 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
180540106002 101 CI 5 9 Nilesh 0
7
Primary Key
} A primary key is a candidate key that is chosen by database designer to identify tuples
uniquely in a relation (table).
Primary Key
Candidate Key Candidate Key
EnrollNo (RollNo, Branch, Sem)
EnrollNo RollNo Branch Sem SPI Name BL
190540107001 101 CE 3 8 Raju 0
180540107002 101 CE 5 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
180540106002 101 CI 5 9 Nilesh 0
8
Alternate Key
} An alternate key is a candidate key that is not chosen by database designer to identify tuples
uniquely in a relation.
Primary Key Alternate Key
Candidate Key Candidate Key
EnrollNo (RollNo, Branch, Sem)
EnrollNo RollNo Branch Sem SPI Name BL
190540107001 101 CE 3 8 Raju 0
180540107002 101 CE 5 7 Mitesh 1
190540106001 101 CI 3 6 Mayur 2
180540106002 101 CI 5 9 Nilesh 0
9
Primary Key rules
} A primary key may have one or more attributes.
} There is only one primary key in the relation (table).
} A primary key attribute value cannot be NULL.
} Generally, the value of a primary key attribute does not change.
10
Foreign Key
} A foreign key is used to link two relations (tables).
} A foreign key is an attribute or collection of attributes in one table that refers to the primary
key in another table.
} A table containing the foreign key is called the child table, and the table containing the primary
key is called the parent table.
Parent Child
Table Table
Student Project
EnrollNo Name Branch Sem ProjectID Title EnrollNo
190540107001 Raju CE 3 101 Bank 190540107001
190540107002 Mitesh CE 3 102 College 190540107002
190540107003 Nilesh CE 3 103 School 190540107003
190540107004 Meet CE 3 104 Hospital 190540107001
11
