Unit I - Foundations of Data Management and ER Model

UpLearning
Database Management System
 UpLearn ng

Disclamer

Notes are prepared by Uplearning for educational support only.


These are not official university/board materials.


Content is for personal use only — no reposting, reselling, or modification.


Accuracy is not guaranteed; refer to official syllabus & textbooks.


Uplearning is not responsible for academic results or losses.


By using this content, you agree to all rules & terms.

SPPU SECOND YEAR ( 2024 PATTERN )

Join Group
Visit Website
1.1 INTRODUCTION TO DATABASE MANAGEMENT SYSTEM

1.1.1 What is data?

• Data is raw facts and figures that have no meaning by themselves.
• Data can be in the form of numbers, text, images, audio, or video.
• Example: "25" is data, but without context it has no meaning.
1.1.2 What is Information?
• Information is processed data that has meaning and context.
• Data becomes information when it is organized and interpreted.
• Example: "Age = 25 years" is information because it provides context.
1.1.3 What is a Database?
• A database is an organized collection of interrelated data stored electronically.
• Data is stored in a structured format for easy access, management, and updating.
• Databases eliminate data redundancy and maintain data consistency.
• Example: Student database containing student records, marks, attendance, etc.
1.1.4 What is Database Management System (DBMS)?
• DBMS is software that enables users to create, manage, and manipulate databases.
• It acts as an interface between the database and end users or application programs.
• DBMS provides tools for data definition, data manipulation, and data control.
• Examples: MySQL, Oracle, PostgreSQL, Microsoft SQL Server, MongoDB.

1.1.5 Traditional File System vs DBMS

Traditional File System:
• Data is stored in separate files for each application.
• Each file is independent and managed by the operating system.
• Leads to data redundancy, inconsistency, and difficulty in accessing data.
Problems with File System:
• Data Redundancy: Same data is stored in multiple files, wasting storage space.
• Data Inconsistency: Different files may have different values for the same data.
• Difficulty in Accessing Data: No standard way to retrieve data; requires custom
programs.
• Data Isolation: Data scattered across multiple files makes it hard to access together.
• Integrity Problems: Difficult to enforce constraints and validation rules.
• Atomicity Problems: Cannot guarantee all-or-nothing execution of operations.
• Concurrent Access Anomalies: Multiple users cannot access data simultaneously
without conflicts.
• Security Problems: Difficult to provide different access levels to different users.
DBMS Advantages:
• Eliminates data redundancy and inconsistency.
• Provides centralized data management and control.
• Ensures data integrity through constraints and validation.
1.2 PURPOSE OF DBMS
1.2.1 Main Purposes of DBMS
• Data Storage and Retrieval: Efficiently store large volumes of data and retrieve it
quickly.
• Data Sharing: Allow multiple users and applications to access the same data
simultaneously.
• Data Security: Protect data from unauthorized access through authentication and
authorization.
• Data Integrity: Ensure accuracy and consistency of data through constraints.
• Data Independence: Separate data description from application programs.
• Backup and Recovery: Provide mechanisms to recover data in case of system
failures.
• Concurrency Control: Manage simultaneous data access without conflicts.
• Data Abstraction: Hide complexity and provide simplified views to users.

1.3 DATABASE APPLICATIONS

1.3.1 Common Database Applications

• Banking Systems: Managing customer accounts, transactions, loans, and credit
cards.
• Railway Reservation Systems: Handling ticket bookings, seat availability, and
scheduling.
• University Management: Storing student records, course registration, grades, and
faculty information.
• E-commerce Platforms: Managing product catalogs, customer orders, inventory,
and payments.
• Hospital Management: Maintaining patient records, appointments, medical history,
and billing.
• Airline Reservation Systems: Managing flight schedules, bookings, and passenger
information.
• Telecommunication: Storing call records, customer data, and billing information.
• Human Resource Management: Managing employee details, payroll, attendance,
and performance.
• Social Media Platforms: Storing user profiles, posts, comments, and connections.
• Library Management: Tracking books, members, issue/return records, and fines.
1.4 DATA MODELS
1.4.1 What is a Data Model?
• A data model is a collection of concepts and tools for describing data, relationships,
semantics, and constraints.
• It provides a way to structure and organize data in a database.
• Data models help in designing databases and understanding data requirements.

1.4.2 Types of Data Models

[Link] Hierarchical Data Model

