Database Systems Chapter 2

CS 4210 Chapter 2

BI Systems

Business Intelligence Systems. used to support management decisions by assessing, analyzing, planning, and controlling

BI Systems usually store their data in a

Data Warehouse

The entire database isnt necessary for every analysis so we get an ____, and its put from the ___ database to the ____ database

extraction; operational database to the offline database

DDL (term and commands in it)

Data Definition Language. Used for creating tables, relationships. Makes the actual structures. Commands: CREATE, ALTER, DROP

DML (term and commands in it)

Data Manipulation Language. Inserting, Updating, and Querying data. SELECT, INSERT, UPDATE, DELETE

TCL

Transaction control language

SELECT x FROM y WHERE z

x: column y: table the column is in z: condition on the entries

Command to get the metadata

DESCRIBE

Command to add a column to a table already created

ALTER. you are changing the structure of the object so it is a DDL command

Command to control how many rows are displayed

TOP

to change a value of something in a table, use this command

UPDATE. not changing the structure so a DML

command to make a brand new table

CREATE. structure command so DDL

Command to include a new row (entry) into a table

INSERT. not changing the structure so DML

Command to remove a table from a database

DROP. changes structure so DDL

SQL underscore

for representing a single unspecified character in a specific position LIKE “_2%”

SQL % sign

any sequence of contiguous unspecified characters in a specific position in character string LIKE “Pete%’

SQL limitations

1) you cannot combine column names with sql functions (ex: SELECT Department, Count(*)

2)You cannot use table names in the WHERE function. WHERE only works for rows

Concatenation

SELECT (Buyer + ‘in’ Department) AS Sponsor (lets you make columns like a string in java

WHERE, GROUP BY, HAVING

WHERE- tells you which rows to use

GROUP BY- creates groups

HAVING- tells you which groups to use

WHERE should always go before GROUP BY and HAVING

All column names in ____ associated with a function must appear in the _____ clause

SELECT; GROUP BY

ETL System in BI Systems

Extract, Transform, and load system