Midterm 2

Question 1

Define:

conceptual database design

Answer 1

Conceptual database design involves modelling the collected information at a high-level of abstraction without using a particular data model of DBMS.

Question 2

Define:

entity-relationship modelling

Answer 2

Entity-relationship modelling (ER modelling) is a top-down approach t o database design that models the data as entities, attributes, and relationships.

Question 3

What is the relationship between conceptual database design and ER modelling?

Answer 3

The ER model is a type of conceptual database design.

Question 4

What are the three stages of database design?

Answer 4

Conceptual
Logical
Physical

Question 5

What happens at the conceptual level of database design?

Answer 5

Conceptual database design involves modelling the collected information at a high-level of abstraction without using a particular data model of DBMS.

e.g. UML or ER-modelling

Question 6

What happens at the physical level of database design?

Answer 6

Physical database design is the process of selecting a DBMS.

Question 7

What are some factors to consider when selecting a DBMS?

Answer 7

Features
Compatibility
Performance
Price

Question 8

What are some factors to consider when selecting a DBMS?

Answer 8

Features
Compatibility
Performance
Price

Question 9

What is the degree of a relationship type?

Answer 9

The number of entity types participating in the relationship.

Question 10

Define:

entity type

Answer 10

An entity type is a group of objects with the same properties which are identified as having an independent existence.

Question 11

Define:

relationship type

Answer 11

A relationship type is a set of associations among entity types. Each relationship type has a name that describes its function.

Question 12

Entity names are usually ______ and relationship names are usually ______.

Answer 12

Entity names are usually nouns and relationship names are usually verbs.

Question 13

Define:

recursive relationship

Answer 13

A recursive relationship is a relationship type where the same entity type participates more than once in different roles.

Question 14

True or False:

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

Answer 14

True. The minimum number is two.

Question 15

Define:

attribute

Answer 15

An attribute is a property of an entity or a relationship type.

Question 16

Define:

simple attribute

Answer 16

An attribute is simple if it contains a single component with an independent existence.
e.g. Social Insurance Number

Question 17

Define:

composite attribute

Answer 17

An attribute is called composite if it consists of multiple components, each with an independent existence.
e.g. address can be broken into street, city, etc.

Question 18

Define:

single-valued attribute

Answer 18

An attribute is a single-valued attribute if it consists of a single value for each entity instance.
e.g. salary

Question 19

Define:

multi-valued attribute

Answer 19

An attribute is multi-valued if it may have multiple values for a single entity instance
e.g. people may have many different phone numbers

Question 20

Define:

derived attributes

Answer 20

A derived attribute is one whose value is calculated from other attributes but is not physically stored.

Question 21

Define:

candidate key

Answer 21

A candidate key is a minimal set of attributes that can uniquely identify each instance of an entity type.

Question 22

Define:

primary key

Answer 22

A primary key is a candidate key that has been selected to identify each instance of an entity type.

Question 23

Define:

composite key

Answer 23

A composite key is a key that consists of two or more attributes.

Question 24

What is multiplicity?

Answer 24

The multiplicity is the number of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.

Question 25

For binary relationships, what are the three common types of multiplicities?

Answer 25

one-to-one (1:1)
one-to-many (1:* or 1:N)
many-to-many (: or N:M)

Question 26

For relationships, what are the two participation constraints?

Answer 26

Cardinality, which is the maximum number of relationship instances for an entity participating in a relationship type.

Participation, which is the minimum number of relationship instances for an entity participating in a relationship type.

Question 27

What is the difference between strong and weak entity types?

Answer 27

A strong entity type is an entity type whose existence is not dependent on another entity type, whereas a weak entity type is dependent upon another entity type.

Question 28

What are the two possible choices for constraints on superclasses and subclasses?

Answer 28

Participation constraint

Disjoint constraint

Question 29

What are the two possible choices for the participation constraints, and what do they mean?

Answer 29

Mandatory: a superclass member must be a member of one of its subclasses

Optional: a superclass member need not be a member of one of its subclasse.

Question 30

What are the two possible choices for the disjoint constraints, and what do they mean?

Answer 30

And: a superclass member may be in more than one of its subclasse

Or: a superclass member may be in only one of its subclasses

Question 31

What are the 8 steps when converting from ER to relational mapping?

Answer 31

1. Strong entities
2. Weak entities
3. Relationships - 1:1
4. Relationships - 1:*
5. Relationships - :
6. Multi-valued attributes
7. N-ary relationship
8. Subclasses

Question 32

The ER model was proposed by ______ in ______

Answer 32

The ER model was proposed by Peter Chen in 1976

Question 33

What happens at the logical level of database design?

Answer 33

Logical database design is the process of constructing a model of the information using a particular data model.