• Data is organized in a tree-like structure with parent-child relationships.
• Each record has one parent and can have multiple children.
• Accessing data requires traversing from root to desired record.
• Limitation: Cannot represent many-to-many relationships easily.
• Example: Organization structure with CEO → Managers → Employees.
[Link] Network Data Model
• Extension of hierarchical model that allows multiple parent-child relationships.
• Records are organized as a graph structure with nodes and edges.
• A child record can have multiple parent records.
• Limitation: Complex to design and maintain.
• Example: Student enrolled in multiple courses, each course taught by multiple
teachers.
[Link] Relational Data Model
• Data is organized in tables (relations) consisting of rows and columns.
• Each table represents an entity, and rows represent records.
• Relationships are established through common attributes (foreign keys).
• Advantages: Simple structure, data independence, easy to understand and use.
• Most widely used data model in modern DBMS.
• Example: Student table, Course table, Enrollment table with relationships.
[Link] Entity-Relationship (ER) Model
• High-level conceptual data model used for database design.
• Represents data as entities, attributes, and relationships.
• Provides graphical representation through ER diagrams.
• Used as a design tool before converting to relational model.
[Link] NoSQL Data Models
• Modern data models for handling unstructured and semi-structured data.
• Types include: Document-based, Key-value, Column-family, Graph databases.
• Designed for scalability, flexibility, and handling big data.
• Example: MongoDB (document), Redis (key-value), Neo4j (graph).
1.5 DBMS ARCHITECTURE
1.5.1 Three-Level DBMS Architecture (ANSI-SPARC Architecture)
The three-level architecture provides data abstraction and data independence.
[Link] External Level (View Level)
• Highest level of abstraction closest to the users.
• Describes how data is viewed by individual users or user groups.
• Multiple external views can exist for the same database.
• Each view shows only the relevant data for specific users.
• Provides data security by hiding unnecessary information.
• Example: Students see only their own records; faculty see all student records.
[Link] Conceptual Level (Logical Level)
• Middle level that describes what data is stored in the database.
• Represents the entire database structure for all users.
• Describes entities, attributes, relationships, and constraints.
• Independent of physical storage details.
• Designed by database administrators (DBAs).
• Example: Complete database schema showing all tables and relationships.
[Link] Internal Level (Physical Level)
• Lowest level of abstraction closest to physical storage.
• Describes how data is physically stored on storage devices.
• Deals with data storage structures, indexing, and access methods.
• Concerned with storage allocation, compression, and encryption.
• Example: B-tree indexing, file organization, buffer management.
1.5.2 Data Independence
[Link] Logical Data Independence
• Ability to change the conceptual schema without changing external schemas or
application programs.
• Users are not affected by changes in the logical structure of the database.
• Example: Adding a new attribute to a table does not affect existing views.
[Link] Physical Data Independence
• Ability to change the internal schema without changing the conceptual schema.
• Changes in physical storage do not affect the logical structure.
• Example: Changing from one indexing method to another does not affect users.
1.6 DBMS LANGUAGES
1.6.1 Data Definition Language (DDL)
• Used to define and modify database structure and schema.
• DDL commands specify data types, structures, and constraints.
• Changes made using DDL are permanent.
• Common DDL Commands:
o CREATE: Creates database objects like tables, indexes, views.
o ALTER: Modifies existing database structure (add/delete columns).
o DROP: Deletes database objects permanently.
o TRUNCATE: Removes all records from a table but keeps the structure.
o RENAME: Renames database objects.
• Example: CREATE TABLE Student (RollNo INT, Name VARCHAR(50));

1.6.2 Data Manipulation Language (DML)

• Used to manipulate and process data in the database.
• DML commands retrieve, insert, update, and delete data.
• Changes can be rolled back (undone) before committing.
• Common DML Commands:
o SELECT: Retrieves data from one or more tables.
o INSERT: Adds new records to a table.
o UPDATE: Modifies existing records in a table.
o DELETE: Removes records from a table.
• Example: INSERT INTO Student VALUES (101, 'Raj Kumar');

1.6.3 Data Control Language (DCL)

• Used to control access rights and permissions on database objects.
• DCL commands manage user privileges and security.
• Common DCL Commands:
o GRANT: Provides specific privileges to users.
o REVOKE: Removes previously granted privileges from users.
• Example: GRANT SELECT ON Student TO User1;

1.6.4 Transaction Control Language (TCL)

• Used to manage transactions in the database.
• TCL commands control changes made by DML statements.
• Common TCL Commands:
o COMMIT: Saves all changes made in the current transaction permanently.
o ROLLBACK: Undoes all changes made in the current transaction.
o SAVEPOINT: Creates a point within a transaction to which you can rollback.
• Example: COMMIT; (saves all pending changes)
1.7 DATABASE DESIGN AND ER MODEL
1.7.1 What is Database Design?
• Database design is the process of organizing data according to a database model.
• It involves defining entities, attributes, relationships, and constraints.
• Good database design minimizes redundancy and ensures data integrity.
• Database design follows a systematic process from requirements to implementation.
1.7.2 Database Design Process
• Requirement Analysis: Gather and analyze user requirements and data needs.
• Conceptual Design: Create high-level ER diagram representing entities and
relationships.
• Logical Design: Convert ER diagram to relational schema (tables).
• Physical Design: Implement the database on a specific DBMS with optimization.
• Implementation: Create actual database, tables, and load data.
• Testing and Refinement: Validate design and optimize for performance.
1.7.3 Entity-Relationship (ER) Model
• The ER model is a high-level conceptual data model for database design.
• It uses graphical representation (ER diagrams) to visualize database structure.
• ER model consists of entities, attributes, and relationships.
• Widely used for database design before converting to relational tables.
1.8 ENTITIES
1.8.1 What is an Entity?
• An entity is a real-world object or thing that has independent existence.
• Entities can be physical (tangible) or conceptual (abstract).
• Each entity has properties called attributes that describe it.
• Example: Student, Employee, Course, Department are entities.

1.8.2 Entity Types vs Entity Instances

