COMP 0301 Middle East College SQL Simple Tasks Paper Task 1 Submit a work proposal for this assignment which must include:  Understanding of deliverables

COMP 0301 Middle East College SQL Simple Tasks Paper Task 1

Submit a work proposal for this assignment which must include:
 Understanding of deliverables – a detail description of deliverables.
 General overview of proposed plan – initial understanding of solution of all tasks.
 Timeline and resources required for completion of the given tasks.
Task 2:
You have been assigned as a database administrator in an organization. Explain your role as a database administrator and compare it with data administrator in insuring good security for the organization’s data. Write your reflection with concepts adopted from literature. Conference papers, academic articles, text & reference books can be included in the literature.
Task 3:
Write the appropriate SQL statement
Task 4:Consider the tables given below and write the relational Algebra.

check the file below for more instructions and requirements Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
IN SEMESTER INDIVIDUAL ASSIGNMENT
Module Code: COMP 0301
Level: 2
Module Name: Fundamentals of Relational Database
Management Systems
Max. Marks: 100
Instructions to Student



Answer all questions.
Deadline of submission: 07/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
Module Learning Outcomes:
The following LOs are achieved by the student by completing the assignment successfully
 Demonstrate the basic concepts of database systems, core concepts of relational
database systems and security issues.
 Programming with Structured Query Language.
Assignment Objective:
This is an individual assignment aimed to give the student exposure to basic concept of database
systems, relational database systems and security manners, beside SQL programming language.
Task 1:
Submit a work proposal for this assignment or before 13/06/2020 (23:59) which must include:

Understanding of deliverables – a detail description of deliverables.

General overview of proposed plan – initial understanding of solution of all tasks.
(3 Marks)
(3 Marks)

Timeline and resources required for completion of the given tasks.
(4 Marks)
The work proposal must be submitted in a word file through the link available in Moodle.
MEC_AMO_TEM_034_01
Page 1 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
(10 Marks)
Task 2:
You have been assigned as a database administrator in an organization. Explain your
role as a database administrator and compare it with data administrator in insuring good
security for the organization’s data. Write your reflection with concepts adopted from
literature. Conference papers, academic articles, text & reference books can be included in the
literature.
(20 Marks)
Task 3:
Write the appropriate SQL statement for the following:
(30 Marks)
Table name: Brand
Column Name
Data Type
Constraint
BrandID
Integer
Primary Key
BrandName
Varchar
Not null
Brandcountry
Varchar
Not null
Brandcategory
varchar
Table Name: Dress
Column Name
Data Type
Constraint
DressID
Integer
Primary key
DressName
varchar
Not null
DressType
varchar
Not null
SellPrice
Numeric(10,2)
MEC_AMO_TEM_034_01
Page 2 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
Quantity
Integer
Should be greater than
0
BrandID
Integer
References to Brand
table
1. Create brand and dress tables as per the above given structure.
(6 marks)
2. Display the total number of dresses for each type whose total number is less than
10.
(6 marks)
3. Increase the selling price by 10% of all the dresses belonging to BrandID 117 and 119.
(6 marks)
4. Create a view to display the ID and name of all dresses of type Cotton and Polimer and whose
quantity is less than 10. Display the result in alphabetical order of the
IDs.
(6 marks)
5. Write a subquery that will display the Dress ID, Dress type, Brand country, Brand category of
all brands whose sell price is greater than the average sell price.
(6 marks)
Task 4:
Consider the tables given below and write the relational Algebra for the following tasks:
(30 Marks)
Table Name: Products
ProductID
ProductName
Unit
Price
SupplierID
1
Chais
10 boxes x 20 bags
18
1
2
Chang
24 – 12 oz bottles
19
1
3
Aniseed Syrup
12 – 550 ml bottles
10
1
4
ChefAnton’s Cajun Seasoning
48 – 6 oz jars
22
2
MEC_AMO_TEM_034_01
Page 3 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
Table Name: Suppliers
SupplierID
SupplierName
ContactName
Address
City
1
Exotic Liquid
Charlotte
Cooper
49 Gilbert St.
Londona
2
New Orleans
Delights
Cajun
Shelley Burke
P.O. Box 78934
New
Orleans
3
Grandma
Homestead
Kelly’s
Regina Murphy
707 Oxford Rd.
Ann Arbor
4
Tokyo Traders
Yoshi Nagase
9-8
Sekimai
Musashino-shi
Tokyo
A. Write the Relational algebra for the following questions.
(3x 6 Marks= 18 Marks)
i. Find all the information of products whose price is from 15 to 25 inclusively.
ii. Find the lowest price of products for each supplier ID.
iii. Find ID, name and address of suppliers whose city is Tokyo.
B. Convert the following SQL statements to Relational Algebra expressions:
(2x 6 Marks= 12 Marks)
i. Select * From Products
Where SupplierID in (1,2,3);
ii Select ProductID, ProductName From Products
Where Price between 10 and 20;
MEC_AMO_TEM_034_01
Page 4 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
Task 5:
In 300 words, write an individual reflective writing section that summarizes the online learning
experience of this semester.
(10 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.
Guidelines:

Assignment must be computer typed.

Font – Times New Roman

Font – Style – Regular

Font – Size – 12

Heading should be with Font Size 14, Bold, Capital and Underline.

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 eLibrary or use internet resource. But you should not cut and paste material
from internet nor provide photocopied material from books. The assignment 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:
MEC_AMO_TEM_034_01
Page 5 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
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 re-submit 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 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.
MEC_AMO_TEM_034_01
Page 6 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
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.
II.
III.
IV.
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.
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.
MEC_AMO_TEM_034_01
Page 7 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
b.
c.
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.
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.
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.
MEC_AMO_TEM_034_01
Page 8 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP

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.
MEC_AMO_TEM_034_01
Page 9 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
Fundamentals of Relational Database Management Systems (COMP 0301)
Student ID:
Student Name:
Assignment 2 Evaluation Grid
Deliverables
Aspects
0
2-6
7-10
Task 1
Work Proposal
No submission
Proposal is incomplete but has relevant information
Complete and
satisfactory proposal.
Deliverables
Aspects
0-1
2-7
8-16
17-20
Task 2
Role of
database
administrator
with
comparsion
between DBA
and DA
irrelevant answer
incomplete answer with
little or not all the
requirements addressed
Satisfactory with all
the requirements
addressed with
citations
Excellent Scenario
with all the
requirements
addressed with
accurate citations
Deliverables
Aspects
0-5
6-17
18-26
27-30
Task 3 + Task 4
SQL
Inappropriate/weak
SQL Statement
partial answer
Satisfactory report
All answers are
correct with SQL
screenshots
Aspects
0-5
6-17
18-26
27-30
Relational
algebra
Inappropriate/weak
relational algebra
Partial answer
Satisfactory report
All answers are
correct
Aspects
Reflection on
online
learning
experince
0
Weak reflection
1-5
Reflection is
incomplete
6-7
Reflection is
satisfactory
8-10
Reflection is
Excellent and
accurately
underpinned with
literature
Statement
Deliverables
Task 5
Mark
Mark
Mark
Mark
Mark
Feedback :
MEC_AMO_TEM_034_01
Page 10 of 11
Fundamentals of Relational Database Management Systems (COMP 0301) – Spring – 2020 – CW
Assignment2 – ALL – QP
Signature of Teacher:
Total Marks
Penalty
Final Marks
MEC_AMO_TEM_034_01
Page 11 of 11

Purchase answer to see full
attachment