Quiz

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

   I agree! Many people find pictures helpful, so it's nice to have them.

2. Can you explain the intermediate tables again.

   For sure. Using intermediate tables is how we do many-to-many mappings.

   For example, Movies and Actors. Each movie can have many actors (the cast), and each actor can be in many movies (their filmography). Hence the credit table, or maybe person_movie if DBDIAGRAM.IO is making the table.

   Another example: friends. Each person can have many friends. So the table would be intermediate between person and person, and each entry (P1, P2) says person with ID P1 is friends with the person with ID P2.

3. For question 4, I know we can use an intermediate table (like the credit table) and the <> notation in a ref statement to create a many to many relationship. But for the for the "line connecting both entity sets", to make it many-many, the line needs to have an asterik on both sides, which can only be done by updating the code with <> after dragging the line right? Since dragging the line defaults to a 1-many relationship. I guess I'm confused about the wording of that "a line connecting both entity sets" option.

   No doubt I could improve the description, but it sound like you've got it.

   For others: create a line by dragging, which then creates a Ref: line of code, then switch to the textual window and modify that line of code.

   There may be other ways, but this works okay for me.

4. I'm confused about creating the different relationships in the dbdiagram software vs a SQL file. So,

    Ref: "pet"."oid" > "owner"."oid"

   is how you can define a one to many relationship in the web app, but when you export the SQL code, it gets replaced with ALTER statements. Are these the same? And why does the exported SQL code ALTER the tables instead of including the foreign keys in their creations?

   Well, ref: is not part of the SQL language. But in terms of meaning, they are the same.

   The ALTER table statements modify an existing table (we mentioned this a bit last time). In this case, they alter the table to add the foreign key relationship.

   Personally, I like putting them in the original table definition, so I teach it that way.

   My guess is that it's easier to implement the export feature by doing it piecemeal. And it works.

5. I'm a little bit confused about when you would use two tables or one table for a one-to-one relationship between two entities.

   Good question. Most of the time, we put everything in one table if there's a 1-1 relationship, so they rarely arise in practice.

   There are situations, though, where it's helpful to separate them.

   • rarely used data. No need to "carry it around," just look it up when it's needed.
   • permissions. You can grant/deny people access to a table, but not to a column, so if you want people to see the name and address, but not the SSN, you can put the SSN in a different table.
6. Could you clarify when to choose between storing subtypes in one table with NULLs versus separate tables with joins?

   Sure. It's similar to the considerations for 1-1 relationships. If you don't need the data often, just use the join on those rare occasions. Then there's no need for a lot of NULLS cluttering up the main table.

   But if you need the data often, better to avoid the extra join, at the cost of storing some nulls.

   Which would you do for on-campus and off-campus students?

7. Can you go over ISA a little more? I am still a bit confused on how they are used when actually working in SQL (as the diagram seems to make sense).

   Great question! I really should say more in the reading about this. Here are some possible uses in SQL.

   If we just want students and their common attributes, we do the obvious:

   
   select * from student where ...;
   
   
   

   If we want to process on-campus students, we add that info:

   
   select * from student inner join on_campus_student using (sid) where ...;
   
   
   

   If we want to process all students, including their residence information, that's trickier. We can use the UNION operator:

   
       select sid,name,is_on_campus, dorm as where
       from student inner join on_campus_student using (sid)
       UNION
       select sid,name,is_on_campus, local_address as where
       from student inner join off_campus_student using (sid)
   
   

   But that's seems rare and unlikely.

8. So far it's clear! Thank you : )