ISYS2095-Assignment 2 SQL and Normalization Solved

The objective of this assignment is to measure your understanding of SQL querying and your ability to apply your knowledge of normalisation to improve database designs. 

Part A: SQL Programming
1.     Write non-nested queries 

2.     Nested queries 

3.     Set Operators 

Part B: Normalisation  
4. Identify design shortcomings and schema writing errors and suggesting improvements. 

Part A: SQL Programming
Preliminaries  
Tasks 1, 2 and 3 utilise a (fictional) ‘research’ database instance with the following schema.

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode) 

 Academic(AcNum, DeptNum*, FamName, GiveName, Initials, Title) 

Paper(PaNum, Title) 

Author(PaNum*, AcNum*) 

Field(FieldNum, ID, Title) 

Interest(FieldNum*, AcNum*, Descrip)  
The following must also be noted.

1.      Each Academic belongs to one Department and is the Author of some (or no) Papers.  

2.      Each Paper has at least one Author, but there could be several Authors for a Paper.  

3.      Each Academic works in some (or no) Fields (i.e., research areas) described in the table Fields.  

4.      The Interest table stores data about the fields of research an academic is interested in and the Descrip attribute provides a more detailed description of an academic’s work.

The research database instance is available on Canvas under Modules-Sample Databases and Tools.

Important Requirements 
For all the questions in Part A, it is essential that you note the following items.

1. Write only one query for each question. Providing more than one query per question will not earn any marks. Note that where nested queries are required, all the nested queries will be considered as one query. 2. The provided queries must be valid and free from syntactical errors.

3. In addition to providing the query, you must also provide a snapshot of the first 10 results of your query. The snapshot must also show the total number of results. A sample snapshot is provided below for your reference.s

 

 

Task 1: Non-Nested Queries
Provide SQL queries that answer the following questions. The queries must be non-nested. 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.

 

Task 2: Nested Queries
Provide nested SQL queries that answer the following questions. Providing non-nested queries in this section will not receive any marks. Views are not to be used. Set operators are not to be used. The LIMIT clause is not to be used.  

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.

 

Task 3: Set Operators 
In this section, all questions must be answered with only one query that uses one or more set operators. Writing multiple separate queries to answer one question will not receive any marks.

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.

 

Part B: Normalisation
Task 4: Relational Database Design 
The ER diagram shown in Figure 2 is designed according to the business rules for the operation of offering courses in a university and shows the interaction between courses, their offerings, staff assigned to deliver the course and staff’s contracts.

 

As a result of incorrect application of the mapping process, the ER diagram shown in Figure 2 is mapped into the following relations.

Course(CCode, Name)

CourseOffering(CCode *, OCode, Start Date, Weeks, Has Break)

Contract(CNumber, Start Date, End Date, Salary, Is Full Time, Is Casual, SNo*, Staff Name*)

Staff(SNo, Staff Name, Academic Level)

Lecture(CCode*, OCode*, SNo*)

Tutor(CCode*, OCode*, SNo*, Hours, Rate)

Coordinate(CCode*, OCode*, SNo*, Hours)

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 (FDs) shown in the schema. Do not write down trivial functional dependencies, such as CCode→CCode.

4.1.2 Compare the FDs created in step 5.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.