• Entity Type: A collection of entities that share common properties.
• Entity Instance (Entity Set): A specific occurrence of an entity type.
• Example: "Student" is an entity type; "Ravi with RollNo 101" is an entity instance.

1.8.3 Types of Entities

[Link] Strong Entity
• An entity that has its own independent existence.
• Can be uniquely identified by its own attributes (primary key).
• Represented by a single rectangle in ER diagram.
• Example: Student entity with RollNo as primary key.
[Link] Weak Entity
• An entity that cannot exist independently without another entity.
• Does not have sufficient attributes to form a primary key.
• Depends on a strong entity (called owner entity) for identification.
• Uses a partial key combined with the owner's key for unique identification.
• Represented by a double rectangle in ER diagram.
• The relationship between weak and strong entity is represented by double diamond.
• Example: Dependent entity depends on
Employee entity; dependent's name
alone cannot uniquely identify them.
1.9 ATTRIBUTES
1.9.1 What is an Attribute?
• An attribute is a property or characteristic that describes an entity.
• Each entity type has a set of attributes associated with it.
• Attributes have values from a specific domain (data type).
• Example: Student entity has attributes like RollNo, Name, Age, Address.

1.9.2 Types of Attributes

[Link] Simple Attribute
• An attribute that cannot be divided into smaller parts.
• Also called atomic attribute.
• Represented by an oval in ER diagram.
• Example: Age, RollNo, Gender are simple attributes.
[Link] Composite Attribute
• An attribute that can be divided into smaller sub-parts.
• Each sub-part represents a basic attribute with independent meaning.
• Represented by an oval with connected sub-ovals in ER diagram.
• Example: Name (FirstName, MiddleName, LastName), Address (Street, City, State,
Pincode).
[Link] Single-Valued Attribute
• An attribute that has a single value for each entity instance.
• Most attributes are single-valued.
• Example: RollNo, DateOfBirth, Age for a student.
[Link] Multi-Valued Attribute
• An attribute that can have multiple values for a single entity instance.
• Represented by a double oval in ER diagram.
• Example: PhoneNumber (a person can have multiple phone numbers), Email (multiple
email addresses).
[Link] Derived Attribute
• An attribute whose value can be calculated or derived from other attributes.
• Not physically stored in the database; computed when needed.
• Represented by a dashed oval in ER diagram.
• Example: Age (derived from DateOfBirth), TotalMarks (derived from subject marks).
[Link] Stored Attribute
• An attribute whose value is physically stored in the database.
• Used as base data to derive other attributes.
• Example: DateOfBirth is a stored attribute used to derive Age.
[Link] Complex Attribute
• Combination of composite and multi-valued attributes.
• Example: Address can be multi-valued and each address is composite.
1.10 RELATIONSHIPS
1.10.1 What is a Relationship?
• A relationship is an association or connection between two or more entities.
• Relationships represent how entities interact with each other.
• Represented by a diamond shape in ER diagram.
• Example: Student "enrolls in" Course; Employee "works for" Department.

1.10.2 Degree of Relationship

[Link] Unary Relationship (Recursive)
• A relationship between instances of the same entity type.
• Degree is 1 (one entity type involved).
• Example: Employee "manages" Employee; Person "is
married to" Person.
[Link] Binary Relationship
• A relationship between instances of two different entity
types.
• Degree is 2 (most common type).
• Example: Student "enrolls in" Course; Employee "works in"
Department.
[Link] Ternary Relationship
• A relationship among instances of three different entity
types.
• Degree is 3.
• Example: Supplier "supplies" Part "to" Project.
1.10.3 Cardinality of Relationship
Cardinality specifies the number of entity instances that can be associated through a
relationship.
[Link] One-to-One (1:1)
• One instance of entity A is associated with at most one instance of entity B, and vice
versa.
• Example: Person "has" Passport (one person has one passport, one passport belongs
to one person).
• Example: Employee "manages" Department (one employee manages one
department, one department is managed by one employee).
[Link] One-to-Many (1:N)
• One instance of entity A is associated with multiple instances of entity B, but each
instance of B is associated with at most one instance of A.
• Example: Department "has" Employees (one department has many employees, but
each employee belongs to one department).
• Example: Customer "places" Orders (one customer places many orders, but each
order is placed by one customer).
[Link] Many-to-One (N:1)
• Multiple instances of entity A are associated with one instance of entity B.
• Reverse of one-to-many relationship.
• Example: Employees "work in" Department (many employees work in one
department).
[Link] Many-to-Many (M:N)
• Multiple instances of entity A are associated with multiple instances of entity B, and
vice versa.
• Example: Student "enrolls in" Courses (one student enrolls in many courses, one
course has many students).
• Example: Author "writes" Books (one author writes many books, one book can have
many authors).
1.10.4 Participation Constraints
[Link] Total Participation (Mandatory)
• Every entity instance must participate in the relationship.
• Represented by a double line connecting entity to relationship in ER diagram.
• Example: Every Employee must work in a Department (total participation of Employee
in "works in" relationship).
[Link] Partial Participation (Optional)
• Some entity instances may not participate in the relationship.
• Represented by a single line connecting entity to relationship.
• Example: Not all Employees manage a Department (partial participation in "manages"
relationship).
1.11 KEYS IN DATABASE
1.11.1 What is a Key?
• A key is an attribute or set of attributes used to uniquely identify records in a table.
• Keys ensure data integrity and establish relationships between tables.
• Keys are essential for database design and querying.

