Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database Design > Quiz 2.1 > Flashcards

Quiz 2.1 Flashcards

1
Q

give the formal name for the following practical components of the relational model - Table

A

Relation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

give the formal name for the following practical components of the relational model Column

A

Attribute

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

give the formal name for the following practical components of the relational model row

A

Tuple

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Includes a name, data type, format, and in some special cases a unit of measurement is referred to as the logical definition of this.

A

Domain

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

For questions 5 & 6 please refer to the following denotation of the relation schema of R…
R(A1, A2, …, An)
What is “R” referred to as?

A

Relation name (Relation Schema)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

For questions 5 & 6 please refer to the following denotation of the relation schema of R…
R(A1, A2, …, An)
What is “A1, A2, …, An” referred to as?

A

Ordered list of attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the degree (or arity) of the following relation schema for STUDENT?
STUDENT(Ssn, Name, DateOfBirth)

A

3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which of the following describes the relation schema in general; Relatively Static or Constantly Changing?

A

Relatively Static

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

For questions 9 & 10 please refer to the following denotation of the relation state r of R…
r(R) = { t1, t2, …, tm }
t = < v1, v2, …, vn >
What is “{t1, t2, …, tm}” referred to as?

A

List of tuples

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

For questions 9 & 10 please refer to the following denotation of the relation state r of R…
r(R) = { t1, t2, …, tm }
t = < v1, v2, …, vn >

What must be true about the value v1 in terms of the dom(A1)?

A

It must be in the domain of A1.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Which of the following describes the relation state in general; Relatively Static or Constantly Changing?

A

Constantly changing

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Give one downside of NULL values and the relational model regarding arithmetic aggregations and comparisons.

A

If we perform a query that compares the values of 2 tuples that are both null, then weird things happen: ie null is not necessarily = to null

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

State the key difference between the entity relationship and relational models in terms of how entities and relationships are represented in each (explain your answer).

A

In entity relationship model (entities, relationships), these are just relations in the relational model.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What assumption states that only true facts about the universe are present within the extension (state) of the relation(s)?

A

The closed world assumption.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Also known as model-based constraints, and are characterized by being inherent in the data model?

A

Implicit constraints.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Also known as explicit constraints, and can be directly expressed in the schemas of the data model, typically by specifying them in the data definition language (DDL)?

A

Schema-based

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Also known as semantic constraints, and cannot be directly expressed in the schemas of the data model, hence they must be expressed and enforced by the application programs or in some other way?

A

Business rules

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

This type of schema-based constraint specifies that within each tuple t, the value v of each attribute A must be an atomic value from domain dom(A).

A

Domain constraint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

This type of constraint can also be classified as what type of constraint (hint: It’s not schema-based)?

A

Model based

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

A subset of attributes S such that for tuples t1 and t2 the following invariant holds: t1[S] ≠ t2[S] is referred to as what?

A

Superkey

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

A subset of attributes S such that for tuples t1 and t2 the following invariant holds: t1[S] ≠ t2[S] is referred to as what?

This must always be true for which subset of attributes (according to the relational model)?

A

All the attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

True or false, the set of attributes constituting a key is an attribute of the relation state r(R)?

A

False, of the schema.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

When a relation schema has more than one key, one must be chosen to uniquely identify tuples. This is referred to as what special kind of key?

A

Primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

A relational database schema S is a set of relation schemas S = { R1, R2, …, Rm } and what else?

A

Set of integrity constraints IC.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

The constraint stating that no primary key values can have NULL values is referred to as what?

A

Entity integrity

26
Q

These kinds of constraints typically arise from the relationships among entities represented by the relation schemas.

A

Referential Integrity Constraints

27
Q

REVIEW PAGE 4 of CHPT 5 Questions

A
28
Q

What does SQL stand for?

A

Structured Query Language

29
Q

What type(s) of DBMS language(s) would SQL be considered (from Chapter 2, pages 39 – 40 in the textbook)?

A

DML and DDL

30
Q

What is the advantage of using a standardized language such as SQL to define and manipulate our database(s) within the context of a relational DBMS?

A

It’s portable i.e., the base implementation must be implemented by the RDBMS vendor.

31
Q

What is a schema?

A

Collection of tables, and other things (types, domains, constraints, authorization grants, views, etc…) generally 1:1 with database applications i.e., your API’s.

32
Q

What are the two required components for creating a schema in SQL?

A

