Brainscape's Knowledge GenomeTM

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


See full index

z Cosc 304 (Databases) > Final Review > Flashcards

Final Review Flashcards

1
Q

What is the difference between a database and database management system (DBMS)?

A

A database is the data stored and a DBMS is the software that manages that data.

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

What does DML stand for and what is the standard?

A

DML stands for Data Manipulation Language, and the standard is SQL (structured query language)

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

Draw the ANSI / SPARC Architecture

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

Describe the Internal View

A

The physical representation of the database on the computer, that is, *how* the data is stored.

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

Describe the Conceptual View.

A

The logical structure of the database, that is, *what* data is stored and its relationships.

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

Describe the External View.

A

The *user’s view* of the database that provides the part of the database relevant to the user.

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

What are the three levels of the ANSI / SPARC architecture starting with the view closest to the user?

A

Dexternal, Conceptual, Physical (Internal)

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

A relation is a _______ with columns and rows.

A

A relation is a table with columns and rows.

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

A _______ is a named column of a relation.

A

An attribute is a named column of a relation.

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

A _______ is a row of a relation.

A

A tuple is a row of a relation

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

A _______ is a set of allowable values for one or more attributes.

A

domain

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

The _______ of a relation is the number of tuples it contains.

A

cardinality

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

The _______ of a relation is t he number of attributes it contains.

A

degree

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

The _______ of a relation is the structure of the relations including its domains.

A

intension

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

The _______ of a relation is the set of tuples currently in the relation.

A

extension

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

Define:

superkey

A

A set of attributes that uniquely identifies a tuple in a relation.

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

Define:

key

A

A *minimal* set of attributes that uniquely identifies a tuple in a relation

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

Define:

candidate key

A

One of the possible *keys* (NOT superkeys) of a relation

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

Define:

primary key

A

The candidate key designated as the distinguishing key of a relation

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

Define:

foreign key

A

A set of attributes in one relation referring to the primary key of another relation.

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

True or false:

A key is always a superkey.

A

True

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

True or false:

It is possible to have more than one key for a table and the keys may have different numbers of attributes.

A

True, as a key is a *minimal* number of attributes, though not necessarily a *minimum*. Thus, a student could be identified by student number, or by first & last name.

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

Define:

domain constraint

A

Every value for an attribute must be an element of the attribute’s domain or null

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

Define:

entity integrity constraint

A

No attribute of a primary key can be null

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

Define:

referential integrity constraint

A

If a foreign key exists in a relation, then the foreign key value must match a primary key value in the referenced relation or be null

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

R is a relation with 10 rows and 5 columns. S is a relation with 8 rows and 3 columns. What is the degree and cardinality of the Cartesian product?

A

degree = 8

cardinality = 80

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

Select *one* valid SQL identifier:

a) 23test
b) ‘fred’
c) test_!
d) field_
e) from

A

d) field_

Because:

a) starts with number
b) & c) have special chars
e) is a reserved word

28
Q

How can you map from SQL to relational algebra?

A
29
Q

True or false:

In SQL queries, it is possible to have two or more identical rows in a relation.

A

True. In relational algebra it doesn’t work because relations are sets, but relations in SQL are bags, and thus duplicates are possible (in queries)

30
Q

How can you remove duplicates from an SQL query?

A

With the DISTINCT clause.

e.g.

SELECT DISTINCT title

FROM emp

31
Q

When are you guaranteed to have unique rows in an SQL query (without using DISTINCT)?

A

When you’re querying a primary key

32
Q

What are the three phases of database design?

A

Conceptual, logica, and physical

33
Q

Explain the conceptual step of database design.

A

This is a high level of data abstraction (e.g. UML); you are organising info without choosing a data model or DBMS.

34
Q

Explain the logical step of database design.

A

This is where you organise the information into/with a data model (e.g. SQL).

35
Q

Explain the physical step of database design.

A

This is the selection of a DBMS (e.g. MySQL)

36
Q

In ER modelling, what is the degree of a relationship?

A

The number of entity types participating in the relationship.

37
Q

True or False:

A composite key consists of two or more attributes.

A

True

38
Q

True or False:

Relationships are generally names with singular verbs.

A

True

39
Q

True or False:

The degree of a relationship is the number of attributes it has.

A

False. The number of entities.

40
Q

True or False:

Composite attributes are also called atomic attributes.

A

False. Atomic attributes are simple attributes, that is, attributes that cannot be broken down into smaller parts.

41
Q

True or False:

A participation constraint is the maximum number of times an entity participates in a relationship.

A

False. That’s cardinality. Participation is the *minimum* number of times an entity participates in a relationship.

42
Q

True or False:

Relationships are represented using a directed edge (with arrows)

A

False. Those are inheritances.

43
Q

True or False:

An entity is typically named using a singular verb.

A

False. A singular noun.

44
Q

True or False:

It is not possible to have a relationship of degree one.

A

True. For recursive relationships, the degree is two, as the entity participates twice.

45
Q

True or False:

A diamond is used to represent a relationship of degree larger than 2.

A

True

46
Q

True or False:

A derived attribute is an attribute who value is calculated from other attributes and then physically stored in the database.

A

False. The value is *not* physically stored.

47
Q

True or False:

Attributes are properties of eith entities or relationships.

A

True

48
Q

True or False:

An attribute cannot be multi-valued.

A

False. For example, the telephone attribute for a person may be multi-valued, as people often have multiple telephone numbers.

49
Q

True or False:

A composite attribute contains two or more components.

A

True.

50
Q

True or False:

It is possible to have two candidate keys with different numbers of attributes.

A

True

51
Q

True or False:

In ER modeling, relationship has a primary key.

A

False. The concept of a primay key does not apply to a relationship in ER modeling, only an entity.

52
Q

True or False:

A weak entity has its own primary key.

A

False. Weak entities are dependent upon other entities, and thus only have partial primary keys.

53
Q

True or False:

A weka entity must be associated with (identified by) a strong entity.

A

True.

54
Q

True or False:

A weak entity can have a relationshpi with another entity besides its identifying strong entity.

A

True

55
Q

True or False:

Generalization is a bottum-up process.

A

True.

56
Q

True or False:

In a UML diagram, the inheritance arrow points towards the superclass.

A

True.

57
Q

What are the two possible choices for the participation constraint in superclasses?

A

Mandatory

Optional

58
Q

What are the two possible choices for the disjoint constraint in superclasses?

A

And

Or

59
Q

True or False:

If the disjoint constraint is AND, a given object can be a member of multiple subclasses.

A

True.

60
Q

True or False:

If the participation constraint is MANDATORY, a given object could be an instance of the superclass without being an instance of a subclass.

A

False. MANDATORY means that a superclass member must be a member of one of its subclasses.

61
Q

What are the steps when converting from an ER Model to a relationship schema?

A
  1. Convert strong entities.
  2. Convert weak entities.
  3. Convert 1:1 relationships
  4. Convert 1:N relationships
  5. Convert M:N relationships
  6. Convert multi-valued attributes
  7. Convert n-ary relationships
  8. Convert subclasses
62
Q

True or False:

The M:N relationship mapping rule could be applied to 1:1 and 1:N relationships, as it’s more general.

A

True.

63
Q

True or False:

A weak entity will always have primary key attributes from the identifying entity.

A

True

64
Q

True or False:

The designer has a choice on which side to put the foreign key when mapping a 1:N relationship.

A

False. You must select the N-side of the relationship as the relations containing the foreign key and relationship attributes.

65
Q
A

z Cosc 304 (Databases) (4 decks)

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