Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
`
IN SEMESTER (INDIVIDUAL ) ASSIGNMENT -2
Module Code: COMP 1005
Module Name: Relational Database Systems
Level: 2
Max. Marks: 100
Instructions to Student
Answer all questions.
Deadline of submission: 8/07/2020 23:59
The marks received on the assignment will be scaled down to the actual weightage
of the assignment which is 50 marks
Formative feedback on the complete assignment draft will be provided if the draft is
submitted at least 10 days before the final submission date.
Feedback after final evaluation will be provided by 20/07/2020
It is mandatory to submit both the assignments of the modules registered even if
students gets overall passing marks through one assignment itself.
Module Learning Outcomes
The following LOs are achieved by the student by completing the assignment successfully
a) Comprehend the core concepts of relational database systems.
b) Create and execute SQL statements using a relational database system.
c) Explore the security requirements of a relational database management system
Assignment Objective
This is an individual assignment aimed to give the student exposure in understanding, designing,
building and analyzing database systems for a given real-time based scenario and be able to
conduct optimal strategies for efficient management of databases.
Assignment Tasks
Task 1:
MEC_AMO_TEM_034_01
Page 1 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
Complete the work proposal in Microsoft Word file format (may include possible answers
based on your initial understanding). Work proposal for the assignment must be
submitted before 23:59 Hrs on Thursday, 07/06/2020 and must include:
a) Understanding of tasks.
b) General overview of proposed plan with the deliverables.
c) Timeline for the completion of tasks
d) References.
The work proposal must be submitted in a word file through the link available in Moodle.
(4+4+1+1=10 marks)
Task 2:
a) “For creating a Student Information System of a University, a database
management system (DBMS) is better option or a file management system.”
Justify the statement given above. (Maximum 350 words)
(10 marks)
b) Explain the role and functions associated with database administration.
(Maximum 350 words and minimum 5 points are expected)
(10 marks)
Task 3:
Table name: PRODUCTS and SUPPLIERS
Details to be noted for products:
unique identity number, name, category, Price,
Manufacture date, Expiry date
Details to be noted for suppliers: unique identity number, name, phone number, address,
email, product identification number as a foreign key
Note: Choose your own relatable table names and attribute names to avoid similarity.
a) Create the table with the details given above, using appropriate attribute names,
data types, sizes and constraints for all the fields.
(12 marks)
b) Identify an appropriate primary key and foreign keys.
(2 marks)
c) Insert at least 2 meaningful records in each table.
(6 marks)
d) Answer the following queries using SQL :
i.
Display average price of all the products per category whose average price
is more than 1000 OMR.
ii.
Add a new column stock in the above table. Choose appropriate datatype
and size.
MEC_AMO_TEM_034_01
(5 marks)
(5 marks)
Page 2 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
iii.
Display product name, manufacturing date, expiry date and price of all the
products by increasing the price by 5%. Choose appropriate column alias
for the calculated column.
iv.
(5 marks)
Display product ID, product name and category of all the products which
were manufactured after the year 2018. Arrange the results in increasing
order of the manufacturing date.
v.
Create a view to include the name of the suppliers who are supplying the
product with id ‘001’.
vi.
(5 marks)
(5 marks)
Display the product names of all those products whose price is less than
the average price of all the products.
(5 marks)
Task 4:
Give the following queries in the relational algebra using the following relational schema:
Student (sid, name, age, address, email, cgpa)
enrolledIn (sid, modulecode, grade)
module (modulecode, modulename, credits, lecturername)
a)
What are the names of students enrolled in module code “cs3020”?
b)
Give the module code of all the modules that are taught by the teacher named “Altaf”?
c)
Who teaches the module with code”cs1500”?
d)
Give the name of all the teachers teaching the student with id”12f6778”.
(20 marks)
Rules & Regulations:
All resources should be cited using CU Harvard style.
The final assignment must have a Title page, Table of Contents, References/ bibliography
using CU Harvard Style and page numbers.
Title Page must have Assignment Name, Module name, Session, your name, ID, and the
name of the faculty.
Softcopy in word format is to be submitted through Turnitin link on Moodle.
Viva will be conducted after the assignment submission as per the dates informed earlier.
MEC_AMO_TEM_034_01
Page 3 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
Guidelines
Follow the guidelines mentioned below for your assignment. Submit a work proposal detailing
aspects such as initial understanding of the given tasks, timelines to complete each task,
literature sources for solving the tasks. Assignment should be typed and uploaded to
Moodle and will undergo plagiarism detection test through Turnitin (a plagiarism detection tool)
Handwritten assignments will not be accepted.
Assignment should have a Title Page. Title Page should contain the following
information.
Assignment Name
Class
Student name
Student ID
It should have Table of Contents
Use page numbers
Assignment should be typed in your own words using Times New Roman font size
12.
Heading should be with Font Size 14, Bold, Underline Use Diagrams and Examples
to explain your topic.
Copy paste from the Internet or other student work is strictly not acceptable.
References should be included in the last page as follows
Author name, Book Title, Publisher, Year in case of books
In case of web site references type the full path of the web page with
referenced date
In case of journals/magazines/periodicals type article name, magazine name,
Issue Number and date
Explain with suitable diagrams wherever required. Diagrams must be drawn using suitable
software or by pencil.
Each student has to do the assignment individually.
You can refer books in e-Library or use internet resource. But you should not cut and paste
material from internet nor provide photocopied material from books. The assignment
MEC_AMO_TEM_034_01
Page 4 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
answers should be in your own words after understanding the matter from the above
resources.
Important Policies to be followed
1. Student Academic Integrity Policy*:
MEC upholds the spirit of academic integrity in all forms of academic work and any form of
violation of academic integrity shall invite severe penalty. Any benefit obtained by indulging
in the act of violation of academic integrity shall be cancelled.
All cases of violation of academic integrity on the part of the student shall fall under any of
the below mentioned categories:
1.
Plagiarism
2.
Malpractice
3.
Ghost Writing
4.
Collusion
5.
Other cases
If the student fails a module and has a proven case of academic integrity violation in this
module, the student is required to re-register the module. This is applicable to first and
second offenders of plagiarism.
1. Plagiarism
A. First offence of plagiarism
I.
If a student is caught first time in an act of plagiarism during his/her course of study
in any assignment other than project work, the student will be allowed to resubmit the assignment once, within a maximum period of one week. However, a
penalty of deduction of 25% of the marks obtained for the resubmitted work will
be imposed.
II.
Period of re-submission: The student will have to re-submit the work one week
from the date he or she is advised to re-submit.
III.
If the re-submitted work is also found to be plagiarized, then that assessment will
be awarded a zero mark. Re-submission of the work beyond the maximum period
of one week will not be accepted and the assessment will be awarded a zero mark.
B. Second offence of plagiarism
If any student is caught second time in an act of plagiarism during his/her course of study
(in a subsequent semester), the student will directly be awarded zero for the work in
which plagiarism is detected. In such cases, the student will not be allowed to resubmit
MEC_AMO_TEM_034_01
Page 5 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
the work. A warning of suspension shall be issued, and student has to sign an undertaking
and undergo counselling session in such cases.
2. Malpractice/Ghostwriting/Collusion
A. First offence of Malpractice/Ghostwriting/Collusion
If a student is caught in an act of Malpractice/Ghostwriting/Collusion for an
assessment component irrespective of coursework or end semester, the student
shall fail the module and shall be required to re-register the module
B. Second Offence of Malpractice/Ghostwriting/Collusion
If a student is caught a second time in an act of
Malpractice/Ghostwriting/Collusion for an assessment component irrespective
of coursework or end semester, the student shall fail the module. A warning of
suspension shall be issued, and student has to sign an undertaking and undergo
counselling session in such cases.
3. Third Offence of Academic Integrity Violation
If a student is caught a third time in an act of Academic Integrity Violation for an assessment
component irrespective of coursework or end semester (in a subsequent semester), the
student shall fail the module and also shall be suspended for one semester from the College,
as recommended by institutional level academic committee, Chaired by the Associate Dean,
Academic Affairs.
4. Fourth Offence of Academic Integrity Violation:
If a student is caught a fourth time in an act of Academic Integrity Violation for an assessment
component irrespective of coursework or end semester (in a subsequent semester), the
student shall fail the module and also shall be expelled from the College, as recommended
by institutional level academic committee, Chaired by the Associate Dean, Academic Affairs.
5. Other cases
If a student commits an act of academic integrity violation as per the definition of “other
cases” mentioned in the previous section or of a different nature, student’s case shall be
forwarded to an institutional level academic committee, Chaired by the Associate Dean,
Academic Affairs. The committee shall investigate the case by means of a viva and/or a
disciplinary hearing and shall take appropriate decision. The penalty that can be granted to
a proven case of academic integrity violation which falls in this category of “other cases” can
be a warning/component zero/ module fail/suspension/expulsion depending on the nature
and gravity of the offence.
6. Types/Variations of Cases:
I.
If plagiarism is detected in any component of one assessment, the deduction in marks will
be applicable for the whole assessment, even if only the component or part submission
alone needs to be resubmitted.
MEC_AMO_TEM_034_01
Page 6 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
II.
III.
IV.
If plagiarism is detected in a group assessment, all students of the group will be considered
as having committed an act of plagiarism and the policy will then be applied to all students
If plagiarism is detected in any component of a group assessment, the deduction in marks
will be applicable for the whole assessment even if only the component or part submission
alone needs to be resubmitted.
All students of the group would be considered as having committed an act of plagiarism
and the policy will then be applied to all the students of the group.
If the assessment consists of components or part submissions that could be a group
assessment component (e.g. group assignment) and an individual assessment component
(e.g. individual reflection), the following will be applicable:
a. If plagiarism is detected in the group assessment component, all students of the
group will be considered as having committed an act of plagiarism, The policy will
then be applied to all students of the group. Group assessment component will
be resubmitted as per the policy.
b. If plagiarism is detected in the individual assessment component, the individual
assessment component will be resubmitted and the policy will then be applied to
that student alone.
c.
For both (a) and/or (b), the deduction in marks will be applicable for the whole
assessment.
* for further details Refer to MEC Student Academic Integrity Policy in Student Handbook.
2. Late Submission Regulations:
It is the students’ responsibility to check all relevant timelines related to assessments.
As per the Assessment Policy at MEC, late submissions are allowed for one week (5
working days) for all UG modules with a penalty. In such cases, a deduction of 5% of the
marks obtained for the submitted work shall be imposed for each working day following
the last date of submission till the date of actual submission. Assessment documents
submitted beyond a period of one week (5 working days) after the last date of submission
will not be accepted and will be awarded a zero for that assessment. In cases where the
submission has been delayed due to extenuating circumstances, the student may be
permitted to submit the work without imposing the late submission policy stated above.
The extended period of submission will be one week from the original last date of
submission. In such cases, the student is expected to submit the supporting certificates
on or before the original last date of submission of the assessment and the decision of
extension rests with faculty responsible for the assessment .The late submission policy
shall be applied if the student fails to submit the work within one week of the original last
date of submission.
Students may contact their teachers for clarification on specific details of the submission
time if required.
MEC_AMO_TEM_034_01
Page 7 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
3. Research Ethics and Biosafety Policy
To protect and respect the rights, dignity, health, safety, and privacy of research subjects
involved including the welfare of animals and the integrity of environment, all student
projects are expected to be undertaken as per the MEC Research Ethics and Biosafety
Policy. Accordingly the following shall apply.
Research and other enterprise activities shall be conducted by maintaining the high
ethical standards consistent with national and international standards and conventions.
Any research at MEC that is categorized as high-risk research shall be subject to review
and approval by the Research Ethics and Biosafety Committee.
Research activities involving collection of human or animal tissues and manipulation of
microbial, animal or plant cells shall be subject to review and approval by the Research
Ethics and Biosafety Committee.
Participants involved in research must be informed about the purpose of research and
intended uses of research findings. Written consent must be obtained from people
involved prior to the commencement of research.
Data obtained from participants must be treated with high confidence and should be used
only for the intended purpose of research.
Assessment Evaluation Criteria
Classification
And % Range
Outstanding
Excellent
Very Good Quality
Good (Acceptable)
MEC_AMO_TEM_034_01
Reflection and critical
analysis.
Highly
competent
analytical
skills
and
reflective
practice,
demonstrating
personal
learning
and growth,
insight
into
required
professional values and
principles and professional
development planning.
Strong analytical skills and
reflective practice used,
demonstrating
personal
learning
and growth,
insight
into
required
professional
values,
principles
and
competencies
and
professional development
planning.
Good use of analytical skills
and reflective practice
demonstrating
personal
learning
and growth,
insight
into
required
professional
values,
principles
and
competencies
and
professional development
planning.
Acceptable
use
of
analytical
skills
and
reflective
practice
demonstrating
personal
learning
and growth,
insight
into
required
professional
values,
principles
and
Knowledge and
Understanding/
Application of Theory
Extensive knowledge and
depth of understanding of
principles and concepts
and
/or
outstanding
application of theory in
practice.
Referencing and
Bibliography
Presentation, Grammar
and Spelling
Evidence of reading an
extensive
range
of
educational
literature/research
and
where
applicable
workplace
strategies,
policies and procedures.
Accurate referencing and
bibliography
correctly
using
appropriate
referencing style
Excellent
presentation,
logically structured, using
correct grammar and
spelling, excellent crossreferencing and links to
supporting evidence
Excellent knowledge and
understanding of principles
and concepts and /or
excellent knowledge and
understanding of the
application of theory in
practice
Evidence of reading a wide
range
of
educational
literature/research
and
where
applicable,
workplace
strategies,
policies and procedures.
Appropriate referencing
and bibliography correctly
using
appropriate
referencing style
Good
presentation,
competently structured,
using correct grammar and
spelling, clear and easy to
use links to supporting
evidence
Good knowledge or key
principles and concepts
and/or good knowledge of
the application of theory in
practice
Evidence of reading a good
range
of
educational
literature/research
and
where
applicable
workplace
strategies,
policies and procedures.
Generally well referenced
with correct use of the
appropriate
referencing
style
Reasonable presentation,
completely
structured,
acceptable grammar and
spelling, acceptable links to
supporting evidence
Acceptable knowledge of
key principles and concepts
and/or knowledge of the
application of theory in
practice
Evidence of reading an
appropriate
range of
educational
literature/research
and
where applicable, relevant
workplace policies and
procedures
Adequate
referencing.
Generally accurate use of
appropriate
referencing
style
Adequate presentation and
structure,
acceptable
grammar and spelling,
adequate
links
to
supporting evidence
Evidence of Reading
Page 8 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
Adequate/ Satisfactory
Weak /Poor
(all learning outcomes not
adequately met)
competencies
and
professional development
planning.
Adequate use of analytical
skills
and
reflective
practice
demonstrating
personal learning and
growth,
insight
into
required
professional
values, principles and
competencies
and
professional development
planning.
Little use of analytical skills
and reflective practice
demonstrating
personal
learning
and growth,
insight
into
required
competencies
and/or
professional development
planning.
Professional
values and principles not
reflected
in
the
submission.
and/or
Insufficient/no use of
analytical
skills
and
reflective
practice
demonstrating
personal
learning
and growth,
insight
into
required
competencies
and
professional development
planning
Adequate knowledge of
key principles and concepts
and/or
satisfactory
evidence of the application
of theory in practice.
Evidence
of
minimal
reading
of educational
literature/research
and
where applicable relevant
workplace policies and
procedures
Adequate
referencing.
Appropriate referencing
style used but may contain
some inaccuracies.
Weak
presentation
,
satisfactory
structure,
grammar and spelling, links
to supporting evidence
Little
evidence
of
knowledge
of
key
principles or concepts
and/or little evidence of
the application of theory in
practice
and/or
No evidence of knowledge
of key principles or
concepts
and/or
no
evidence of application of
theory in practice
Little or no evidence of
reading outside of the
course textbook and/or
reference to relevant work
place
policies
and
procedures
and/or
No evidence of reading
outside of the course
textbook and/or reference
to
relevant workplace
policies and procedures
Little or no referencing,
incorrect style, or very
inaccurate
use
of
appropriate
referencing
style
Poor
presentation,
grammar and spelling, links
to supporting evidence
and/or
Unacceptable
presentation,
grammar
and spelling, structure is
very poor, links to
supporting evidence
Feedback:
Detail feedback will be provided as follows:
Submission
Week
Channel
Assignment work proposal:
Detail feedback on the assignment work
proposal will be provided. This feedback
will help you to understand and reflect
on your initial understanding of
assignment tasks.
15/06/2020
MEC Moodle
Final Assignment Submission:
Detail feedback on the assignment
document will be provided. This
feedback will help you to reflect on your
work.
20/07/2020
MEC Moodle
MEC_AMO_TEM_034_01
Page 9 of 12
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
Relational Database Systems (COMP 1005) Spring 2020
Assignment -2 Evaluation Grid
Student ID:____________ Student Name:_________
Deliverables
Task 1
Aspects
Proposal
Submission
•
•
•
Timeline and references .Include a list of suitable literature to complete
the assignment using a Harvard Style Notation.
0-3
4-6
7-8
9-10
Aspects
Task 2 (a)
Choice of File
system
Incomplete/
Weak/Plagiarized
attempt.
Deliverables
Aspects
0-3
Deliverables
Task 3(a)
Deliverables
Task 3 (b)
Information is
incomplete.
4-6
Partially correct
role and functions
of DB
administrator
Satisfactory
description on role
and functions of
DB administrator
Aspects
Create table
3-5
Partially correct
query with
incorrect
datatypes/sizes/
constraints or
incorrect syntax
6-9
Partially
correct
query
with
inappropriate
datatypes/ sizes/
constraints with
correct syntax.
0-2
Incomplete/
Weak/Plagiarized
attempt
0
Identification Incomplete /
of Primary
Weak / Plagiarized
Key/Foreign Report
key
Deliverables
Aspects
Task 3 (c)
Insert records
MEC_AMO_TEM_034_01
0-1
Incomplete/
Weak/Plagiarized
Report
Mark
Satisfactory attempt Complete
with
weak justification
justification.
provided with
relevant
information.
7-8
9-10
Role and
Incomplete/
functions of DB Weak/Plagiarized
administrator
attempt.
Aspects
Mark
General overview on the proposed solution of all the tasks.
Deliverables
Task 2 (b)
Session:__
Mark Range: 0-10
General overview of initial understanding to all the tasks.
Complete
details on
role and
functions of
DB
administrator
10-12
Correct query
with
appropriate
datatypes/
sizes/
constraints
Mark
Mark
1
2
Correct PK
Both PK and FK
identified/missing identified
FK
correctly.
2 -4
Syntax error
3-4
Partially correct
Mark
5-6
Mark
Complete
discussion on
measures to
estimate
storage
requirements
with reference
Page 10 of 12
Deliverables
Task 3 (d) i
Aspects
SQL query
Deliverables
Aspects
Task 3 (d) ii
SQL query
Deliverables
Aspects
Task 3 (d) iii
SQL query
Deliverables
Aspects
Task 3 (d) iv
SQL query
Deliverables
Aspects
Task 3 (d) v
SQL query
Deliverables
Aspects
Task 3 (d)vi
SQL query
Deliverables
Aspects
Task 4(a)
Relational
algebra query
Deliverables
Aspects
MEC_AMO_TEM_034_01
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
to
the
scenario.
0-1
2-4
5
Mark
Incomplete/
Partially correct
Correct query
Incorrect/Plagiarized query
content
0-1
2-4
5
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Mark
Mark
Mark
Mark
Mark
Mark
Mark
Page 11 of 12
Task 4(b)
Relational
algebra query
Deliverables
Aspects
Task 4(c)
Relational
algebra query
Deliverables
Aspects
Task 4(d)
Relational
algebra query
Relational Database Systems (COMP 1005) – Spring 2020-CW2 (Assignment) - ML
Incomplete/
Partially correct
Correct query
Incorrect/Plagiarized query
content
0-1
2-4
5
Mark
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Correct query
0-1
5
2-4
Incomplete/
Partially correct
Incorrect/Plagiarized query
content
Mark
Correct query
Marks Obtained (100)
Penalty
Final Mark (out of 100)
Feedback:
Signature:
Date:
MEC_AMO_TEM_034_01
Page 12 of 12
Purchase answer to see full
attachment