CREATE SCHEMA X AUTHORIZATION Y – DDL (Schema name, authorization identifier)

33
Q

What is the default schema, and what does it stand for?

A

dbo (Database owner)

34
Q

What is a collection of schemas referred to as?

A

Catalog, referred to as the database.

35
Q

What is the name of the special schema that includes information on all the schemas of the database and all the element descriptors of those schemas?

A

INFORMATION_SCHEMA

36
Q

CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);

  1. CONSTRAINT PK_STUDENT_SSN
A

C

37
Q

CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
10. CHAR(9)

A

B

38
Q

CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
PRIMARY KEY

A

D

39
Q

CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
Ssn

A

A

40
Q

CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);

What types of constraints can be specified directly within the CREATE table statement from above?

A

All the constraints within the schema-based constraints

41
Q

Modify the DDL statement from questions 9 – 12 to create the STUDENT table explicitly within the UNIVERSITY schema instead of the implicitly the default defined for the current database user.

A

I think the UNIVERSITY. does this already

42
Q

The STUDENT table would be considered one of these types, which along with its attributes is always created and stored as a file by the DBMS?

A

Base tables (relations)

43
Q

Describe the main difference between the two main character string datatypes, CHAR and VARCHAR.

A

CHAR – padded with blank spaces.
VARCHAR – not padded with blank space.
CHAR(10), VARCHAR(10)
‘NATE ’ VS. ‘NATE’

44
Q

DECIMAL( i, j )

  1. What is i (i.e., give its name, and describe what it means)?
A

Precision – total number of digits

45
Q

DECIMAL( i, j )
What is j (i.e., give its name, and describe what it means)?

A

Scale – number of digits after the decimal

46
Q
  1. What is the smallest “i” we can specify to represent the value 1.1?
A

2

47
Q
  1. What is the smallest “j” we can specify to represent the value 1.1?
A

1

48
Q
  1. True or false, the default value is included in any new tuple if an explicit value is not provided for that attribute?
A

True*

49
Q
  1. The UNIQUE keyword specifies alternate unique keys known as what?
A

Candidate keys

50
Q
  1. What is the default referential triggered action when referential integrity is violated (when tuples are updated, or deleted)?
A

RESTRICT (CASCADE, SET NULL, SET DEFAULT) – qualified with ON DELETE or ON UPDATE

51
Q
  1. What are the other referential triggered action(s), in general when would or wouldn’t you use each one?
A

I got this

52
Q

SELECT <attribute>
FROM <table list>
WHERE <condition>;</condition></attribute>

  1. Compared to the comparison operators in Java, those in SQL which are used in the <condition> after the WHERE keyword, are the same except for which operator(s) (give the operator(s) in Java and SQL)?</condition>
A

<> is != (= or ==)
Use BETWEEN instead of > X and <= Y

53
Q
  1. Describe the difference between a selection condition and a join condition (give an example).
A

Selection condition attributename = ‘’, join condition attributename = someotherattributename;

54
Q
  1. True or false, in SQL the same name can be used for two (or more) attributes if the attributes are in different tables?
A

True

55
Q
  1. Renaming tables in the FROM clause to avoid repeated typing of long tables is referred to as what?
A

Aliasing

56
Q
  1. To retrieve all the attribute values of the selected tuples, we do not have to list the attribute names explicitly in SQL; we just specify this, which stands for all the attributes?
A

, relationname.

57
Q
  1. This optional keyword can be added after the SELECT keyword and preserves the function of the query.
A

SELECT ALL = SELECT

58
Q
  1. What’s the difference between “%” and “_” in terms of use in the WHERE <condition>?</condition>
A

% multiple chars, _ single char

59
Q
  1. What is the default ordering when using the ORDER BY clause without specifying explicitly what the ordering should be?
A

ASC Other is DESC

60
Q
  1. Explain two of the different ways we can INSERT tuples into a relation.
A

Insert INTO UNIVERSITY.COURSE VALUES (‘X’, 242, 3, ‘Y’)

OR

Insert INTO UNIVERSITY.COURSE (Crn, Credits) VALUES (‘X’, 3)

61
Q
  1. True or false, zero, one or many tuples may be deleted by a single DELETE command?
A

TRUE
DELETE FROM UNIVERSITY.COURSE
WHERE 1 = 1;

62
Q
  1. True or false, I can update multiple relations with a single UPDATE command?
A

FALSE

Database Design (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions