Data Modeling

Dr. V. G. M. Jagtap
[Link]
 Abstract Data Model
Need of Design DB

● Flat file: Sequential

● Table
● Redundancy and incompleteness
● Design phase Implementation
○ Conceptual design
■ Functional requirements
■ Conceptual schema
○ Logical design
■ Relational schema Models
○ Physical design
■ Indexing
■ Data structure
Entity Relation Model Normalization Theory
Entity Relationship Model

● Overall Logical Structure

○ Entity set
○ Relationship set
■ XYZ is client to PQR Company
○ Attributes
Terminologies

● Domain D ={d|d ∈ atomic value } ; dom(A)

○ XYZ name
● Data Type or format
● Relationship R(A)
○ XYZ is client to PQR Company
● Attributes
○ Person: Age, Name
● Tuple
Entity set and Attributes
Relationships
Roles
Type of relationships
● Binary Vs Non-binary
○ One to one
○ One to many
○ Many to one
○ Many to many
● Simple and composite attributes()
● Derived Vs Stored attributes
● Single Vs Multivalued attributes{}
● Simple Vs Complex Attributes
● Null Value
● Entity type
● Entity Set
One to One
One to Many
Many to Many

Total Vs Partial Participation

Symbols
Relationships
Keys
● Integrity- identify unique tuple
● Superkey- power set
○ NULL
○ Composite key
● Candidate key- Minimal supe key
● Primary key- Unique and Not Null
○ candidate key
○ Not changed or rarely changed
● Unique key- unique and null
● Alternate key- non primary candidate key
● Foreign key - Referential integrity
● Weak Entity
Schema
Extended ER

● Reduction of ER Diagram
● Generalization
● Specialization
● Attribute Inheritance
● Aggregation
Constraints
● Domain Constraints
○ Key constraints
○ Integrity constraints (entity)
■ Valid
■ Invalid state
○ Referential integrity
● Constraints with languages
○ Trigger
○ Assertion
● Inherent model based constraints/ Implicit constraints
● Schema based constraint/ explicit constraints
● Application based constraints/ semantic constraints/ business rules
Relational Query Languages

● Types
○ Imperative- sequence of operations
○ Functional
○ Declarative
● Relational Algebra
○ Unary- selection
○ Binary- selection
Relational Algebra
● Operations
○ (=), (≠), (<), (≤), (>), (≥), (∧), (∨), (¬), (+), (-), (*), (÷), (×)
○ Select σ - σsalary>90000 (instructor)
○ Project Π - ΠID, name, salary(instructor)
○ Cartesian product x
○ Join ⋈ -σinstructor. ID = teaches. ID (instructor × teaches), r ⋈θ s = σθ(r × s), left, right full
○ Union ∪
○ Intersection ∩
○ Assignment←
○ Rename ρ
○ Insert
○ Delete
○ Update
○ Transaction
Type of joins

• INNER JOIN: Matches records from both tables

• LEFT JOIN: All left table records + matching right records
• RIGHT JOIN: All right table records + matching left records
• FULL JOIN: All records from both tables
• CROSS JOIN: Cartesian product
• SELF JOIN: Joins a table to itself
Inner join

● Returns records that have matching values in both tables.

● Syntax:SELECT columns FROM table1 INNER JOIN table2 ON
table1.common_column = table2.common_column;
● Example: Customers and Orders tables
LEFT JOIN

● Returns all records from the left table and matching records from the right
table.
● Syntax:SELECT columns FROM table1 LEFT JOIN table2 ON
table1.common_column = table2.common_column;
● Example: Employees and Departments
RIGHT JOIN

● Returns all records from the right table and matching records from the left
table.
● Syntax:SELECT columns FROM table1 RIGHT JOIN table2 ON
table1.common_column = table2.common_column;
● Example: Products and Suppliers
FULL JOIN

● Returns all records when there is a match in either left or right table.
● Syntax:SELECT columns FROM table1 FULL JOIN table2 ON
table1.common_column = table2.common_column;
● Example: Customers and Sales Data
CROSS JOIN

● Produces a Cartesian product of both tables.

