Question 1) This question is based on the Wunschier book.

Homework answers / question archive / Question 1) This question is based on the Wunschier book

Question 1) This question is based on the Wunschier book

Computer Science

Share With

Question 1) This question is based on the Wunschier book.

(a) Create a tab-separated file in your AWS instance that contains all the data from the ‘expression’ table. Your file should look like the following

gene expr_value

alrl1207 8303

alr2938 10323

alr3395 1432

alr3556 8043

alr4392 729

alr4851 633

alr5000 5732

Explain how you created this file on your Linux command lines in the AWS instance. Put your file on Github so it can be checked.

(b) Create a tab-separated file in your AWS instance that contains all the data from ‘annotation’ table. Explain how you created this file on your Linux command lines in the AWS instance. Put your file on Github so it can be checked.

p_{gene, function_1,expr_value, metabolism} (annotation ¥_gene=gene expression)

Put your Linux command on Github so it can be checked.

(d) Consider the problem of listing all duplicate metabolisms in the annotation table using SQL.

i. Give the relational algebra for this query.

ii. Based on your relational algebra above, give MySQL code to implement this query. (Put your code on Github so it can be checked.)

Question 2) This question is based on the Churcher book.

(a). Consider the problem of listing all members (last names, first names, then memberID)

who don’t have a coach.

i. Give the relational algebra for this query.

ii. Give the relational calculus for this query.

iii. Give the MySQL code for this query. (Put your code on GitHub so it can be checked. )

(b). Consider the problem of listing all members (last names, first names, then memberID) who joined during the 2013 calendar year.

i. Give the relational algebra for this query.

ii. Give the relational calculus for this query.

iii. Give MySQL code for this query. (Put your code on Github so it can be checked.)

(c). Consider the problem of finding the member names and IDs of those who didn’t compete in any tournaments in 2013, but have competed in other years

i. Give the relational algebra for this query.

ii. Give the relational calculus for this query.

{m.MemberID, m.LastName, m.FirstName|Member(m), Entry(e) and

m.MemberID=e.MemberIDand ? Entry(y) and y. Year=2013}

iii. Give MySQL code for this query. (Put your code on GitHub so it can be checked.)

(d). Consider the problem of finding the member names and IDs of those who have competed in every year that club members have competed.

i. Give the relational calculus for this query.

ii. Give MySQL code for this query. (Put your code on GitHub so it can be checked.)

End of assignment 1 Task 3

The submission deadline for Task 3 of Assignment 1 is 12pm noon on April 16, 2021. You need to submit your solutions (in one .pdf file) to the link on iLearn prior to this time. Your solutions may be typed or handwritten and scanned. You must show all your working in your solutions for full marks. Refer to iLearn for further submission details. Please note that uploading a file can take up to 15 minutes. You need to submit your file at least 20 minutes before the deadline to ensure a successful submission.

Place your code on GitHub so it can be checked. Include its location (the URL of the repository it’s in, and the file names, so all bits and pieces can be identified and re-assembled by a total stranger in minimal time) in your written document so it can be found.

16 Relational Databases with MySQL

Now, let us login as awko1ogist and create the database compbio1. Terminal 199 shows how.

Terminal 199: Creating Database compbio1

1. $ mysq1 -u awko1ogist

2. ERROR 1045: Access denied for user ‘awko1ogint’d’ loca1host’ (using password: NO)

3. $ mysq1 -u awko1ogist -p

4. Enter password:

5. Welcome to the MySQL monitor. Commands end with; or \g.

6. Your MySQL connection id is 57

7. Server version: 5.5.24-0ubuntu0 .12. 04 .1 (Ubuntu)

10.

11. Oracle is a registered trademark of oracle Corporation and/or its

12. affiliates. Other names may be trademark of their respective

13. Owners.

14.

15. Type ‘help;‘ or ‘\n’ for help. Type ‘\c’ to clear the current input statement.

16.

17. mysq1> SHOW DATABASES;

18. + ---------- +

19. | Database |

20. + ------- +

21. | information_ schema |

22. | test |

23. + ----------- +

24. 2 rows in set (0.00 sec)

25.

26. mysq1> CREATE DATABASE mydb;

27. ERROR 1044: Access denied tor user ‘awko1ogint’d'1oca1hest’ to database ‘mydb’

28. mysq1: CREATE DATABASE comphisa1;

29. Query OK, 1 row affected (0.04 sec}

30.

31. mysq1> SHON DATABASES;

32. + ---------- +

33. | Database |

34. + --------- +

35. | information_schema |

36. | compbio1 |

37. | test |

38. + -------- +

39. 3 rows in set (0.00 sec)

40.

41. mysq1>

Lines 1-2 show an unsuccessful login. This is a common error: the option -p has been forgotten. Line 3 shows the correct command. You will be asked to enter your password and have to press the (Enter) key. In line 17, we instruct MySQL to show us all available databases. As you can see, two databases are already present. They belong to the MySQL system. In line 26, we try to create the database mydb. Since user awko1ogist has only access to database compbio1 (see Terminal 198 on the facing page), he cannot create another database. Thus the error message in line 27. The database compbio1, however, can be created without any problems (line 28). In line 31, we ask MySQL to display all existing databases again and—there it is: compbio1. Now, we have a user and a database—time to play around.

16.2.4 Set Up an User Account and Database

Up to now, you only have a root account for the MySQL server. You should, however, always work as a normal user to avoid trouble caused by, e.g.. too little sleep causing accidental deletions. Therefore, let us set up an user account for a user named awkologist with the password awkelogy. You can of course use your own name.

Terminal L198: Setting Up Account for User awko1ogist

1 $ mysq1 -u root -p

2 Enter password:

3 Welcome to the MySQL monitor. Commands end with; or \g.

4 Your MySQL connection id is 55

5 Server version: 5.5.24 – 0ubuntu0.12.04.1 (Ubuntu)

9 Oracle is a registered trademark of Oracle Corporation and/or its

10 affiliates. Other names may be trademarks of their respective

11 owners.

13 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

15 mysq1> GRANT ALL ON compbio1.* TO awko1ogistd1oca1host IDENTIFIED BY “awko1ogy”;

16 Query OK, 0 rows affected (0.01 sec}

18 mysq1> QUIT

19 Bye

20 $

With the GRANT command in line 15 in Terminal 198 we can set access rights for users. Here, we provide all (ALL) to all tables (*) rights on the database compbiol to the user awkologist from the localhost, e.g., the computer you are sitting in front of. If you would like to allow remote access, you should use either awkologist@123.123.123.123 or awkologist@client.com to specify an IP or client domain, or awkologist@% to allow access from any computer. Anyway, user aivkologist has to identify himself by the password awkology. That is it.