ISYS1057 - Database Concepts Assignment 1
Important: Assignment 2 is worth 30% of your final grade
Due Date: Sunday, 20 th of October 2019 at 11.59pm
1. This assignment is to be attempted individually.
2. We will use the Canvas for assignment submission. A submission link will be enabled on Canvas closer to the submission date.
3. This assignment is worth 100 marks in total and the deliverable will be a single pdf named as ‘s3111111.pdf’ where ‘s3111111’ should be replaced with your actual student number that contains responses to all 4 questions.
Submission Procedure and Late Penalties
The individual must submit ONLY 1 PDF via Canvas.
If you are unable to save a document as a PDF on your system there are free pdf converters online you can utilise.
e.g.http://convertonlinefree.com/
Remember: Submit one PDF only.
Late submissions of assignments will be penalised as follows:
Time Submitted after Due Date Late Penalty
0 to 1 hour no late penalty
1 hour to 1 day 10% of the total marks
2 days late 20% of the total marks
3 days late 30% of the total marks
4 days late 40% of the total marks
5 days late 50% of the total marks
more than 5 days 100% penalty (you will get 0 marks)
1. SQL (32 marks)
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 (20 marks)
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 (13 marks)
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 (20 marks)
This task can be completed using lucidchar t.
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:
Yes No
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.
5. Relational Schema mapping (15 marks)
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.
Yes
relationName(attribute1, attribute2*)
Relations not expressed in the approved representation are not permitted and will incur a penalty for this question.
Plagiarism Notice
Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism will be given zero marks for that assignment. In the event that a hurdle requirement is not met, this will result in the failure of the course.
You should familiarize yourself with the contents of the university website for Academic Integrity. (http://www.rmit.edu.au/academicintegrit y) All work is to be done individually and plagiarism of any form will be dealt with according to the RMIT plagiarism policy.
Special Consideration
If unexpected circumstances affect your ability to complete the assignment you can apply for special consideration. If you seek a short extension, you can directly contact me at:
ian.baker@rmit.edu.au
For longer extensions, you must follow the instructions provided at https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/spe cial-consideration