8/24/2022
DBMS: Course Objectives
COURSE OBJECTIVES
APEX INSTITUTE OF TECHNOLOGY The Course aims to:
• Understand database system concepts and design databases for different applications
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
and to acquire the knowledge on DBMS and RDBMS.
• Implement and understand different types of DDL, DML and DCL statements.
Database Management System (21CSH-243)
• Understand transaction concepts related to databases and recovery/backup techniques
Faculty: Prof. (Dr.) Vineet Mehan (E13038)
required for the proper storage of data.
Lecture - 7 DISCOVER . LEARN . EMPOWER
1 2
Relational Model
COURSE OUTCOMES Unit-1 Syllabus
Unit-1 Introduction to Databases and Relational Algebra
On completion of this course, the students shall be able to:-
Overview of Database concepts, DBMS, Data Base System Architecture (Three
Understand the database concept, system architecture and role of database Databases: Level ANSI-SPARC Architecture), Advantages and Disadvantages of
CO1
administrator DBMS, Data Independence, DBA and Responsibilities of DBA,
Relational Data Structure, Keys, Relations, Attributes, Schema and
Instances, Referential integrity, Entity integrity.
Data Models: Relational Model, Network Model, Hierarchical Model, ER Model:
Design, issues, Mapping constraints, ER diagram, Comparison of
Models
Relational Introduction, Syntax, Semantics, Additional operators, Grouping and
Algebra & Ungrouping, Relational comparisons, Tuple Calculus, Domain
Relational Calculus, Calculus Vs Algebra, Computational capabilities
Calculus:
3 4
SUGGESTIVE READINGS WHY RELATIONAL DATABASE MODEL?
• TEXT BOOKS • It is the primary model used today for commercial data processing.
• Ramez Elmasri and Shamkant B. Navathe, “Fundamentals of Database
System”, The Benjamin / Cummings Publishing Co
• Korth and Silberschatz Abraham, “Database System Concepts”, McGraw Hall • Why primary?
• Pratt, “ DBMS”, Cengage Learning
• Because of its simplicity(which makes the job easier for programmers)
• REFERENCE BOOKS
• [Link], “An Introduction to Database Systems”, Addison Wesley
• Rob,” Database Principal Fundamental Design, Cengage Learning
By: Pramod Vishwakarma (E9758) 5 6
1
8/24/2022
WHY RELATIONAL DATABASE MODEL? WHY RELATIONAL DATABASE MODEL?
• Why Commercial? • Microsoft: SQL Server
• Because leading software companies use this model. • IBM: DB2
• Oracle, Microsoft, IBM. • 2 indicates a shift from hierarchical databases like Information
Management System (IMS) to relational databases.
• Oracle Corporation: Oracle
7 8
RELATIONAL MODEL STRUCTURE RELATIONAL MODEL STRUCTURE
• Relational Model represents database as a collection of Table Account Table
relations.
Table in a Database Relation
Table in a Database Relation
Table Account Relation
Row in a Table Tuple
Account_No Branch_Name Balance
Column Header in a Table Attribute SBIA101 SBI _PKL 5000
SBPA102 SBP_JAL 20000
Set of permitted values in Domain HDFCA103 HDFC_IET 0
each attribute IDBIA104 IDBI_BADDI 500
9 10
RELATIONAL MODEL STRUCTURE RELATIONAL MODEL STRUCTURE
Column Header in a Table Attribute
Row in a Table Tuple
3 Column Headers 3 Attributes
There are 4 Rows 4 Tuples
Account_No Branch_Name Balance Account_No Branch_Name Balance
SBIA101 SBI _PKL 5000 SBIA101 SBI _PKL 5000
SBPA102 SBP_JAL 20000 SBPA102 SBP_JAL 20000
HDFCA103 HDFC_IET 0 HDFCA103 HDFC_IET 0
IDBIA104 IDBI_BADDI 500 IDBIA104 IDBI_BADDI 500
11 12
2
8/24/2022
RELATIONAL MODEL STRUCTURE RELATIONAL MODEL STRUCTURE
Set of permitted values in each attribute Domain Domains
Set of valid Account No of Customers Account_ No Domain
Attributes
Set of valid Branch Names Branch_ Name Domain (or columns)
customer_name customer_street customer_city
Set of valid Balance Available Balance Domain
Amit Street1 Chandigarh
Kapil Street2 Panchkula Tuples
Account_No Branch_Name Balance
Aman Street3 Mohali (or rows)
SBIA101 SBI _PKL 5000
Rahul Street4 Ropar
SBPA102 SBP_JAL 20000
HDFCA103 HDFC_IET 0 Customer
IDBIA104 IDBI_BADDI 500
13 14
CARDINALITY CARDINALITY
• In mathematics • In RDBMS
• Cardinality of a set is a measure of the "number of elements of the • Cardinality is the number of unique data values present in a particular
set". attribute(column) of a relation(table).
• Highest Cardinality: All data values in a column are unique.
• For example, the set A = {2, 4, 6} contains 3 elements, and therefore A • Average Cardinality: Some data values in a column are same.
has a cardinality of 3. • Lowest Cardinality: All data values in a column are same.
15 16
CANDIDATE KEYS CANDIDATE KEY
• A candidate key is a column(attribute) that uniquely identifies rows in
a table.
CANDIDATE KEY
• Any of the identified candidate keys can be used as the table's
primary key.
• Candidate keys that are not part of the primary key are called
PRIMARY KEY ALTERNATE KEYS alternate keys.
17 18
3
8/24/2022
CANDIDATE KEY CANDIDATE KEY
Account_No Branch_Name Balance Account_No Branch_Name Balance
SBIA101 SBI _PKL 5000 SBIA101 SBI _PKL 5000
SBPA102 SBP_JAL 20000 SBIA102 SBI_PKL 5000
HDFCA103 HDFC_IET 0 SBPA102 SBP_JAL 20000
IDBIA104 IDBI_BADDI 500 HDFCA103 HDFC_IET 0
IDBIA104 IDBI_BADDI 500
According to this table every column(attribute) has unique values
Therefore any column(attribute) can act as primary key Therefore Branch_Name and Balance can’t be used a Primary Key
Once a Primary Key column is chosen, other unique key columns are called alternate keys The best option here is to choose Account_No as Primary Key
19 20
Super can be: Account_No
SUPER KEY Super can be: Account_No+Branch_Name Assessment Questions
• A super key is a column or set of columns that uniquely identifies a • IDENTIFY THE DIFFERENCES BETWEEN
row within a table. • PRIMARY KEY AND CANDIDATE KEY
Super can be: Account_No + Branch_Name + Balance • PRIMARY KEY AND SUPER KEY
• SUPER KEY AND CANDIDATE KEY
Account_No Branch_Name Balance
SBIA101 SBI _PKL 5000
SBIA102 SBI_PKL 5000
SBPA102 SBP_JAL 20000
HDFCA103 HDFC_IET 0
IDBIA104 IDBI_BADDI 500
Here Primary Key is Account_No
21 22
REFERENCES REFERENCES
• [Link] • [Link]
• [Link] notations/
• [Link]
• [Link]
[Link]
23 24
4
8/24/2022
THANK YOU
For queries
Email: vineet.e13038@[Link]
25
