COSC 301 Midterm 1

Explain how to create and use macros and a macro recorder

1. To add a development tab go to File, Options, Customize Ribbon 2. To record a macro go to View and select "Record Macro" button

Write an if statement to print "Pass" if a value in C is >= 20 AND a value in column D is >= 30. Otherwise print "fail"

=IF((AND(C2 >=20, D2=30)), "PASS", "FAIL")

Write a condition to highlight all the odd numbers

=ISODD(B4)

Write a conditional statement that highlights Apple, Lime, and Kiwi out of the set

=OR(B4="apple",B4="kiwi",B4="lime")

Write a function to add together row 3 to 17 in column be and a function to find the median of column A

=SUM(B3:B17) =MEDIAN(A3:A17)

Database

A collection of logically related data for a particular domain

Computer

A device that can be programmed to solve problems

Memory

A device which allows the computer to store data either temporarily or permanently

Class

A generic template (blueprint) for creating an object

Cloud

A network of distributed computers on the internet that provides storage applications

What does a data analyst do?

A person who uses tools and applications to transform raw data into a form that will be useful

Degree

A relation is the number of attributes it contains

What is a macro?

A reordered set of actions that is saved so they can be easily executed again

Tuple

A row of a relation (single record in a table)

Domain

A set of allowable values for one or more attributes

Method

A set of statements that performs an action

Relation

A set of tuples

What is a spreadsheet and why is it useful?

A spreadsheet organizes information into a two dimensional array of cells ( a table) and we can write simple formulas to perform calculations and immediately see the results of those calculations

What is the difference between a subroutine and a function?

A subroutine is a set of commands that performs a certain task that is different from the main function.

Property

An attribute or feature of an object

Object

An instance of a class that has its own properties and methods that define what the object is and what it can do

What is the difference between analog and digital?

Analog information is translated into electric pulses of varying magnitude and digital is turned into binary information

Create an example table

CREATE TABLE emp ( Eno CHAR(5), Ename VARCHAR(30) NOT NULL Bdate DATE, Title CHAR(2), PRIMARY KEY (eno)

What is the usefulness of solver?

Determines what values need to be in multiple input cells to achieve a desired result in a formula cell

file encoding

How the bytes represent data in a file

Insert a row using the insert command

INSERT INTO emp VALUES ('E9', 'S. Smith', '1985-03-05')

Write a VBA statement that tests if the value in Range A2 is greater than 0, if so, set Range B2 equal to "Positive"

If Range("A2") Value > 0 Then Range ("B2" Value = "Positive"

What is the importance of data analytics?

It is important because society is collecting more and larger data sets all the time and it helps us learn from the data

What is a primary key and what is it used for?

It uniquely identifies a record in the table

What are some security issues with macros and how can you handle them?

People can make macros with malicious intent such as destroying your file or manipulating it in a wrong way.

Software

Programs that a computer follows to perform functions

Return all projets who have an employee working on them whose title is 'EE'

SELECT pname FROM emp, proj, workson WHERE emp.title = 'EE' AND workson.eno = emp.eno AND workson.pno = proj.pno;

Cardinality

The cardinality of a relation is the number of tuples it contains

Schema

The description of the structure of the database, refers to a visual representation or a set of rules that govern a database

Compare absolute vs relative address

The dollar sign '$' is a symbol that indicates an absolute address, By default, addresses are "relative" in the sense that if they are in a formula that is copied to another cell, they will be changed relative to where they were copied from their origin.

Big data

The immense volumes of data that inhibits the use of traditional data processing applications

What is data analysis

The processing of data to yield useful insights or knowledge

What is the usefulness of goal seek?

Used to determine what value needs to be in an input cell to achieve a desired result in a formula cell

Concatenation

When two or more things are combined by appending them in order

Free format

White space is ignored

Reserved words

Words that cannot be used as names for database fields and tables

What is the usefulness of What-If scenarios?

You are able to see the outcome of more data without changing the data

Attribute

a characteristic in a database

File

a sequence of bytes on a storage device

Formula

any expression that begins with an equal sign (=)

Why is ASCII used?

because its a method to give all computers the same language, allowing them to share documents and files

metadata

data that describes other data

text file

encoded in a character format such as ascii or unicode. These files are readable by humans

Binary file

encoded in binary

Function

ex) ROUND(), CONCATENATE(), etc..

Why is unicode used in certain situations instead of ASCII?

for other languages such as chinese where a large number of symbols represent their basic alphabet

Data size

measured in bytes

Explain the role of metadata for interpreting data

metadata is Data that describes other data, it helps you understand how to interpret and manipulate the data

Database system

software that manages the data