COMP 20002 MEC Computer Science Fundamentals of Relational Database Worksheet

User Generated

Yvnaarqreguny

Computer Science

COMP 20002

middle east college

COMP

Description

Please defer from using plagiarized content.

All the requirements can be found in the attached file below.

Thanks very much!

Unformatted Attachment Preview

Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP IN SEMESTER INDIVIDUAL ASSIGNMENT Module Code: COMP 20002 Level: 2 Module Name: Fundamentals of Relational Database Management Systems Max. Marks: 100 Instructions to Student   Answer all questions. Deadline of submission: 03/06/2020  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 17/06/2020  23:59 Module Learning Outcomes The following LOs are achieved by the student by completing the assignment successfully 1) Creating visual representation of the logical structure of a database using EntityRelationship diagram. 2) Apply normalization techniques to reduce redundancy in a database. Assignment Objective This is an individual assignment aimed to give the student exposure to the Entity Relationship Diagram and the concepts of Normalization. Task 1: 1) Submit a work proposal for this assignment or before 09/05/2020 (23:59) which must include:  Understanding of deliverables – a detail description of deliverables.  General overview of proposed plan - initial understanding of solution to task2 and task3.  Timeline and resources required for completion of the given tasks. The work proposal must be submitted in a word file through the link available in Moodle. (10 Marks) MEC_AMO_TEM_034_01 Page 1 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP Task 2: You may choose any one organization in Oman to solve this task. a. For the chosen organization, find the data needs, you may use different sources of information for the data collection such a : website of the company, publications about the company in newspapers, magazines, specialized journals, if needed you may also conduct interview with company’s representatives. After analyzing the data needs, you have to write a detailed scenario, containing at least four entities, their attributes and the relationships between them in the organization with appropriate cardinality constraints. You have to also identify at least one associative entity in the scenario. Use appropriate citations and references on the data collected from various source. (10 Marks) b. Construct the Entity Relationship Diagram (ERD) for the drafted scenario in the above task. Include all the entities, attributes of each entity assuming the primary key and not null constraints, relationship between the entities with cardinality constraints. The ERD is expected to have: i. Minimum 4 Entities with suitable attributes and constraints. ii. iii. iv. ( 12 Marks) Minimum one associative entity with attributes and referential constraints. (3 Marks) Minimum 5 relationships between the identified entities with appropriate names and cardinality constraints. (10 Marks) All the Primary keys in the all entities are properly identified and marked. (10 Marks) Task 3: Normalize the below given voyage attendance report to First Normal Form, Second Normal Form and Third Normal Form. Make assumptions for the identification of the primary key wherever necessary. (45 Marks) MEC_AMO_TEM_034_01 Page 2 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP Voyage Attendant statement Attendant ID: 05777 Carrier Code: FO Attendant Name: Adam Al Harbi Carrier Name: Oman Carrier Rank/Position: PL Country Carrier: Oman Contanct Number: 00968 99999999 Attendant-flight Details: Flight Flight Date Number FO 768 12/2/2014 FO 123 12/5/2014 FO 567 12/8/2014 FO 772 12/15/2014 FO 768 12/20/2014 FO 123 12/25/2014 FO 345 12/28/2014 Origin Destination Mileage Pilot ID Pilot Name Muscat London Muscat Manila Muscat London London Muscat Manila Muscat London Muscat 15000 15000 7000 7000 15000 15000 P980 P767 P 767 P 560 P569 P980 Hussain Ahmed Mohammed Issa Mohammed Issa Khalid Ali Khalid Ali Hussain Ahmed Muscat California 12000 P235 Majid Khalifa Note: The flight number and mileage from an origin to a destination never varies. 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. MEC_AMO_TEM_034_01 Page 3 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP    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: 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 MEC_AMO_TEM_034_01 Page 4 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP 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. 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 semes ter), 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: MEC_AMO_TEM_034_01 Page 5 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP 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. 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 6 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP 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. Fundamentals of Relational Database Management Systems (COMP 20002) MEC_AMO_TEM_034_01 Page 7 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP Student ID: Student Name: Assignment Evaluation Grid Deliverables Aspects 0 2-6 7-10 Ta s k 1 Work Proposal No s ubmi s s i on Propos al is incomplete but has relevant i nformation Compl ete a nd s a tisfactory proposal. Deliverables Aspects 0 1-4 5-6 7-10 Ta s k 2(a ) Scena ri o for the data needs of the chos en i rrel eva nt Scena ri o i ncomplete Scenario wi th l i ttl e or not a l l the requi rements a ddres s ed Sa ti sfa ctory Scena ri o wi th a l l the requi rements a ddres s ed wi th ci ta ti ons Excel l ent Scena ri o wi th a l l the requi rements a ddres s ed wi th a ccura te ci ta ti ons dra fted orga ni za ti on i n the s cena ri o Mark Mark Deliverables Aspects 0-5 6-9 10-12 13-15 Ta s k 2(b) Enti ti es a nd As s oci a ti ve Enti ti es Ina ppropri a te/wea k i denti fi ca ti on of di fferent entities, a nd a s sociative enti ty, the a ttri butes pa rtial Identi fi ca ti on of di fferent enti ti es a nd a s s oci a ti ve enti ty, the Sa ti s fa ctory i denti fi ca ti on of di fferent entities a nd a s sociative enti ty, the a ttri butes Compl ete a nd a ccura te i n Identi fi ca ti on of di fferent entities, the a ttri butes Aspects 0-4 5-10 11-16 17-20 Rel ations hi ps of ERD a nd Pri ma ry Keys a nd notations for a l l entities Ina ppropri a te/wea k i denti fi ca ti on of rel a tionships a nd key cons tra i nts Pa rti al Identi fi ca ti on of di fferent relationships and Sa ti s fa ctory i denti fi ca ti on of di fferent relationships a nd key cons tra i nts Compl ete a nd a ccura te i n Identi fi ca ti on of di fferent rel a tionships and key cons tra i nts 0 1-3 1st Norma l Irrel eva nt norma l Form form. Wrongl y norma l i zed ta bl es . Ma rk ma y be a s signed for creating base ta bl e. (ERD) Deliverables Ta s k 3 Norma l i za ti on a ttri butes key cons tra i nts . 4-6 Mark Mark 7-10 Mark Pa rti ally s atisfi es 1NF Compl etely satisfies rul es . 1NF rul es . 0 1-5 2nd Norma l Irrel eva nt norma l Wrongl y normalized mos t Pa rti ally s atisfi es 2NF Compl etely satisfies Form form. of the ta bl es . rul es . 2NF rul es . 0 1-5 Irrel eva nt norma l form. Wrongl y normalized mos t of the ta bl es . 3rd Norma l Form MEC_AMO_TEM_034_01 6-11 6-16 Pa rti ally s atisfi es 3NF rul es . 12-15 Mark 17-20 Mark Compl etely satisfies 3NF rul es . Page 8 of 9 Fundamentals of Relational Database Management Systems (COMP 20002) – Spring - 2020 – CW Assignment1 – ALL – QP Feedback : Signature of Teacher: Total Marks Penalty Final Marks MEC_AMO_TEM_034_01 Page 9 of 9
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.

