Fundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 1 of 8 Hand out week: Week 4 Duration: 6 weeks Proposal submission week: week 7 Final Submission week: week 10 Total Marks: 100 Objective: This is an individual assignment aimed to give the student exposure to the concepts relating to database systems and optimal strategies for efficient management of databases Learning Outcomes assessed: 1. Create E-R diagrams. 2. Normalize tables. 3. Design a database. Assessment feedback: Fundamentals of Relational Database Management Systems (COMP 0301) Individual Assignment Fall 2016 Assessment Nr. Submission Week of Assessment Week of Giving Feedback Type of Feedback 2 Week 10 Week 12 Interim formative feedback (for the work proposal submitted in week 7) will be uploaded in Moodle by the end of Week 8 Individual feedback will be uploaded in Moodle by the end of week 12Fundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 2 of 8 Task 1: (10 Marks) 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 task 2 which includes, the name of entities, associative entities and relationships. Initial understanding of solution to task 3. c) Timeline for completion of task 2 and task 3. The work proposal must be submitted in a word file through the link available in Moodle. The proposal can also include draft answers. Scenario: Polyglot institute conducts Certification Exams. These exams are basically organized by different certification councils. The institute operates in the following way. A candidate identified by a unique id, name, address and mobile no. should register to write an examination. A candidate can choose and register one or more certification exams. However one exam has to be registered by at least one candidate. Every exam is identified by its code, name and council name. The institute would like to track the number of attempts of each candidate when he/she registers for an exam. When a candidate registers for an exam, the institute allocates room identified by its no, building name and location. An exam can be scheduled in at least one room and a room may be scheduled for more exams or may not be scheduled for any exams at all. In order to avoid clashes, the institute needs to store the exam date whenever a room is scheduled for an exam. The institute assigns one invigilator to each room. Each invigilator is identified by his/her ID, name, contact no. and specialization. An invigilator is assigned to exactly one room and a room should have one invigilator compulsorily. An invigilator belongs to exactly one certification council. Each council is designated by its code, name and country. It is not mandatory that a council should have invigilators.Fundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 3 of 8 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, foreign key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design. (30 Marks) b) Write an SQL statement to create the tables and insert at least 2 records for each table created above. (18 marks) c) Write at least two SQL statements having "subqueries" based on the tables created above to demonstrate the concept of "subquery". (6 Marks) d) Write at least two SQL statements having "joins" based on the tables created above to demonstrate the concept of "Join". (6 Marks) Task 3: a. Normalize the below given import summary form to First Normal Form, Second Normal Form and Third Normal Form. (20 Marks) Import Summary Import id: IM92-2016 Manager id: M567 Import Date: 2/2/2016 Manager name: Ali Al Rawahi Source: Dubai Destination: Oman Import Parts Details: Partscode Partsname Type Materialtype Weight Quantity Total Weight P2345 Couch Furniture Leather 400 150 60,000 P3413 Door Building Accessory Aluminum 85 1,500 127,500 P2415 Office Chair Furniture Leather 70 600 42,000 P4424 Sofa Furniture Wood 300 400 120,000 P2476 Door Knob Building Accessory Iron 30 600 18,000 P3412 Window frame Building Accessory Aluminum 100 400 40,000 P3422 Office table Furniture Wood 250 100 25,000Fundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 4 of 8 b) Write your refection by describing the normalization process carried in the task above in connection with concepts taken from literature in 400-500 words. The literature can include conference papers / scholarly articles / text / reference books. (10 Marks) Task 4: Be ready for a presentation and 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: Task 4 is compulsory. No marks will be awarded to tasks 1-3 if no presentation conducted. 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/scanned 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 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 dateFundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 5 of 8 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 as to refer books in Library or use internet or computer magazines or any other source. Plagiarism Policy Plagiarised documents, in parts or whole, submitted by the students will be rejected. For the first offence of plagiarism, a student enrolled in undergraduate programme is penalised with a deduction of 25% of the maximum marks on the assessment as per the plagiarism policy statement and is allowed to resubmit the work once, within a week's period. The student has the right to appeal against the decision made, to the respective Programme Coordinator. Further appeal on decision taken may be made to the Head of the concerned department. If the student is not satisfied by the decisions taken by the department, s/he can appeal to the Plagiarism Appeals Committee. For the second offence of plagiarism against the student, (committed in any semester after the semester of the student's first offence of plagiarism in the tenure of the student in the undergraduate programme), the student is awarded zero in the assessment where plagiarised content was found. The student is not allowed to re-submit the assessment. The student has the right to appeal against the decision made to the respective Programme Coordinator. Further appeal on decision taken may be made to the Head of the concerned department. If the student is not satisfied by the decisions taken by the department, s/he can appeal to the Plagiarism Appeals Committee. Repeated offences of plagiarism by the student (more than two times) are dealt with as per the college policy on Academic Dishonesty as mentioned in the Student Handbook (6.2). All decisions on plagiarism related offences will be communicated to the student by the respective module instructors, using routine channels of communication with students, currently used by the college, preferably on the same day the charges are confirmed or the very next working day. The student has the right to appeal against the decision informed by the module instructor to the respective Programme Coordinator. Further appeal on decision taken may be made to the Head of the concernedFundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 6 of 8 department. If the student is not satisfied by the decisions taken by the department, s/he can appeal to the Plagiarism Appeals Committee. In certain cases, the student may be required to appear for an interview with the module instructor, if deemed necessary. The maximum time limit given to a student to raise appeal against the charges of plagiarism is two working days including the day on which the matter is informed to the student. It is preferable that the appeals are made on the same day of the college on which the student is informed of the issue. All appeals must be made during regular working hours of the college. 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.Fundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 7 of 8 Fundamentals of Relational Database Management Systems (COMP 0301) Student ID: ` Student Name: Assignment Evaluation Grid – Fall 2016 Deliverables Aspects 0 2-6 7-10 Mark Task 1 Work Proposal No submission Proposal is incomplete but has relevant information Complete and satisfactory proposal. Deliverables Aspects 0 1-15 15-25 25-30 Mark Task 2(a) ER Diagram Incomplete diagram in all aspects Identification of different entities, the attributes, key constraints, and the relationship with cardinalities between the entities is partial. Information not satisfactory Information is satisfactory with minimal errors. Complete and accurate in all aspects Deliverables Aspects 0 1-9 10-15 16-18 Mark Task 2(b) Create tables and insert records Incomplete work Created tables and records with errors Created tables and records with minimal errors Create tables and records successfully Deliverables Aspects 0 1-2 3-5 6 Mark Task 2(c) Sub queries Incomplete work Provided 1 subquery with or without errors Provided 2 sub queries with minimal errors Provided 2 sub queries with no errors Deliverables Aspects 0 1-2 3-5 6 Mark Task 2(d) Sub queries Incomplete work Provided 1 join query with or without errors Provided 2 join queries with minimal errors Provided 2 join queries with no errorsFundamentals of RDBMS (COMP 0301) – Fall – 16 – CW2(ASSMT) – All – QP COMP 0301 Page 8 of 8 Deliverables Aspects 0 1-5 6-15 16-20 Mark Task 3(a) Normalization, Design tables Incomplete work Wrongly normalized tables. Mark may be assigned for creating base table. Partially normalized tables Completely normalized tables. Deliverables Aspects 0 1-2 3-5 6-10 Mark Task 3(b) Reflection on normalization Incomplete/ Weak/Plagiarized Report Description is weak and not underpinned with literature Description is satisfactory and underpinned with literature Description is Excellent and accurately underpinned with literature Signature of Teacher: Feedback: Total Marks Penalty Final Marks