Exam Prep

Question 1

analysis phase

Answer 1

specifies database requirements represented as entities
AKA conceptual design, entity-relationship modeling

Question 2

logical design

Answer 2

implements database requirements by converting entities, relationships and attributes into tables, keys and columns

Question 3

physical design

Answer 3

adds indexes and specifies how tables are organized on storage media

Question 4

MySQL Command-line client

Answer 4

text interface in MySQL Server
- returns error code

Question 5

DDL (data definition language)

Answer 5

defines structure of database

Question 6

DQL (data query language)

Answer 6

retrieves data from database

Question 7

DML (data manipulation)

Answer 7

manipulates data stored in database

Question 8

DCL (data control)

Answer 8

controls database user access

Question 9

DTL (data transaction)

Answer 9

manages database transactions

Question 10

Drop table

Answer 10

deletes a table and its rows

Question 11

Update statement

Answer 11

modifies existing rows by using SET clause and optional WHERE clause

Question 12

Truncate

Answer 12

deletes all rows from a table
- like delete statement without WHERE clause

Question 13

auto-increment column

Answer 13

numeric column that is assigned an automatically incrementing value when a new row is inserted

Question 14

RESTRICT

Answer 14

rejects an insert, update, or delete that violates referential integrity

Question 15

CASCADE

Answer 15

propagates primary key changes to foreign keys

Question 16

TRIM

Answer 16

returns string s without leading and trailing spaces

Question 17

INNER JOIN

Answer 17

selects only matching left and right table rows

Question 18

FULL JOIN

Answer 18

selects all left and right table rows, regardless of match

Question 19

LEFT JOIN

Answer 19

selects all left table rows, but only matching right table rows

Question 20

RIGHT JOIN

Answer 20

selects all right table rows, but only matching left table rows

Question 21

OUTER JOIN

Answer 21

any join that selects unmatched rows

Question 22

UNION

Answer 22

combines two results into one table

Question 23

EQUIJOIN

Answer 23

compares columns of two tables with the = operator

Question 24

CROSS-JOIN

Answer 24

combines 2 tables without comparing columns

Question 25

subquery

Answer 25

AKA nested query/inner query - query within a query

Question 26

materialized view

Answer 26

view for which data is stored at all times, must be refreshed

Question 27

WITH CHECK

Answer 27

database rejects inserts and updates that do not satisfy the view query WHERE clause

Question 28

Analysis steps

Answer 28

1. discover entities, relationships, and attributes 2, determine cardinality 3. distinguish strong and weak entities 4, create supertype and subtype entities

Question 29

logical design steps

Answer 29

1. implement entities 2. implement relationships 3. implement attributes 4. apply normal form

Question 30

IsA relationship

Answer 30

identifying relationship

Question 31

partition

Answer 31

is a group of mutually exclusive subtype entities

Question 32

candidate key

Answer 32

simple or composite column that is unique and minimal

Question 33

third normal form

Answer 33

whenever non-key column A depends on column B, then B is unique

Question 34

Boyce-codd normal form

Answer 34

whenever column A depends on column B, then B is unique - GOLD STANDARD - ideal for tables with frequent inserts, updates, and deletes

Question 35

trivial dependency

Answer 35

when columns of A are subset of columns of B, A always depends on B

Question 36

normalization

Answer 36

eliminates redundancy by decomposing table into 2 or more tables

Question 37

heap table

Answer 37

no order imposed on rows - fast for bulk

Question 38

hash table

Answer 38

rows assigned to buckets

Question 39

bucket

Answer 39

block or group of blocks containing rows

Question 40

hit ratio

Answer 40

AKA filter factor/selectivity - percentage of table rows selected by query

Question 41

dense index

Answer 41

contains an entry for every table rows

Question 42

sparse index

Answer 42

contains entry for every table block

Question 43

bitmap index

Answer 43

grid of bits, index contains ones and zeros

Question 44

tablespace

Answer 44

database object that maps one or more tables to a single file