UNIT –II

ELECTRONIC SPREADSHEET(ADVANCE)
Analyze data using Scenarios and Goal
seek
+Analyzing data is the process to extract useful information for
making effective decisions.
+Spread sheet is one of the best software used for data
analysis.
Consolidating data
+Consolidation is a function used to combine information from multiple sheets of the
spread sheet into one place.
+Steps to consolidate:
Step 1: Open the spreadsheet which has the data.
Step 2: Create a new sheet where the data has to be consolidate.
Step 3: Choose Data >> Consolidate.
Step 4: Choose the required function.
Step 5: Select source of data ranges from multiple sheets.
Step 6: Click on ‘Options” that will list three checkbox, ‘Row Lables’ and ‘Column
Labels’ and link to source data.
Step 7: Finally click on OK button.
Subtotals
+The subtotal tool in calc create the group automatically and
applies common function sum on grouped data.
Step 1: The sheet where this is to be applied.
Step 2: Click on data tab and choose subtotal.
Step 3: Select the column by clicking the checkbox.
Step 4: Click on OK button.
What-if Scenarios
+What-if Scenario is a set of values that can be used within the calculations in the
spreadsheet.
+This tool is used to predict the output while changing the inputs which reflects the
output.
Step 1: Select the cells which contains values in the sheet that needs to be changed.
Step 2: Choose Data >> What-if Analysis >> Scenario.
Step 3: Enter the name for the new scenario and leave the other fields unchanged.
Step 4: Click on OK button.
Step 5: if you want to add more Scenarios, repeat the above steps
Goal seek
+ Goal seek helps in finding out the input for the specific output.
+ For Example, if you want to know the number of units produced to get the desired output
then use Goal seek analysis tool.
Following steps to use Goal seek tool:
Step 1: Enter the values in the Worksheet.
Step 2: Write the formula in the cell
Step 3: Place the cursor in the formula cell, Choose Data tab >> What-if Analysis >> Goal
seek
Step 4: Goal seek dialog window will appear.
Step 5: Enter the desired result in the target value.
Step 6: Click OK
Hyperlinks to the sheet
+Some times it is required to jump to a document stored at different
location from within a document.
+It can be done by creating Hyperlink.
+It is possible to jump from a sheet in the same spreadsheet, different
spreadsheet or a website by creating a hyperlink.
Step 1: Open the spreadsheet document.
Step 2: Choose Insert tab >> click Hyperlink or Ctrl + K
Step 3: Select the file which you want link.
Step 4: Click OK.
Macros
+Macros are set of stored functions used to automate process repeatedly.
+They are tools which can be used to perform most of the redundant tasks with relative
ease.
Recording a Macro:
Step 1: Open a new worksheet.
Step 2: Choose View >> Macros option.
Step 3: open macro dialog box.
Step 4: Give the name of macro, if you want select the shortcut key to your macro.
Step 5: Click on OK button. Macro recording start.
Step 6: After recording Click on stop recording.
 UNIT –III
Database Management System
Introduction of DBMS
+A database is a collection of interrelated data that helps in the
efficient retrieval, insertion, and deletion of data from the
database and organizes the data in the form of tables, views,
schemas, reports, etc.
+For Example, a university database organizes the data about
students, faculty, admin staff, etc. which helps in the efficient
retrieval, insertion, and deletion of data from it.
Database Languages
+Data Definition Language
+Data Manipulation Language
+Data Control Language
Data Definition Language (DDL)
+DDL is the short name for Data Definition Language, which
deals with database schemas and descriptions, of how the data
should reside in the database.
CREATE: to create a database and its objects.
ALTER: alters the structure of the existing database
DROP: delete objects from the database
Data Manipulation Language (DML)
+DML is the short name for Data Manipulation Language which
deals with data manipulation and includes most common SQL
statements such SELECT, INSERT, UPDATE, DELETE, etc.,
SELECT: retrieve data from a database
INSERT: insert data into a table
UPDATE: updates existing data within a table
DELETE: Delete all records from a database table
Data Control Language (DCL)
+DCL is short for Data Control Language which acts as an
access specifier to the database.
GRANT: grant permissions to the user.
REVOKE: revoke permissions to the user.
Benefits of using DBMS
+No data redundancy:
+Data inconsistency can be avoided
+Secured and sharable data
Relational Database Model(RDBMS)
+ In Relational Data Model, the data is organized into talbes(i.e., Rows and Columns)
Relation: A table is also called as Relation. Name of a table.
Table: Student --------Relation
Roll no Name Class

1 Raju X
2 Vinay X
3 Rani X

Tuple/Row/Record:
Rows of1 relations are called as Tuple.
Raju X
Attribute/Columns/Fields:
Columns of relations are called as attributes.
Roll no Name Class

Fields
Date Items:
The value stored in a relation are called Data Items.
1 Raju X

Data Items
Degree of a Relations:
Number of Columns / Attributes in a Relation.
Degree = 3
Cardinality of Relation:
Number of Rows / Tuples in a Relation.
cardinality=3
KEYS
1. Primary key:
• A primary key is a set of one or more attributes that uniquely
identify tuples with in a relation. It should not be NOT NULL and
Unique. Primary Key

Roll No Adm_no Name Stream

1 SR0001 Raju Medical
2 SR0002 Ajay Engineering
3 SR0003 Venu Medical
4 SR0004 Gopal Engineering
2. Candidate Key:
• The columns which are able to become primary key are called
Candidate key.
• It should be NOT NULL and Unique.
• It does not have a duplicate values.

Roll No Adm_no Name Stream

1 SR0001 Raju Medical
2 SR0002 Ajay Engineering
3 SR0003 Venu Medical
4 SR0004 Gopal Engineering

Candidate Key
3. Alternate Key:
• The candidate key which is not primary key is called as
Alternate key.
4. Foreign Key:
• A foreign key is used to represent the relationship between two
tables.
• A non-key attributes, whose values are derived from the
primary key of some other table is known as foreign key in
current table
 Table: Student Table: Stream
Roll No Name Stream_code Stream_code Stream_Name
1 Ajay S101 S101 Humanities
2 Raju S102 S102 Commerce
3 Gopal S103 S103 Medical

Foreign key Primary key

MY SQL COMMANDS
1. Create Database:
This command is used to create your own database.
Syntax: Create database DatabaseName;
2. Show:
This command shows all the databases created already.
Syntax: show databases;
3. Use Database:
This command is used to access the specific database.
Syntax: Use DatabaseName;
4. Show tables:
This command is used to display all the tables for the selected database.
Syntax: Show tables;
5. Create Table:
This command is used to create a table in a selected database.
Syntax: Create table TableName(
ColumnName Datatype,
ColumnName Datatype,
………..);
Data Types in MYSQL:
1. Numeric: int, Bigint, Float,Double
2. String: Char, Varchar,Enum
3. Data/Time: Date, Time, Datetime
6. Desc Command:
This command is used to see the structure of a table.
Syntax: desc TableName;
7. Insert into command:
This command is used to insert the rows in a table.
Syntax: insert into TableName values(Value1, Value2,….ValueN);

Note: Data values are in the same order as the column names in
a table.
8. Select Command:
Select command is used to retrieve a subset of rows or columns
from one or more tables.
Syntax: select * from <TableName>;

Note: the Asterisk(*) can be substituted to display all columns.