Python for Data Science (MCS104A) VIJAY SIR

MODULE 1(Foundations of Data Science, Statistics & SQL)

Q1
a) Define Data Science and explain its interdisciplinary nature. (4M)
Definition:
Data Science is a multidisciplinary field that uses scientific methods, statistics, algorithms,
and computing to extract knowledge and insights from structured and unstructured data.
Interdisciplinary Nature
┌──────────────┐
│ Mathematics │
│ & Statistics│
└──────┬───────┘
│
┌──────────────┐ │ ┌──────────────┐
│ Computer ├───┼───┤ Domain │
│ Science │ │ │ Knowledge │
└──────────────┘ │ └──────────────┘
│
┌─────▼─────┐
│Data Science│
└───────────┘
Statistics → inference, probability
Computer Science → programming, ML, databases
Domain Knowledge → business understanding

Algorithm: CRISP-DM Inspired Data Science Process

Step 1: Define Business Problem
Step 2: Collect Relevant Data
Step 3: Clean & Preprocess Data
Step 4: Perform Exploratory Data Analysis (EDA)
Step 5: Select Model
Step 6: Train Model
Step 7: Evaluate Model
Step 8: Deploy & Monitor
Explanation
• Step 1 ensures alignment with business goals.
• Steps 2–3 prepare high-quality data.
• Steps 4–6 build predictive capability.
• Steps 7–8 ensure performance & continuous improvement.
b) Discuss Big Data and Data Science hype. How can organizations get past the hype? (4M)
Big Data
Data with high:
Volume
Velocity
Variety
Veracity
Value
Hype
Overpromising AI results
Assuming more data = better decisions
Blind tool adoption
How Organizations Can Get Past Hype
Business Problem → Data Audit → Pilot Project → ROI Measurement → Scale
✔ Focus on real business problems
✔ Invest in data quality
✔ Hire skilled professionals
✔ Start small, scale gradually

Algorithm: Getting Past Big Data Hype

Input: Organizational problem
Output: Data-driven solution with ROI

Step 1: Identify measurable business objective

Step 2: Audit available data
Step 3: Conduct feasibility study
Step 4: Build small pilot model
Step 5: Measure performance (ROI, accuracy)
Step 6: Scale if results are positive
Explanation
Prevents blind AI adoption. Focuses on measurable impact.

c) Explain why Data Science has gained importance now. (4M)

Explosion of digital data (social media, IoT)
Cloud computing & big data tools
Cheap storage
AI/ML advancements
Example platforms:
Google
Amazon
Facebook
These companies use data science for recommendations, ads, forecasting.
Algorithm: Data Value Creation
Raw Data → Storage → Processing → Modeling → Insight → Decision → Value
Explanation
Organizations like Google and Amazon follow similar pipelines for ads and
recommendations.
Q2
a) Explain “Datafication” with real-world examples. (4M)
Definition: Converting real-world activities into digital data.
Examples:
• Fitness tracking (steps, heart rate)
• E-commerce purchase history
• Social media likes
Example platform:
• Netflix → converts viewing behavior into recommendation data.
Human Activity → Digital Capture → Storage → Analysis → Insights

Algorithm: Datafication Process

Step 1: Identify human activity
Step 2: Capture digitally (sensor/app/log)
Step 3: Store in database
Step 4: Analyze patterns
Step 5: Generate recommendations
Example: Netflix converts viewing history into recommendations.
Explanation
Transforms qualitative actions into quantitative variables.

b) Describe the current landscape of Data Science perspectives. (4M))

Data Engineering
│
Machine Learning ─── AI
│
Business Analytics
│
Big Data Systems
Perspectives:
• Academic research
• Industry analytics
• AI-driven automation
• Data governance & ethics
Algorithm: Data Science Workflow Architecture
Data Collection
↓
Data Engineering
↓
Model Building
↓
Evaluation
↓
Deployment
Explanation
Shows collaboration between:
• Data Engineers
• Data Scientists
• ML Engineers
• Business Analysts
c) List and explain the essential skill sets required for a data scientist. (4M)
Skill Explanation
Programming Python, R, SQL
Statistics Probability, inference
ML Regression, classification
Data Wrangling Cleaning, preprocessing
Visualization Tableau, Power BI
Communication Storytelling

