Asked  7 Months ago    Answers:  5   Viewed   22 times
SELECT id, amount FROM report

I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?


       IF(type = 'P', amount, amount * -1) as amount
FROM report


Additionally, you could handle when the condition is null. In the case of a null amount:

       IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report

The part IFNULL(amount,0) means when amount is not null return amount else return 0.

Tuesday, June 1, 2021
answered 7 Months ago

An entity is not weak because it can't exist independently, but because it can't be identified independently. Therefore, a relationship that "leads" to a weak entity is called "identifying" relationship. In practice, this means that the parent's primary key is migrated into (usually proper) subset of child's PK (the term "weak entity" is usually defined in relation to primary keys, though it could in theory apply to any key).

It is perfectly legit to have an entity that can't exist independently, but can be identified independently - in other words, that is in a non-identifying relationship to a non-NULL.

You have to ask: can historyLineID be unique alone, or in combination with orderID? I suspect the latter is the case, which would make it a weak entity.

Is this really a correct weak entity relationship?

What you have shown us isn't a weak entity - parent's PK is not migrated into the child's PK.

Is there other ways to identify them?

You have essentially two options:

  • orderHistory has a composite PK: {orderID, historyLineID}, where orderID is FK. BTW, this PK could be considered "natural":

    enter image description here

  • orderHistory has a surrogate PK: {orderHistoryID}, while orderID is outside of the PK. You'd still need to have an alternate key {orderID, historyLineID} though:

    enter image description here

Should I add the PK of table order to table orderHistory and make it a composite primary key?

Yes, this is the first option described above. Unless you have child relationships on orderHistory itself, this is also the best solution. If orderHistory does have children, than this may or may not be the best solution, depending on several factors.

What if I decide to model this as a normal One-To-Many relationship where orderID is added as a foreign key instead? what are the cons of doing so?

This is not either-or. A field can be both FK and a part of a (primary or alternate) key, as shown above.

Will ignoring Weak entities at all cause any problems later in a design provided all tables are in 3rd normal form?

You won't be able to reach 3NF unless you specify your keys correctly, and you won't be able to do that without considering which entity can be identified independently and which can't.

Thursday, July 22, 2021
answered 5 Months ago

You can adding apply tuple, when there is list in a cell , pandas sometime return the wired result

df.loc[df['list_value'].apply(tuple) == tuple([1,2,3])]
    text list_value
0  text1  [1, 2, 3]
Wednesday, August 25, 2021
answered 4 Months ago

try this and select export to self constrained file .sql along with the path.

enter image description here

Tuesday, September 14, 2021
answered 3 Months ago

you may use .isin():

In [28]: df2[df2['id'].isin(['SP.POP.TOTL','NY.GNP.PCAP.CD'])]
                  id                                        name  
7478  NY.GNP.PCAP.CD  GNI per capita, Atlas method (current US$)
9568     SP.POP.TOTL                           Population, total

7478  World Development Indicators
9568  World Development Indicators

7478  GNI per capita (formerly GNP per capita) is th...
9568  Total population is based on the de facto defi...

7478  b'World Bank national accounts data, and OECD ...
9568  b'(1) United Nations Population Division. Worl...

7478  Economy & Growth ; Climate Change
9568           Health  ; Climate Change
Tuesday, September 14, 2021
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :