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.

Assessment criteria

This assessment will measure your ability to:

• Write SQL statements for retrieving data for specific user requirements,
• Write various forms of SQL queries to demonstrate your understanding of the main concepts; and
• Identify and understand design problems and suggesting improvements.

Course learning outcomes

This assessment is relevant to the following course learning outcomes:

CLO3	Identify issues with, compare and justify relational database designs using the functional dependency concepts.
CLO 4	Apply SQL as a programming language to define database schemas and update database contents.

Assessment details

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 academics 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.
2. 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 staffs 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 CCodeCCode.

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.

Referencing guidelines

Use RMIT Harvard referencing style for this assessment.

You must acknowledge all the courses of information you have used in your assessments.

Refer to the RMIT Easy Cite referencing tool to see examples and tips on how to reference in the appropriated style. You can also refer to the library referencing page for more tools such as EndNote, referencing tutorials and referencing guides for printing.

Submission format

You should submit one PDF document with all answers together.

You must use Lucidchart to work on Part 1 of your assignment. You may use Word or any other word processor to compile your submission. Use section titles to indicate which question you are answering. At the end, convert it into PDF format. Do not submit Word files. If that option is not available on your system there are free pdf converters online you can utilise (e.g. http://convertonlinefree.com/).

Submit the PDF file by the due date to the Canvas.

Academic integrity and plagiarism

Academic integrity is about honest presentation of your academic work. It means acknowledging the work of others while developing your own insights, knowledge, and ideas.

You should take extreme care that you have:

• Acknowledged words, data, diagrams, models, frameworks and/or ideas of others you have quoted (i.e., directly copied), summarised, paraphrased, discussed, or mentioned in your assessment through the appropriate referencing methods.
• Provided a reference list of the publication details so your reader can locate the source if necessary. This includes material taken from Internet sites.

If you do not acknowledge the sources of your material, you may be accused of plagiarism because you have passed off the work and ideas of another person without appropriate referencing, as if they were your own.

RMIT University treats plagiarism as a very serious offence constituting misconduct.

Plagiarism covers a variety of inappropriate behaviours, including:

• Failure to properly document a source
• Copyright material from the internet or databases
• Collusion between students

For further information on our policies and procedures, please refer to the University website.

Penalties for late submissions

Late submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than 5 days late, 100% penalty applies.

Assessment declaration

When you submit work electronically, you agree to the assessment declaration.