Quiz

1. Could you please explain why/how an intermediate table can be used to represent many to many relationships? 

   Sure. This is an important question. Let's use the CREDIT table as our example. That intermediate table is used to represent the many-to-many relationship between people (actors) and movies:

   • Each person can act in many movies
   • Each movie can have many actors

   A (nm,tt) row of the CREDIT table says that the person with that NM acted in the movie with that TT.

   So, the rows of the intermediate table are the set of foreign keys (FK) for the entities involved in the relationship.

   As a generalization, consider several many-many relationships among people:

   • friends-with
   • admires
   • has met
   • supervises
   • ...

   Each can be represented as a list of (ID1,ID2) pairs. And we store those pairs in a table.

   Maybe today we'll represent a 3-entity relationship! or 4!

2. Could we go over question 4 please

   Sure. Here it is:

    Using DBDiagram.io, we can create a many-many relationship by
   * Using an intermediate table
   * a line connecting both entity sets
   * The <> notation in a "ref" statement
   * any of the above
   

   DBDiagram allows all of these options.

   • Using an intermediate table, as we did with credit
   • You can drag a line between the entities and adjust the "Ref"
   • You can write the ref statement

   When you just drag a line, you'll get one many-to-many relationship, named foo_bar. If you want a different name or multiple many-many relationships, you'll have to name your own intermediate tables.

3. In SQL, can you represent a many-many relationship without an intermediate table? / Is it possible to create a many to many relationship in mySQL without an intermediary table?

   Two people with the same question! I'm glad you asked.

   In traditional SQL, values are relatively small and fixed in size, so we don't have lists as column values.

   Instead, we use additional tables and JOINs.

   For example, in the Vet office, the owner doesn't have a LIST of pets; instead, each pet has an OwnerID.

   Similarly for many-to-many relationships.

   So, yes, we need intermediate tables, one for each relationship.

   (However, if we represent a value using JSON, we could have lists within table rows. If you want to do that in your projects, you are welcome to, but for now we'll use the traditional SQL representation.)

4. Why did the dbdiagram.io get rid of the enum for dorm_type?

   Wow, good catch! That seems to have been an editing error on my part. I re-exported the table and it's there now. The SQL is:

   
   CREATE TABLE `student` (
     `sid` char(9) PRIMARY KEY COMMENT 'this is the b-number',
     `name` varchar(40),
     `is_on_campus` ENUM ('no', 'yes'),
     `dorm` ENUM ('BAT', 'BEB', 'CAZ', 'CED', 'CER') COMMENT 'null for off-campus students',
     `local_address` varchar(80) COMMENT 'null for on-campus students'
   );
   
   

5. I am a bit confused about putting the asterisk or number 1 on the table to represent one to many, many to one relationships. Could we please go slowly over these differences in class? 

   Sure. let's try this:

   
     table building {
       bid int [pk, increment]
       name varchar(50)
       }
   
       table room {
       rid int [pk, increment]
       floor int
       number char(5)
       bid int
     }
   
   

6. In the part you did the many-to-many diagram. I don't understand why the table has a person (and a 1 next to it) and it points to nm in the credits table with an asterisk. Could you explain how to read these notations from the perspective of the person table vs the credit table?

   Sure. The idea is that 1 person has many (asterisk) credits, and 1 movie has many (asterisk) cast members.

7. So, instead of trial and error with writing queries to make a complicated database with many tables, once can just use an ER website like this one to write the queries? Would it end up being less or more work since you have to use a different language either way?

   It's not writing queries. It's helping with the creating of tables.

   DDL (Data Definition Language) versus DML (Data Manipulation Language).

   The website isn't helping with SELECT, INSERT, UPDATE, DELETE, grouping, having, subqueries, CTEs, aggregate functions, join conditions or any of the stuff that we learned in the first two weeks. It only helps with the CREATE TABLE.

   And it still requires you to make decisions about datatypes, primary keys and foreign key relationships.

8. So DBDiagram is just a visualizer for sql? Feel like it would've been nice to start using it earlier.

   It's a visualizer for tables, not for all of SQL. And we did see some diagrams about the WMDB last week.

   Still, I take your point. There are lots of ways to organize a database course, and I've chosen to start with queries, because that's most of what people do. Creating tables is rare. Working with data is the more common day-to-day thing.

9. None! I find it very helpful to have table visualizations, so this website is a great tool.

   I'm glad!