DATABASE MANAGEMENT SYSTEM PROPOSAL

Name

Introduction
The motive behind this project is to implement a time-effective and efficient database for
Oman Air,the flight need to maintain the data about their pilot, carrier and route, flight_jet, attendants
and the flights as well. The project is therefore meant to maintain the database for Omani Air. The
organization needs to keep track of every flight attendant; therefore, the name of the attendant must be
reflected in the database. There is also the route that must be saved by the company. The details about
the attendants that must be kept by the company include name, rank and contract details. Flight data
that must be saved include date, destination and the unique identity of the flight. Another data that
must be kept include pilot_id, name. The data must be saved in a database and therefore need to be
normalized to reduce data redundancies..
Entities

The entities involved in the flight database include Flight, attendant, Pilot and routes. The
company also keeps the jet table.
Entity Relationship Diagram

Database Management System Proposal

2

Steps of the Normalization Process to Ensure the 3NF Level

Normalization is one of the techniques used in cleaning the database. The main aim of
Normalization is decomposing tables to eliminate redundancy. When redundancy is not eliminated, the
tables remain difficult to update thus leading to loss. The process of Normalization follows the
following three steps.

1NF

The first Normal form gives the foundation of normalization. According to the relational database
principles, the first Normal form states that every column in database must be unique; this means that
separate tables have to be created for separate set of data. The first process of First Normal Form
include the removal of the repeating groups, the principle st...


Anonymous
Really helped me to better understand my coursework. Super recommended.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags