This looks like a homework assignment, so I am going to give you a few tips without doing you (or your potential future coworkers) the disfavor of helping you gain credentials that do not reflect acquired skills. First, you need to study the syntax of the language you will use for this assignment. An excellent depiction of it is here, [SQL As Understood By SQLite](https://sqlite.org/lang.html). Second, you need to study [SQLite Foreign Key Support](https://sqlite.org/foreignkeys.html), where you will find not just how to effect foreign key relationships between tables but a good explanation of what foreign keys are for. All of these web pages can be found via the [Permuted Index](https://sqlite.org/sitemap.html#pindex). Once you have exhausted your self-directed study options, and have made a sincere attempt to write the SQL required for your assignment, if it does not quite work as **you intend**, you can bring your SQL here (to this forum) with specific questions, demonstrating your understanding or misunderstanding related to specific language features, and you will find a number of participants here willing to help you complete the learning that your homework is designed to promote.