1.11.2 Types of Keys

[Link] Super Key
• A super key is a set of one or more attributes that can
uniquely identify each tuple in a relation.
• It may contain additional attributes that are not
necessary for unique identification.
• Example: In Student(Roll_No, Name, Email, Phone),
super keys are:
o {Roll_No}
o {Roll_No, Name}
o {Roll_No, Email}
o {Roll_No, Name, Email} etc.
[Link] Candidate Key
• A candidate key is a minimal super key (no proper subset
is a super key).
• It contains only essential attributes required for unique
identification, no redundant attributes.
• A relation can have multiple candidate keys.
• Example: In Student(Roll_No, Name, Email, Phone),
candidate keys are:
o {Roll_No}
o {Email}
[Link] Primary Key
• Primary key is the candidate key chosen by the
database designer to uniquely identify tuples.
• Each relation must have exactly one primary key.
• Primary key values must be unique and cannot be
NULL.
• Example: In Student table, Roll_No is chosen as
primary key.
• Representation: Underlined attribute in relational schema: Student(Roll_No, Name,
Email).
[Link] Alternate Key
• Alternate keys are candidate keys that are not chosen
as the primary key.
• They can also uniquely identify tuples but are not
designated as primary.
• Example: In Student(Roll_No, Name, Email), if
Roll_No is primary key, then Email is alternate key.
[Link] Foreign Key
• A foreign key is an attribute (or set of attributes) in one relation
that refers to the primary key of another relation.
• It establishes referential integrity and creates relationships
between tables.
• Foreign key values must either match a primary key value in the
referenced table or be NULL.
• Example: In Enrollment(Roll_No, Course_ID, Grade), Roll_No is
foreign key referencing Student(Roll_No), and Course_ID is foreign
key referencing Course(Course_ID).

[Link] Composite Key

• A composite key is a key that consists of two or more attributes to uniquely identify
tuples.
• No single attribute alone can uniquely identify records.
• Example: In Enrollment(Roll_No, Course_ID, Grade), the combination {Roll_No,
Course_ID} is composite primary key.
[Link] Surrogate Key
• A surrogate key is an artificially generated unique identifier (usually an auto-
incrementing number).
• It has no business meaning and is used purely for identification purposes.
• Example: Auto-generated ID column in tables, like Student_ID (1, 2, 3, ...).
1.12 CONSTRAINTS IN DATABASE
1.12.1 What are Constraints?
• Constraints are rules enforced on data to ensure accuracy, consistency, and integrity.
• Constraints prevent invalid data from being entered into the database.
• Defined during table creation and enforced by DBMS.

1.12.2 Types of Constraints

