Blogonaut

club306: JOIN, IN, EXISTS

club306: JOIN, IN, EXISTS wonders if there is any difference between these three. I recommend reading this link and this link for some detailed analysis. Net net, they overlap but provided different results in important situations.

In the test are we responsible for all three (ie are you going to ask specifically) or can we use any of the three when ask to join multiple tables?

You need to be able to recognize when they are similar.

Icehouse: Remembering Associative Entities

Icehouse: Remembering Associative Entities gives a nice rule for how to do associative entities. Icehouse generates a lot of thes handy rules of thumb.

1) Picture/draw the ER diagram Many-to-Many relationship between the two entity types (without the associative entity). 2) Add the box for the associative entity inbetween them. 3) Move each of the two cardinalities that are next to the two entity type boxes so they are on the opposite sides of the associative enitity box (in other words, next to the associative entity box but on the other side of it). 4) Put 1:1 cardinality next to the boxes for both entity types

Kornstein999: Subqueries

Kornstein999: Subqueries discusses the relative merits of subqueries. Why drive yourself crazy with a subquery when you can just join tables using the WHERE clause?

It seems much simpler to me to just connect tables using the WHERE clause. Maybe it's just me, but I would prefer to avoid subqueries as much as possible. And in a lot of cases, you can avoid them. I just don't see why someone would prefer to use one.

Frankly, in cases where there are multiple ways of doing things, people usually choose one preferred style. However, (inner) joining tables through the WHERE clause does not always lead to the result you want. In particular, inner joins limit you to cases where your results are characterized by the presence of some combination of attributes across multiple tables. If you want cases that exclude that combination, you must first do a query that selects the combination and then another query that selects everything from the original set that is not in that combination.

A concrete example is contained in this conversation between myself and Hail to the Victors.

thecrew: Ternary Relationship

thecrew: Ternary Relationship discusses ternary relationships.

But without it, i think the ER diagram is still normalized to the 3NF, and the information wanted is still there. Any answer is welcomed.

It's not a question of normalization. It's a question of the type of relationship you want to model. If the relationship requires that three things be present, then use the ternary relationship.

Blog O' Rama: Premiere Products Exercises Question.

Blog O' Rama: Premiere Products Exercises Question. asks about the answers to the SQL exercices.

Maybe I missed a handout or a link on the course website?

Indeed, try this link to the schedule and look under the day when the chapters were assigned.

RissRoss: ER Questions

RissRoss: ER Questions asks some rather good detailed questions. I don't have the coursepack in front of me, but I think I can provide some "philosophical" guidance that might help.

1) In the coursepack, when talking about subtypes, it mentions "optional subtyping is represented by leaving a single line from the supertype to the circle." My question is, what is "circle" referring to? 2) Are subtypes necessary or these entites always abe to be represented as regular entity types with additional relationships attached to them? 3) Referring to the ER end of chapter questions in the coursepack, could the answer to question 3, part 2 be drawn up using a ternary relationship to express the relatoinship between employee, skill and machine type? It is not shown this way in the chapter answers, but I thought it was a possibility.

In regards to Question 1, I'm not sure, but if you refer to the in-class example (ER2), you see that the subtype is represented by a single line. BTW, this is an area that I said would not be heavily represented on the exam.

I'm not sure I entirely get Question 2, but my main observation is that subtypes are more of a finessing move (i.e., you can make it by without them with only a small loss in your ability to model a situation).

Question 3 is interesting. I think a ternary relationship could be used. The key feature of a ternary relationship is that you cannot enforce binary constraints between entities. Mind you, one does not rule out the other in most instances. You can have both in many cases.

bskool bloggin: ER 2 ?

bskool bloggin: ER 2 ? found what he thinks is a typo in ER 3. Fixing his numbering to reflect the ER 3, he says:

In ER Excercise #3 the answer to question #1 states that advisor, which is the 1-M relationship between professor and student needs to be represented by a table, while position, which is the associative entity between professor and department does not need to be represented by a table. This seems contrary to our rule that M-M relationships, entity types, and associative entities are the only things we respresent as tables.

This is correct as far as what we emphasized in class. If you review the E-R modeling slides carefully, Slide 9 indicates that the specific instance Mike points out is a case where we would use a table to represent the relationship. This case will not play a role in the exam. Our general rule will still hold.

bskool bloggin: ER 2 ?

bskool bloggin: ER 2 ? found what he thinks is a typo in ER 3. Fixing his numbering to reflect the ER 3, he says:

In ER Excercise #3 the answer to question #1 states that advisor, which is the 1-M relationship between professor and student needs to be represented by a table, while position, which is the associative entity between professor and department does not need to be represented by a table. This seems contrary to our rule that M-M relationships, entity types, and associative entities are the only things we respresent as tables.

This is correct as far as what we emphasized in class. If you review the E-R modeling slides carefully, Slide 9 indicates that the specific instance Mike points out is a case where we would use a table to represent the relationship. This case will not play a role in the exam. Our general rule will still hold.

RissRoss: Normalization Questions

RissRoss: Normalization Questions asks many questions

1) It says in the coursepack and handouts that for 1NF you should remove all repeating groups and put them in another relation. However, I believe it also says that there is only one relation (or table) when the database is in the 1NF form only. Therefore, how does this work? And if this is the case, what would another table of the repeating groups come to look like, what fields should be included?

The key point is to get rid of the repeating group, things like Address1, Address2, etc. and just make a single well defined attribute (e.g., Address). Either at this point or later in the process, this attribute will go in its own table with its own primary key.

2) What does it mean when it says that X & Y of Multi-Value Dependency (MVD) can contain either single or composite attributes?

Composite attribute just means more than one attribute together.

3) What does entity refer to when talking about normalization? Does it refer to a particular field (column name) of the relation?

Is there a specific use of the word that is confusing?

4) For Handout Normalization Exercise #2, how is sell date part of the primary key? What if the customer hasn't sold the stock yet, and therefore the value is null? because a primary key can't have a null value in it, and therefore it does not make sense to me.

I addressed this question in a previous discussion with maulin that occurred one month ago today.

icemountain: Late Nite Blogging Thought

icemountain: Late Nite Blogging Thought provides some observations about what would make the blogosphere better.

My thought/question comes here: Is there anyway we can group the blogs according to category? For example, have a link to all the recent blogs within the Project Issues category, Class Issues, ect. This way, if I'm looking for help on my project, I could read only the Project Issues blogs instead in deciphering through ALL the blogs. Just some food for thought...

Try clicking on one of the category links in the 96 hours of posts. You'll get the last 96 hours of that category. Also, you can do search for specific topics as was demonstrated in class.