Quiz

1. When are ER diagrams used in real life problems?

   All the time! In fact, I learned about dbdiagram.io from a colleague on the diabetes project.

   It's a good graphical way to display the relationships, instead of having to infer them from foreign keys

2. Could we please go over keys and key constraints in class?

   Sure. A key for an entity set is a unique identifier. People have SSN, B-number, email address, etc. Cars have a VIN. Movies have a TT.

   The key constraint is that the key is unique. So, if you try to insert a movie and the TT already exists, you'll get a key constraint error.

3. Can we go over the definition of a foreign key constraint again?

   Sure. The TT is a foreign key in the credit table, because it's a key in the movie table.

   The foreign key constraint is that every TT in the credit table is a valid key in the movie table.

4. Is there a limit to the number of relationships between entities?

   No really. Consider an entity set of Person (people). What relationships could we have between people? (1) Friends, (2) siblings, (3) enemies, (4) employer/employee, (5) colleague, (6) teammmate (7) ...

   We could have lots

5. Are there limits on how many intermediate tables we can have?

   Nope. The only issue is naming them. Dbdiagram.io can create one called person_person, but you'll be better off naming them yourselt if there are lots.

6. Can you explain intermediate tables with an example?

   The credit table is our prototypical example. It's how we represent the many-to-many actor relationship between Person and Movie.

   We could also have intermediate tables for all of the inter-personal relationship examples above.

7. What is the best practice when understanding each of the different relationships so we don't get them all mixed up?

   I like to cling to

   • (1) director as a prototypical example of a one-to-many relationship: one director and many movies, and
   • (2) credit as a prototypical example of a many-to-many relationship: each person can act in many movies and each movie can have many actors.

   Relationships that are one-to-one are less common, because typically we would just make more attributes of our entity. One exception is when we want the data in a different table for reasons of privacy or security. For example, we might have a one-to-one relationship between accounts and passwords. Or a one-to-one relationship between people and medical information.

8. Do you mind going over entity & entity set vs relationship & relationship set? / Can you go over relationship set vs entity set? / What exactly is the difference between relations and relationships as mentioned in Q2 in the quiz?

   "Barbie" is an entity, while movie is an entity set.

   "Greta Gerwig" is an entity, while person is an entity set.

   directs("Greta Gerwig","Barbie") is a relationship, while directs is a relationship set.

9. Is there a way to create multiple many-many relationships in dbdiagram? Is it recommended to do so?

   Yes, and sure, make as many as you need.

10. Can you go over ISA hierarchies in class I'm still a little confused. / Could you go over ISA hierarchies / Can you explain a bit more about ISA hierarchies?

    We often create subtypes when the subtype participates in a relationship that the parent type doesn't. Say, international students have a relationship with the Slater Center and with the visa people and such, that US students don't. So, in some cases, we create subtypes to illustrate those.

    Unfortunately, AFAIK, dbdiagram doesn't do that (yet).

    Whether to create different tables or not is a judgment call.

11. Are subtypes also entities?

    yes

12. Can ALTER TABLE change overall table structure/layout?

    AFAIK, it can't change the primary key. But it can add/remove columns, indexes, and constraints. So, it can do a lot.

13. Can we go over the Alter Table syntax?

    Sure. Alter Table W3schools and Alter Table MySQL documentation

14. I would like to practice more with creating ER diagrams in class.

    We will!

15. Are we supposed to make a diagram first and then work on the code we export from dbdiagram? Or what do we do with the code we downloaded.

    Yes. You can scp the downloaded SQL code to the server and use/adapt it to create your tables, say in your projects.