[Link] Domain Constraint
• Specifies the permissible values for an attribute (data type and range).
• Ensures that attribute values come from a defined domain.
• Example: Age must be an integer between 0 and 120; Gender must be 'M' or 'F'.
[Link] Key Constraint (Uniqueness Constraint)
• Ensures that primary key values are unique and not null.
• No two records can have the same primary key value.
• Automatically enforced on primary keys.
[Link] Entity Integrity Constraint
• Ensures that the primary key attribute cannot have null values.
• Every record must be uniquely identifiable.
• Fundamental rule for relational databases.
[Link] Referential Integrity Constraint
• Ensures that foreign key values must match primary key values in the referenced
table or be null.
• Maintains consistency between related tables.
• Prevents orphan records (records with invalid foreign keys).
• Example: DeptID in Employee table must exist in Department table.
[Link] NOT NULL Constraint
• Ensures that an attribute cannot have null (empty) values.
• Forces users to provide a value for that attribute.
• Example: Name attribute cannot be left empty.
[Link] UNIQUE Constraint
• Ensures that all values in an attribute are distinct (no duplicates).
• Allows one null value (unlike primary key).
• Example: Email must be unique for each student.
[Link] CHECK Constraint
• Ensures that values in an attribute satisfy a specific condition.
• Example: Age > 18; Salary > 0; Grade IN ('A', 'B', 'C').
[Link] DEFAULT Constraint
• Assigns a default value to an attribute if no value is provided.
• Example: Country = 'India' by default; Status = 'Active' by default.
1.13 ER DIAGRAMS
1.13.1 What is an ER Diagram?
• An ER diagram is a graphical representation of entities, attributes, and relationships.
• Provides a visual blueprint of the database structure.
• Used in the conceptual design phase before creating actual tables.
• Makes database design easier to understand and communicate.
1.13.2 ER Diagram Symbols
Basic Components:
• Rectangle: Represents an entity type.
• Double Rectangle: Represents a weak entity type.
• Oval: Represents a simple attribute.
• Double Oval: Represents a multi-valued attribute.
• Dashed Oval: Represents a derived attribute.
• Composite Attribute: Oval connected to sub-ovals.
• Diamond: Represents a relationship.
• Double Diamond: Represents identifying relationship for weak entity.
• Line: Connects entities to relationships and attributes to entities.
• Double Line: Represents total participation.
• Single Line: Represents partial participation.
• Underline: Indicates primary key attribute.
• Dashed Underline: Indicates partial key in weak entity.
Cardinality Notation:
• 1 (One): Written near entity side in relationship.
• N or M (Many): Written near entity side in relationship.
• 1:1, 1:N, M:N: Cardinality ratios.
1.13.3 Design Process for ER Diagrams
• Identify all entities from requirements.
• Identify attributes for each entity and mark primary keys.
• Identify relationships between entities.
• Determine cardinality and participation constraints.
• Identify weak entities and their dependencies.
• Draw the complete ER diagram with all components.
• Validate the design with stakeholders.
1.14 DESIGN ISSUES IN ER MODELING
1.14.1 Entity vs Attribute
• Issue: Deciding whether a concept should be an entity or an attribute.
• Guideline: If a concept has its own attributes or relationships, make it an entity.
• Guideline: If a concept is a simple property with no further structure, make it an
attribute.
• Example: "Address" can be an attribute (simple) or an entity (if it has relationships
with other entities).
1.14.2 Entity vs Relationship
• Issue: Deciding whether a concept should be an entity or a relationship.
• Guideline: If a concept connects two or more entities, make it a relationship.
• Guideline: If a concept has its own attributes and relationships, make it an entity.
• Example: "Enrollment" can be a relationship between Student and Course, or an entity
if it has attributes like EnrollmentDate, Grade.
1.14.3 Binary vs Ternary Relationship
• Issue: Whether to use a ternary relationship or break it into multiple binary
relationships.
• Guideline: Use ternary relationships when the relationship inherently involves three
entities.
• Guideline: Breaking into binary relationships may lose semantic meaning.
• Example: Supplier supplies Part to Project is best as ternary; breaking it may not
capture the complete meaning.
1.14.4 Redundant Relationships
• Issue: Avoiding unnecessary duplicate relationships that can be derived.
• Guideline: Remove relationships that can be inferred from other relationships.
• Example: If Student → Department and Department → Building exist, then direct
Student → Building is redundant.
1.14.5 Naming Conventions
• Use clear, meaningful names for entities, attributes, and relationships.
• Use consistent naming style (singular for entities, verb phrases for relationships).
• Avoid abbreviations unless universally understood.
1.15 EXTENDED ER FEATURES
1.15.1 Why Extended ER Model?
• Basic ER model has limitations in representing complex real-world scenarios.
• Extended ER (EER) model adds more semantic concepts for better modeling.
• EER features include specialization, generalization, aggregation, and inheritance.
1.15.2 Specialization
[Link] Concept
• Specialization is the process of defining subgroups within an entity type.
• A higher-level entity (superclass) is divided into lower-level entities (subclasses).
• Subclasses inherit all attributes of the superclass.
• Subclasses have additional specific attributes.
• Represented by a triangle or circle labeled "ISA" (is-a relationship) in EER diagram.
• Top-down design approach.
[Link] Example
• Employee entity can be specialized into Manager, Engineer, and Clerk.
• All have common attributes (EmployeeID, Name, Salary).
• Manager has additional attribute: ManagementLevel.
• Engineer has additional attribute: Specialization.
• Clerk has additional attribute: TypingSpeed.

[Link] Types of Specialization

• Disjoint Specialization: An entity can belong to only one subclass (mutually
exclusive).
o Represented by "d" in the diagram.
o Example: Employee is either Manager OR Engineer OR Clerk (not multiple).
• Overlapping Specialization: An entity can belong to multiple subclasses.
o Represented by "o" in the diagram.
o Example: Student can be both Undergraduate AND Athlete.
[Link] Participation in Specialization
• Total Specialization: Every entity in the superclass must belong to at least one
subclass.
o Represented by double line.
o Example: Every Employee must be Manager or Engineer or Clerk.
• Partial Specialization: An entity in the superclass may or may not belong to any
subclass.
o Represented by single line.
o Example: Not all Persons are Employees.

1.15.3 Generalization
[Link] Concept
• Generalization is the reverse process of specialization.
• Multiple lower-level entities (subclasses) are combined to form a higher-level entity
(superclass).
• Common attributes are extracted to form the generalized entity.
• Bottom-up design approach.
[Link] Example
• Car, Truck, and Motorcycle entities share common attributes: VehicleID, Color, Model.
• These can be generalized into a Vehicle entity.
• Specific attributes remain with subclasses (Car has NumDoors; Truck has
CargoCapacity).