Algorithm: Skill Integration Model

If (Statistics + Programming + Domain Knowledge) present
→ Effective Data Scientist
Else
→ Skill Gap Exists
Explanation
Balanced expertise ensures accurate and business-relevant insights.

Q3
a) Define statistical inference. (4M))
Definition: Drawing conclusions about a population using sample data.
Population → Sample → Analysis → Conclusion
Methods:
• Estimation
• Hypothesis testing

Algorithm: Hypothesis Testing

Input: Sample Data

Step 1: State H0 (Null hypothesis)

Step 2: State H1 (Alternative hypothesis)
Step 3: Choose significance level (α)
Step 4: Compute test statistic
Step 5: Compute p-value
Step 6: If p < α → Reject H0
Else → Fail to reject H0
Explanation
Used in A/B testing and experiments.

b) Distinguish between population and sample with suitable examples. (4M))

Feature Population Sample
Definition Entire group Subset
Size N n
Example All students in college 100 selected students
Algorithm: Sampling Procedure
Step 1: Define population
Step 2: Choose sampling method (random/systematic)
Step 3: Select sample size (n)
Step 4: Collect sample data
Explanation
Ensures representation of entire population.

Example:
Population = All voters
Sample = 1000 surveyed voters

c) Explain the role of statistical inference in data science. (4M))

• Model validation
• A/B testing
• Risk estimation
• Forecasting
Example:
E-commerce A/B test → Decide better webpage.

Algorithm: Inference in Data Science Models

Train model on sample data
Estimate parameters
Validate on test data
Generalize results to population
Explanation
Allows predictions beyond observed data.

Q4
a) Explain statistical modelling and its importance. (4M))
Definition: Mathematical representation of relationships between variables.
Example:
Linear Regression
Y=β0+β1X+εY = β0 + β1X + εY=β0+β1X+ε
Importance:
• Prediction
• Decision-making
• Pattern identification

Algorithm: Linear Regression

Input: Dataset (X, Y)

Step 1: Initialize β0, β1

Step 2: Predict Y = β0 + β1X
Step 3: Compute error (Y - Ŷ)
Step 4: Minimize error using Least Squares
Step 5: Update β values
Step 6: Repeat until error minimized
Explanation
Finds best-fit line.
b) Describe probability distributions used in data science. (4M))
Common distributions:
• Normal
• Binomial
• Poisson
• Uniform
Probability Distribution
│
Discrete Continuous
(Binomial) (Normal)

Algorithm: Selecting Probability Distribution

If data is discrete & binary → Binomial
If data is continuous & symmetric → Normal
If data counts rare events → Poisson
Explanation
Helps select correct statistical model.

c) Explain the concept of model fitting. (4M))

Definition: Adjusting model parameters to best match data.
Steps:
1. Select model
2. Estimate parameters
3. Minimize error (e.g., MSE)
Algorithm (Simple):
Initialize parameters
Compute prediction
Calculate error
Update parameters
Repeat until error minimized

Algorithm: Model Fitting Using Gradient Descent

Initialize parameters randomly
Repeat until convergence:
Compute predictions
Compute loss (MSE)
Compute gradient
Update parameters:
θ = θ - α * gradient
Explanation
Minimizes loss function to improve accuracy.
Q5
a) Explain Normal and Binomial probability distributions with examples. (6M))
Normal Distribution
• Continuous
• Bell-shaped
• Mean = Median = Mode
Formula:
f(x)=1σ2πe−(x−μ)2/2σ2f(x)=\frac{1}{σ\sqrt{2π}}e^{-(x-μ)^2/2σ^2}f(x)=σ2π1e−(x−μ)2/2σ2
Example:
Height of students.
Graph:
/\
/ \
/ \

Binomial Distribution
• Discrete
• Fixed trials (n)
• Two outcomes (success/failure)
Formula:
P(X=k)=(nk)pk(1−p)n−kP(X=k)= \binom{n}{k} p^k (1-p)^{n-k}P(X=k)=(kn)pk(1−p)n−k
Example:
Number of heads in 10 coin tosses.

