Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 1 of 8 Hand out week: Week 3 Duration: 7 weeks Submission Date & Time: Before 15th December 2016, 11:55 pm Total Weightage: 100 marks scaled down to 30 marks 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. Intended Learning Outcomes covered: 1. Design the logical structure of a database using Entity-Relationship diagram. 2. Apply normalization techniques to reduce redundancy in a database. Task 1: 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 the end of week 7 (before 11:55 PM, November 24, 2016) and must include:  What you will do with the given tasks: task 2, task 3 and the dates (timeline) by when they will be completed  General overview of initial understanding of solutions to task 2 and task 3  Identification of Literature Resources (10 marks) Relational Database Systems (COMP 1005) Individual Assignment Fall 2016 Level: 5Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 2 of 8 Task 2: Scenario: Apollo Hospitals (AH) is a popular medical service provider in Oman. AH wants to automate its business functions into an information system. The proposed information system must be developed keeping in mind the following business rules. As part of this, you need to analyse and develop an Entity Relationship model depicting the following various business requirements and functionalities for the proposed Pharmacy Information System of AH. Dr. Prathap Reddy, Chairman, is aware that other multi-specialty hospitals are coming up in a big way and AH will face fierce competition from new healthcare systems. So, he has decided to take all measures to always be ahead of others in the market. It is increasingly important to AH that Reddy be able to analyse services rendered by AH and perception of patients more thoroughly. The Pharmacy places requests to the Purchase Department whenever it is in need of drugs. Pharmacy is identified by unique id, name and telephone number. Purchase Department is identified by a unique number. The other elements are phone number, manager. The Purchase Department submits many orders or may not submit an order. An order is uniquely identified by an id. The other elements of order are placement date, fulfillment date, received date, order amount. An order consists of one or more drugs or items. A drug is supplied by one or more supplier. A supplier supplies many drugs or may not supply a drug. Whenever a supplier supplies a drug, AH wants to record the shipment details such as date of supply. The elements of drug are unique id, name, type, manufacturer id, manufacture date, expiry date, batch number, unit cost. A supplier is uniquely identified by id. The other elements of supplier are name, address, phone number, email, location and track record. a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design. (30 marks) b) For the task 2a, analyse and recommend how the proposed ER model could accommodate the shipment and payments. (15 marks)Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 3 of 8 Task 3: a) Normalize the below given Patient Prescription Bill to First Normal Form, Second Normal Form and Third Normal Form. (30 marks) b) For the finally arrived normalized form in task 3a above, discuss how the redundancy has been minimized. (15 marks) Task 4: Be ready for a written viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the written viva will be announced in the class and on Moodle/MEC mail. Marks for task 2 and task 3 will depend on the written viva. Note: Task 4 is compulsory. No marks will be awarded to tasks 2 & 3 if written viva is not taken by a student. 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. PHARMACY INFORMATION SYSTEM, APOLLO HOSPITALS Patient Prescription Bill Bill number: 0000501 Bill date: 02-May-2016 Registration Number: 0500010001 Prescription Number: 501 Patient Name: Mohsin Patient Address: Al Khuwair Patient Category: M Doctor ID: 129 Doctor Name: Imaan Pres. Date Drug ID Drug Name Quantity Unit Price Total 29/12/2015 1111 AAAAA 10 2.00 20.00 19/03/2016 1232 GGGGG 4 9.00 36.00 19/03/2016 2425 SYRINGE 10 0.50 5.00 21/03/2016 1111 AAAAA 10 2.00 20.00 21/03/2016 3339 PANADOL 10 1.00 10.00 TOTAL LESS 10% 91.00 9.10 NETT PAYABLE AMOUNT (OMR) 81.90Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 4 of 8  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 dateRelational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 5 of 8 Rules & Regulations  If any topic or diagram of an assignment is found copied from others, then marks will be deducted from both the assignments.  The purpose of assignment is to do some research work so you can consult books in the Library or use internet or computer magazines or any other source. Plagiarism Policy: DO NOT DIRECTLY COPY/PASTE FROM INTERNET/BOOKS! DO NOT COPY FROM ONE ANOTHER! PRODUCE YOUR OWN ASSIGNMENT SOLUTION REPORT. The work submitted must be your own and must not be based on inputs from others. Your assignments shall be submitted to electronic databases to identify plagiarism. If assignment is found to be plagiarised, action will be initiated. Please refer to the amended MEC policy on plagiarism for more details as given below: Clarification on Plagiarism Policy As per MEC policy, any form of violation of academic integrity will invite severe penalty. Plagiarised documents, in part or in whole, submitted by the students will be subject to this policy. A. First offence of plagiarism a.) A 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.) Mark deduction: When the work is resubmitted, the marking will be undertaken according to the marking criteria. In compliance with this policy, the 25% deduction is then made on the marks obtained. For example, in an assessment that carries a maximum of 50 marks, suppose a student were to obtain 30 marks for the resubmitted work, the final marks for that assessment will be 22.5 (after deducting 25% of the marks actually obtained for the resubmitted work). c.) Period of resubmission: The student will have to resubmit the work one week from the date he or she is advised to resubmit. For example, if the formal advice to resubmit was communicated to the student on a Sunday (latest by 5 pm), the student will have to resubmit the work latest by next Sunday 5 pm. d.) If the re-submitted work is also detected to be plagiarized, then the work will be awarded a zero. e.) Resubmission of the work beyond the maximum period of one week will not be accepted and the work will be awarded a zero.Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 6 of 8 B. Any further offence of plagiarism a.) If any student is again caught in an act of plagiarism during his/her course of study (either in the same module, same semester or in any other 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. C. Guidelines a.) Type 1: In case plagiarism is detected in any component or part submission (submitted at different times) of one assessment (assignment), the deduction in marks will be applicable for the whole assessment (assignment), even if only the component or part submission alone needs to be resubmitted. b.) Type 2: In case plagiarism is detected in a group assessment, all students of the group will be considered as having committed an act of plagiarism irrespective of whether plagiarism is on account of the act of all or a few or only one member. The policy will then be applied to all students. c.) Type 3: Combination of Type 1 and Type 2: In case plagiarism is detected in any component or part submission (submitted at different times) of a group assessment (assignment), the deduction in marks will be applicable for the whole assessment (assignment), 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 irrespective of whether plagiarism is on account of the act of all or a few or only one member. The policy will then be applied to all the students of the group. d.) Type 4: Variation of Type 1 and Type 2: In cases where 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: 1. If plagiarism is detected in the group assessment component, all students of the group will be considered as having committed an act of plagiarism, irrespective of whether plagiarism is on account of the act of all or a few or only one member. The policy will then be applied to all students of the group. In such cases the group assessment component will be resubmitted as per the policy. 2. If plagiarism is detected in the individual assessment component, the individual assessment component will be resubmitted as per the policy. The policy will then be applied to that student alone. 3. In both cases (a) and/or (b), the deduction in marks will be applicable for the whole assessment (assignment). D. Amount of similar material a.) The total amount of similar material in any form of student work from all sources put together should not exceed 30% (including direct quotations). b.) The total amount of quoted material (direct quotations) in any form of student work from all sources put together should not exceed 10%. c.) The total amount of similar material in any form of student work from a single source should not exceed 7 percent. However, cases having a similarity of less than 7 percent in such cases may still be investigated by the faculty depending on the seriousness of the case.Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 7 of 8 d.) If faculty member find enough merit in the case of a student work with a similarity (with a single source) of more than 7 percent as not a case of plagiarism, the faculty member should provide detailed comments/remarks to justify the case. Late Submission Penalty for late submission - 5% of the maximum mark specified for the assessment will be deducted for each working day. Assessment documents submitted beyond a period of one week after the last date of submission will not be accepted and will be marked as zero for that assessment. Note: Must follow assignments instructions. This assessment shall assess the following learning outcomes: Learning Outcomes Skills Acquired  Design the logical structure of a database using Entity-Relationship diagram. Critical Thinking, Analytical, Comparing, Contrasting, Decision-making, Data Modeling, Researching, Making judgment about complex issues, Communicate conclusions effectively.  Apply normalization techniques to reduce redundancy in a database. Critical Thinking, Analytical, Cognitive, Problem Solving, Evaluating Evidence, Making judgment about complex issues, Researching, Demonstrate Creativity, Communicate conclusions effectively.Relational Database Systems(COMP 1005) – Fall – 16 – CW1–All COMP 1005 Page 8 of 8 Relational Database Systems (COMP 1005) Fall 2016 Student ID:____________ Student Name:_________________________________________ Assignment Evaluation Grid Deliverables Aspects Mark Range: 0-10 Mark Task 1 Proposal Submission  What you will do with the given tasks and the dates by when they will be completed  General overview of initial understanding of solutions to all the tasks  Identification of Literature Resources Deliverables Aspects 0-3 3.5-13.5 14-26.5 27-30 Mark Task 2 (a) ER Diagram Incomplete/ Weak/Plagia rized Report Information is incomplete. Identification of different entities, the attributes, Primary key, and the relationship between the entities are partial. Information is satisfactory. Complete and accurate in all aspects Deliverables Aspects 0-3 3.5-10 10.5-14 14.5-15 Mark Task 2 (b) ER Diagram Incomplete/ Weak/Plagia rized Report Information is incomplete. Mostly recommended the shipment and payments Properly recommended accommodatin g the shipment and payments Deliverables Aspects 0-3 3.5-13.5 14-26.5 27-30 Mark Task 3 (a) Normalisati on Incomplete/ Weak/Plagia rized Report Wrongly normalised relation. Mark may be assigned for creating base table. Partially normalised tables with detailed explanation on normalization Completely normalised all the relations with proper explanation. Deliverables Aspects 0-3 3.5-10 10.5-14 14.5-15 Mark Task 3 (b) Normalisati on Incomplete/ Weak/Plagia rized Report Incorrectly discussed measures to minimize the redundancy Partially discussed measures to minimize the redundancy Properly discussed measures to minimize the redundancy Marks Obtained (100) Penalty Final Mark (out of 100)