Cs474 Relational Model

Front 1

Relational Database Management System (RDBMS) is a system which:

Back 1

1. data represented as collection of tables (relations)
2. tables are logical structures not physical ones.
3. The operators at the user level can generate new tables from old ones.

Front 2

Columns are called:

Back 2

Attributes

Front 3

Rows are called:

Back 3

Tuples

Front 4

Mathematical relations and database relations not are different. (True/False)

Back 4

False

Front 5

What does the domain define?

Back 5

The nature of the values that can appear in each column of the relation.

Front 6

The degree of a relation refers to what?

Back 6

The number of attributes it has.

Front 7

The cardinality a relation refers to what?

Back 7

The number of tuples it has.

Front 8

a primary key is:

Back 8

a unique identifier for a relation

Front 9

keys are based off of their semantic. (True/False)

Back 9

True

Front 10

Why are primary keys important

Back 10

The only way a RDMS system can guarantee retrieving any individual tuple is via the combination (R,k), where R is the name of the relation and k is the primary key value of the tuple.

Front 11

Foreign keys reference the relation with the primary key. (T/F)

Back 11

True

Front 12

What command is used to add new tuples into a table?

Back 12

the INSERT INTO command.

INSERT INTO employee (last_name, first_name, id, home_phone, office_phone, department )
VALUES (‘Davidson’, ‘Jane’, ‘320’, ‘433-2071’, NULL, NULL);

Front 13

What type of operator is the SELECT command?

Back 13

unary. it applies to only one relation and evaluates all tuples that are in it.

Front 14

What does the PROJECT operator do?

Back 14

produces a new relation which is a “vertical view” of the original relation. This operator extracts values from specified columns of a table with no duplicates.

Front 15

What is the JOIN command used for?

Back 15

It allows the processing of multi-table queries and the exercising of data relationships in a database.

Front 16

SQL uses the SELECT statement for all of its queries? (T/F)

Back 16

True

Front 17

Data Retrieval Statements

Back 17

SELECT

Front 18

Data Definition Keywords

Back 18

CREATE
ALTER
DROP

Front 19

Data Manipulation Statement

Back 19

INSERT
UPDATE
DELETE
COMMIT
ROLLBACK

Front 20

Are SQL*Plus commands stored int the SQL buffer?

Back 20

No

Front 21

Execution Commands

Back 21

RUN
EXECUTE
LIST

Front 22

Environment Commands

Back 22

SET
SHOW
PAUSE

Front 23

Edit Commands

Back 23

APPEND
CHANGE
DEL
INPUT
EDIT

Front 24

File Manipulation Commands

Back 24

SAVE
GET
SPOOL
@ / RUN

Front 25

Interactive Commands

Back 25

DEFINE
PROMPT
ACCEPT
PRINT

Front 26

What is the syntax of a SELECT statement?

Back 26

SELECT {Attribute | *} [, Attribute]
FROM {Table}
[WHERE {condition}];

Front 27

SELECT DISTINCT

Back 27

used to select distinct tuples.

Front 28

How is the JOIN operation completed?

Back 28

JOIN operations are implemented using variations of the SELECT statement.

equi-join example
SELECT *
FROM staff, department
WHERE staff.dept = department.dept ;

Front 29

How do you implement the LIKE command to find similar tuples?

Back 29

SELECT *
FROM staff, department
WHERE staff.dept = department.dept
AND etitle LIKE 'EVAN%' ;

Front 30

What is the “worst case” of all possible joins.

Back 30

CROSS PRODUCT

Front 31

What must be true for both tables in order to preform a UNION or MINUS between their two SELECT statements?

Back 31

each table must have the same number of columns and data types.

SELECT dept
FROM staff
UNION / MINUS
SELECT fdept
FROM faculty;

Front 32

How do you create a table?

Back 32

CREATE TABLE table_name
( attribute datatype
.
.
CONSTRAINT <constraint_name> PRIMPARY KEY (attribute)
);

Front 33

Why name a constraint?

Back 33

it is easier to drop them without having to ALTER the table.

Front 34

What is a CHECK CONSTRAINT?

Back 34

It makes sure that an attribute has desired values.

CONSTRAINT <constraint_name>
CHECK (attribute IN ('<accepted_value>', ...))

Front 35

How do you name a primary key when defining the attribute?

Back 35

model_name VARCHAR2(8) CONSTRAINT models_model_name_pk PRIMARY KEY,

Front 36

How do you add a foreign key using the ALTER command?

Back 36

ALTER TABLE cars
ADD CONSTRAINT cars_model_name_fk FOREIGN KEY (model_name)
REFERENCES models (model_name);

Front 37

What does DESCRIBE table_name; do?

Back 37

It shows the name, null value, and type of each attribute.

Front 38

How do you add a new tuple to a table?

Back 38

INSERT INTO <table_name> [(<attribute_list>)] VALUES (<attribute_values>)

Front 39

What does &<value_id> do when using the INSERT VALUES command?

Back 39

It prompts the user to:
"Enter value for <value_id>:

Front 40

What is the COMMIT command used for?

Back 40

To make the changes to the database permanent.

Front 41

What does the CASCADE CONSTRAINTS command do when dropping a table?

Back 41

It will drop any constraints associated with the table (including foreign keys).

Front 42

How do you edit an attributes datatype?

Back 42

Using the MODIFY command:

ALTER TABLE employee
MODIFY (last_name VARCHAR(30));

Front 43

How do you give a new value to an attribute in a given tuple?

Back 43

UPDATE cars SET miles_to_date = 67456
WHERE registration = ‘H286 MHU’;