1.15.4 Aggregation
[Link] Concept
• Aggregation is a feature that allows a relationship to be treated as an entity.
• Used when a relationship needs to participate in another relationship.
• Represented by enclosing the relationship and connected entities in a rectangle.
[Link] Example
• Employee works on Project (relationship).
• This relationship needs to be monitored by Manager.
• "Works on" relationship is aggregated and connected to Manager entity.
• Allows representing: Manager monitors (Employee works on Project).
[Link] When to Use
• When a relationship itself has relationships with other entities.
• When a relationship has its own attributes that need to be associated with another
entity.
1.15.5 Inheritance
• Subclasses inherit all attributes and relationships of the superclass.
• Reduces redundancy by avoiding repetition of common attributes.
• Subclasses can have additional attributes and relationships specific to them.
• Example: Manager inherits EmployeeID, Name from Employee and adds
ManagementLevel.
1.16 CONVERTING ER/EER DIAGRAMS TO RELATIONAL TABLES
1.16.1 Why Conversion is Needed?
• ER diagrams are conceptual models used for design.
• Relational databases store data in tables (relations).
• ER diagrams must be converted to relational schema for implementation.
• Conversion follows systematic rules to preserve all information and constraints.
1.16.2 Conversion Rules
[Link] Rule 1: Strong Entity to Table
• Create a separate table for each strong entity.
• Include all simple attributes as columns.
• For composite attributes, include only the component attributes (leaf attributes).
• For multi-valued attributes, create a separate table (explained in Rule 5).
• Derived attributes are generally not included (computed when needed).
• Primary key of the entity becomes the primary key of the table.
Example:
• Entity: Student (RollNo, Name, DateOfBirth, Address(Street, City))
• Table: Student (RollNo PK, Name, DateOfBirth, Street, City)
[Link] Rule 2: Weak Entity to Table
• Create a separate table for the weak entity.
• Include all attributes of the weak entity.
• Include the primary key of the owner (strong) entity as a foreign key.
• Primary key of the weak entity table = Partial key + Foreign key (from owner).
• Foreign key references the owner entity's primary key.
Example:
• Weak Entity: Dependent (DependentName, Age) depends on Employee (EmployeeID).
• Table: Dependent (EmployeeID FK, DependentName, Age, PK: {EmployeeID,
DependentName})
[Link] Rule 3: Binary 1:1 Relationship to Table
Approach 1: Foreign Key Approach (Preferred)
• Choose one of the participating entity tables.
• Add the primary key of the other entity as a foreign key.
• Add relationship attributes (if any) to the same table.
• Guideline: Include foreign key in the table with total participation.
Approach 2: Separate Table (Less Common)
• Create a new table for the relationship.
• Include primary keys of both entities as foreign keys.
• Add relationship attributes (if any).
• Choose one of the foreign keys as primary key.
Example:
• Employee (1:1) manages Department.
• Approach 1: Add DeptID FK in Employee table OR add EmployeeID FK in Department
table.
[Link] Rule 4: Binary 1:N Relationship to Table
• Add the primary key of the "1" side entity to the "N" side entity table as a foreign key.
• Add relationship attributes (if any) to the "N" side table.
• No separate table is created for 1:N relationships.
Example:
• Department (1:N) has Employees.
• Add DeptID FK to Employee table.
• Table: Employee (EmployeeID PK, Name, Salary, DeptID FK)
[Link] Rule 5: Binary M:N Relationship to Table
• Create a new separate table for the relationship.
• Include primary keys of both participating entities as foreign keys.
• Primary key of the relationship table = Combination of both foreign keys (composite
key).
• Add relationship attributes (if any) to this table.
Example:
• Student (M:N) enrolls in Course.
• Create Enrollment table.
• Table: Enrollment (StudentID FK, CourseID FK, EnrollmentDate, PK: {StudentID,
CourseID})
[Link] Rule 6: Multi-valued Attribute to Table
• Create a separate table for the multi-valued attribute.
• Include the primary key of the entity as a foreign key.
• Include the multi-valued attribute as a column.
• Primary key = Foreign key + Multi-valued attribute.
Example:
• Student has multi-valued attribute PhoneNumber.
• Create table: StudentPhone (StudentID FK, PhoneNumber, PK: {StudentID,
PhoneNumber})
[Link] Rule 7: N-ary Relationship to Table (Ternary, Quaternary, etc.)
• Create a new table for the n-ary relationship.
• Include primary keys of all participating entities as foreign keys.
• Primary key = Combination of all foreign keys.
• Add relationship attributes (if any).
Example:
• Supplier (M:N:P) supplies Part to Project.
• Table: Supply (SupplierID FK, PartID FK, ProjectID FK, Quantity, PK: {SupplierID, PartID,
ProjectID})
1.16.3 Converting EER Features
[Link] Specialization/Generalization to Tables
Approach 1: Single Table for Entire Hierarchy
• Create one table for the superclass.
• Include all attributes from superclass and all subclasses.
• Add a discriminator attribute to identify the subclass type.
• Disadvantage: Many null values if specialization is disjoint.
Example:
• Employee (EmployeeID, Name, Salary, Type, ManagementLevel, Specialization,
TypingSpeed)
• Type attribute indicates Manager/Engineer/Clerk.
Approach 2: Separate Tables for Each Subclass
• Create separate tables for each subclass only.
• Each subclass table includes all attributes (inherited + specific).
• No separate table for superclass.
• Disadvantage: Redundancy of common attributes across tables.
Example:
• Manager (EmployeeID PK, Name, Salary, ManagementLevel)
• Engineer (EmployeeID PK, Name, Salary, Specialization)
• Clerk (EmployeeID PK, Name, Salary, TypingSpeed)
Approach 3: Tables for Superclass and Subclasses (Preferred)
• Create a table for the superclass with common attributes.
• Create separate tables for each subclass with specific attributes only.
• Primary key of superclass is used as primary key and foreign key in subclass tables.
• Most normalized approach.
Example:
• Employee (EmployeeID PK, Name, Salary)
• Manager (EmployeeID PK/FK, ManagementLevel)
• Engineer (EmployeeID PK/FK, Specialization)
• Clerk (EmployeeID PK/FK, TypingSpeed)
[Link] Aggregation to Tables
• Treat the aggregated relationship as a separate table (as per relationship conversion
rules).
• Connect this table with other entities using foreign keys.
• Example: If "Employee works on Project" is aggregated and monitored by Manager,
create WorksOn table and Monitors table referencing it.
1.17 CASE STUDY: UNIVERSITY DATABASE SYSTEM
1.17.1 Problem Statement
Design a database for a university to manage students, programme, branch , university

