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

Assignment Help:

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.


Related Discussions:- Create a query to produce single output-er model

Produce a logical database design for pharma, Using a database design appro...

Using a database design approach of your choice, produce a logical design for Pharma. 1) Your answer must consist of ONE the following: An entity-relationship (ER) diagra

Assign, #quesDefine a job scheduling strategy that will meet business requi...

#quesDefine a job scheduling strategy that will meet business requirement of reporting availability by 6am CST for the following cubes? Show the job scheduling dependencies in a pi

Creating views with replace option, Creating views with Replace option : Th...

Creating views with Replace option : This option is used to alter the definition of the view without recreating and dropping it or regranting object privileges previously granted o

Define transitive dependencies, What do you understand by transitive depend...

What do you understand by transitive dependencies? Define with an example any two problems that can take place in the database if transitive dependencies are available in the datab

Data manipulation language, Data Manipulation Language Data manipulatio...

Data Manipulation Language Data manipulation language (DML) describes a set of commands that are used to query and change data within existing schema objects. In this case comm

File organization, what is file organization? what are file organization te...

what is file organization? what are file organization techniques.explain in detail? explain random file organization technique, multikey file organization technique?what is indexin

Discuss the count function, Discuss the count function? The COUNT funct...

Discuss the count function? The COUNT function used returns the number of tuples or values fixed in a query. The count function comprises two types of syntax: (1) COUNT (*)

DBMS, to develop elrctronic database from manual forms

to develop elrctronic database from manual forms

What are the reasons of bucket overflow, What are the reasons of bucket ove...

What are the reasons of bucket overflow? Explain any two methods for solving this problem. Ans:  It is general for file structures to be divided into equal-length partitions, k

Which is an advantage of view, Which is an advantage of view? The Advan...

Which is an advantage of view? The Advantages of view is Data security, Derived columns and Hiding of complex queries

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd