Create a query to produce single output-er model, Database Management System

This assignment concerns the Research database. The database ER model is shown above, where attributes are omitted for clarity. The relational model for the database is:

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)

DeptNum, AcNum, PaNum, and FieldNum are the identifiers of departments, academics, papers, and fields of interest respectively. Each Academic belongs to one Department and is the author of some (or no) Papers. Each Paper has at least one author. The table Fielddescribes areas of research work; each Academic works in some (or no) Fields, and may provide a description Descrip of their work.

The research database is used by the Department of Education, Science and Training of the Australian government on a regular basis to report on academic departments of education institutions. To this end, you are asked to write SQL queries, and present the report based on the output from your queries. Note that data inconsistency has been introduced during the data entry process, which you have to take care of in producing your report. It is known that the same state information may be represented in different ways. For example, Victoria may be kept as VIC, vic, or Vic. It is also known there are missing values for some attributes. For example, not all academic departments have their State information recorded in the database.

1. How many institutions contain the word "Technology" in their name. Your query must produce a single number as its output.

2. Make a list of academics that have more than five interests and have written or co-written less than ten papers. Use one IN construct and one EXISTS construct in your query. Output all details of the academics in the list.

3. Use a Set operator to create a list of academics who have written or co-written more than 5 papers and also have greater then 3 interests. List their academic number in the output.

4. Explain the following SQL query in English;

select givename, famname, instname

from academic natural join department

where acnum in

(select acnum

from author

where acnum not in

(select acnum

from interest

group by acnum))

and deptNum in (select deptNum

from academic

where deptname = 'Computer Science');

5. Find all the academics who have co-written a paper with academic number 151. Use only the Author table in a join query. You can use multiple instances of the table. The output should list the academic number of each co-author just once in the output. The output should not include academic number 151.

6. Write a query to find papers with more than five authors where the authors all come from different departments.

Posted Date: 3/19/2013 5:34:57 AM | Location : United States







Related Discussions:- Create a query to produce single output-er model, Assignment Help, Ask Question on Create a query to produce single output-er model, Get Answer, Expert's Help, Create a query to produce single output-er model Discussions

Write discussion on Create a query to produce single output-er model
Your posts are moderated
Related Questions
What are the different approaches for database design? There are two basic approaches for database design which are as follows: Attribute driven: It compiles a list of att

The participation Constraints shows whether the existence of an entity depends on its being associated to another entity by the relationship type. There are two kinds of participat

The aims of the project are: 1. To enable the students to undertake the detailed design of a specific plant or process 2. The design projects includes: process selection; und

Define about the Stored Procedure - It's a set of T-SQL statements combined together to perform a single task formed by combining numerous small tasks. - When you essentiall

What is the error handling Traditionally, bits have been sent over line lacking any error correcting scheme in physical layer. The presence of the CPU in each modem makes it po

Consider the following relations: BRANCH( bno, street, area, city, pcode, Tel_no, Fax_no) STAFF( Sno, Fname, Lname, address, position , salary, bno) Express the subsequent qu

What is conceptual model ? A conceptual model is a independent of both hardware and software.

How to create a table from existing table? Create table as e.g, To create a new table ‘N_emp' with employee names and their identification numbers only from employee ta

Key to represent relationship amongs tables is called ? A Key that represent the relationship between tables is called Foreign Key.

What is Nested Trigger? A trigger can also having INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause ano