[SOLVED] Assessment 2 ISY2095 Database Concepts

Task 1: Non-Nested Queries

Provide SQL queries that answer the following questions. The queries must be nonnested. Providing nested queries in this section will not receive any marks. Views are not to be used. The LIMIT clause is not to be used. Your SQL statements must be valid for SQLite Studio environment and free of any errors.

QUESTION 1.1 How many times the same department name is used across multiple institutions? Write a query that only lists department names and the number of times, named DeptNameCount, each unique name has been used across multiple institutions. Empty names are not meaningful and should be removed from the output. Sort the results by, firstly, the number of times of use of each name from large to small and, secondly, department names in alphabetical order.

QUESTION 1.2 We would like to know if there are academics who are doctors, whose work description in one of their fields of interest (i.e. Interest.Descrip ) contains the keyword “database”, and who also have more than one paper with titles containing the same keyword. Write a query that only lists academic title, first name, surname, and the number of papers containing the keyword “database” (named PaperCount ) for such academics. Sort the results by, firstly, the number of papers from large to small and, secondly, surname and first name in alphabetical order.

QUESTION 2.1 Which departments in Queensland or Victoria do not have any academics? List the department number, institution name, department name, and state. Use IN or NOT IN as part of your query.

QUESTION 2.2 Which departments in Queensland or Victoria do not have any academics? List the department number, institution name, department name, and state. Use EXISTS or NOT EXISTS as part of your query.

Question 2.3 Find the academic number and full name of academics who have written one or more papers with Adam Kilg. Do not include Adam Kilg in the results. Do not include duplicates. Sort the results by surname and then first name in alphabetical order.

Question 2.4 Find and list the most common department name. The LIMIT clause must not be used.

Question 3.1 List the academic number of academics who have five or more interests and yet have not authored any papers. Use a set operator as part of your query.

Question 3.2 List the academic number of academics whose interests include all the interests of academic number 114. Do not report duplicates. Do not report academic 114. Use a set operator as part of your query.

Question 4.1 Complete the following tasks and show all your work.

4.1.1 For each of the relations written in the schema, write down all functional dependencies (FD’s) shown in the schema. Do not write down trivial functional dependencies, such as Ccode→CCode.

4.1.2 Compare the FD’s created in step 4.1.1 with the business rules shown in Figure 2 and, if there are any mismatches between the two, correct the FDs and provide explanation for why the correction is made.

Question 4.2 Complete the following tasks and show all your work.

4.2.1 Write down the highest normal form each of the relations shown in the schema is in. For each of the relations, state the reasons why it does not meet the next normal form requirements. This is not required if the relation is in 3NF.

4.2.2 Write the final and corrected schema as the result of your analysis in the previous sections.