SQL Game
1) After graduation you just joined the FBI Data Analytics Division in a team of up to three colleagues. So together with your teammates you are about to receive your first task. Before that you need to introduce yourselves to each other within the team. Also start a report .docx file named fak_nom1_fak_nom2_fak_nom3_SQL_murder.docx and write down three common things for everyone in the team. Based on that create your team logo, and team motto and add them to the report file.
2) Next set of things you have to decide on is general team issues such as:
- what are going to be your communicational channels
- who is in charge of writing down the important discoveries
- who would present at the end in front of the audience
- etc.
3) In order to setup your database environment, you should then upload the attached file sql-murder-mystery-no-sol.db in the IDE https://sqliteonline.com/
4) Next comes the case. The FBI wanted your team to help them solve a crime by reading through the crime scene report, but the report got scrambled beyond reading during your party for the new job. Still being smart ones as you are, you partly remember that it was a murder that occurred sometime on Jan.15, 2018. Also you remember it took place in SQL City.
5) So you should start by retrieving the corresponding crime scene report from the police department’s database. and continue doing SQL queries to solve the crime itself.
6) Working with the SQL database you should first construct its graphical scheme representation (also known as entity relationship diagram) which shows the relations within the database tables. You may use any graphical tools to do that. Once you have done it, add it to the report file.
7) Main task is to find correctly the name of the killer through properly worded SQL queries. TRIPPLE CHECK your solution through various logic ways and various queries - after all you do not want to accuse an innocent person but also you do not want to leave a murderer free on the streets. Write down the name it in the report file.
8) Secondary task is to find correctly who the real villain is through properly worded SQL queries. TRIPPLE CHECK your solution through various logic ways and various queries - after all you do not want to accuse an innocent person but also you do not want to leave a criminal-mastermind free on the streets. Write down the name it in the report file.
9) Next write in the report with regular words what is the full story of the crime - exactly what happened, how it happened, and why it happened.
10) Copy your SQL queries as code that led to the successful result to the report file in sequential execution order.
11) Take some time to format your report file aesthetically so that it is most informative and at the same time most readable. If you wish you may rearrange it in a way that makes most sense to you to improve its understanding.
12) Before the deadline go to the link: https://forms.gle/BpCr8tBhX6Xx7Bow6 and input the fields as following (If you send in any other way, YOUR RESULT MAY NOT BE ACCEPTED):
- Faculty number 1: faculty number of the first student
- Name Surname 1: name of the first student
- Your e-mail: put any e-mail regardless
- Assignment title: SQL Game
- Result 1: faculty number of the second student
- Result 2: name of the second student
- Result 3: faculty number of the third student
- Result 4: name of the third student
- Result 5: empty
- Result 6: write the name of the murderer
- Result 7: write the name of the real villain
- File upload: submit your .docx report file