Ensuring relationship between child tables exists prior to SQL insert

I have a situation where I have three tables: user, assignment and test. A user must have completed an assignment before he can take the test. This means the test table has both both a user foreign key and an assignment foreign key on it.

I could write a sql statement like this: insert into test (name, user_id, assignment_id) values ("final exam", 1, 1) which would check to see if the user and assignment exist before doing the insert. However it would not check to see if the user and assignment were related.

The easy way to solve this problem is to do a separate query before the insert to ensure the user has an assignment. I’m wondering if I can accomplish both in one query though. I’m not all that experienced with constraints or subqueries, both of which could be solutions. Looking for a best practice here as it will be used throughout an application.

Get top 5 students for each class in many-to-many relationship

So I’m doing this in MySQL.

I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.


students

student_id | name | email | GPA


classes

class_id | name | room


student_class_rel

student_id | class_id


I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this?

For example, this query gets 100 classes and then gets every student_id for all students in that class

SELECT *,      JSON_ARRAYAGG(student_id)  FROM classes      INNER JOIN student_class_rel      USING(class_id)  GROUP BY class_id;  LIMIT 100 

I want that query, expect the JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student.

Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query.

And I’m aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I’ve spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there’s any additional info needed).

How can a ‘Request’ store a ‘Result’ code and its associated ‘Error’ code and still enforce the relationship between Result and Error?

I’m an application developer creating the database structure to represent a flat file message format. I’d like to ask the collective knowledge the best/correct way to represent the following scenario:

Request table (PK: RequestID) contains requests; a request has a Result property which is indeterminate (null) until the request has completed.

Result table (PK ResultID) is a lookup table containing (currently) two possible results:

  • Success (Result ID = 0)
  • Failure (Result ID = 1)

ErrorCode table (PK ErrorCodeID) is a lookup table containing error details and their parent ResultID:

  • No Error (ErrorCodeID = 0, ResultID = 0)
  • Generic Error (ErrorCodeID = 1, ResultID = 1)
  • Queue Full (ErrorCodeID = 2, ResultID = 1)
  • Unsupported Interface (ErrorCodeID = 3, ResultID = 1)
  • etc…

I’ve created a one to many relationship between Result (one) and ErrorCode (many). A ‘Success’ Result can only have a ‘No Error’ Error Code, while a ‘Failure’ Result can have a single error code of ‘Generic Error’, ‘Queue Full’, ‘Unsupported Interface’, etc.

When the Request has completed, I need to store the result and its associated error code.

I’ve thought of combining the two tables but that strikes me as repeating columns.

I’ve also thought of having the Request table store the ResultID and the ErrorCodeID but this doesn’t enforce the Result to ErrorCode relationship.

I’m a big believer in database that ‘defends itself’ from bad data so I want the relationship to reject a Result/ErrorCode combination that is invalid; a.k.a. a Result of ‘Success’ and an ErrorCode of ‘Generic Error’ or a Result of ‘Failure’ and an ErrorCode of ‘No Error’.

I’m also a big believer in solid initial design so when changes come down the pike at a later date (as they always do) the structure will not need rework.

Thank you in advance for your time.

Regards, John E.

Best approach for extending a foreign relationship for an existing table

I’m not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don’t uniquely identify a row:

+--------+------+------+------+------+ | RateId | col1 | col2 | col3 | Rate | +--------+------+------+------+------+ 

I have a new table that needs to match a rate. The new table has 3 of the required columns to make a match but those columns will return a number of rows in the Rate table. To uniquely identify a rate for the new table I need to match on MaterialTypeId and UnitTypeId where UnitTypeId can be null.

+------------+------+------+------+----------------+------------+ | MaterialId | col1 | col2 | col3 | MaterialTypeId | UnitTypeId | +------------+------+------+------+----------------+------------+ 

What is the best approach to resolving this? I could add the two additional columns to the Rate table but that would not be relevant for the current uses of the Rate table, i.e. the two additional columns would be null for all existing rows.

