Relational Data Model Flashcards
What is a Database Schema?
Database Schemas
• A database schema is a set of relation
schemas
• A relation schema has a name, and a set
of attributes (+ types):
Explain difference between Schema vs Instance
Explain how you go from Schema to Database.
Why should we have relations in databases?
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
Explain Keys
What are composite keys?
What are the things to avoid when construction databases?
Things to avoid!
• Redundancy
• Unconnected relations
• Too much decomposition
Describe a database schema
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.
What are tuples?
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
What is a DBMS?
Data Base Management System
What are keys?
Keys uniquely identify the other values of a tuple in a relation
- Composite keys combine several attributes
What is the Entity-Relationship approach
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
Which are the three main elements of an E/R diagram?
E/R Model
• Three main element types:
– Entity sets
– Attributes, and
– Relationships
What are entities?
What are Entities set?
What are Attributes?
What is a Relationship?
How to translate E/R to Schema?
Explain the usage of References
What is a Foreign Key?
Can relationships have attributes?
Can a relationship have a key?
How to differentiate between entity and relationship?
Describe a Many-to-Many Relationship.
Describe a Many-to-One relationship.
Describe a Many-to-”exactly one”.
Describe a “One-to-One” relationship.
How do you translate Many-to-Many relationship?
What does NULL mean?
Is this a good or bad design?
When should we model something as an
entity in its own right (as opposed to an
attribute of another entity)?
How to translate this?
Translate schema.
Translate to schema.
Translate to schema.
Explain SubClassing.
Explain what ISA means in this context.
Can a subclass have many superclasses?
How to Translate ISA to relations?
What are 2 Alternate ISA translations to the E-R approach?
How is E-R in comparison to Object Oriented and NULL approach?
How does Object Oriented compare to E-R and NULL approach?
How does NULL compare to the E-R and Object Oriented approach?
What are Weak Entities?
What are Weak Entities dependent on? How do you draw a Weak Entity?
How are Weak Entities’ discriminator drawn?
Explain how Multiway Relationships can be modeles as Weak Entities.
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.
Should you use Multivalued Attributes?
”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!!
Should you use “Flag” attributes on relationships?
”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!
Does and don’ts of special relationships?