● Syntax:SELECT columns FROM table1 CROSS JOIN table2;
● Example: Combining all students with all available courses
SELF JOIN

● Joins a table to itself based on a related column.

● Syntax:SELECT A.column1, B.column2 FROM table A, table B WHERE
A.common_column = B.common_column;
● Example: Employee Hierarchies
Election Database Case Study
Election Entities
Voter Political_Party

● Voter_ID ● Party_ID
● Name ● Party_Name
● Age ● Symbol
● Gender ● Leader_Name
● Address ● Founded_Year
● State
Constituency
Candidate
● Constituency_ID
● Candidate_ID ● Constituency_Name
● Name ● State
● Age ● Number_of_Voters
● Gender
Election Entities
Election Result

● Election_ID ● Result_ID
● Year ● Votes_Received
● Type (Lok Sabha / Vidhan Sabha) ● Position (Winner/Runner-up)
● State
● Election_Date Booth

Vote ● Booth_ID
● Booth_Name
● Vote_ID ● Location
● Timestamp
Election Entities Keys
Voter Political_Party

● Voter_ID (Primary Key) ● Party_ID (Primary Key)

● Name ● Party_Name
● Age ● Symbol
● Gender
● Leader_Name
● Address
● Founded_Year
● State
● Constituency_ID (Foreign Key)
Constituency
Candidate
● Constituency_ID (Primary Key)
● Candidate_ID (Primary Key) ● Constituency_Name
● Name ● State
● Age ● Number_of_Voters
● Gender
● Political_Party_ID (Foreign Key)
● Constituency_ID (Foreign Key)
Election Entities Keys
Election Result

● Election_ID (Primary Key) ● Result_ID (Primary Key)

● Year ● Election_ID (Foreign Key)
● Type (Lok Sabha / Vidhan Sabha) ● Candidate_ID (Foreign Key)
● State ● Votes_Received
● Constituency_ID (Foreign Key) ● Position (Winner/Runner-up)
● Election_Date
Booth
Vote
● Booth_ID (Primary Key)
● Vote_ID (Primary Key) ● Booth_Name
● Voter_ID (Foreign Key) ● Constituency_ID (Foreign Key)
● Candidate_ID (Foreign Key) ● Location
● Election_ID (Foreign Key)
● Timestamp
Relationships

● A Voter belongs to a Constituency.

