COMP 1005 Middle East College Relational Database Systems Paper

User Generated

nvurycre0

Computer Science

COMP 1005

middle east college

COMP

Description

a) Comprehend the core concepts of relational database systems.

b) Create and execute SQL statements using relational database system.

c) Explore the security requirements of relational database management system

Without proposal
Similarity less than 7%

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Relational Database Systems1

RELATIONAL DATABASE SYSTEMS

Course number
Professor’s name
Institutional affiliation
Date

Relational Database Systems2

Contents
TASK 2......................................................................................................................................................... 3
DBMS VS FILE SYSTEM ..................................................................................................................... 3
DATABASE ADMINISTRATION ROLES AND FUNCTIONS ....................................................... 3
TASK 3: SQL QUERIES ........................................................................................................................... 5
CREATING SQL TABLES ................................................................................................................... 5
PRIMARY KEYS AND FOREIGN KEYS .......................................................................................... 5
INSERTING RECORDS........................................................................................................................ 6
QUERIES USING SQL .......................................................................................................................... 6
TASK 4: RELATIONAL ALGEBRA ....................................................................................................... 9
REFERENCE ............................................................................................................................................ 10

Relational Database Systems3

TASK 2
DBMS VS FILE SYSTEM
In the recent past, the file system was mainly used to manage d...


Anonymous
Excellent! Definitely coming back for more study materials.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags