Lecture 6

Question 1

How to draw a high-level database model?

Answer 1

1. Draw diagram, as starting point for you relational database, DO NOT start with FDs, normal forms and decompositions. Instead start with objects like Customers, Invoices and Orders.
2. Convert diagram to version 1 of you relation schemas
3. Apply database-design theory to normalize schemas.

Question 2

What does ER stand for?

Answer 2

Entity/Relationship model

Question 3

What does ES stand for?

Answer 3

Entity Sets

Question 4

What is the definition for a key in an ES?

Answer 4

A set K of one or more attributes s.t., given two distinct entities e1 in A and e2 in E, they cannot have identical values for each attribute in key K

Question 5

What are the requirements of keys in ES?

Answer 5

1. Every ES must have a key
2. There can be multiple keys for an ES
3. When ES is in isa-hierarchy, root ES is required to have all attributes needed for a key

Question 6

What does an arrow with and without referential integrity look like?

Answer 6

With thin, without fat.

Question 7

What is referential integrity?

Answer 7

A value appearing in one context, to appear as well in another context.

Question 8

What is the definition of a weak entity set?

Answer 8

An ES with key composed of some attributes belonging to other ES(s)

Question 9

Why do weak ESs typically arise?

Answer 9

1. Weak ES is part of hierarchy, not of the isa type

2. Weak ES is a connecting ES (to replace multiway relationship), don’t have a key.

Question 10

What follows from a weak ES?

Answer 10

If E is a weak ES, then its key consist of:

• Zero or more of its own attributes, and
• key attributes from ESs that are reached by ‘specific’ many-one relationships R from E to other ESs.

Question 11

What is relationship R, that makes ES F ‘contribute’ to key of weak ES E called?

Answer 11

A supporting relationship

Question 12

How to convert an ER diagram to relation schema?

Answer 12

1. Turn each ES into relation with same attributes as ES

2. Replace relationship by relation, with keys from connected ESs as attributes

Question 13

What are the 3 approaches to convert subclass structures?

Answer 13

1. Follow ER viewpoint, for each ES E in hierarchy, create a relation
2. Treat entities as object belonging to only a single class: for each possible subtree including the root, create on relation
3. Use null values: one big relation with all attributes of all ESs in hierarchy.