FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML Fundamentals of Relational Database Management Systems (COMP 20002) Individual Assignment Hand out week: Week 6 Duration: 5 weeks Proposal submission Date: 28/04/2018 Final Submission Date: 19/05/2018 Total Marks: 100 (scaled down to 40 marks) Objective: This is an individual assignment aimed to allow the students to design database systems and apply optimal strategies for efficient management of databases. Outcome: 1) Creating visual representation of the logical structure of a database using Entity-Relationship diagram. 2) Apply normalization techniques to reduce redundancy in a database. Task 1: Submit a work proposal for this assignment by the end of week 8 which must include: a) Understanding of deliverables – a detail description of deliverables. b) General overview of proposed plan - initial understanding of solution to task2 and task 3. c) Timeline for completion of task2 and task 3. d) Resources required for completion of the assignment. The work proposal must be submitted in a word file through the link available in Moodle. The proposal can also include draft answers. (10 Marks) The proposal can be in the below format: COMP 20002 Page 1 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML Task Understanding Deliverables Start date of End date of the Resources the task task required for the task. Scenario: Consider the following scenario modeling project management activities in an organization. Muscat Festival is conducted every year during spring in the Sultanate of Oman. The festival comprises of many events showcased by participants from various countries. Each event is identified by its unique ID, and has a name and location. Each participant can be identified by their ID and has name, specialization and contact number. An event can have any number of participants but a participant belongs to one event only. The Festival offers several commercial outlets to develop commercial sectors in Oman. An Outlet can be owned by one registered vender and a vendor can own any number of outlets. Each Outlet is given a unique number, name, location and contact person and a vendor is identified by an ID, and has name and phone number. Once a visitor visits the outlet, the details of the visitor such as name, contact number, address and residence ID number, which is unique to every visitor will be recorded. An outlet can have any number of visitors or it may not have any visitors, a visitor may visit one or more outlets. Whenever a visitor visits an outlet, the date and amount he spent will be recorded to track the profit of the outlet. The festival also offers different games. Each game has unique ID, name, and price. A visitor can play any number of games, or need not play. A game can be played by at least one visitor or need not be played at all; when a visitor plays a game, score and date will be recorded. In addition to games and outlets, the visitors may also participate in events. The Festival organizers have imposed a condition that each visitor should participate in an event only once, but an event may have any number of visitors. Task 2: a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, not null, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design. (40 Marks) COMP 20002 Page 2 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML b) For the above task, write your refection by describing the ERD in connection with concepts taken from literature in 400 to 500 words. The literature can include conference papers, scholarly articles, text & reference books. (5 Marks) c) Consider any simple restaurant with not more than four entities, explain the scenario and draw an ERD for the same. (5 Marks) Task 3: a. Normalize the below given Water Consumption Report to First Normal Form, Second Normal Form, Third Normal Form. (35 Marks) OFIC LLC. Water Consumption Report Consumption Report Number: RS10013 Reader ID: 1023 Consumption Period: March 1-31, 2018 Reader Name: Said Al Sinani Area Code: 24 Area Name: Al Hail Meter code Owner Name House address 76589 65467 14562 25876 Hussain Said Ali Khalfan Said Hamood Majid Khalid Way# 221, house#23 Way#221, house#24 Way#143,house#44 Way#123,house#52 Owner Phone number 24897658 24678987 24561821 24678767 Previous meter Reading 345 560 760 345 Current meter Reading 450 600 761 346 Meter status Working Working Not Working Not working Note: Water meter belongs to exactly one owner COMP 20002 Page 3 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML b) Write your refection by describing the normalization process carried in the task above in connection with concepts taken from literature in 400 to 500 words. The literature can include conference papers, scholarly articles, text & reference books. (5 Marks) Instruction: Be ready for Viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the presentation will be announced in the class. Marks for task 2-3 will depend on the presentation and viva. Note: Viva is compulsory. No marks will be awarded to tasks 1-3 if no viva conducted. Feedback: Proposal feedback will be given in 10th week and assignment feedback will be given in 14th week. Guidelines Follow the guidelines mentioned below for your assignment.  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 COMP 20002 Page 4 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML  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 is strictly not acceptable.  Reference 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 magazines/ periodicals type article name, magazine name, Issue Number and date Rules & Regulations  If any topic or diagram of an assignment is found copied from the other then marks will be deducted from both assignments.  Assignments with 3 or more identical topics will be awarded 0 Marks.  The purpose of assignment is to do some research work so you can consult books in Library or use internet or computer magazines or any other source. Plagiarism Policy A. First offence of plagiarism a. 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. b. 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. c. If the re-submitted work is also detected to be plagiarized, then the work will be awarded a zero. d. Re-submission of the work beyond the maximum period of one week will not be accepted and the work will be awarded a zero. e. If the student fails the module and has a proven case of academic integrity violation in this module , the student is required to reregister the module COMP 20002 Page 5 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML f. If plagiarism is detected in Project work (Project 1, Project Planning and Project Design and Implementation) the above clauses (a,b,c,d) do not apply and the work will be summarily rejected. In these cases the student will be awarded a fail (F) grade and is required to re-register the module B. Second offence of plagiarism a. 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 re-submit the work. b. If the student fails the module and has a proven case of academic integrity violation in this module, the student is required to re-register the module C. Third Offence of plagiarism If any student is caught for the third time in an act of plagiarism during his/her course of study (in a subsequent semester), the student will be penalized with a fail in the module and shall be required to re-register the module. D. Fourth Offence of plagiarism If any student is caught for the fourth time in an act of plagiarism during his/her course of study (in a subsequent semester), he shall be suspended from the College for a period of one semester. E. Fifth offence of plagiarism If any student is caught for the fifth time in an act of plagiarism during his/her course of study (in a subsequent semester), he shall be expelled from the College Malpractice (MP) A. First offence of MP If a student is caught in an act of malpractice 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 MP If a student is caught a second time in an act of malpractice for an assessment component irrespective of coursework or end semester (in a subsequent semester), the student shall be suspended for one semester from the College. COMP 20002 Page 6 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML C. Third Offence of MP If a student is caught a third time in an act of malpractice for an assessment component irrespective of coursework or end semester (in a subsequent semester), the student shall be expelled from the College. Other cases It denotes all other forms of academic misconduct including but not limited to ghostwriting, collusion, fabrication, falsification, unauthorized access to unseen examination papers and other academic and administrative documents/systems and aiding academic dishonesty/misconduct If a student commits an act of academic integrity violation whether defined as above as “other cases” or of a different nature, those cases shall also be forwarded to a departmental level committee set for the purpose .The committee shall investigate the case by means of a viva and/or a disciplinary hearing and shall take appropriate decision. Late Submission The recommended practice at MEC is to set the cutoff time to 23:59 on the due date for all assessment submissions. As per the Assessment Policy at MEC, for any late submissions, a penalty of deduction of 5% of the marks obtained for the resubmitted work will 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 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. COMP 20002 Page 7 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML Fundamentals of Relational Database Management Systems (COMP 20002) Student ID: Student Name Assignment 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-5 6-11 12-17 18-20 Task 2(a) Entities and Associative Entities Inappropriate/wea k identification of different entities, and associative entity, the attributes partial Identification of different entities and associative entity, the attributes Satisfactory identification of different entities and associative entity, the attributes Complete and accurate in Identification of different entities, the attributes Aspects 0-4 5-10 11-16 17-20 Relationships of ERD and Primary Keys and notations for all entities Inappropriate/wea k identification of relationships and key constraints Partial Identification of different relationships and key constraints. Satisfactory identification of different relationships and key constraints Complete and accurate in Identification of different relationships and key constraints Deliverables Aspects 0 1-3 4 5 Task 2(b) Reflection on ERD irrelevant Reflection incomplete reflection with no underpinning Reflection is satisfactory and underpinned with literature Reflection is Excellent and accurately underpinned with literature Deliverables Aspects 0 1-3 Task 2(c) Banking ERD with scenario Irrelevant ERD and explanation ERD and scenario are weak with logical errors 0 1-2 Irrelevant normal form. Wrongly normalized tables. Mark may be assigned for creating base table. 0 1-4 Irrelevant normal form. Wrongly normalized most of the tables. 0 1-5 Irrelevant normal form. Wrongly normalized most of the tables. (ERD) Deliverables Task 3(a) 1st Normal Form 4 Mark Mark Mark Mark 5 ERD and scenario is satisfactory with detailed entities, attributes and relationship and explanation 3-6 Partially satisfies 1NF rules. Mark ERD and scenario is Excellent with accurate entities, attributes and relationship and explanation 7-8 Mark Completely satisfies 1NF rules. Normalisation 2nd Normal Form 3rd Normal Form COMP 20002 5-9 Partially satisfies 2NF rules. 6-12 Partially satisfies 3NF rules. 10-12 Mark Completely satisfies 2NF rules. 13-15 Mark Completely satisfies 3NF rules. Page 8 of 9 FRDBMS (COMP 20002) – SPRG – 18 – CW2(ASSMT) –QP -ML Deliverables Aspects 0 1-3 4 5 Mark Task 3(b) Reflection on normalization Weak reflection Reflection is incomplete and not underpinned with literature Reflection is satisfactory and underpinned with literature Reflection is Excellent and accurately underpinned with literature Signature of Teacher: Total Marks Penalty Final Marks COMP 20002 Page 9 of 9