I could introduce an intermediate table and join on MaterialTypeId & UnitTypeID which would return multiple rows and then join using the existing columns to uniquely identify the rate:

+--------+----------------+------------+ | RateId | MaterialTypeId | UnitTypeId | +--------+----------------+------------+ 

Is that the correct approach?

Is there an effective way to declare a web page’s informational relationship to multiple social media accounts?

In the <head> of each article page on a given website, I would like to indicate a relationship between that article page and multiple social media accounts.

At the very least, wherever possible, I would like to <link> each article-based web page to social media accounts for:

  • the website brand
  • the article publisher’s brand
  • the article author

It’s worth noting that:

  • the website is always the same
  • the publisher-contributor sometimes changes
  • the author-contributor often changes

E.g. I understand that a conventional link to a Twitter account looks like this:

<link rel="me" href="https://twitter.com/example-twitter-account"> 

which is great, but given that (I’ve just learned) rel="me" is the XFN equivalent of rel="author", I conclude this is an appropriate form to use only when referring to the author-contributor.

So what should I use for the publisher-contributor and for the website itself?


Ideas:

For the website’s own Twitter account, could I (possibly?) use rel="alternate" or should I be using rel="[something else]"?

I initially believed that I wouldn’t be able to use rel="author" or rel="publisher" to link to social media accounts, because I was already using those rel attributes to express relationships with specific web pages.

But now it occurs to me that I might use rel="publisher" more than once, like this:

<link rel="publisher" href="https://publisher-site.com/" /> <link rel="publisher" href="https://twitter.com/example-publisher-account" /> 

Question:

How should I approach <link>-ing a web document to three separate twitter accounts?


Added:

I note that I can use the following:

  • Website: <meta name="twitter:site" content="@websiteAccount">
  • Author: <meta name="twitter:creator" content="@authorAccount">

But that’s still only two out of three.

Additionally, it’s Twitter-specific and I am looking for something which can apply to any social media platform (Facebook, LinkedIn etc.)

What relationship do dwarves and half-orcs have in Golarion?

I am just writing an adventure around the dwarven and orc history. In my party I have a dwarf and a half-orc (we have not played yet). Reading the Pathfinder Handbook Dwarfes of Golarion it is quite clear that they absolutely hate Orcs. But there is no information about the relation to half orcs. Is there any source with information about it?

RelationshipBlog.pw – Relationship News, Automated, 100% Monetized with High Potential For $39

Do not miss this professional looking website for those who are new in this business specially for Newbies. This website has potential to earn from Ad Networks like Adsense, Clickbank, CJ, and more.

Website Name: RelationshipBlog.pw
Price: $ 39

This site is Autopilot, updates from different resources. No Need to do anything. Everything is ready to go. Contents are updating on daily basis or weekly.

Website Characteristics:
– Quality Designed WordPress site.
– Relationship…

RelationshipBlog.pw – Relationship News, Automated, 100% Monetized with High Potential For $ 39

Relationship between circuit size and formula size in Sipser text

The Sipser text (3rd edition) contains a proof that 3-SAT is NP-Complete based on Boolean circuits. Part of the proof contains the remark that the reduction from the circuit to the Boolean formula can be done in polynomial time.

First question: is it correct to say that if a circuit C of polynomial size exists, then there must exist a formula $ \varphi$ of polynomial size where C is satisfiable if and only if $ \varphi$ is satisfiable?

Second question: is the Boolean formula $ \varphi$ still of polynomial size if C is of polynomial size and C is derived from a deterministic Turing machine M? This seems to be described in the proof of the earlier theorem in Sipser where (by building C from a tableau of M) it is shown that if $ \mbox{A $ \in$ TIME$ (t(n))$ for $ t(n) \geq n$ and $ n \in \mathbb{N}$ }$ then A has circuit complexity $ O(t^2(n))$ .