Unit-2 :Relational Data Model: Relational model concepts. Characteristics of relations. Relationalmodel constraints: Domain constraints, key constraints, integrity constraints RelationalAlgebra: Basic Relational Algebra operations-union ,intersection, selection, projection,cartesian product. JOIN operations.- inner , outer, equi .

Similar to Database Management Systems(DBMS):UNIT-II Relational Data Model BCA SEP SEM III DBMS

More from Kuvempu University

Recently uploaded

Database Management Systems(DBMS):UNIT-II Relational Data Model BCA SEP SEM III DBMS

• 1.
  Database Management Systems(DBMS) Unit-II BCA Semester III1Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaUNIT–II: Relational Data ModelRelational Data Model• The Relational Data Model is the most widely used data model in modern databases.• In this model, all data is stored in the form of tables (also called relations).• Each table represents an entity, and each row in the table represents a record.• Common relational databases: MySQL, Oracle, PostgreSQL, SQL Server.Relational Model ConceptsThe basic terms in the relational model are:1. Relation (Table)A relation is a table consisting of rows and columns.Example: Student Table
• 2.
  Database Management Systems(DBMS) Unit-II BCA Semester III2Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaReg_No Name Age Department101 Rohan 19 CS102 Priya 20 IT103 Manoj 18 CSHere,• The table name: Student• Each row = one student record• Each column = an attribute of the student2. Tuple (Row)A tuple is a single row in a table.Example:102 Priya 20 ITis a tuple.3. Attribute (Column)An attribute represents a property of the entity.Example:In the Student table: Reg_No, Name, Age, Department4. DomainA domain is the set of valid values an attribute can take.Examples:• Age domain: 15 to 30• Department domain: {CS, IT, ECE}• Name domain: Alphabetic string5. Degree of a RelationNumber of attributes in a table.Example: Student table has 4 attributes → Degree = 4
• 3.
  Database Management Systems(DBMS) Unit-II BCA Semester III3Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bca6. Cardinality of a RelationNumber of tuples (rows) in a table.Example: There are 3 student records → Cardinality = 37. Primary KeyA primary key uniquely identifies each record in a table.Example:• Reg_No is unique for each student → Primary Key = Reg_NoPrimary keys cannot be NULL and cannot be duplicated.8. Foreign KeyA foreign key is an attribute that refers to the primary key of another table.Used to link two tables.Example:Department TableDept_ID Dept_Name10 CS20 ITStudent TableReg_No Name Age Dept_ID (FK)101 Rohan 19 10102 Priya 20 209. Candidate KeyAll attributes that can uniquely identify a tuple.Example:In the Student table, if both Reg_No and Email are unique, then both are candidate keys.We choose one of them as the Primary Key.10. Composite KeyA key formed by combining two or more attributes.
• 4.
  Database Management Systems(DBMS) Unit-II BCA Semester III4Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaExample:In a Course Registration table:Student_ID Course_ID Grade• No single column uniquely identifies a record.• But (Student_ID, Course_ID) together form a Composite Key.11. Null ValuesNull means value is unknown, missing, or not applicable.Example:If a student has not provided an email ID, the Email field may be NULL.Characteristics of RelationsRelational tables follow certain rules:1. Rows are Unique: No two rows (tuples) are identical.2. Columns have Unique Names: Every attribute has a distinct name.3. Order of Rows Does Not Matter: Rows can appear in any order; it does not affectthe relation.4. Order of Columns Does Not Matter: Even if the column order changes, themeaning of the table remains same.5. Values are Atomic (Single-valued): Each cell must contain one value only, notmultiple.Wrong: Phone = {9876, 9988}✔ Correct: Store separate rows or create a separate table for phone numbers.6. Each Attribute Has a Domain: All values in a column must come from the samedomain.Example:Age column → must contain valid age numbers only.7. No Multi-valued or Composite Attributes in a Relation: These attributes must besplit before creating the table.8. NULL Values are Allowed: But only when value is unknown or not applicable.
• 5.
  Database Management Systems(DBMS) Unit-II BCA Semester III5Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaRelational Model ConstraintsConstraints are rules applied to tables to ensure accuracy, validity, and consistency of thedata stored in a database.Without constraints, incorrect or meaningless data may enter the system.The main relational model constraints are:1. Domain Constraints2. Key Constraints3. Integrity Constraintso Entity Integrityo Referential Integrity1. Domain ConstraintsA domain constraint defines the set of valid values that an attribute (column) can take.Examples:• Age must be between 18 and 60• Gender must be only 'M' or 'F'• Email must follow proper format• Salary must be a positive numberExample Table: EmployeeEmp_ID Name Age Salary101 Arjun 25 30000102 Reema 17 40000 (Invalid – age < 18)103 Neha -5 25000 (Invalid – negative age)Here,• Age < 18 violates the domain constraint• Negative age violates the domain constraintPurpose: Ensures all data values fall within the permitted range or format.
