1

Normally we query using tables and schema but in my case I have to use Entity Relationship Diagram ONLY to query on a piece of paper in MYSQL Format.

These are really making things complicated. I require your help for example this question.

QUESTION:

NOTE:

Zeus
  • 59
  • 7
  • 1
    I don't undertsand your problem? You have been provided information about table definitions and their relationships in the above diagram. What you don't understand? – Alex Jun 11 '16 at 05:18
  • @Alex I know that but we have to Query using these relationships using Primary and Foreign Keys by making assumptions. Unfortunately I am little struggling how to connect these tables. Just read the question. Thanks – Zeus Jun 11 '16 at 05:35
  • 2
    First of all, I did read the question in all entirety before posting comment. In answer to your comment: The only assumption question is asking you to make is regarding FK column names e.g. Wine_Maker.winemaker_id would have a matching column in Wines. You have to come wih the name of the column yourself. – Alex Jun 11 '16 at 05:53
  • Friend can you write a query if you don't mind. Thats the real problem and challenge. Thanks – Zeus Jun 11 '16 at 06:04
  • You do not need to know PKs, FKs or any other constraints to query. You just have to know what the entities and relationships/associations mean, ie under which circumstances a row goes into or stays out of a table/relation. – philipxy Jun 11 '16 at 07:37
  • @philipxy my teacher specially said to look to Primary Keys given and find out Foreign Keys yourself and query between entities – Zeus Jun 11 '16 at 16:58
  • Nevertheless, they are not needed for querying. It is necessary and sufficient for update & querying to know what the tables mean. (CKs, FKs & other constraints just allow the DBMS to keep out invalid states & to optimize. They are determined by the meanings & what situations can arise.) You will see this if you clearly write the rules (*predicates*) for what rows go in tables and queries in terms of their columns. Eg Countries holds the rows that make a true statement from "country code CTRY_CODE identifies country named CTRY_NAME". (This is commonly misunderstood.) – philipxy Jun 11 '16 at 17:29
  • @philipxy, I think you are mistaking logical PKs, FKs with PK / FK constraints. Logical PKs are needed to know which field uniquely identifies a row in a table and therefore how to join the two entities toghether, where referential constraints are rules explicitly added to enforce the relationship. To summarise: you must have logical PKs and FKs in order to join tables toghether but you do not necessarily need constrains to enforce this join. – Alex Jun 12 '16 at 02:11
  • See [this](http://stackoverflow.com/a/37639248/3404097) or [this](http://stackoverflow.com/a/23842061/3404097) or many other of my answers re not needing CKs/PKs/FKs to query or re table predicates. Good luck. – philipxy Jun 12 '16 at 05:25
  • @philipxy, I read your posts and failed, though I tried, to see how they answer their respective questions. I did get the gist of the idea you are promoting and I think the much better avenue to promote it would be to write and publish a research paper (or a blog if this is not new). Writing "random" answers on SO will not help promote it and ceratinly will confuse the hell out of students still grappling with SQL basics. – Alex Jun 12 '16 at 06:30

1 Answers1

1

Based on the data above INNER JOINs would be written like this:

SELECT *
FROM Wines
    INNER JOIN Carry ON Wines.wine_id = Carry.wine_id

Notice that diagram relationship between Wines and Caries shows 0 to many (1..1 - 0..*). Also notice that wine_id is not listed in the Carry table column list but implied throught the relation.

Next you want to know price for today (hint: since table Carry table has price_start_date and price_end_date it implies that prices are not fixed and you need to use these fields):

WHERE price_start_date <= CURDATE() AND CURDATE() <= price_end_date

To get prices below $15:

WHERE price_start_date <= CURDATE() AND CURDATE() <= price_end_date
    AND price_on_carry < 15

Question 1 query would be something like below (you will need to add relevant column names):

SELECT *
FROM Wines
    INNER JOIN Carry ON Wines.wine_id = Carry.wine_id
WHERE price_start_date <= CURDATE() AND CURDATE() <= price_end_date
    AND price_on_carry < 15
    AND Wines.color = 'Red'

Note: English is not my first language so I am confused by "whether or not they are from Canada", specifically if we need to include information about whether the wine is from Canada or not. I assumed that we don't need to include this information.

Alex
  • 4,885
  • 3
  • 19
  • 39
  • The question mentions a recordset, you did not provide it. – Alex Jun 11 '16 at 06:48
  • It was fictitious data thats why I didn't provided it. Thanks for it but for that Canada I think we will need to connect with that Countries table some how but which Keys? Here I stuck – Zeus Jun 11 '16 at 06:52
  • For wines from Canada you need to JOIN tables from Countries all the way to Curry (notice that it is asking you to sort by price). Use columns that have PK next to the name in brackets e.g. ctry_code. PK stands for PRIMARY KEY. – Alex Jun 11 '16 at 06:56
  • Added "AND Wines.color = 'Red'" since customer wants Red wines – Alex Jun 11 '16 at 06:59
  • If you want, you can post your question 2 query in the question (or an answer) and someone can check it. – Alex Jun 11 '16 at 07:03
  • Ok I got one thing. Countries is a parent table so its Primary Key will be referenced in child Wine Maker key as a foreign key if I am not wrong. Then how would be the end result of you query by including this? – Zeus Jun 11 '16 at 07:13
  • 1
    You are not wrong. In the `WHERE` clause you need to add `ctry_name = 'Canada'`. To summarise: you only need to join a table if you need to filter by one of the columns in that table e.g. `ctry_name = 'Canada'` or you need to get to another table e.g. WineMaker. We don't need any information out of WineMaker but we need to join this table to get to Countries. – Alex Jun 11 '16 at 07:15
  • That makes sense :) Thanks for you precious time and effort – Zeus Jun 11 '16 at 07:17