Dpa : Data Processing And Analysis Vnsgu Sem1

VNSGU BCA Sem 1: Data Processing and Analysis - DPA

What you'll learn

Fundamentals of Workbook and Spreadsheet

Concepts of workbook, adding worksheet rename sheet

Insert, delete, column, rows, cells

Format Cells, cell address, formula bar

Format Page Insert Header & Footer

Split, Hide and freeze panes in worksheet.

Creating 2D and 3D charts ( Columns, Line, Pie, Bar, Scatter)

Functions Power, Sqrt, Abs, Floor, Celling, Int, Trunc, Round, sum, average, count, max, min, sumif, pmt, stddev, if, AND, OR, NOT,

Sort Data, Filter Data, Conditional Formatting, Cell validation,

Introduction of DBMS, Database characteristics

Data Independence ( Logical and Physical), Components of Database , Components of Database

Database Models ( Hierarchical, Network, E/R, Relational)

Schema and Instance, Entity, Relationship, Attribute, Types of Attribute

E/R Diagram, Aggregation,

Mapping Cardinalates: One to one, one to many , many to one, many to many Mapping

Total and Partial Participation, Weak entity set, Types of Relationship

Super key, candidate key, Primary key, Composite key, Unique key, Foreign key

Need of normalization ( Insertion, Updating, Deletion anomalies)

Functional Dependency, Armstrong Axioms, Closure of a set of FD

Prime and Non-prime Attribute

1st Normal Form, 2nd Normal Form, 3rd Normal Form

Concepts of Structure Query Language (SQL)

DDL Statements : Create , Drop, Truncate, Rename, Alter

DML Statements : Insert, Update, Delete

DQL(Data Query Language) Statements : select

Constraints ( Table level and Attribute Level), NOT NULL, CHECK, DEFAULT, UNIQUE, Primary Key, Foreign Key, On Delete Cascade

Using where clause and operators with where clause: In, between , like, not in, =, !=, >, =, <=, wildcard operators, AND, OR operators

Order by, Distinct, Exists and not Exists, Use of Alias

Aggregate Functions: avg(), max(), min(), sum(), count(), Group by, Having

Scalar Functions: ucase(), lcase(), round(), mid().

Creating sequence, Creating simple view, updating view, dropping view, Difference between View and Table.

Requirements

Basic knowledge of computer , No programming knowledge require you learn about calc, database and sql in this course

Description

Worksheet, Workbook, and Spreadsheet BasicsWorksheet Concept: A worksheet is a single page within an Excel workbook where data is stored and manipulated.Workbook Fundamentals: A workbook is a collection of worksheets, saved in a single file.Spreadsheet Basics: A spreadsheet is the digital equivalent of a paper ledger, containing data organized in rows and columns.Managing Worksheets and CellsAdding, Renaming, and Deleting Worksheets: Users can create, rename, and delete worksheets within a workbook.Inserting/Deleting Rows and Columns: Insert or delete entire rows, columns, or cells.Cell Size Adjustment: Modify cell dimensions, including row height and column width.Cell Address and Formula Bar: Cells are identified by a combination of letters (columns) and numbers (rows) and can contain data or formulas.Cut, Copy, Paste: Basic operations for moving data within the sheet, including options like "Paste Special" for conditional pasting.Format Painter: Copies formatting from one cell or area to another.Formatting and EnhancementsCell Formatting: Customize cell appearance, including number formats, font styles, and colors.Inserting Media: Add pictures, shapes, and textboxes to a worksheet.Page Layout: Set headers, footers, and other print-related settings.File Management: Save, save as (different format like CSV), spell check, and sheet protection options.Split, Hide, Freeze Panes: Useful for managing large datasets by freezing certain areas, hiding content, or splitting the screen.Charts, Data, and FunctionsChart Types: Create 2D and 3D charts such as columns, lines, pies, bars, and scatter plots.Basic Functions: Functions like Power, Sqrt, Abs, Floor, Ceiling, Int, Trunc, Round, Sum, Average, Count, Max, Min, Sumif, Pmt, Stddev.Logical Functions: IF, AND, OR, NOT, TRUE, FALSE for conditional logic.Date and Time Functions: Date, day, time, now, hour, minute, second, month, Days360, weekday.Data Management: Sort, filter, and apply conditional formatting or cell validation.Advanced Options: Subtotals, text-to-columns, and duplicate removal.Database ConceptsDBMS Introduction: A Database Management System (DBMS) is software for storing and retrieving users' data efficiently.Database Characteristics: Includes data independence, minimized redundancy, and optimized data access.Data Independence: Logical and physical separation between data and applications.DBA Role: Database Administrator manages the database's performance, security, and maintenance.Database Architecture and ModelsDatabase Architecture: 1-tier (standalone), 2-tier (client-server), 3-tier (multi-tier).Database Models: Hierarchical, Network, E/R (Entity-Relationship), and Relational models.Schema and Instance: Schema is the design, while the instance is the actual data.E/R Diagram Elements: Entities, relationships, attributes, and mapping cardinalities (1:1, 1, M:1, M).Keys and RelationshipsTypes of Keys: Super key, candidate key, primary key, composite key, unique key, and foreign key.Normalization: Organizing data to reduce redundancy and improve data integrity.Anomalies: Insert, update, delete.Functional Dependency: Relationships between columns.Normal Forms: 1NF, 2NF, 3NF, BCNF.SQL FundamentalsSQL Overview: Structured Query Language (SQL) is used to manage and manipulate relational databases.SQL Datatypes: Different data types (e.g., INT, VARCHAR, DATE).DDL (Data Definition Language): Commands like Create, Drop, Truncate, Rename, Alter for managing table structure.DML (Data Manipulation Language): Insert, Update, Delete to manage data.DQL (Data Query Language): SELECT to retrieve data.Querying and ConstraintsConstraints: NOT NULL, CHECK, DEFAULT, UNIQUE, primary key, foreign key, and cascading deletes.WHERE Clause and Operators: Filtering data with operators like IN, BETWEEN, LIKE, AND, OR.Ordering and Aggregation: ORDER BY, DISTINCT, GROUP BY, HAVING, and aggregate functions like AVG, MAX, MIN, SUM, COUNT.Views: Virtual tables created from SQL queries for simplified data access.

