Relational Database Management System RDBMS RAGENDHU S P

Relational Database Management System RDBMS RAGENDHU S P KV RAJAHMUNDRY HYDERABAD REGION

Contents o o RDBMS Terminologies in RDBMS Keys in RDBMS Relational Algebra

Database Management System What is a DBMS ? o A big C/C++ program (software)written by someone else that allows us to manage efficiently a large database and allows it to persist over long periods of time Give examples of DBMS o DB 2 (IBM), SQL Server (MS), Oracle, Sybase, My. SQL, Postgres… 3

Relational DBMS o There are different data models available, based on how are we going to store the data in the database. o Eg: Relational, Object Oriented, Network etc. o Relational Databases store data or information in tables(also known as relations) o Each table represents a real world entity o A table refers to a two dimensional representation of data using rows and columns

ER diagram

RDBMS o Data is organized into separate tables. o Once the tables have been set up, a relationship can be created to link them together. o Such a database that stores data in separate tables that are related through the use of a common column is called a Relational database.

Related Terminologies o Each row represent one particular record of that table and is know as tuples o Each column represent one particular property of that table and is known as attributes o Number of rows in a relation is known as cardinality of a relation o Number of columns in a relation is known as degree of a relation

Example o Degree of the relation? o Cardinality of the relation?

Keys in RDBMS o Primary Key: One or more columns of a table which is used to uniquely identify each record in a table o Candidate Key: There may be more than one set of attributes, eligible to become primary key in a table. All those eligible set of attributes are called candidate keys o Alternate Key: Only one of the candidate keys is selected as primary key of a table. All other candidate keys are called alternate keys

Example q Candidate keys: {Admission_no, Roll. No, Name} q Selected Primary Key: {Admission_no} q Alternate Keys: {Roll. No, Name}

Foreign Key

Foreign Key q Department and Employee tables Department(departmentid, departmentname) Employee(employeeid, firstname, lastname, departmentid, dateofjoining, salary)

Related Terminologies o o o Cardinality of a relationship Domain of an attribute Key Attributes Super Key Schema and instance of a relation

Relational Algebra o Relational database systems are expected to be equipped with a procedural query language that can assist its users to query the database instances. o Fundamental operators of relational algebra are • • • Select Project Union Set difference Cartesian product

Select Operation (σ) o Used to fetch rows(tuples) from table(relation) which satisfies a given condition. Syntax: σp(r), where p is predicate and r is the name of the relation σage > 17 (Student) σage > 17 and gender = 'Male' (Student)

Project Operation (∏) o Project operation is used to project only a certain set of attributes of a relation. Syntax: ∏A 1, A 2. . . (r) where A 1, A 2 are column names and r is the name of the relation q ∏Name, Age(Student)

Union Operation (∪) o This operation is used to fetch data from two relations(tables) o Relations specified should have same number of attributes and same attribute domain. o Duplicate tuples are automatically eliminated from the result.

Union Operation (∪) o Syntax: A ∪ B, where A and B are relations. o For example, if we have two relations Regular. Class and Extra. Class, both have a column name to save name of student, then, o ∏name(Regular. Class) ∪ ∏name(Extra. Class)

Set Difference (-) q This operation is used to find data present in one relation and not present in the second relation. q This operation is applicable on two relations q Syntax: A – B, where A and B are relations. q For example, if we want to find name of students who attend the Regular. Class but not the Extra. Class: ∏name(Regular. Class) - ∏name(Extra. Class)

Cartesian Product (X) o This is used to combine data from two different relations(tables) into one and fetch data from the combined relation. o Syntax: A X B Table: E Table: D enr ename dept dnr dname 1 Bill A A Marketing 2 Sarah C B Sales 3 John A C Legal

Cartesian Product(EXD) enr ename dept dnr dname 1 Bill A A Marketi ng 1 Bill A B Sales 1 Bill A C Legal 2 Sarah C A Marketi ng 2 Sarah C B Sales 2 Sarah C C Legal 3 John A A Marketi ng 3 John A B Sales 3 John A C Legal

Join o σ dept = dnr (E X D) enr ename dept dnr dname 1 Bill A A Marketing 2 Sarah C C Legal 3 John A A Marketing

Thank You