Algorithm: Binomial Probability

Input: n trials, p probability, k successes

Compute:
P(X=k) = (nCk) * p^k * (1-p)^(n-k)
Return probability
Explanation
Used for yes/no outcomes (e.g., coin toss).

Algorithm: Normal Probability (Z-score)

Input: x, mean μ, standard deviation σ

Compute Z = (x - μ) / σ
Find probability from Z-table
Return probability
Explanation
Standardizes data for comparison.

b) Explain how probability distributions help in decision-making. (6M))

• Risk analysis
• Quality control
• Forecasting
• Hypothesis testing
Example:
Loan default probability → Bank decision
Medical trial success probability → Drug approval

Algorithm: Decision-Making Using Probability

Estimate probability of event
Compute expected value
If expected benefit > risk
Approve decision
Else
Reject decision
Explanation
Used in finance, healthcare, marketing.

Q6
a) Explain the Relational Data Model with neat diagram. (6M)
Proposed by E. F. Codd
Data stored in tables (relations).
Diagram:
STUDENT Table
+--------+--------+------+
| RollNo | Name | Dept |
+--------+--------+------+

COURSE Table
+--------+---------+
| Dept | Course |
+--------+---------+
Relations through keys.

Algorithm: Create Relational Schema

Step 1: Identify entities
Step 2: Define attributes
Step 3: Assign primary key
Step 4: Define foreign keys
Step 5: Normalize tables
Proposed by E. F. Codd.

b) Explain tables, rows, and columns in RDBMS with examples. (6M))

• Table → Collection of data
• Row (Tuple) → Record
• Column (Attribute) → Field
Example:
EMPLOYEE
+----+--------+--------+
|ID | Name | Salary |
+----+--------+--------+
|101 | Ravi | 40000 |
SQL Example:
CREATE TABLE Employee (
ID INT,
Name VARCHAR(50),
Salary INT
);

Algorithm: Insert Data in RDBMS

For each new record:
Validate primary key uniqueness
Check foreign key constraint
Insert into table
Explanation
Ensures data integrity.

Q7
a) Explain Primary key, foreign key, and Unique key with examples. (6M))
Primary Key
• Uniquely identifies row
ID INT PRIMARY KEY
Foreign Key
• References primary key
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
Unique Key
• No duplicate values allowed
Email VARCHAR(50) UNIQUE

Algorithm: Primary Key Validation

For each new row:
If primary key exists
Reject insertion
Else
Insert row
Explanation
Ensures uniqueness.

b) Explain One-to-One, One-to-Many, and Many-to-Many relationships. (6M))

1:1
One person → One passport
Person ─── Passport
1:M
One department → Many students
Department ───< Students
M:N
Students ↔ Courses
Student >───< Course
(via junction table)

Algorithm: Handling Many-to-Many Relationship

Create Table A
Create Table B
Create Junction Table:
A_ID (FK)
B_ID (FK)
Primary Key (A_ID, B_ID)
Explanation
Breaks M:N into two 1:M relationships.

Q8
a) Explain SQL fundamentals and its role in Data Science. (4M)
SQL → Structured Query Language
Role:
• Data retrieval
• Data cleaning
• Aggregation
• Joining tables
Example:
SELECT AVG(Salary) FROM Employee;

Algorithm: SQL Query Execution

Parse SQL query
Validate syntax
Optimize query plan
Fetch data from tables
Return result set
Explanation
Shows how DBMS processes queries.

b) Explain DDL commands: CREATE, ALTER, DROP with syntax. (4M))

CREATE
CREATE TABLE Student(ID INT, Name VARCHAR(50));
ALTER
ALTER TABLE Student ADD Age INT;
DROP
DROP TABLE Student;

Algorithm: DDL Execution

Receive CREATE/ALTER/DROP command
Update data dictionary
Modify schema structure
Commit changes
c) Explain DML commands: INSERT, UPDATE, DELETE. (4M))
INSERT
INSERT INTO Student VALUES (1,'Rahul');
UPDATE
UPDATE Student SET Name='Amit' WHERE ID=1;
DELETE
DELETE FROM Student WHERE ID=1;