1.17.2 ER Diagram Design

Entities:
• STUDENT (STD_ID, name, city, login, password, academic_record)
• PROGRAMME (Prog_ID, Prog_Name, Duration, Eligibility, Br_ID)
• BRANCH (Br_ID, Br_Name, Address, City)
• UNIVERSITY (Name, Address, Contact)
Weak Entity
• Academic Record (10th Score, 12th Score, Graduation Score)
Relationships:
• STUDENT – PROGRAMME → Applies
• STUDENT – BRANCH → Allots
• PROGRAMME – UNIVERSITY → Has
• BRANCH – UNIVERSITY → Has
1.17.3 Conversion to Relational Tables
Tables:
• STUDENT(STD_ID PK, name, city, login, password)
• ACADEMIC_RECORD(STD_ID FK, tenth_score, twelfth_score, graduation_score)
• PROGRAMME(Prog_ID PK, Prog_Name, Duration, Eligibility, Br_ID FK)
• BRANCH(Br_ID PK, Br_Name, Address, City)
 • UNIVERSITY(Uni_Name PK, Address, Contact)
• APPLIES(STD_ID FK, Prog_ID FK)
• ALLOTS(STD_ID FK, Br_ID FK)
Foreign Key Constraints:
• ACADEMIC_RECORD.STD_ID → STUDENT.STD_ID
• PROGRAMME.Br_ID → BRANCH.Br_ID
• APPLIES.STD_ID → STUDENT.STD_ID
• APPLIES.Prog_ID → PROGRAMME.Prog_ID
• ALLOTS.STD_ID → STUDENT.STD_ID
• ALLOTS.Br_ID → BRANCH.Br_ID

1.18 ADDITIONAL CASE STUDY: HOSPITAL MANAGEMENT SYSTEM

1.18.1 Problem Statement
Design a database for a hospital to manage patients, doctors, appointments, and treatments.
1.18.2 ER Diagram Design
Entities (Strong Entities)
• User (user_id, user_name, user_email, user_mobile, user_address)
• Login (login_id, login_username, user_password, login_role_id)
• Roles (role_id, role_name, role_desc)
• Permission (per_id, per_name, per_module, per_role_id)
• Doctors (doc_id, doc_name, doc_mobile, doc_email, doc_pass, doc_add)
• Hospitals (hos_id, hos_type, hos_desc, hos_place, hos_doc_id)
• Patient (pat_id, pat_name, pat_mobile, pat_email, pat_add)
• Medicines (mdcn_id, mdcn_type, mdcn_desc)
Weak Entity:
• Permission
o Depends on Roles (identified using per_role_id)
o Cannot exist without a Role
Relationships:
• User – Login – Roles → Has
• Roles – Permission → Has
• User – Hospitals – Doctors – Patient → Manage
• Hospitals – Medicines → Has

1.18.3 Conversion to Relational Tables

Tables:
• User(user_id PK, user_name, user_email, user_mobile, user_address)
• Login(login_id PK, login_username, user_password, login_role_id FK)
• Roles(role_id PK, role_name, role_desc)
• Permission(per_id PK, per_name, per_module, per_role_id FK)
• Doctors(doc_id PK, doc_name, doc_mobile, doc_email, doc_pass, doc_add)
• Hospitals(hos_id PK, hos_type, hos_desc, hos_place, hos_doc_id FK)
• Patient(pat_id PK, pat_name, pat_mobile, pat_email, pat_add)
• Medicines(mdcn_id PK, mdcn_type, mdcn_desc)
Foreign Key Constraints:
• Login.login_role_id → Roles.role_id
• Permission.per_role_id → Roles.role_id
• Hospitals.hos_doc_id → Doctors.doc_id
1.19 BEST PRACTICES FOR DATABASE DESIGN
1.19.1 Design Guidelines
• Identify all entities, attributes, and relationships clearly from requirements.
• Choose appropriate primary keys that are unique, stable, and meaningful.
• Avoid redundancy by normalizing the design (covered in Unit III).
• Use meaningful names for tables, columns, and constraints.
• Document all assumptions and design decisions.
• Validate the design with stakeholders before implementation.
1.19.2 Common Mistakes to Avoid
• Using multi-valued attributes directly in entity tables.
• Not identifying weak entities and their dependencies.
• Incorrect cardinality assignment in relationships.
• Forgetting to include foreign keys for relationships.
• Creating unnecessary tables for 1:1 and 1:N relationships.
• Not enforcing integrity constraints.
IMPORTANT SPPU EXAM QUESTIONS – UNIT I
Theory Questions (10 Marks)
1. Explain the three-level DBMS architecture (ANSI-SPARC) with a neat diagram. Discuss
the concept of data independence.
2. What is an Entity-Relationship (ER) model? Explain different types of attributes with
examples and ER diagram symbols.
3. Describe the different types of relationships in ER modeling with cardinality and
participation constraints. Support your answer with diagrams.
4. Explain the Extended ER features: Specialization, Generalization, and Aggregation
with suitable examples and diagrams.
5. Differentiate between the following with examples:
o Entity and Attribute
o Strong Entity and Weak Entity
o Simple and Composite Attribute
o Derived and Stored Attribute
6. Explain different types of keys in a database: Super key, Candidate key, Primary key,
Foreign key, and Composite key with examples.
7. What are integrity constraints in databases? Explain entity integrity, referential
integrity, and domain constraints with examples.
8. Describe the database design process. What are the major design issues in ER
modeling?
9. Explain the advantages of DBMS over traditional file systems. Why is DBMS preferred
for modern applications?
10. What are data models? Explain different types of data models: Hierarchical, Network,
Relational, and Object-Oriented.