● A Candidate contests in a Constituency and belongs to a
Political Party.
● An Election occurs in a Constituency.
● A Vote is cast by a Voter for a Candidate in an Election.
● A Result is generated for each Election, declaring a
Candidate as a winner.
Create
CREATE TABLE Voter ( CREATE TABLE Candidate (

Voter_ID INT PRIMARY KEY, Candidate_ID INT PRIMARY KEY,

Name VARCHAR(100), Name VARCHAR(100),

Age INT, Age INT,

Gender VARCHAR(10),
Gender VARCHAR(10),
Political_Party_ID INT,
Address TEXT,
Constituency_ID INT,
State VARCHAR(50),
FOREIGN KEY (Political_Party_ID) REFERENCES Political_Party(Party_ID),
Constituency_ID INT,
FOREIGN KEY (Constituency_ID) REFERENCES
FOREIGN KEY (Constituency_ID) REFERENCES Constituency(Constituency_ID)
Constituency(Constituency_ID)
);
);
Create
CREATE TABLE Political_Party ( CREATE TABLE Constituency (
Party_ID INT PRIMARY KEY, Constituency_ID INT PRIMARY KEY,
Party_Name VARCHAR(100), Constituency_Name
Symbol VARCHAR(50), VARCHAR(100),

Leader_Name VARCHAR(100), State VARCHAR(50),

Founded_Year INT Number_of_Voters INT

); );
Create
CREATE TABLE Election ( CREATE TABLE Vote (

Election_ID INT PRIMARY KEY, Vote_ID INT PRIMARY KEY,

Year INT, Voter_ID INT,

Type ENUM('Lok Sabha', 'Vidhan Sabha'), Candidate_ID INT,

Election_ID INT,
State VARCHAR(50),
Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
Constituency_ID INT,
FOREIGN KEY (Voter_ID) REFERENCES Voter(Voter_ID),
Election_Date DATE,
FOREIGN KEY (Candidate_ID) REFERENCES
FOREIGN KEY (Constituency_ID) REFERENCES Candidate(Candidate_ID),
Constituency(Constituency_ID)
FOREIGN KEY (Election_ID) REFERENCES Election(Election_ID)
);
);
Create
CREATE TABLE Booth (
Booth_ID INT PRIMARY KEY,
Booth_Name VARCHAR(100),
Constituency_ID INT,
Location VARCHAR(255),
FOREIGN KEY (Constituency_ID)
REFERENCES
Constituency(Constituency_ID)
);
Insert
-- Insert into Constituency
-- Insert into Election
INSERT INTO Constituency VALUES (101, 'Pune',
INSERT INTO Election VALUES (301, 2024, 'Lok Sabha',
'Maharashtra', 500000);
'Maharashtra', 101, '2024-04-15');
-- Insert into Political Party
-- Insert into Vote
INSERT INTO Political_Party VALUES (1, 'Democratic
INSERT INTO Vote (Vote_ID, Voter_ID, Candidate_ID,
Party', 'DP Symbol', 'John Doe', 1950);
Election_ID) VALUES (1, 1001, 201, 301);
-- Insert into Voter
-- Insert into Result
INSERT INTO Voter VALUES (1001, 'Rahul Sharma', 30,
INSERT INTO Result VALUES (401, 301, 201, 350000,
'Male', 'Pune, Maharashtra', 'Maharashtra', 101);
'Winner');
-- Insert into Candidate
-- Insert into Booth
INSERT INTO Candidate VALUES (201, 'Amit Verma', 45,
INSERT INTO Booth VALUES (501, 'Booth 1', 101, 'Pune
'Male', 1, 101);
City Center');
Select Queries
-- Retrieve all voters
-- Get total votes received by each candidate
SELECT * FROM Voter;
SELECT Candidate_ID, COUNT(*) AS Total_Votes
πVoter_ID, Name, Age, Gender, Address, State, FROM Vote GROUP BY Candidate_ID;
Constituency_ID​
(Voter)
-- Find the winner of a specific election
-- Get all candidates in a specific constituency
SELECT [Link], R.Votes_Received
SELECT * FROM Candidate WHERE Constituency_ID = 101;
FROM Result R
σConstituency_ID=101​
(Candidate)
JOIN Candidate C ON R.Candidate_ID =
-- Get election results for a specific election
C.Candidate_ID
SELECT * FROM Result WHERE Election_ID = 301;
WHERE R.Election_ID = 301 AND [Link] =
σElection_ID=301​
(Result) 'Winner';
Update & Delete
-- Update voter's address
-- Delete a voter
UPDATE Voter SET Address = 'New Pune,
DELETE FROM Voter WHERE Voter_ID = 1001;
Maharashtra' WHERE Voter_ID = 1001;
-- Delete all votes for a specific election
-- Update candidate's political party
DELETE FROM Vote WHERE Election_ID = 301;
UPDATE Candidate SET Political_Party_ID = 2 WHERE
Candidate_ID = 201; -- Delete a constituency (only if no dependencies
exist)
-- Update election date
DELETE FROM Constituency WHERE Constituency_ID =
UPDATE Election SET Election_Date = '2024-04-20'
101;
WHERE Election_ID = 301;
Aggregate
-- Count the total number of voters

SELECT COUNT(*) AS Total_Voters FROM Voter;

γCOUNT(*)​(Voter)

-- Count the total number of votes in an election

SELECT COUNT(*) AS Total_Votes FROM Vote WHERE Election_ID = 301;

-- Find the candidate with the highest votes in an election

SELECT Candidate_ID, MAX(Votes_Received) AS Max_Votes FROM Result WHERE Election_ID = 301;

-- Find the number of candidates per political party

SELECT Political_Party_ID, COUNT(*) AS Number_of_Candidates FROM Candidate GROUP BY Political_Party_ID;

γCandidate_ID,COUNT(*)→Total_Votes​(Vote)
Joins
-- Get voter details along with their constituency name

SELECT V.Voter_ID, [Link], [Link], C.Constituency_Name FROM Voter V; JOIN Constituency C ON V.Constituency_ID = C.Constituency_ID;

πVoter_ID, Name, Age, Constituency_Name​(Voter⋈Voter.Constituency_ID=Constituency.Constituency_ID​Constituency)

-- Get candidates along with their party names

SELECT C.Candidate_ID, [Link] AS Candidate_Name, P.Party_Name FROM Candidate C JOIN Political_Party P ON C.Political_Party_ID = P.Party_ID;

πCandidate_ID, Name, Party_Name​(Candidate⋈Candidate.Political_Party_ID=Political_Party.Party_ID​Political_Party)

-- Get election results with candidate details

SELECT [Link], [Link], [Link] AS Candidate_Name, P.Party_Name, R.Votes_Received, [Link] FROM Result R; JOIN Candidate C ON R.Candidate_ID =
C.Candidate_ID JOIN Political_Party P ON C.Political_Party_ID = P.Party_ID; JOIN Election E ON R.Election_ID = E.Election_ID WHERE [Link] = 2024;

πYear, Name, Party_Name, Votes_Received, Position ​((Result⋈Result.Candidate_ID=Candidate.Candidate_ID​Candidate) ⋈

Candidate.Political_Party_ID=Political_Party.Party_ID​Political_Party ⋈ Result.Election_ID=Election.Election_ID​Election)
Nested and Subqueries
-- Find the candidate who received the highest votes in an election

SELECT Candidate_ID, Votes_Received FROM Result

WHERE Votes_Received = (SELECT MAX(Votes_Received) FROM Result WHERE Election_ID = 301);

σVotes_Received=max(πVotes_Received​σElection_ID=301​(Result))​(Result)

-- Get the list of voters who voted in a specific election

SELECT Voter_ID FROM Vote WHERE Election_ID = 301;

πVoter_ID​(σElection_ID=301​(Vote))

-- Find the party with the most elected candidates

SELECT Political_Party_ID FROM Candidate

WHERE Candidate_ID IN (SELECT Candidate_ID FROM Result WHERE Position = 'Winner')

GROUP BY Political_Party_ID ORDER BY COUNT(*) DESC LIMIT 1;

πPolitical_Party_ID​(γPolitical_Party_ID,COUNT(*)→Total_Candidates​(σCandidate_ID∈(πCandidate_ID​σPosition=′Winner′​(Result))​(Candidate)))
Set Operations (∪, ∩, -)
-- Find voters who are also candidates (Intersection)
SELECT Voter_ID FROM Voter INTERSECT SELECT Candidate_ID FROM Candidate;
πVoter_ID​(Voter)∩πCandidate_ID​(Candidate)
-- Find voters who are NOT candidates (Set Difference)
SELECT Voter_ID FROM Voter EXCEPT SELECT Candidate_ID FROM Candidate;
πVoter_ID​(Voter)−πCandidate_ID​(Candidate)
-- Get all unique election years and political party founding years (Union)
SELECT Year FROM Election UNION SELECT Founded_Year FROM Political_Party;
πYear​(Election)∪πFounded_Year​(Political_Party)
View
-- Create a view for election results with candidate and party details

CREATE VIEW Election_Results_View AS

SELECT [Link], [Link], [Link] AS Candidate_Name, P.Party_Name, R.Votes_Received, [Link] FROM Result R

JOIN Candidate C ON R.Candidate_ID = C.Candidate_ID

JOIN Political_Party P ON C.Political_Party_ID = P.Party_ID

JOIN Election E ON R.Election_ID = E.Election_ID;

-- Create a view for voter details with constituency name

CREATE VIEW Voter_Info AS

SELECT V.Voter_ID, [Link], [Link], [Link], C.Constituency_Name, [Link] FROM Voter V

JOIN Constituency C ON V.Constituency_ID = C.Constituency_ID;

Storage Procedure
-- Stored procedure to get election results by year

DELIMITER //

CREATE PROCEDURE GetElectionResults(IN electionYear INT)

BEGIN

SELECT [Link], [Link] AS Candidate_Name, P.Party_Name, R.Votes_Received, [Link] FROM Result R

JOIN Candidate C ON R.Candidate_ID = C.Candidate_ID JOIN Political_Party P ON C.Political_Party_ID = P.Party_ID

JOIN Election E ON R.Election_ID = E.Election_ID WHERE [Link] = electionYear;

END //

DELIMITER ;

-- Call the stored procedure

CALL GetElectionResults(2024);
Indexing
-- Create index on voter ID for faster lookups
CREATE INDEX idx_voter_id ON Voter(Voter_ID);

-- Create index on election ID for faster result retrieval

CREATE INDEX idx_election_id ON Election(Election_ID);

-- Create index on candidate ID for result lookups

CREATE INDEX idx_candidate_id ON Candidate(Candidate_ID);
Trigger
-- Prevent duplicate votes by the same voter in the same election -- Automatically update the number of voters in a constituency
when a new voter is added
CREATE TRIGGER Prevent_Duplicate_Votes
CREATE TRIGGER Update_Constituency_Voter_Count
BEFORE INSERT ON Vote
AFTER INSERT ON Voter
FOR EACH ROW
FOR EACH ROW
BEGIN
BEGIN
IF EXISTS (SELECT 1 FROM Vote WHERE Voter_ID =
NEW.Voter_ID AND Election_ID = NEW.Election_ID) THEN UPDATE Constituency

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A SET Number_of_Voters = Number_of_Voters + 1

voter can only vote once in an election';
WHERE Constituency_ID = NEW.Constituency_ID;
END IF;
END;
END;
Census Database Case Study
Entities

Citizen District

● Citizen_ID ● District_ID
● Name
● District_Name
● Age
● Gender ● Population
● Address
Household
State
● Household_ID
● State_ID ● Income
● State_Name
● Population
● House_Type
● GDP
● Literacy_Rate Employment

Education ● Employment_ID
● Occupation
● Education_ID
● Industry
● Education_Level
● Degree ● Salary
● Institution_Name
Entities with Keys

Citizen District

● Citizen_ID (Primary Key) ● District_ID (Primary Key)

● Name
● Age ● District_Name
● Gender ● State_ID (Foreign Key → State)
● Address ● Population
● State_ID (Foreign Key → State)
● District_ID (Foreign Key → District) Household
State
● Household_ID (Primary Key)
● State_ID (Primary Key) ● Citizen_ID (Foreign Key → Citizen)
● State_Name ● Income
● Population ● House_Type
● GDP
● Literacy_Rate
Employment
Education
● Employment_ID (Primary Key)
● Education_ID (Primary Key) ● Citizen_ID (Foreign Key → Citizen)
● Citizen_ID (Foreign Key → Citizen) ● Occupation
● Education_Level ● Industry
● Degree
● Institution_Name
● Salary
Relationships
Citizen belongs to a District Each Household is associated with a Citizen (Head of
Household)
● (Citizen → District) → Many-to-One Relationship
● Foreign Key: Citizen.District_ID → District.District_ID ● (Household → Citizen) → Many-to-One Relationship
● Foreign Key: Household.Citizen_ID → Citizen.Citizen_ID
Citizen belongs to a State
Each Citizen can have Employment
● (Citizen → State) → Many-to-One Relationship
● (Employment → Citizen) → One-to-One or One-to-Many
● Foreign Key: Citizen.State_ID → State.State_ID
Relationship
● Foreign Key: Employment.Citizen_ID →
Each District is part of a State
Citizen.Citizen_ID
● (District → State) → Many-to-One Relationship Each Citizen can have Education Records
● Foreign Key: District.State_ID → State.State_ID
● (Education → Citizen) → One-to-One or One-to-Many
Relationship
● Foreign Key: Education.Citizen_ID → Citizen.Citizen_ID
Create
-- Citizen Table
-- State Table
CREATE TABLE Citizen (
CREATE TABLE State (
Citizen_ID INT PRIMARY KEY,
State_ID INT PRIMARY KEY,
Name VARCHAR(100),

Age INT, State_Name VARCHAR(100),

Gender VARCHAR(10), Population BIGINT,

Address VARCHAR(255),
GDP BIGINT,
State_ID INT,
Literacy_Rate DECIMAL(5,2)
District_ID INT,

FOREIGN KEY (State_ID) REFERENCES State(State_ID),

);

FOREIGN KEY (District_ID) REFERENCES District(District_ID)

);
Create
-- District Table -- District Table
CREATE TABLE District ( CREATE TABLE District (
District_ID INT PRIMARY KEY, District_ID INT PRIMARY KEY,
District_Name VARCHAR(100), District_Name VARCHAR(100),
State_ID INT, State_ID INT,
Population BIGINT, Population BIGINT,
FOREIGN KEY (State_ID) REFERENCES FOREIGN KEY (State_ID) REFERENCES
State(State_ID) State(State_ID)
); );
Create
-- Employment Table -- Education Table
CREATE TABLE Employment ( CREATE TABLE Education (
Employment_ID INT PRIMARY KEY,
Education_ID INT PRIMARY KEY,
Citizen_ID INT,
Citizen_ID INT,
Occupation VARCHAR(100),
Education_Level VARCHAR(50),
Industry VARCHAR(100),
Degree VARCHAR(100),
Salary BIGINT,
Institution_Name VARCHAR(100),
FOREIGN KEY (Citizen_ID) REFERENCES
Citizen(Citizen_ID) FOREIGN KEY (Citizen_ID) REFERENCES
); Citizen(Citizen_ID)

);
Insert
-- INSERT Queries INSERT INTO Household (Household_ID, Citizen_ID, Income,
House_Type)
INSERT INTO Citizen (Citizen_ID, Name, Age, Gender, Address,
State_ID, District_ID) VALUES (301, 101, 50000, 'Apartment');

VALUES (101, 'Rajesh Kumar', 35, 'Male', '123 Main Street', 10,
501);
INSERT INTO Employment (Employment_ID, Citizen_ID,
INSERT INTO State (State_ID, State_Name, Population, GDP, Occupation, Industry, Salary)
Literacy_Rate)
VALUES (201, 101, 'Software Engineer', 'IT', 1200000);
VALUES (10, 'Maharashtra', 125000000, 400000000000, 88.5);

INSERT INTO District (District_ID, District_Name, State_ID,

Population) INSERT INTO Education (Education_ID, Citizen_ID,
Education_Level, Degree, Institution_Name)
VALUES (501, 'Pune', 10, 7500000);
VALUES (401, 101, 'Postgraduate', '[Link]', 'IIT Bombay');
Update
-- UPDATE Queries UPDATE Household
UPDATE Citizen
SET Income = 60000
SET Address = '456 New Road, Pune'
WHERE Household_ID = 301;
WHERE Citizen_ID = 101;
UPDATE Employment
UPDATE State
SET Salary = 1500000
SET Population = 126000000

WHERE State_ID = 10; WHERE Citizen_ID = 101;

UPDATE District UPDATE Education

SET District_Name = 'Mumbai' SET Degree = 'PhD'
WHERE District_ID = 501; WHERE Education_ID = 401;
Delete
-- DELETE Queries DELETE FROM Household
DELETE FROM Citizen WHERE Household_ID = 301;
WHERE Citizen_ID = 101;

DELETE FROM Employment

DELETE FROM State
WHERE Citizen_ID = 101;
WHERE State_ID = 10;

DELETE FROM District DELETE FROM Education

WHERE District_ID = 501; WHERE Education_ID = 401;
Select and Project
Retrieve all citizens
SELECT * FROM Citizen;
πCitizen_ID, Name, Age, Gender, Address, State_ID, District_ID​(Citizen)
Retrieve all households in a specific district
SELECT * FROM Household WHERE District_ID = 101;
σDistrict_ID=101​(Household)
Retrieve all employed citizens earning above 50,000
SELECT * FROM Employment WHERE Salary > 50000;
σSalary>50000​(Employment)
Join
Get citizens with their state name
SELECT C.Citizen_ID, [Link], S.State_Name FROM Citizen C JOIN State S ON
C.State_ID = S.State_ID;
πCitizen_ID, Name, State_Name​(Citizen⋈Citizen.State_ID=State.State_ID​State)
Get employed citizens along with their education details
SELECT E.Citizen_ID, [Link], Ed.Education_Level, [Link] FROM
Employment E JOIN Education Ed ON E.Citizen_ID = Ed.Citizen_ID;
πCitizen_ID, Occupation, Education_Level,
Degree​(Employment⋈Employment.Citizen_ID=Education.Citizen_ID​Education)
Aggregate

Count the total number of citizens

SELECT COUNT(*) AS Total_Citizens FROM Citizen;

γCOUNT(*)​(Citizen)

Find the average income of households per district

SELECT District_ID, AVG(Income) AS Avg_Income FROM Household GROUP

BY District_ID;

γDistrict_ID,AVG(Income)→Avg_Income​(Household)
Set Operations (∪, ∩, -)
Find citizens who are both students and employed (Intersection)
SELECT Citizen_ID FROM Education INTERSECT SELECT Citizen_ID FROM
Employment;
πCitizen_ID​(Education)∩πCitizen_ID​(Employment)
Find citizens who are only employed (Set Difference)
SELECT Citizen_ID FROM Employment EXCEPT SELECT Citizen_ID FROM
Education
πCitizen_ID​(Employment)−πCitizen_ID​(Education)
Nested Queries and Subqueries
Find the state with the highest literacy rate

SELECT State_Name FROM State WHERE Literacy_Rate = (SELECT MAX(Literacy_Rate) FROM State);

σLiteracy_Rate=max(πLiteracy_Rate​(State))​(State)

Find citizens who belong to a state with GDP above 1 trillion

SELECT Citizen_ID FROM Citizen WHERE State_ID IN (SELECT State_ID FROM State WHERE GDP > 1000000000000);

σState_ID∈(πState_ID​σGDP>1000000000000​(State))​(Citizen)

Find the total employment count per industry

SELECT Industry, COUNT(*) AS Employee_Count FROM Employment GROUP BY Industry;

γIndustry,COUNT(*)→Employee_Count​(Employment)
Example Summary

Operation Query Type

Aggregations COUNT, MAX, GROUP BY

Joins INNER JOIN, LEFT JOIN

Subqueries Nested SELECT

Views CREATE VIEW

Triggers BEFORE INSERT, AFTER INSERT

Stored Procedures CREATE PROCEDURE

Indexing CREATE INDEX

Scenario based Question
1. Database Design for an E-commerce System:
A company wants to store information about customers, orders, and products. The initial design has a single table:
Orders(OrderID, CustomerName, ProductName, Quantity, Price, OrderDate, Address).
Identify issues with this design based on the relational model and suggest improvements.
2. Keys and Constraints in a University Database:
A university maintains a database with a table:
Student(StudentID, Name, Course, CourseInstructor, InstructorEmail, Department).
What problems arise if StudentID is not a primary key? How would you normalize this design while ensuring integrity constraints?
3. Functional Dependencies in an Employee Database:
A company has the following table:
Employee(EmpID, Name, DOB, Address, DeptID, DeptName, ManagerID, ManagerName).
Identify the functional dependencies and suggest an improved schema using the relational model principles.
4. Joins and Redundant Data in a Hospital Database:
A hospital database has the following table:
Patients(PatientID, Name, DoctorID, DoctorName, AppointmentDate, Prescription, RoomNumber, RoomType).
How does this design violate relational model principles? What relational operations could be used to reduce redundancy?
5. Anomalies in a Banking Database:
A bank maintains a CustomerAccounts table:
(AccountNumber, CustomerName, CustomerPhone, Balance, BranchID, BranchName, BranchAddress).
Identify insertion, deletion, and update anomalies. Suggest an improved relational schema.