Test #1

Question 1

CRUD

Answer 1

Create, Read, Update, Delete

Question 2

Analysis

Answer 2

Read world data needs -> conceptual model (ER model)

Question 3

Design

Answer 3

ER model -> logical model (relational model)

Question 4

For implementation with a specific DBMS

Answer 4

logical model -> physical model

Question 5

Primary key

Answer 5

Primary (naturally belongs) spot where specific data is held (ex: prof id in prof info)

Question 6

Foreign key

Answer 6

same field in another table (ex: prof id in class info)

Question 7

Association between records in diff tables are kept:

Answer 7

FK -> PK

Question 8

SQL

Answer 8

Declarative language, NOT procedural

Question 9

SQL sub languages

Answer 9

Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL)

Question 10

Data definition language (DDL)

Answer 10

used to define database objects

Question 11

Data manipulation langage (DML)

Answer 11

Used to support CRUD operations

Question 12

Data Control Language (DCL)

Answer 12

used to control access to data stored in the database

Question 13

relation

Answer 13

set of tuples

Question 14

tuple

Answer 14

row with fields

Question 15

Why use multiple tables?

Answer 15

reduce redundancy
store one piece of info in only one place

Question 16

Normal Forms

Answer 16

“good” forms that can reduce redundancy in tables/relations
- first normal form (1NF)
- second normal form (2NF)
- third normal form (3NF)

Question 17

Normalization

Answer 17

• into 1NF: split into divisible parts
• into 3NF: split table into three narrower ones

Question 18

anomalies

Answer 18

update, insertion, and deletion

Question 19

first normal form (1NF)

Answer 19

atomic fields

Question 20

second normal form

Answer 20

has a key

Question 21

third normal form

Answer 21

non-key attributes intransitively depend on the key

Question 22

Cross join

Answer 22

simply generate all possible combinations, most of them are bogus though

Question 23

“joining condition”

Answer 23

goes in the where clause
usually FK = PK

Question 24

problem with joining condition

Answer 24

its mixed with normal condition

Question 25

common mistakes in joining tables

Answer 25

intermediate tables not included

Question 26

Inner join

Answer 26

returns rows in two tables matched in certain ways

Question 27

Outer join

Answer 27

extends the result set of an inner join by adding unmatched rows in either or both of the tables

Question 28

equijoins

Answer 28

FK = PK, involving two tables

Question 29

non-equijoins

Answer 29

matching rows using non-equality conditions ex: BETWEEN _ & _, instead of =

Question 30

Self-joins

Answer 30

two copies of the same table will be joined

Question 31

COUNT ( )

Answer 31

counts all entries in a table/group

Question 32

GROUP BY clause

Answer 32

normal terms listed together with aggregate terms

Question 33

HAVING clause

Answer 33

may use aggregate terms and constants only

Question 34

aggregate functions in relational algebra

Answer 34

use the G operator ex. Gavg

Question 35

selection RA

Answer 35

WHERE clause : σ ex: σState="GA"(ΠVendorName, City, State(Vendors))

Question 36

project RA

Answer 36

SELECT clause : Π ex: ΠVendorName, City, State(Vendors)

Question 37

Cartesian Product

Answer 37

X ex: Vendors X Invoices

Question 38

rename RA

Answer 38

ρ ex: ρV(Vendors) JOIN V.VendorID = I.VendorID ρI(Invoices

Question 39

Assignment RA

Answer 39

->