CONVERSION QUESTIONS (10 MARKS)

11. Explain the rules for converting ER/EER diagrams to relational tables. Illustrate with
examples for 1:1, 1:N, M:N relationships.
12. How are specialization and generalization hierarchies converted to relational tables?
Explain different approaches with examples.
13. Convert the following ER diagram to relational tables with proper primary and foreign
keys: [A complex ER diagram would be given involving strong entities, weak entities,
and various relationship types]
14. Design an ER diagram for a Library Management System with entities: Book, Member,
Author, Publisher. Convert it to relational tables.
15. Design an ER diagram for an Online Shopping System with entities: Customer,
Product, Order, Payment. Show all attributes, relationships, and convert to relational
schema.
 n i e p
a r p L U in
L e U n g rn
p g n i e a
in r L
UNIT 3 – Blockchain, Cryptocurrencies & DeFi

g a p
(VERY IMPORTANT – CONCEPT + DIAGRAM BASED)

n
Course Combo Pack (notes + At Just

n r e U
MOST GUARANTEED LONG QUESTIONS

n i
1. imp
a
ExplainQ's )

Blockchain

p L
technology with its features and block structure.

₹159
r e ₹199
2. Explain cryptographic techniques used in blockchain.

a L U
3. Explain Smart Contracts and their applications.

g
UNLOCK ALL SUBJECT UNITS

p
4. Explain Decentralized Finance (DeFi) and its advantages.

g in
5. Explain cryptocurrencies
CLICK ON BUTTON TO VISTand their key features.

U
WEBSITE

in n e
 

n r L
VERY IMPORTANT MEDIUM QUESTIONS

g r a p
6. Explain types of blockchain.

in a e U
7. Explain consensus mechanisms (PoW and PoS).

Elective Subject (notes + imp At Just

e L
8. Explain applications of blockchain in finance.

9.

rnQ's
L p
)
 CBDC and its importance.

Explain

p ₹79 ₹49
g
a U n
10. Explain disruptive potential of blockchain in global finance.

e U i
 

L g n
UNLOCK ALL SUBJECT UNITS

r
VERY COMMON DIFFERENCE QUESTIONS

p n a
· Blockchain vsONTraditional database

i
CLICK BUTTON TO VIST WEBSITE

e
· Cryptocurrency vs CBDC

g rn
DeFi vs Traditional finance

n L p
g a p
· PoW vs PoS

in
·

i e
Public vs Private blockchain

n U U
r L
 

rn a p
SHORT QUESTIONS (2 Marks)

e
· Define Blockchain

L U
Define Smart Contract

g
p
· What is DeFi?
Purchase to Access Premium Content

g n r
· What is cryptocurrency?

U · What is hashing?

g in n i e a
n r
· Name any two cryptocurrenciesVERY IMPORTANT MEDIUM QUESTIONS

n r a L
6. Explain algorithmic trading and high-frequency trading.

7.

n i a e p
Explain sentiment analysis and its role in investment decisions.

e
r L U
8. Explain AI-driven portfolio management.

a L p
9. Explain role of AI in digital lending and wealth management.

e p g
10. Explain RegTech and its importance.

L U U i
UNIT 4 – AI, Machine Learning & Financial Analytics

n
g n
(SCORING + EASY THEORY)

r
MOST GUARANTEED LONG QUESTIONS

1.

in a
Explain applications of AI and ML in finance.

g n e
Explain predictive analytics in stock markets and trading

in g in a r p L
n rn L e U g
e a p in
p L U rn
U a
 FOR NOTES & UPDATES FOR NOTES & UPDATES
UpLearning
Click

Click
Want to Purchase or download Notes , vist out website!
ClickTo

Click to
to Join
To Join
Contact us on +919049988617 | contact@[Link]
Join

Join
Stay Connected &(Updated with us...!
Grou Grou

Telegram Channel for SPPU Second Year ( 2024 Pattern ) WhatsApp Group for SPPU Second Year ( 2024 Pattern )
ChanneChanne
© 2025 Uplearning(Uplearning , Maharashtra , Pune([Link]
SPPU SECOND YEAR ( 2024 PATTERN ) SPPU SECOND YEAR ( 2024 PATTERN )
SPPU SECOND YEAR ( 2024 PATTERN )
CLICK ON QR TO VIST WEBSITE
website
vist out
Want to
Purchase or
download Notes