Relational Data Model

Question 1

Explain the concept of map the domain?

Answer 1

”Map” the domain, find out what the
database is intended to model
– The database should accept all data possible
in reality
– The database should agree with reality and
not accept impossible or unwanted data

Question 2

Which setup is the blueprint for the database?

Answer 2

Construct the ”blueprint” for the database
– the database schema

Question 3

What is a Database Schema?

Answer 3

Database Schemas
• A database schema is a set of relation
schemas
• A relation schema has a name, and a set
of attributes (+ types):

Question 4

Explain difference between Schema vs Instance

Answer 4

Question 5

Explain how you go from Schema to Database.

Answer 5

Question 6

Why should we have relations in databases?

Answer 6

Why relations?
• Relations often match our intuition
regarding data
• Very simple model
• Has a good theoretical foundation from
mathematics (set theory)
• The abstract model underlying SQL, the
most important database language today

Question 7

Explain Keys

Answer 7

Question 8

What are composite keys?

Answer 8

Question 9

Answer 9

Question 10

Answer 10

Question 11

Answer 11

Question 12

What are the things to avoid when construction databases?

Answer 12

Things to avoid!
• Redundancy
• Unconnected relations
• Too much decomposition

Question 13

Describe a database schema

Answer 13

A database schema is a blueprint
- Consists of a set of relations e.g. Courses(code, name, teacher)
where “Courses” is the relation name and code, name and teacher are
attributes.

Question 14

What are tuples?

Answer 14

A database instance holds actual data

• Tuples are instances of a relation.
• E.g. (‘TDA357’, ‘Databases’, ‘Mickey’)

In a DBMS, a table holds relations where:

• Each row holds a tuple
• Each column stores a different attribute

Question 15

What is a DBMS?

Answer 15

Data Base Management System

Question 16

What are keys?

Answer 16

Keys uniquely identify the other values of a tuple in a relation
- Composite keys combine several attributes

Question 17

What is the Entity-Relationship approach

Answer 17

The Entity-Relationship approach
• Design your database by drawing a picture
of it – an Entity-Relationship diagram
– Allows us to sketch the design of a database
informally (which is good when
communicating with customers)
• Use (more or less) mechanical methods to
convert your diagram to relations.
– This means that the diagram can be a formal
specification as well

Question 18

Which are the three main elements of an E/R diagram?

Answer 18

E/R Model
• Three main element types:
– Entity sets
– Attributes, and
– Relationships

Question 19

What are entities?

Answer 19

Question 20

What are Entities set?

Answer 20

Question 21

What are Attributes?

Answer 21

Question 22

What is a Relationship?

Answer 22

Question 23

How to translate E/R to Schema?

Answer 23

Question 24

Answer 24

Question 25

Explain the usage of References

Answer 25

Question 26

What is a Foreign Key?

Answer 26

Question 27

Can relationships have attributes?

Answer 27

Question 28

Answer 28

Question 29

Answer 29

Question 30

Can a relationship have a key?

Answer 30

Question 31

Answer 31

Question 32

How to differentiate between entity and relationship?

Answer 32

Question 33

Describe a Many-to-Many Relationship.

Answer 33

Question 34

Describe a Many-to-One relationship.

Answer 34

Question 35

Describe a Many-to-”exactly one”.

Answer 35

Question 36

Describe a "One-to-One" relationship.

Answer 36

Question 37

How do you translate Many-to-Many relationship?

Answer 37

Question 38

Answer 38

Question 39

What does NULL mean?

Answer 39

Question 40

Answer 40

Question 41

Is this a good or bad design?

Answer 41

Question 42

When should we model something as an entity in its own right (as opposed to an attribute of another entity)?

Answer 42

Question 43

Answer 43

Question 44

How to translate this?

Answer 44

Question 45

Translate schema.

Answer 45

Question 46

Translate to schema.

Answer 46

Question 47

Translate to schema.

Answer 47

Question 48

Explain SubClassing.

Answer 48

Question 49

Explain what ISA means in this context.

Answer 49

Question 50

Can a subclass have many superclasses?

Answer 50

Question 51

How to Translate ISA to relations?

Answer 51

Question 52

Answer 52

Question 53

What are 2 Alternate ISA translations to the E-R approach?

Answer 53

Question 54

Answer 54

Question 55

Answer 55

Question 56

How is E-R in comparison to Object Oriented and NULL approach?

Answer 56

Question 57

How does Object Oriented compare to E-R and NULL approach?

Answer 57

Question 58

How does NULL compare to the E-R and Object Oriented approach?

Answer 58

Question 59

What are Weak Entities?

Answer 59

Question 60

What are Weak Entities dependent on? How do you draw a Weak Entity?

Answer 60

Question 61

Answer 61

Question 62

How are Weak Entities' discriminator drawn?

Answer 62

Question 63

Explain how Multiway Relationships can be modeles as Weak Entities.

Answer 63

Multiway relationships as WEs • Multiway relationships can be transformed away using weak entities – Subtitute the relationship with a weak entity. – Insert supporting relationships to all entities related as ”many” by the original relationship. – Insert ordinary many-to-one relationships to all entities related as ”one” by the original relationship. What’s the point? • Usually, relationships work just fine, but in some special cases, you need a weak entity to express all multiplicity constraints correctly. • A weak entity is needed when a part of an entity’s key is a foreign key.

Question 64

Should you use Multivalued Attributes?

Answer 64

”Multivalued” attributes • Inflexible if you later want more attributes on teachers. • No guarantees against e.g. spelling errors of teacher names. – less flexible to insert a constraint on what values are allowed than to use an extra table. • Tables are cheap – references are cheap – No reason NOT to use an entity. • Rule of thumb: Don’t use multivalued attributes!!

Question 65

Should you use "Flag" attributes on relationships?

Answer 65

”Flag” attributes on relationships • Less intuitively clear. • Inflexible if later you need more roles. • Tables are cheap, union of two tables is a cheap operation (O(1)) – filtering can be expensive (O(n))! • Only benefit: automatic mutual exclusion (a teacher can only be either responsible or an assistant). – If important, can be recovered via assertions (costly). • Rule of thumb: Don’t use flag attributes on relationships!

Question 66

Does and don'ts of special relationships?

Answer 66