Reference no: EM133177041 , Length: word count:1500

ISYS2095 Database Concepts - RMIT University

Overview
The objective of this assignment is to measure your understanding of SQL querying, your ability to apply your knowledge about database normalisation to improve database designs, and advance your knowledge of database system technologies by carrying out research.

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 C: Database Systems Research
5. Carrying out research to understand various data modelling and database system technologies and applying the acquired knowledge in the context of a specific system.

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,
• Identify and understand design problems and suggesting improvements, and
• Understand the differences between various database systems, and choose suitable platforms given the context of a specific system.

Learning outcome 1: Describe various data modelling and database system technologies.
Learning outcome 2: Apply SQL as a programming language to define database schemas and update database contents.
Learning outcome 3: Apply SQL as programming language to extract data from databases for specific users' information needs.
Learning outcome 4: Design a database schema using conceptual modelling mechanisms such as entity- relationship diagrams.

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 query must be 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.

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 or LIMIT clause must not be used. Your SQL statements must be valid for SQLite Studio environment and free of any errors.

QUESTION 1.1 How many times has the same family name been used in the same department? Write a query that lists (column 1) family names, (column 2) department names, and (column 3) the number of times each unique family name has been used. Column 3 must be named FamCount. Regardless of whether the current database instance contains any empty family name or department name cells, the query must also check for the empty names and remove them from the output.

QUESTION 1.2 We are looking for academics who are either Associate Professors or Professors (i.e., academic title), and have interests in arithmetic or logic design. To find out about an academic's interests, we must examine Interest.Descrip to see if it contains the phrases "arithmetic" or "logic design" in any form. Write a query that only lists (column 1) academic title, (column 2) first name, (column 3) surname, (column 4) the number of papers containing the phrases "arithmetic" or "logic design" in any form. Column 4 must be named as "Paper Count". Sort the results by, firstly, the number of papers from large to small and, secondly, surname and first name in alphabetical order.

This question is asking you to examine two different sources of data about the interests of each academic.
1. Interest.Descrip should be used to judge whether an academic has declared interest in the specified domains. This consideration is used only for the purpose of filtering.
2. Paper.Title should be used to measure the actual interest of an academic demonstrated by the papers he/she published. This is related to Column 4.
In other words, an academic can declare that they are interested but might never get around to publish any papers in that field. You are examining both elements to answer the question.
For this question, it is acceptable to have sub-selects in the FROM clause.

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, set operator or LIMIT clause must not be used.
Question 2.1 Find the departments with postcodes between 2000-5000 that have three or more academics. List (column 1) the department number, (column 2) institution name, (column 3) department name, and (column 4) state. Use IN or NOT IN as part of your query.
Question 2.2 Find the departments in Victoria that do not have any academics or the departments in New South Wales that have more than 5 academics. List (column 1) the department number, (column 2) institution name, (column 3) department name, (column 4) state, (column 5) the number of academics in the department. Use EXISTS or NOT EXISTS as part of your query.
Question 2.3 Which academics have written one or more papers with another academic with the same first name. List (column 1) academic #1's name, (column 2) academic #1's surname, (column 3) academic #2's name, (column

