Business Database Management

1st normal form

eliminate repeating fields

2nd normal form

eliminate fields not dependent of Primary key

3rd normal form

eliminate transitive dependencies

transit dependency

non-key field is dependent on non-key field

Granularity

level of detail of the data

Problem with flat data and design

redundency

Only good redundent data is ______

Foreign key

Same data can be used in different user groups through________ 

input/put user data masks.

metadata

data within data

Indexes

separate files that are used to improve sorting

indexes have___

faster performance and retrieval 

DBMS

Software to create, store, organize, and retrieve data 

Different database software

Oracle, Access, My SQL, SQL server, Enterprise RDMS, DB2

Application metadata: ______

data entry forms, reports, retrieval request formulas

DB object classes: ____

tables, queries, objects, classes

Advantages of DBMS:

Program data independence, minimal data redundancy, improved data consistancy, improved data sharing, enforcement of standards, data inegrity

primary key

unique identifier field its own table, fields within its table refer to it

Alternate key

an alternative primary key that can unqiley identify tables alongside the primary key

composite/compound key

multiple primary/alternative keys working together as 1 primary key

Foreign key

PK in another table, but also a regular field in current table

entity-relationship model

method of describing how tables are connected

Cardinality

number of records allowed on each side of the relationship

Maximum cardinality

largest possible relationship

minimum cardinality

smallest possible relationship

discriminator field

‘label’ that tells you what type of record you are dealing with

CODD quote

“Every field in a table must directly depend upon the entire primary key of the table so help me CODD:”

Boyes-CODD NF

Order primary key fields from the largest aggregation group to the smallest

Leave warning to____

turn on cascading deletes

referential integrity

parent must exist before child

What table to populate first

Strongest entities

subtype

catergories within a group (cars - ford, mustang, toyota)

supertype

General category (cars)

intersection table/Associative entity

helps join tables by turning M:M to 1:M, avoids data duplication

inclusive subtype

related to one or more subtypes (strong)

exclusive subtypes

relates to one subtype at most (weak)

Recursive/unary relationship

Entity can have relationship with itself (1:1, 1:M)

ID dependent/weak entities

record cannot exist withot the parent

ID independent/strong entity

record can exist on its own

augmented database

database with AI/ML

Upadate operations:

insert operation, delete operation, modify operation

Data definition language

modifyes structure of databse (alter, create, drop)

Data manipulation language

used for fata (insert into, select, delete, update

Data contol language

used for data access control

Transaction control language

manages database transactions

SQL data types

CHAR, VCHAR, INT, NUMERIC, DATE

Other SELECT key words

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

DISTINCT

eliminates duplicates from query result

SQL aggregate functions

AVG, MIN, MAX, SUM, COUNT

Inner Join

combines rows from tables only if they match the condition (E.studentID = C.studentID)

Cartesian Join

gives every possible combo from joined tables

alter table

changes structure of relation

VIEW/virtual table

when invokes, executres query to retrieve data, used before SELECT (CREATE VIEW)

union compatible

two sets have same number of columns and data types

UNION

used to combine 2 SELECT statements by listing every row from each SELECTS result

INTERSECT

used to combine results of 2 SELECT statements byt listing same results from both tables

MINUS/DIFFERENCE

Used to combine 2 SELECTs when rows selected form one SELECT does not appear in other SELECT

Outer Join/Left Join/ Right Join

all records do not need to match to combine tables

database front-end

provides access to DB for indirect use (input and retreival), Can have menus, charts, graphs, maps

candidate key

can be used to find value of every attribute in table

ASSERTION

used for user-defined constraints (CREATE ASSERTION)

Trigger

rule activated by deletion, modification, update of data

stored procedures

stored DB steps that return no value

functions

stored DB steps that return a value

CRUD

Create, Read, update, delete

temporary SQL virtual table created from SELECT

Query result set

Common Inventory Table field acronyms 

SKU, UPC, QTY, MOQ, EOQ, ERP

SKU

Stock keeping unit

UPC

Universal produuct code

QTY

Quantity

MOQ

minimum order quantity

EOQ

Economic order quantity

ERP

Enterprise resource planning

Instance class

size and performance

FIFO

financials

entity class

a table

entity instance

a record

Associative entity

used to connect 2 M:M tables

lookup tables are used for ______ checking

reference integrity

why use subqueries

helps break down complex problems into small pieces