Lesson 2

Question 1

relational database

Answer 1

Conceptual framework for a database systems with 3 parts:
-data structures: how data is organized
-operations: that manipulate data structures
-rules: logical constraints that ensure data is valid

Question 2

set

Answer 2

unordered collection of elements enclosed in braces
Ex: {a,b,c} and {c,b,a}

Question 3

tuple

Answer 3

ordered collection of elements enclosed in parentheses

Question 4

table

Answer 4

has a name, a fixed tuple of columns, and a varying set of rows
Synonyms: Tables, File, Relation

Question 5

column

Answer 5

has a name and a data type
Synonyms: Column, Field, Attribute

Question 6

row

Answer 6

-unnamed tuple of values
-since rows are a set, rows have no inherit order
Synonyms: Row, Record, Tuple

Question 7

relational data type

Answer 7

a data type is a named set of values, from which column values are drawn

Question 8

relational algebra

Answer 8

theoretical foundation of the SQL language

Question 9

relational rules

Answer 9

-rules are logical constraints that ensure data is valid
-govern data in every relational database

Question 10

Relational Operations: SELECT

Answer 10

selects a subset of rows of a table

Question 11

Relational Operations: JOIN

Answer 11

combines two tables by comparing related columns

Question 12

Relational Operations: UNION

Answer 12

selects all rows of two tables

Question 13

Relational Operations: AGGREGATE

Answer 13

computes functions over multiple table rows, such as sum and count

Question 14

Business rules

Answer 14

based on business policy and specific to a particular database
Ex: All rows of the ‘Employee’ table must have a valid entry in the ‘DepartCode’ column

Question 15

Structured Query Language/SQL

Answer 15

-high-level computer language for storing, manipulating, and retrieving data
-standard language for relational database

Question 16

SQL statement Literals

Answer 16

Strings: must be surrounded by single or double quotes
Numeric: 123
Binary: represented with x’0’ where the 0 is any hex values

Question 17

Data Definition Language (DDL)

Answer 17

defines the structure of the database

CREATE, ALTER, DROP

Question 18

Data Manipulation Language (DML)

Answer 18

manipulates data stored in a database

INSERT, UPDATE, DELETE

Question 19

Data Query Language (DQL)

Answer 19

retrieves data from database

SELECT

Question 20

Data Control Language (DCL)

Answer 20

controls database user access

GRANT, REVOKE

Question 21

Data Transaction Language (DTL)

Answer 21

manages database transactions

SAVEPOINT, ROLLBACK, COMMIT

Question 22

table

Answer 22

has a name, fixed sequence of columns (tuples), and a varying set of rows

Question 23

table rules

Answer 23

-one value per cel
-Unknown data is represented with NULL
-no duplicate column names in the same table
-no duplicate rows
-no row order

Question 24

column

Answer 24

has a name and a data type

Question 25

row

Answer 25

unnamed sequence of values

Question 26

cell

Answer 26

single column of a single row

Question 27

empty table

Answer 27

table without rows

Question 28

ALTER TABLE

Answer 28

DDL adds, delete, or modifies

Question 29

Data types: INT

Answer 29

positive or negative integer values

Question 30

Data types: VARCHAR(N)

Answer 30

values with 0 to N characters

Question 31

Data types: DATE

Answer 31

Date values YYYY-MM-DD

Question 31

Data types: CHAR(N)

Answer 31

fixed string value

Question 32

Dat types: TIME

Answer 32

hh:mm:ss

Question 33

Data types: DATETIME

Answer 33

YYYY-MM-DD HH:MM:SS

Question 34

Data types: DECIMAL

Answer 34

numeric values of which digits follow the decimal point

Question 35

Integer Storage

Answer 35

Question 36

Operator

Answer 36

operator is a symbol that computes a value from one or more values, called operands

Question 37

Arithmetic operators

Answer 37

compute numeric values from operands

Question 38

Comparison operators

Answer 38

compute logic values TRUE or FALSE

Question 39

Logical operators

Answer 39

compute of logical values from logical operand

Question 40

Operator/Operand

Answer 40

Question 41

Operator precedence

Answer 41

Question 42

NULL

Answer 42

value that represents unknown or inapplicable data

Question 43

INSERT

Answer 43

adds rows to a table

Question 44

UPDATE

Answer 44

uses SET clause to specify new column values optional WHERE clause specifies which rows are updated

Question 45

DELETE

Answer 45

deletes existing rows in a table

Question 46

TRUNCATE

Answer 46

deletes all rows from a table

Question 47

MERGE

Answer 47

-selects data from one table, called the source, and inserts the data to another table, called the target - MySQL does not support MERGE

Question 48

Primary Keys

Answer 48

a column, or a group of columns, used to identify a row Must be: UNIQUE NOT NULL

Question 49

Simple primary key

Answer 49

consists of a single column

Question 50

Composite primary key

Answer 50

consist of multiple columns

Question 51

auto-increment

Answer 51

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

Question 52

foreign key

Answer 52

column, or group of columns, that refer to primary key

Question 53

foreign key constraint

Answer 53

when specified, database rejects insert, update, and delete statements that violate referential integrity

Question 54

fully NULL

Answer 54

simple or composite foreign key in which all columns are NULL

Question 55

Referential integrity

Answer 55

relational rule that requires foreign key values are NULL or match primary key value

Question 56

RESTRICT

Answer 56

rejects an insert update, or delete that violate referential integrity

Question 57

RESTRIC, SET NULL, SET DEFAULT

Answer 57

Applies to primary key update and delete Applies to foreign key insert and update

Question 58

CASCADE

Answer 58

propagates primary key changes to foreign keys applies to primary key update and delete only

Question 59

Constraint

Answer 59

rule that governs allowable values in a database

Question 60

UNIQUE

Answer 60

ensures that values in a column, or group of columns, are unique

Question 61

CHECK

Answer 61

-specifies an expression on one or more columns of a table. -constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL.

Question 62

SQL CONSTRAINT