Final Review

Question 1

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

Answer 1

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

Question 2

What does DML stand for and what is the standard?

Answer 2

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

Question 3

Draw the ANSI / SPARC Architecture

Answer 3

Question 4

Describe the Internal View

Answer 4

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

Question 5

Describe the Conceptual View.

Answer 5

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

Question 6

Describe the External View.

Answer 6

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

Question 7

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

Answer 7

Dexternal, Conceptual, Physical (Internal)

Question 8

A relation is a _______ with columns and rows.

Answer 8

A relation is a table with columns and rows.

Question 9

A _______ is a named column of a relation.

Answer 9

An attribute is a named column of a relation.

Question 10

A _______ is a row of a relation.

Answer 10

A tuple is a row of a relation

Question 11

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

Answer 11

domain

Question 12

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

Answer 12

cardinality

Question 13

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

Answer 13

degree

Question 14

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

Answer 14

intension

Question 15

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

Answer 15

extension

Question 16

Define:

superkey

Answer 16

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

Question 17

Define:

key

Answer 17

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

Question 18

Define:

candidate key

Answer 18

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

Question 19

Define:

primary key

Answer 19

The candidate key designated as the distinguishing key of a relation

Question 20

Define:

foreign key

Answer 20

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

Question 21

True or false:

A key is always a superkey.

Answer 21

True

Question 22

True or false:

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

Answer 22

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.

Question 23

Define:

domain constraint

Answer 23

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

Question 24

Define:

entity integrity constraint

Answer 24

No attribute of a primary key can be null

Question 25

# Define: referential integrity constraint

Answer 25

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

Question 26

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?

Answer 26

degree = 8 cardinality = 80

Question 27

Select \*one\* valid SQL identifier: a) 23test b) 'fred' c) test\_! d) field\_ e) from

Answer 27

d) field\_ Because: a) starts with number b) & c) have special chars e) is a reserved word

Question 28

How can you map from SQL to relational algebra?

Answer 28

Question 29

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

Answer 29

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)

Question 30

How can you remove duplicates from an SQL query?

Answer 30

With the DISTINCT clause. e.g. SELECT DISTINCT title FROM emp

Question 31

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

Answer 31

When you're querying a primary key

Question 32

What are the three phases of database design?

Answer 32

Conceptual, logica, and physical

Question 33

Explain the conceptual step of database design.

Answer 33

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

Question 34

Explain the logical step of database design.

Answer 34

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

Question 35

Explain the physical step of database design.

Answer 35

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

Question 36

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

Answer 36

The number of entity types participating in the relationship.

Question 37

True or False: A composite key consists of two or more attributes.

Answer 37

True

Question 38

True or False: Relationships are generally names with singular verbs.

Answer 38

True

Question 39

True or False: The degree of a relationship is the number of attributes it has.

Answer 39

False. The number of entities.

Question 40

True or False: Composite attributes are also called atomic attributes.

Answer 40

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

Question 41

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

Answer 41

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

Question 42

True or False: Relationships are represented using a directed edge (with arrows)

Answer 42

False. Those are inheritances.

Question 43

True or False: An entity is typically named using a singular verb.

Answer 43

False. A singular noun.

Question 44

True or False: It is not possible to have a relationship of degree one.

Answer 44

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

Question 45

True or False: A diamond is used to represent a relationship of degree larger than 2.

Answer 45

True

Question 46

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

Answer 46

False. The value is \*not\* physically stored.

Question 47

True or False: Attributes are properties of eith entities or relationships.

Answer 47

True

Question 48

True or False: An attribute cannot be multi-valued.

Answer 48

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

Question 49

True or False: A composite attribute contains two or more components.

Answer 49

True.

Question 50

True or False: It is possible to have two candidate keys with different numbers of attributes.

Answer 50

True

Question 51

True or False: In ER modeling, relationship has a primary key.

Answer 51

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

Question 52

True or False: A weak entity has its own primary key.

Answer 52

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

Question 53

True or False: A weka entity must be associated with (identified by) a strong entity.

Answer 53

True.

Question 54

True or False: A weak entity can have a relationshpi with another entity besides its identifying strong entity.

Answer 54

True

Question 55

True or False: Generalization is a bottum-up process.

Answer 55

True.

Question 56

True or False: In a UML diagram, the inheritance arrow points towards the superclass.

Answer 56

True.

Question 57

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

Answer 57

Mandatory Optional

Question 58

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

Answer 58

And Or

Question 59

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

Answer 59

True.

Question 60

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.

Answer 60

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

Question 61

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

Answer 61

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

Question 62

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

Answer 62

True.

Question 63

True or False: A weak entity will always have primary key attributes from the identifying entity.

Answer 63

True

Question 64

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

Answer 64

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