Overview

Section 1: Concept of Worksheet

Lecture 1 Fundamentals of Workbook and Spreadsheet

Lecture 2 Concepts of workbook, adding worksheet rename sheet

Lecture 3 Insert, delete, column, rows, cells, cell size ( height, width)

Lecture 4 Cell address, formula bar

Lecture 5 Cut, copy, paste, paste special, format painte

Lecture 6 Format Cells

Lecture 7 Insert picture, shapes, textbox

Lecture 8 Format Page Insert Header & Footer

Lecture 9 Save, save as, save file as csv, protect sheet and Workbook

Lecture 10 Print, Quick print, Print preview

Lecture 11 Split and freeze panes in worksheet.

Section 2: Formulas, Chart and Data

Lecture 12 Creating 2D and 3D charts ( Columns, Line, Pie, Bar, Scatter)

Lecture 13 Functions Power, Sqrt, Abs, Floor, Celling, Int, Trunc, Round

Lecture 14 Functions sum, average, count, max, min, sumif, pmt, stddev

Lecture 15 Logical ( if, AND, OR, NOT )

Lecture 16 Date and Time functions

Lecture 17 Sort Data, Filter Data

Lecture 18 Conditional Formatting

Lecture 19 Cell Validation

Lecture 20 Subtotal

Lecture 21 Text to columns, Remove Duplication

Section 3: Concepts of Database

Lecture 22 Introduction of DBMS

Lecture 23 Database characteristics

Lecture 24 Data Independence ( Logical and Physical)

Lecture 25 Users of DBMS

Lecture 26 DBA

Lecture 27 Components of Database

Lecture 28 Database Architecture (1-tier, 2-tier, 3-tier)

Lecture 29 Database Models ( Hierarchical, Network, E/R, Relational)

Lecture 30 Schema and Instance

Lecture 31 Entity, Relationship, Attribute

Lecture 32 Types of Attribute

Lecture 33 E/R Diagram

Lecture 34 Aggregation

Lecture 35 Mapping Cardinalates : One to one, one to many , many to one, many to many

Lecture 36 Total and Partial Participation

Lecture 37 Weak entity set

Lecture 38 Types of Relationship

Lecture 39 Super key, candidate key, Primary key, Composite key, Unique key.

Lecture 40 Foreign key

Section 4: Normalization and Concepts of SQL

Lecture 41 Need of normalization ( Insertion, Updating, Deletion anomalies)

Lecture 42 Functional Dependency

Lecture 43 Armstrong Axioms

Lecture 44 Clouser of a set of FD

Lecture 45 Prime and Non-prime Attribute

Lecture 46 1st Normal Form, 2nd Normal Form, 3rd Normal Form

Lecture 47 BCNF

Lecture 48 Concepts of Structure Query Language (SQL)

Lecture 49 SQL datatypes

Lecture 50 DDL Statements : Create , Drop, Truncate, Rename, Alter Syntax

Lecture 51 DDL Statements : Create , Drop, Truncate, Rename, Alter Practical

Lecture 52 DML Statements : Insert, Update, Delete Syntax

Lecture 53 DML Statements : Insert, Update, Delete Practical

Lecture 54 DQL(Data Query Language) Statements : select Syntax

Lecture 55 DQL(Data Query Language) Statements : select Practical

Section 5: Queries (Single Table only)

Lecture 56 Constraints NOT NULL, CHECK, DEFAULT, UNIQUE, Primary Key, Foreign Key

Lecture 57 Where clause and operators (In, between, like, not, wildcard operators, and, or)

Lecture 58 Order by, Distinct

Lecture 59 Exists and not Exists

Lecture 60 Use of Alias

Lecture 61 Aggregate Functions: avg(), max(), min(), sum(), count(), Group by, Having.

Lecture 62 Scalar Functions: upper(), lower(), substr(), round(), floor().ceil(), trunc()

Lecture 63 Using Date Datatype and Functions

Lecture 64 Sequence

Lecture 65 Views

VNSGU BCA SEM1 Student for Subject Data Processing and Analysis