• 6.
  Database Management Systems(DBMS) Unit-II BCA Semester III6Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bca2. Key ConstraintsA key constraint ensures that a key attribute (primary key or candidate key) uniquelyidentifies each row in a table.Requirements of a Primary Key:• Must be unique• Must be NOT NULL• Cannot change frequentlyExample:Roll_No (PK) Name Branch1 Raj CS2 Priya IT2 Manoj CS (Duplicate Roll_No – violates key constraint)Here,• Roll_No must be unique• Duplicate value is not allowedPurpose: Prevents duplicate or missing primary key values.3. Integrity ConstraintsIntegrity constraints ensure the correctness, consistency, and reliability of the data stored.There are two important types:a) Entity Integrity ConstraintEntity integrity ensures that the primary key cannot be NULL.Example:Emp_ID (PK) Name1 ArjunNULL Reema (Invalid – primary key cannot be NULL)b) Referential Integrity ConstraintReferential integrity ensures that a foreign key must match an existing primary key inanother table, or be NULL.It ensures relationships between tables remain valid.
• 7.
  Database Management Systems(DBMS) Unit-II BCA Semester III7Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaExample:Department TableDept_ID (PK) Dept_Name10 CS20 ITEmployee TableEmp_ID Name Dept_ID (FK)1 Arjun 102 Priya 203 Neha 30 (Invalid – Dept_ID 30 does not exist)Here,• Dept_ID 30 is invalid because the Department table does not contain 30.• This violates referential integrity.Purpose: Prevents orphan records and maintains valid links between tables.SummaryConstraint Type Meaning Example of ViolationDomain Attribute values must be valid Age = -5Key Primary key must be unique & notnullDuplicate Roll_NoEntity Integrity Primary key cannot be null Emp_ID = NULLReferentialIntegrityFK must refer to existing PK Dept_ID = 30 when only 10,20 existRelational Algebra• Relational Algebra is a procedural query language used to operate on relations(tables) in a database.• It uses mathematical operations to retrieve data, combine tables, and filter records.• Each operation takes one or more relations as input and produces a new relation asoutput.
• 8.
  Database Management Systems(DBMS) Unit-II BCA Semester III8Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaBasic Relational Algebra Operations1. UNION ( ∪ )Union returns all tuples from both relations without duplicates.It is similar to combining two tables.Conditions:• Both relations must have same number of attributes• Attributes must have the same domain (same type and meaning)Example:2. INTERSECTION ( ∩ )Intersection returns the tuples that are common to both relations.Example:
• 9.
  Database Management Systems(DBMS) Unit-II BCA Semester III9Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bca3. SELECTION ( σ )Selection chooses specific rows from a relation based on a condition.Symbol: σ(condition)(Relation)Example:StudentName AgeAsha 20Ravi 18John 22σ(Age > 18)(Student) → Select students older than 18Name AgeAsha 20John 224. PROJECTION ( π )Projection selects specific columns from a table.Symbol: π(column1, column2,...)(Relation)Example:StudentRoll Name Age1 Asha 202 Ravi 18π(Name, Age)(Student)Name AgeAsha 20Ravi 185. CARTESIAN PRODUCT ( × )Cartesian Product combines every row of one relation with every row of another relation.Also called Cross Product.If R has m rows and S has n rows, output has m × n rows.Example:
• 10.
  Database Management Systems(DBMS) Unit-II BCA Semester III10Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaJOIN OperationsJoin is used to combine related tuples from two relations based on a common attribute.1. INNER JOINInner join returns only the matching rows from both tables.Example:2. EQUI JOINEqui join is a join that uses the equality (=) condition only.Example join condition:Employee.Dept_ID = Department.Dept_IDEqui join is a special case of inner join.Example:3. OUTER JOINOuter join returns:• Matching rows• PLUS non-matching rows from one or both tables
• 11.
  Database Management Systems(DBMS) Unit-II BCA Semester III11Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaThere are 3 types:a) LEFT OUTER JOINReturns all rows from left table and matching rows from right table.Example:EmpID Name DeptID1 Asha 102 Ravi NULL3 Neha NULLRavi and Neha have no department → NULL.b) RIGHT OUTER JOINReturns all rows from right table and matching rows from left.Example:EmpID Name DeptID1 Asha 102 Ravi NULLRight table (EmployeeDept) has only EmpID 1 and 2.Neha is missing because RIGHT JOIN keeps right table rows only.c) FULL OUTER JOINReturns:• All matching rows• All non-matching rows from both tablesWhere no match exists, NULL is inserted.EmpID Name DeptID1 Asha 102 Ravi NULL3 Neha NULLFull outer join includes:✔ Matching (Asha–10)✔ Non-matching from left (Neha)✔ Non-matching from right (none here)
• 12.
  Database Management Systems(DBMS) Unit-II BCA Semester III12Notes by Dr. Chandrakantha T S, Vagdevi College of BCA, Melinakuruvalli, Thirthahalli-577 432For more notes and resources, visit: https://sites.google.com/view/chandrakanthats/bcaSummary: Relational Algebra & Join OperationsOperation Purpose (Simple Meaning) ExampleUnion Combines rows from two tableswithout duplicatesStudents from Class A ∪ Class BIntersection Shows only common rows from bothtablesStudents common in Class A ∩ Class BSelection (σ) Filters rows based on a condition σ Course = 'BCA'Projection (π) Selects specific columns only π (Name, Course)CartesianProduct (×)Combines all rows from two tables(pairing each row)Students × SubjectsInner Join Returns only matching rows fromboth tablesMatching Employee–Department IDsEqui Join Inner join using equality (=) conditiononlyEmp.DeptID = Dept.DeptIDOuter Join Matching rows plus non-matchingrows with NULLsEmployees with or without departments*** ** ***