Algorithm: DML Execution

Receive INSERT/UPDATE/DELETE
Check constraints
Modify table rows
Commit transaction

SQL & R - PROGRAM ORIENTED Questions

Q1 . “Consider a STUDENT table with attributes (USN, Name, Dept, CGPA).”

a. Write SQL query to create the table with appropriate keys. (4M)
✅ SQL Query
CREATE TABLE STUDENT (
USN VARCHAR(15) PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Dept VARCHAR(30),
CGPA DECIMAL(3,2) CHECK (CGPA BETWEEN 0 AND 10)
);
🔎 Explanation
• USN → Primary Key (unique student number)
• NOT NULL → Name must be provided
• CHECK → CGPA between 0–10
📌 Table Structure Diagram
STUDENT
+------+--------+------+------+
| USN | Name | Dept | CGPA |
+------+--------+------+------+
(PK)
⚙ Algorithm: Create Table
Step 1: Define table name
Step 2: Define attributes with data types
Step 3: Assign Primary Key
Step 4: Add constraints
Step 5: Execute CREATE command
b) Write SQL query to insert at least three records. (4M))
✅ SQL Query
INSERT INTO STUDENT VALUES ('1MS21CS001','Ravi','CSE',8.5);
INSERT INTO STUDENT VALUES ('1MS21CS002','Anita','ISE',9.1);
INSERT INTO STUDENT VALUES ('1MS21CS003','Kiran','ECE',7.8);
⚙ Algorithm: Insert Operation
For each record:
Check USN uniqueness
Validate CGPA constraint
Insert row into table

c) Write SQL queries to update CGPA and delete a record. (4M))

✅ Update
UPDATE STUDENT
SET CGPA = 9.3
WHERE USN = '1MS21CS001';
✅ Delete
DELETE FROM STUDENT
WHERE USN = '1MS21CS003';
⚙ Algorithm
Step 1: Locate record using WHERE
Step 2: If UPDATE → Modify field
Step 3: If DELETE → Remove row
Step 4: Commit transaction

Q2. “Consider a table EMPLOYEE(EmpID, Name, Dept, Salary, DOJ).”

a) Write SQL command to create the table with EmpID as Primary Key. (4M)
✅ SQL Query
CREATE TABLE EMPLOYEE (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Dept VARCHAR(30),
Salary DECIMAL(10,2),
DOJ DATE
);
📌 Structure
EMPLOYEE
+-------+------+-------+--------+------+
|EmpID |Name |Dept |Salary |DOJ |
+-------+------+-------+--------+------+
(PK)
b) Insert at least three records into the EMPLOYEE table. (4M))
INSERT INTO EMPLOYEE VALUES (101,'Rahul','HR',60000,'2022-01-10');
INSERT INTO EMPLOYEE VALUES (102,'Sneha','IT',75000,'2021-06-15');
INSERT INTO EMPLOYEE VALUES (103,'Amit','Sales',45000,'2023-03-20');
⚙ Algorithm
Check EmpID uniqueness
Validate data types
Insert record

c) Write a query to display employees whose salary is greater than 50,000. (4M))
SELECT * FROM EMPLOYEE
WHERE Salary > 50000;
⚙ Algorithm: Selection
Scan table
For each row:
If Salary > 50000
Display row

Q3. “Consider two tables:

STUDENT(USN, Name, Dept)
MARKS(USN, Subject, Marks)”

a) Create both tables by specifying appropriate Primary and Foreign keys. (6M))
CREATE TABLE STUDENT (
USN VARCHAR(15) PRIMARY KEY,
Name VARCHAR(50),
Dept VARCHAR(30)
);

