Database Test 2

Front 1

Normalization

Back 1

process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihoodof data anomalies

Front 2

Denormalization

Back 2

produces a lower normal form; that is, a 3NF will be converted to a 2NF through denormalization.

Front 3

partial dependency

Back 3

exists when there is a functional dependence in which the determinant is only part of the primary key (remember the assumption that there is only one candidate key)

Front 4

transitive dependency

Back 4

exists when a non-key attribute determines other non-key attribute

Front 5

atomic attribute

Back 5

is one that cannotbe further subdivided.

Front 6

granularity

Back 6

refers to the level of detail represented by the values stored in a table's row.

Front 7

determinant

Back 7

any attribute whose value determines other values within a row

Front 8

key attribute

Back 8

An attribute that is a part of a key

Front 9

non-key attribute

Back 9

not part of any candidate key

Front 10

functional dependency

Back 10

the value of one or more attributes determines the value of one or more other attributes

Front 11

full functional dependency

Back 11

functional dependencies in which the entire collection of attributes in the determinant is necessary for the relationship

Front 12

repeating group

Back 12

a group of multiple entries of the same type can exist for any single key attribute occurrence.

Front 13

1NF

Back 13

All of the key attributes are defined. There are no repeating groups in the table. In other words, each row/column intersection contains one and only one value, not a set of values. All attributes are dependent on the primary key.

Front 14

2NF

Back 14

1NF and It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key

Front 15

3NF

Back 15

2NF and It contains no transitive dependencies

Front 16

BCNF

Back 16

every determinant in the table is a candidate key

Front 17

4NF

Back 17

3NF and no independent multivalued dependencies

Front 18

Good Normalized Tables

Back 18

Evaluate PK Assignments & Naming Conventions

Refine Attribute Atomicity, Identify New Attributes & Relationships. Refine PKs as Required for Data Granularity. Maintain Historical Accuracy.

Front 19

alias

Back 19

is an alternate name given to a column or table in anySQL statement.

Front 20

inner query

Back 20

A subquery, also known as a nested query or an inner query, is a query that is embedded (or nested) inside another query.

Front 21

outer query

Back 21

executed last, consists of outer most sections of sq

Front 22

recursive query

Back 22

A nested query that joins a table to itself

Front 23

schema

Back 23

A logical grouping of database objects, such astables, indexes, views, and queries, that are related to eachother. Usually, a schema belongs to a single user orapplication.

Front 24

COUNT

Back 24

Returns the number of rows with non-null values for a given column

Front 25

DISTINCT

Back 25

Limits values to unique values

Front 26

SQL Create a Table

Back 26

CREATE TABLE EMP_1 (EMP_NUMCHAR(3) NOT NULL,EMP_LNAMEVARCHAR(15) NOT NULL, PRIMARYKEY (EMP_NUM),INDEX (JOB_CODE),FOREIGNKEY (JOB_CODE) REFERENCES JOB(JOB_CODE),CONSTRAINTC_JOBCODE CHECK (JOB_CODE IN("500","501","502")));

Front 27

SELECT CONCAT

Back 27

SELECT CONCAT(EMP_FNAME, ' ', EMP_INITIAL, ' ', EMP_LNAME) AS EMP_NAME, JOB_CODE FROM EMP_1 ORDER BY EMP_LNAME;

Front 28

SELECT COUNT AND ALIAS

Back 28

SELECT JOB_DESCRIPTION, COUNT(*) AS NUM_EMPLOYEES FROM JOB,EMP_1 WHERE JOB.JOB_CODE = EMP_1.JOB_CODE GROUP BY (JOB_DESCRIPTION) ORDER BY JOB_DESCRIPTION;

Front 29

SELECT AGGREGATE AND ROUND

Back 29

SELECT ROUND(SUM(CUS_BALANCE),2) AS "TOTAL BALANCE"

FROM CUSTOMER

WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE);