Reference no: EM132386787

ISYS1057 - Database Concepts Assignment - RMIT University, Australia

1. SQL

This question uses the Research database available in Oracle and as a build file for use in SQLite.

The ER model for the Academics database is as follows:

The Relational model for the Academics database is as follows:

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)

Write one SQL query for each question below to extract information from the database. Do not supply the output of the query. Only the SQL query is required for each question. All SQL must be presented in text format, i.e. no screenshots.

1.1. Use an IN subquery to find the title, given name and family name of all academics who work in departments located in Queensland. (Postcodes for Queensland start with the digit 4)

1.2. Using joins only, list all the title of fields of interest to doctors that work in Queensland. Output the field names, without duplicates. Do not use Natural Join for this question.

1.3. Display a list of academics that have collaborated with another academic on more than one paper. List individual pairs of academics on each line. List only their academic numbers. Do not list duplicate pairs. (e.g 56,113 and 113,56 are duplicate pairs)

1.4. There are some academics that have written more than 12 papers and there are some academics are interested in fields that have the word "database" in the title. List the academic number of each academic that meet either or both of these conditions.

1.5. There is concern about the integrity of the data in the Academics table. Are there any academics in the database who are missing Initials or a Title? Write a query to list the academic number of any academics that do not have initials or a title. Do not use a sub-query.

1.6. There are two requirements for this question:

1. Find all academics who have an interest in the field titled "Natural Language Processing". You must use an EXISTS sub-query. Output the initials of these academics. HINT: Don't forget to use TRIM when testing the title.

2. Further limit this list of academics by adding a NOT EXISTS condition test so only academics who have no other interests other than "Natural Language Processing" are present in the output.

1.7.What does the following query do? Describe what its purpose is using only plain English. Avoid use of SQL keywords. You may refer to Question 1 for help regarding postcodes.

select distinct p1.title

from paper P1, author A1

where P1.panum=A1.panum

and acnum in (select acnum

from academic

where title like 'Dr%'

or title like 'Prof%')

and exists (select *

from academic AC1

where AC1.deptnum in

(select deptnum FROM department

WHERE postcode >=4000 and postcode <= 4999)

And AC1.acnum=A1.acnum);

1.8. Write an SQL query to create a View that displays the title and surname of each academic and how many papers they have written. If an academic have not written any papers, then a "0" should be displayed against their name.

Hardcoding of identifiers not given in the question is not permitted and will incur a penalty for the question. Only use the information provided in each question.

Do not use the SQL keyword 'ROWNUM', 'FETCH' or 'LIMIT' to get a result.

2. Relational Modelling

For the relation: R (A, B, C, D, E, F, G)

The following functional dependencies hold:

F -> D

G -> B

C -> D

F -> C, E

B -> F

A -> F, G

2.1 Use Inference rules to find the minimal ?basis.

2.2 Determine the primary key? of the relation.

2.3 Based on this key, determine if the relation R is in BCNF. Explain your answer in terms of the FDs and the key.

2.4 If the relation R is not in 3NF or BCNF, then decompose the relation to 3NF/BCNF.

3. Normalisation

The table below has basic information about Sales staff and their sales for a small appliance sales chain. It shows the employee, the items they sell as well as which store they work in.

SALES(Emp_Name, Emp_ID , Make, Grade , Retail_Price, Wholesale_Price, Serial_No, Commission, Store _Address, Date, Store _Phone, Model)

The following functional dependencies apply and there are no redundancies present:

Store_Address -> Store_Phone

Grade -> Commission

Emp_ID -> Store _Address

Serial_No -> Model, Make

Emp_ID , Serial_No -> Date, Retail_Price

Serial_No ->Wholesale_Price

Emp_ID -> Grade

Emp_ID -> Emp_Name

3.1 Find the Primary Key of the SALES relation.

3.2 Decompose the SALES relation into 3NF. Show the final schema in full with all primary keys and foreign keys mark appropriately.

3.3 The following FD also holds true: Store_Phone-> Store_Address

Which relation in your decomposition would it map to? Test that relation to show that it is still in 3NF. Show your working.

4. Entity Relationship Modelling

This task can be completed using lucidchart.

If you prefer to use something else you are more familiar with that is also ok.

e.g. Visio, MS Paint, GIMP or even Google Drive.

Once you have created your diagram just insert it into your final document.

You must use only the following notation:

Use of Crows feet, Chen, IE notation or any other form is not permitted and will incur a penalty for this question.

According to the given description, construct an Entity Relationship (ER) diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that cannot be expressed in the ER diagram.

Bob The Builders is a company working in the construction industry and want a database to help manage their activities. They hire out equipment and people for construction work. Here are some relevant information about the company's operation.

The company has many types of equipment but all have some common information required for the database. They are; registration number, registration cost, weight, purchase date.

All equipment belongs to one of 4 categories; Earth, Lift, Move? and Other?

Each has specific data that needs to be recorded.

• For Earth : Size, Fuel
• For Lift : Height, Fuel, Capacity
• For Move : Type, Fuel, Length, Height Limit
• For Other : Power Source, Type, Rating

Employees are also represented in the database. Their employee number, name, address, contact number and specialty are recorded.

As the company takes on new jobs, these need to be loaded into the database as projects. Each project has a code to identify it, a name and address.

Employees form teams with names. Only the current team assignment for each employee is recorded in the database. Each team has a team leader. An employee can be a team leader or a team member but not both. Sometimes an employee is not available for work in a team at all.

A construction has one team allocated to it to work on just one project. A team only works on one construction at any given time. The start date and duration of the construction will be recorded.

Do not create any new attributes not already provided in this business description.

5. Relational Schema mapping

This is the ER diagram for a proposed database for an independent supermarket. It records information about staff and their activities as well as any awards they earn. The supermarket has cash register equipment and a roster that needs to be managed in the database.

Convert the ER diagram into a relational schema.

For each relation in your relational database schema, you should:

• Underline a primary key for each relation - each relation MUST have a primary key
• Denote any foreign keys with asterisks(*) in your relations
• Make sure you create a relation for each entity and relationship you see.

Relations not expressed in the approved representation are not permitted and will incur a penalty for this question.

Attachment:- Database Concepts Assignment File.rar