You are using the inheritance (also known in entity-relationship modeling as “subclass” or “category”). In general, there are 3 ways to represent it in the database: “All classes in one table”: Have just one table “covering” the parent and all child classes (i.e. with all parent and child columns), with a CHECK constraint to ensure … Read more
SQL treats NULL specially per its version of 3VL (3-valued logic). Normalization & other relational theory does not. However, we can translate SQL designs into relational designs and back. (Assume no duplicate rows here.) Normalization happens to relations and is defined in terms of operators that don’t treat NULL specially. The term “normalization” has two … Read more
With with() is for eager loading. That basically means, along the main model, Laravel will preload the relationship(s) you specify. This is especially helpful if you have a collection of models and you want to load a relation for all of them. Because with eager loading you run only one additional DB query instead of … Read more
You have a few options, all varying in “correctness” and ease of use. As always, the right design depends on your needs. You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table. You could create M:M reference tables enabling both ticket:user and ticket:group relationships. Perhaps in … Read more
For INNER joins, no, the order doesn’t matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.*, b.*, c.*. For (LEFT, RIGHT or FULL) OUTER joins, yes, the order matters – and (updated) things are much more complicated. First, outer joins are not commutative, so … Read more
We need to know all the FDs (functional dependencies) that hold to determine CKs (candidate keys), not just those in some list. Look at a (correct & general) definition of CK or algorithm for finding CKs (in a published textbook, not a youtube video). Is your list appropriately a closure (all the FDs that hold) … Read more
One-to-one: Use a foreign key to the referenced table: student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a # “link back” if you need You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating … Read more
You have at least these five options for modeling the type hierarchy you describe: Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row. Class Table Inheritance: one table for Products, … Read more
Is there any systematic step-by-step or mathematical way to construct SQL query from a given human-readable description? Yes, there is. It turns out that natural language expressions and logical expressions and relational algebra expressions and SQL expressions (a hybrid of the last two) correspond in a rather direct way. (What follows is for no duplicate … Read more
For Oracle Database provide this information: Describe the symptoms of the problem Describe the behavior that cause the problem. Is the behavior of the query stable or does the problem occurs only sometimes, with specific parameters or simple random. Can you reproduce this behavior in an IDE (e.g. SQL Developer)? Describe the environment Define the … Read more