CREATE TABLE MARKS (

USN VARCHAR(15),
Subject VARCHAR(30),
Marks INT,
PRIMARY KEY (USN, Subject),
FOREIGN KEY (USN) REFERENCES STUDENT(USN)
);
📌 Relationship Diagram
STUDENT (1) ─────── (M) MARKS
USN (PK) USN (FK)
⚙ Algorithm
Create parent table (STUDENT)
Create child table (MARKS)
Define Foreign Key reference
b) Write a SQL query to display student names along with their marks. (6M))
SELECT [Link], [Link], [Link]
FROM STUDENT S
JOIN MARKS M
ON [Link] = [Link];
⚙ Algorithm: Join
For each STUDENT row:
Match USN in MARKS
Combine matching rows
Display result

Q4. “Consider a table COURSE(CourseID, CourseName, Credits).”

a) Write SQL query to add a new column FacultyName to the table. (4M))
ALTER TABLE COURSE
ADD FacultyName VARCHAR(50);
⚙ Algorithm
Locate table
Modify schema
Add new column
Update data dictionary

b) Write SQL query to update credits of a given course. (4M))

UPDATE COURSE
SET Credits = 4
WHERE CourseID = 'CS101';

c) Write SQL query to delete a course with a specific CourseID. (4M)

DELETE FROM COURSE
WHERE CourseID = 'CS101';

Q5. “Consider a table ORDERS(OrderID, CustomerID, OrderDate, Amount).”

a) Write SQL query to display all orders placed after a given date. (4M)
SELECT * FROM ORDERS
WHERE OrderDate > '2024-01-01';
⚙ Algorithm
Compare OrderDate
If date > given date
Display row
b) Write SQL query to calculate total order amount per customer. (4M))
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM ORDERS
GROUP BY CustomerID;
⚙ Algorithm: Aggregation
Group rows by CustomerID
Add Amount values
Return total per group

c) Write SQL query to display customers having total order amount greater than 1,00,000.(4M))
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM ORDERS
GROUP BY CustomerID
HAVING SUM(Amount) > 100000;
⚙ Algorithm
Group by CustomerID
Compute total
If total > 100000
Display customer

Q6. “Consider tables DEPARTMENT(DeptID, DeptName) and EMPLOYEE(EmpID, Name,

DeptID).”

a) Write SQL query to display employee name and department name. (6M )
SELECT [Link], [Link]
FROM EMPLOYEE E
JOIN DEPARTMENT D
ON [Link] = [Link];
📌 Diagram
EMPLOYEE (M) ─────── (1) DEPARTMENT
DeptID (FK) DeptID (PK)
⚙ Algorithm
For each employee:
Match DeptID in DEPARTMENT
Display Name + DeptName

b) Write SQL query to display departments with no employees. (6M))

SELECT [Link]
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E
ON [Link] = [Link]
WHERE [Link] IS NULL;
⚙ Algorithm
Perform LEFT JOIN
If no matching employee
EmpID will be NULL
Display such departments

✅ Flowchart of SQL Query Processing (General – Useful for Any Question)

User Query
↓
Parser
↓
Optimizer
↓
Execution Engine
↓
Result Output

Q7. “Write SQL queries to:”

a) Display distinct department names .
✅ SQL Query
SELECT DISTINCT Dept
FROM EMPLOYEE;
⚙ Algorithm
Step 1: Scan EMPLOYEE table
Step 2: Identify unique Dept values
Step 3: Remove duplicates
Step 4: Display result
🔎 Explanation
DISTINCT eliminates duplicate department names.

b) Sort employees by salary in descending order.

✅ SQL Query
SELECT * FROM EMPLOYEE
ORDER BY Salary DESC;
⚙ Algorithm
Step 1: Retrieve all employee records
Step 2: Compare Salary values
Step 3: Arrange from highest to lowest
Step 4: Display sorted table
📌 Sorting Diagram
Before: 45000, 75000, 60000
After : 75000, 60000, 45000
Q8. Write SQL query using WHERE clause to filter records based on multiple
conditions.(4M)
✅ SQL Query
SELECT * FROM EMPLOYEE
WHERE Dept = 'IT' AND Salary > 50000;
⚙ Algorithm
For each row in EMPLOYEE:
If Dept = 'IT' AND Salary > 50000
Display row
🔎 Explanation
Uses logical operators:
• AND
• OR
• NOT

Q9. Write SQL query to demonstrate One-to-Many relationship with

example tables.(6M)
One-to-Many Relationship
Example Tables
• DEPARTMENT (DeptID, DeptName)
• EMPLOYEE (EmpID, Name, DeptID)
✅ SQL
CREATE TABLE DEPARTMENT (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

CREATE TABLE EMPLOYEE (

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID)
);
📌 Relationship Diagram
DEPARTMENT (1) ───────< (M) EMPLOYEE
DeptID (PK) DeptID (FK)
⚙ Algorithm
Step 1: Create parent table (DEPARTMENT)
Step 2: Assign primary key
Step 3: Create child table (EMPLOYEE)
Step 4: Add foreign key referencing DeptID
🔎 Explanation
One department can have many employees.
Q10.
a) Write an R program to create a vector of numeric values. (4M)

✅ R Program
marks <- c(45, 67, 89, 72, 56, 91, 60)
print(marks)
⚙ Algorithm
Define vector using c()
Store numeric values
Print vector

b) Find mean, median, and standard deviation of the vector. (4M))

mean(marks)
median(marks)
sd(marks)
⚙ Algorithm
Mean = Sum(values) / Count
Median = Middle value after sorting
SD = sqrt( Σ(x - mean)^2 / n )

c) Plot a histogram for the vector. (4M))

hist(marks, col="blue", main="Histogram of Marks")
📌 Diagram
| █
|███
|████
+----------------
⚙ Algorithm
Divide data into bins
Count frequency in each bin
Draw bars representing frequency

Q11.
a) Write an R program to create a data frame with student details. (4M))
students <- [Link](
USN=c(1,2,3),
Name=c("Ravi","Anita","Kiran"),
Marks=c(85,90,78)
)
print(students)
⚙ Algorithm
Define columns
Combine using [Link]()
Store as structured table
b) Display summary statistics of the data frame. (4M))
summary(students)
🔎 Output Includes
• Min
• 1st Quartile
• Median
• Mean
• 3rd Quartile
• Max

c) Extract records based on a condition. (4M))

students[students$Marks > 80, ]
⚙ Algorithm
Check condition (Marks > 80)
Filter matching rows
Display result

Q12.
a) Write an R program to read data from a CSV file. (6M))
data <- [Link]("[Link]")
⚙ Algorithm
Specify file path
Use [Link]()
Store dataset in variable

b) Display first five rows and structure of the dataset. (6M))

head(data, 5)
str(data)
🔎 Explanation
• head() → first 5 rows
• str() → structure of dataset

Q13.
a) Write an R program to generate random numbers using normal distribution. (4M)
[Link](123)
x <- rnorm(100, mean=50, sd=10)
⚙ Algorithm
Set seed for reproducibility
Use rnorm(n, mean, sd)
Generate random values
b) Plot the distribution using a density plot. (4M)
plot(density(x), main="Density Plot")
📌 Diagram
/\
/ \
/ \

c) Explain the significance of probability distribution in data science. (4M))

🔎 Explanation
• Models uncertainty
• Supports hypothesis testing
• Helps risk prediction
• Used in ML algorithms
⚙ Decision Algorithm
Estimate probability
Measure risk
Choose action with highest expected benefit

Q14.
a) Write an R program to perform simple linear regression. (6M)
x <- c(1,2,3,4,5)
y <- c(2,4,5,4,5)

model <- lm(y ~ x)

summary(model)
⚙ Algorithm
Input X and Y
Fit model using lm()
Estimate coefficients (β0, β1)
Minimize squared error
Regression Equation:
Y = β0 + β1X

b) Plot regression line and interpret the output. (6M))

plot(x, y)
abline(model, col="red")
📌 Diagram
* *
*
* *
---------------
🔎 Interpretation
• β1 → slope (change in Y per unit X)
• R² → goodness of fit
• p-value < 0.05 → significant relationship
⚙ Flowchart
Input Data
↓
Fit Model
↓
Evaluate R² & p-value
↓
Interpret Relationship

✅ General R Data Science Workflow

Data Collection
↓
Cleaning
↓
Exploration
↓
Modeling
↓
Visualization
↓
Interpretation