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
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
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
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
select distinct p1.title
from paper P1, author A1
and acnum in (select acnum
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)
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
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:
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 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,
● 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 (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.
Relations not expressed in the approved representation are not permitted and will incur a
penalty for this question.
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/academicintegrity) All work is to be done individually and
plagiarism of any form will be dealt with according to the RMIT plagiarism policy.
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:
For longer extensions, you must follow the instructions provided at
An application for an extension must be made within two working days after your assessment
or due date.
本网站支持 Alipay WeChatPay PayPal等支付方式
E-mail: email@example.com 微信号:vipnxx