Problem I. 392. (January 2016)
I. 392. The file talalmanyok.txt (downloadable from our web page) contains data about some Hungarian inventions and inventors. The file is an UTF-8 encoded and tabulator-separated text file, and it does not contain field names.
Columns of the table have the following meaning:
| \(\displaystyle 1^{st}\) column | Family and given names of the inventor or researcher. |
| \(\displaystyle 2^{nd}\) column | Year of birth of the researcher. |
| \(\displaystyle 3^{rd}\) column | Year of death—empty if currently alive. |
| \(\displaystyle 4^{th}\) column | The name of the invention. |
Design and complete a relational database i392 containing the data for inventors and inventions.
– When designing the structure of the database and tables, you should take into account that many inventors have more than one invention, and one invention could be created by more than one inventor.
– Data in the source file should be organized logically into several tables, together with setting up the appropriate field names and types.
– Keys should be created in the tables, and you should design the connections between the individuals.
– By using a Boolean value, the source data should be expanded by a new field specifying whether the invention can be considered as a novelty in the field of electricity/electronics. (In the case of the transformer for example, this Boolean value is true. But for a nuclear power plant, the field value is false, since its essential novelty is not directly related to electricity.) This new field value should be set for each record.
You should also solve the following tasks. Queries should be saved by using the names given in parentheses.
List the inventor(s) who
1. had more than 5 inventions (1otneltobb);
2. created their inventions on their own without any collaborators (2egyedul);
3. had inventions related to electronics and also some other inventions not related to this field (3tobb);
4. had joint inventions with Kálmán Kandó. List both the inventors and the inventions. (4kando);
5. has their family name appearing in the name of the invention. The inventor's full name and the name of the invention should be displayed. (5nevado).
List the inventions
6. that were created by the same inventor who invented radial engine (6csillag);
7. that were invented by the oldest inventor appearing in our database (7régi);
8. whose inventor does not appear in the database, because the inventor is unknown or the invention has too many inventors (8nevtelen).
Your database (i392.odb, i392.accdb, i392.sql) and data tables in tabulator-separated text files should be submitted as a compressed file i392.zip.
(10 pont)
Deadline expired on February 10, 2016.
Statistics:
4 students sent a solution. 10 points: Radnai Bálint. 7 points: 1 student. 5 points: 1 student. 1 point: 1 student.
Problems in Information Technology of KöMaL, January 2016