Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/39

Click to flip

39 Cards in this Set

  • Front
  • Back

What are the 4 components of the database system?

1. Users


2. Database Application


3. DBMS


4. Database

Referential Integrity Constraints

A column from a database can reference to the primary key of another table.


DBMS enforces this constraint.

Integrated tables

Tables that store both data & relationships among the data

Normalization

Determining how to effectively design the database (Should this data be in 1 table, or split into two?)



Data Model

The blueprint of the database process

Database design

The construction of the database in DBMS

Database migration

Adapting the database to new or changing requirements (deleting relationships, editing formats, etc)

Who is E F Codd?

Formed the concepts for normalization in relational databases.




Organized by principles based on identified relations between various kinds of data. (keys)

Database schema

Complete logical view of the database, containing all the tables, all columns in each table, primary key, and foreign keys.




ORDER(OrderNumber, SKU, Quantity)

Data Sublanguage

The SQL language - it only refers to the statements needed for creating/processing data & metadata

Data Definition Language (DDL)

statements used for creating tables, relationships, and other structures

Data Manipulation Language (DML)

statements used for querying, inserting, modifying, and deleting data

SQL/Persistent Stored Modules (SQL/PSM)

statements that extend SQL by adding procedural programming capabilities within the SQL framework (flow of control, variables, etc)

Transaction Control Language (TCL)

statements used to marked boundaries and control transaction behavior

Data Control Language (DCL)

statements used to grant and/or revoke permissions to users and groups

SQL SELECT...

specifies which columns are listed in the query

SQL FROM...

specifies which tables are listed in the query

SQL WERE...

specifies which rows are to be listed in the query, can be narrowed & also compounded into AND, OR operators




narrowed:


SELECT *FROM SKU_DATA


WHERE Department = "Water Sports"


AND Buyer = 'Nancy Meyers'


OR Buyer = 'Joe Smith'

SQL SELECT DISTINCT...

specifies to remove all duplicate rows of data

SQL ORDER BY...

specifies which order to display the rows (defaults to ascending)




can specify further with:




ORDER BY Price DESC, OrderNo ASC

SQL WHERE IN/NOT IN...

To specify multiple values or exclude multiple values




SELECT *


FROM SKU_DATA


WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin')




SELECT *FROM SKU_DATA


WHERE Buyer NOT IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin')

SQL Wildcard

% - Can be used to find values not 100% specified




WHERE Buyer LIKE 'Pete%'

SQL Underscore Wildcard

specifies a single, unspecified character




WHERE SKU LIKE '%2_ _'

SUM, AVG, MIN, MAX, COUNT (AS)

Mathematical functions used in the SELECT operator, AS can rename that column




SELECT SUM(OrderTotal) AS OrderSum

SQL JOIN...

specifies where to join tables in the FROM operator

Functional Dependencies

where 1+ attributes determines the value of another attribute




ObjectColor --> Weight


ObjectColor --> Shape




ObjectColor--> (Weight, Shape)

Key

A combination of one or more columns that is used to identify a particular row in a relation

Composite Key

keys that have two or more columns

Candidate key

determines all of the other columns in a relation

Primary key

the primary means for finding rows in tables in the DBMS

Surrogate keys

added to the table to serve as the primary key

Foreign key

the primary key of a table other than the one which its assigned to

Overlapping candidate keys

keys that share the same attribute (StudentID)

Multi-valued dependency

when a determinant is matched with a set of values




Name --> --> Sibling

SQL TOP...

Used in the SELECT portion to grab the top number of rows




SELECT TOP 5 *


FROM SKU_DATA;

SQL IS NULL...

to return any Null values

Entity

an identifiable thing that users want to track


(Sales, Orders, Customers, etc)

Composite determinant

Where a determinant has more than one attribute




(StudentName, ClassName) --> (Grade)




StudentName -x-> Grade


ClassName -x-> Grade

Inconsistent Values

when different users data is not standardized




DrinkName


Coffee


Cofee


Coffeee