Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


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

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.

(c) Using the join command in the Linux shell (not MySQL) on your AWS instance, perform the following computation.

pgene, 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)

8.

9. Copyright (c) 2000, 2011, Oracle and/or its affiliates. A11 rights reserved.

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)

6

7 Copyright (c) 2000, 2O11, Oracle and/or its affiliates. All rights reserved.

8

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

10 affiliates. Other names may be trademarks of their respective

11 owners.

12

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

14

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

16 Query OK, 0 rows affected (0.01 sec}

17

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.

Purchase A New Answer

Custom new solution created by our subject matter experts

GET A QUOTE

Related Questions