4) academic #2's surname, and (column 5) the number of papers jointly written by academics #1 and #2. Column 5 must be named "No of Papers". The result set must be ordered by academic #1's name and must not contain duplicates.
In the context of this question, the two tuples containing {Academic #1, Academic #2} and {Academic #2, Academic #1} are two different results and not considered duplicates.
Question 2.4 Find and list the most common academic family name. List (column 1) the academic family name, and (column 2) the number of times the academic family name is 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 Find the academics in Victoria who have four or more interests but have not authored any papers yet. List (column 1) academic name, (column 2) academic surname, (column 3) number of interests, (column 4) department name, and (column 5) department state. Use a set operator as part of your query.

Question 3.2 Find the academics whose interests include all the interests of academic Sheehan Ahson or all the interests of academic Makki Adam. List (column 1) first name, and (column 2) surname. The result set must not contain duplicates or the name of Sheehan Ahson or Makki Adam.

Part B: Normalisation

Task 4: Relational Database Design
In this task, an ER diagram is presented that represents the business rules for a business. This ER diagram is created in consultation with the business owner and is a true and correct representation of business rules.

You are a member of the database design team. A colleague attempted to transform the ER diagram into the corresponding schema through the application of the mapping process. However, you know that their schema is incorrect because the mapping process was not applied carefully. You are asked to fix the schema by identifying Functional Dependencies (FDs), testing for Normal Forms (NFs), and carrying out decomposition where necessary.

As only the ER diagram is correct, the correct FDs can only be understood from the ER diagram. However, you are not asked to transform the ER diagram to a new schema but to correct the given schema through the application of normalisation and decomposition.

Figure: University ER Diagram

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

For each of the relations written in the schema, write down all its Functional Dependencies (FDs) shown in the schema. Do not write down trivial functional dependencies, such as CCode→CCode.

It is essential to note that this question is not making any reference to Figure 2, which means you are expected to exercise your ability to read the FDs embedded in the given schema. Do not forget that business rules (FDs) can be written in plain English, shown in a conceptual ER model, or embedded in schema language. In this exercise, you are dealing with the latter.

Compare the FDs 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 corrections are made.

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

Write down the highest Normal Form (NF) each of the relations shown in the schema is in. For each of the relations, state the reasons why the requirements of the next level NF are not met. This is not required if the relation is in 3NF.
Write the final and corrected schema as the result of your analysis in the previous sections.

Part C: Database Systems Research

Task 5: Research for the Suitable Database System

As a database expert, you are invited to make a recommendation for the backend database solution to the data for the system explained below. Commercial DBMS vendors can supply one of the following platforms for this purpose.
1. Traditional relational database systems (such as Oracle and SQL Server)
2. No-SQL database systems (such as MongoDB)
A final decision will be made by the owner of the system based on your recommendations.
Write a report identifying the advantages and disadvantages of both approaches specifically for this application. Present a convincing recommendation based on your findings. Your report may include case studies for both paradigms and draw conclusions based on their findings.

Personalised Travel Recommendation System

A fictitious company, ABC Travels, wants to create an online system, named PersoTraveller, that enables travellers to do the following via the web.

1. Book airplane tickets and hotel accommodation.
2. Receive personalised recommendations about travel destinations and special offers.
3. Receive personalised recommendations about the type of accommodations in each travel destination.

Details of System Features

F1. ABC Travels has arrangements with numerous airlines and PersoTraveller must store the details of all the flights of these airlines along with all the details specific to that airline. Beside the general flight information (e.g., date, departure time, arrival time, etc), there are many airline-specific features that PersoTraveller must keep track of (e.g., various types of meals and portions, types and arrangements of seats, fare classes, etc). Not all airline-specific features can be known in advance as each airline has total freedom to offer various features for marketing purposes.

F2. PersoTraveller must also receive data about all accommodation providers that partner with ABC Travel. The structure of this data includes some known attributes such as the provider's name, address, number of rooms, number of bedrooms in each room, and whether each room is booked for a specific date. There are some provider-specific attributes that should be stored too. The nature of this data is not fixed and can vary for each provider. Some examples include special features of rooms, additional luxury services, bundle offers and more.

F3. PersoTraveller is expected to not only store the abovementioned data but also provide highly personalised recommendations for each customer according to their browsing habits, past travels, preferred accommodation-provider-specific attributes and airline-specific features.

This is a research task. You are required to find high quality material to educate yourself regarding the strengths and weaknesses of each named platform. After reporting this information in your report, you should also discuss how they relate to the requirements specified in the abovementioned system. Finally, you should recommend which platform is suitable and convincingly justify your recommendation.

The length of the report should be between 1000-1500 words.

Attachment:- Database Concepts.rar