Converting EER to DB

Question 1

relational data model

Answer 1

physically represented as a set of relations or tables that store data

Question 2

3 components of relational data model

Answer 2

• relational data structure - Where to store data?
• data manipulation - how to manipulate data?
• relational data integrity - how to maintain integrity?

Question 3

main data structure used in relational data structure

Answer 3

relation

Question 4

relation

Answer 4

two-dimensional grid/table that holds data about a set of objects

Question 5

each row in RDS represents

Answer 5

an instance of an entity

Question 6

each column in RDS represents

Answer 6

properties or attributes of an object

Question 7

relational schema

Answer 7

a specification of the name of the relation, followed by list of attributes/columns from table

Question 8

t/f: in a relational schema, you do not underline the primary key

Answer 8

false

Question 9

assigning NULL as cell value is diff than leaving it blank because

Answer 9

it indicates that the decision was made to assign that value

Question 10

data manipulation

Answer 10

a way to access and manipulate data in the relations

Question 11

example of a manipulation language

Answer 11

structured query language (SQL)

Question 12

2 types of database integrity constraints

Answer 12

entity integrity and referential (reference) integrity

Question 13

super key

Answer 13

attribute/combination of attributes that uniquely identifies each row in the table

Question 14

candidate key

Answer 14

minimal (irreducible) superkey
- a super key that doesn’t contain a different super key as a part of it

Question 15

primary key

Answer 15

candidate key selected to be used as primary row identifier

Question 16

a primary key is a type of ______, which is a type of ________

Answer 16

candidate key, super key

Question 17

a primary key cannot contain

Answer 17

NULL entries

Question 18

foreign key

Answer 18

attribute/combination of attributes in one table whose values must either match primary key in another table, or be NULL

Question 19

secondary key

Answer 19

attribute(s) used for data retrieval

Question 20

entity integrity implies that:

Answer 20

• every relation in relational data model has a unique primary key
• the data values for all attributes in primary key are not NULL

Question 21

referential integrity implies that:

Answer 21

• every non-NULL foreign key references an existing primary key value

Question 22

enforcing referential integrity rule means

Answer 22

it’s impossible to have an invalid foreign key value, or to delete a row whose primary key has matching foreign key values in another table

Question 23

enforcing entity integrity rule ensures

Answer 23

primary key values exist

Question 24

if a student is part of a department, and the department is removed from the database:

Answer 24

must remove student or move to different department or change foreign key value to null to ensure referential integrity

Question 25

t/f: relations can be complex

Answer 25

false. they present one consistent method of representing data

Question 26

no matter how complex the ER diagram is, the end result of the DB is

Answer 26

a set of related tables defined with keys

Question 27

standard notation

Answer 27

foreign key points to associated primary key(s)

Question 28

four primary elements from an ER diagram that we need to find a way to represent

Answer 28

- entities with attributes - relationships with cardinalities - associative entities - supertypes/subtypes

Question 29

first step in mapping ER to DB

Answer 29

mapping entities to relations and their attributes into columns

Question 30

simple attributes (mapping)

Answer 30

ER attributes map directly onto relation

Question 31

composite attributes use

Answer 31

only their simple, component attributes

Question 32

multi-valued attribute becomes

Answer 32

a separate relation with a foreign key taken from the superior entity

Question 33

a customer entity type with only simple attributes looks like _______ and is mapped into _______ for a relation

Answer 33

- rectangle with connected ovals - 3 columns (1 per attribute)

Question 34

standard practice to list primary key as ______ in relational database

Answer 34

first column

Question 35

mapping a customer entity type with 3 attributes, one of which is a composite attribute

Answer 35

the relation has the first two attributes as columns, and then the components OF the composite attribute (not the composite itself) as the other columns

Question 36

when mapping a multi-valued attribute,

Answer 36

it becomes a separate relation with a foreign key (make a copy of original table's primary key and place it in the new table)

Question 37

the primary key added to a multi-valued attribute's relation can only be a foreign key because

Answer 37

primary key value has to be unique for every single row, and a multi-valued attribute can have multiple rows for he same entity

Question 38

mapping a multi-valued attribute as a separate relation creates a

Answer 38

one to many relationship between the entity and the multi-valued attribute

Question 39

mapping a weak entity

Answer 39

becomes a relation with a foreign key that is adopted from its associated strong entity's primary key

Question 40

primary key for weak entity relation is composed of (and equals)

Answer 40

- partial identifier of weak entity - primary key of identifying relation (strong entity) = composite primary key

Question 41

example of weak entity mapping with employee and dependent

Answer 41

dependent of employee is weak entity. it becomes its own relation, and its composite primary key consists of the primary key, employee_id, from employee strong entity, and the name of the dependent

Question 42

3 types of binary relationships

Answer 42

one-to-many, one-to-one, many-to-many

Question 43

one-to-many

Answer 43

primary key on one side becomes a foreign key on the many side

Question 44

one-to-one

Answer 44

primary key on the mandatory side becomes a foreign key on the optional side

Question 45

many-to-many

Answer 45

creates a new relation with the primary keys of the two entities as its primary key

Question 46

with a many-to-many relationship,

Answer 46

an associative entity is formed between two original entities

Question 47

mapping supertype/subtype relationships

Answer 47

define one relation for the supertype and one for each subtype

Question 48

when mapping, subtype-specific attributes go into ______, and primary key of supertype also becomes _____

Answer 48

each subtype, primary key of subtype relation

Question 49

when mapping super/sub, a 1:1 relationship is established between

Answer 49

supertype and each subtype, with supertype as primary table

Question 50

two unary relationship types

Answer 50

one to many and many to many

Question 51

for one-to-many, you add a

Answer 51

new column for the foreign key

Question 52

for ternary relationships, you create a

Answer 52

fourth relation that represents the ternary relationship

Question 53

for ternary relationships, the primary key from the first three relations

Answer 53

migrates to the new ternary relation

Question 54

when we convert EER diagrams to relations (downside)

Answer 54

some of the richness of the context is lost