PG-DSA-ECM38IS-Spring-16-CW2 1 Database systems and Administration (ECM38IS) Spring 2016 Individual Assignment Hand out Week: Week 2 Duration: 9 weeks Submission Date: Week 11 Total Marks: 100 Objective: The assignment constitutes 70% of the module mark. Assessment focuses on design and research issues related to Database Management Systems. Each student submits a detailed report as per the task specifications through Moodle. The assignment has two sections (Part A and Part B) each focusing on advanced database concepts: in Part A, you are required to write a scholarly academic article based on critical engagement with a chosen topic; part B focuses on analysis of key factors of database systems. Outcome: 1. Evaluate the role of conceptual modeling in the development of database systems and apply the entity-relationship modeling approach to a realistic scenario 2. Apply the essential concepts of relational databases, (including keys, normalization and referential integrity) to sample data sets and demonstrate best practice in the development of an appropriate and effective database system 3. Select and apply appropriate techniques involved in database administration 4. Critically evaluate security issues relating to database management systems and propose appropriate strategies to address security problems PG-DSA-ECM38IS-Spring-16-CW2 2 Part A Write a research article in IEEE Format exploring innovative concepts/techniques in the context of Challenges and Solutions for Structuring the Unstructured data. You are required to focus on any related topics of your interest. You can visit the website of ERCIM - the European Research Consortium for Informatics and Mathematics to get an idea about the importance of research on database systems. Prepare a research article (approx. 1500 words) and a poster presentation covering the following aspects of research:  Problem specification, importance of research and research questions  Theoretical framework  Rigor of literature review  Methods or empirical analysis techniques;  Key findings and conclusions; The article can be a survey paper or a concept paper emphasizing on the current research trends in the area of Huge/Big database systems. As a Post Graduate student, you are required to spend time in research and study. Apply the research skills (such as ability to formulate research questions; technical and analytical skills to solve those questions, ability to communicate results) to prepare your article. It is expected that a Post Graduate student demonstrates the research abilities to expand his/her knowledge on latest database technology. Importantly, to achieve the above goal, you are expected to learn with much greater independence and need to do a significant background reading on the chosen topic. The template (Microsoft word file) for the article will be made available on the Moodle soon. The date, venue and specifications of the poster presentation will be communicated to you. No marks will be awarded for Part A if poster presentation is not done. Part B Scenario: PEC offers several programs. Each program is supervised / chaired by a Head of the department (HoD) who is a lecturer. One HoD can lead only one program. There are several departments for each program. Many modules are offered by each department. Few departments are non-academic departments which do not offer any module. A department may offer several sessions of the same module. A session is taught by a lecturer at a given class room in a building. Every lecturer may have PG-DSA-ECM38IS-Spring-16-CW2 3 a maximum of three sessions. Though every department may have several lecturers, only one lecturer will be in the position of a chairman. A lecturer involves in teaching or administration. A Student may be enrolled in several sessions and the management wishes to keep track of enrolment date. There are numerous students in each department and the major field of study is offered by that department. Each student has been assigned an advisor who is a lecturer. Advisors counsel the students. Propose a conceptual model after an in-depth research on university/college information management system's operations and processes. In order to come up with an appropriate design/solution, you must study the requirements, issues and best practices in this problem context. You can make relevant assumptions required to formulate business rules, security, administration and modelling. Based on the above information execute the following tasks. You may have to make additional assumptions to execute each of the given tasks. Task 1: Design an Entity Relationship Diagram (ERD) to model the above scenario. Identify the different entities and attributes of each entity. (A minimum of 8 entities need to be identified). Suggest and justify the choice of relationship and cardinality of the relationship. State the assumptions made by you (if any) to develop the ERD based on your research. Provide detailed analysis and justification for the selection of entities and attributes based on your research. Task 2: Derive a set of relational tables from the ER diagram (Task 1), using appropriate choices for the table attributes. List the integrity rules and business rules, which you would recommend to ensure the quality of data. Indicate all necessary key constraints. Critically analyze the constraints and business rules based on your study. Task 3: Outline the kinds of anomalies that can arise in the scenario given above by using un-normalized tables. Show how the table created in task 2 could be re-organized into separate tables to avoid anomalies (Decompose the table structures to a set of 3NF tables). PG-DSA-ECM38IS-Spring-16-CW2 4 Task 4: As a database administrator identification of specific roles for users, authentication policy, storage management and database recovery are critical. The efficiency and complexity of database administration depends on the identified strategy and tools. Propose and critically review user management, backup/recovery strategy, storage management/techniques formulated for the system discussed in Task 1 to Task 3. Task 5: Be ready for a presentation with your teacher to discuss/demonstrate your knowledge of the different concepts used in preparing the assignment. Produce evidences (if any) during your presentation. Note: Task 5 is compulsory. No marks will be awarded to the tasks 1 to 4 without presentation. Guidelines Follow the guidelines mentioned below for your assignment. i. Students are expected to do a critical analysis of the tasks and present their analysis by drawing on relevant theoretical concepts. Marks will be awarded based on the ability of students to undertake the task in a logical manner by drawing on appropriate concepts and theoretical frameworks/literatures. ii. Report should have a Title Page. Title Page should contain the following information. a. Assignment Name b. Student name c. Student ID iii. It should have Table of Contents. iv. Use page numbers. v. Assignment should be typed in your own words using Times New Roman font. vi. Use Diagrams to explain in detail. vii. Use suitable notations in your diagrams. viii. Use appropriate SQL syntax wherever applicable. ix. Copy paste from the Internet is strictly not acceptable. x. References should be included in the last page and literature under pinning is needed at the point of explaining the concept. xi. Attach the evidences (if any). PG-DSA-ECM38IS-Spring-16-CW2 5 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. We are obliged to establish assessment criteria and then mark you upon how well (or otherwise) you meet those criteria. In turn, our assessment of you will be scrutinized by the external examiners, so we must be able to defend the marks we award. We will be far more impressed by a student who demonstrates their active participation, interest and understanding of the module subject matter than by a meticulously presented submission.  Work submitted up to 1 week (5 working days) after the due date for submission will lose 10% of the mark (after internal moderation).  Work submitted after this, but up to 2 weeks (10 working days) late will lose 20% of the overall marks.  Work tendered for submission more than 2 weeks (10 working days) after the due date will not be accepted and an absence will be recorded for the assessment concerned. This will count as a failed attempt and may result in you failing the module overall. Plagiarism Policy Please refer to the plagiarism policy in the PG student handbook. PG-DSA-ECM38IS-Spring-16-CW2 6 Each criteria will be graded as follows: Marks Evidence of research Critical evaluation Overall Range 70-100 Shows evidence of extensive investigation of relevant concepts, technologies and issues. Clearly defined argument/ problem definition and source for lucid argumentation. Shows clear evidence of reflective thinking about the issues presents a clear and relevant argument. Substantial originality of innovation and thinking. Work of a very high standard 60-69 Shows evidence of investigation of relevant concepts, technologies and issues. Presentation of data, arguments and decisions in a useful form. Accurate data and conclusions of the analysis. Shows reasonable evidence of reflective thinking about the issues with adequate evaluation. Demonstration of detailed analysis and clear expectations are set based on literature. Good logic and coherence of argumentation. Work of good quality 50-59 Reasonable level of investigation. Adequate application of theoretical framework and research methods. Adequate evaluation, lacking in depth of reflective thinking with respect to major points. Demonstration of adequate ability to conduct critical analysis. Acceptable And adequate work 45-49 Less adequate research into the relevant concepts, technologies and issues. Satisfactory conceptualization of the area of study. Largely descriptive, with little reflective thinking. Work with some merit but at the lower end only just achieving what is required. 40-44 Poor research. Inadequate, inaccurate or irrelevant treatment of the task specifications. Poor application of theoretical framework and research methods. Clear signs of understanding, but presentation, reflection and analysis are poor. The work is only just Acceptable in its present form. 0-38 Lack of evidence of research or weak report. No discernible reflection or evaluation. Inability to deal with the chosen research question in a critical manner. The work is deemed weak and inadequate. PG-DSA-ECM38IS-Spring-16-CW2 7 CRITERIA Maximum Marks Marks Obtained Part A: Research article:  (a) Quality, originality and relevance of research article on big data management (5M)  (b) Evidence of independent research and critical thinking (3M)  (c) Depth of understanding of problem context (3M)  (d) Literature review(3M) 25  (e) Concise objectives of research problem (3M)  (f) Research methodology (3M)  (g) IEEE format (5M) Part A: Poster presentation:  (a) Contents: Innovative / Creative (5M)  (b) Design: Organization (5M), compliance with documentation, grammar and spelling (5M) 25  (c) Presentation: Presentation skill (5M), Q&A (5M) Part B: Task 1: Extent of clarity in evaluating the role of conceptual modeling in the development of database systems and apply the entity-relationship modeling approach to a realistic scenario 12  (a) 8 entities with attributes including justification (8M)  (b) Justification of choice of relationship and cardinalities (4M) Part B: Task 2: Ability to apply the essential concepts of relational databases to sample data sets and demonstrate best practice in the development of an appropriate and effective database system. 22  (a) Formation of 8 tables (8M)  (b) Integrity rules and business rules (4+4=8M)  (c) Key constraints (4M)  (d) Critical analysis of constraints and business rules (2M) Part B: Task 3:  (a) Outline the 3 anomalies (3*2=6M) 10  (b) Normalization and normal forms (4M) Part B: Task 4: Ability to review  (a) User management (2M) 6  (b) Backup/recovery strategy (2M)  (c) Storage management (2M) PG-DSA-ECM38IS-Spring-16-CW2 8 Student's Name: ID Marks Awarded for Part A: Marks Awarded for Part B: Marks obtained out of 100: Penalty (if any): Marks Obtained out of 100 (after deducting penalty) Marks Obtained out of 70: Name of the Assessor (Instructor) Signature of the Assessor Comments: Module Title: Database Systems and Administration (ECM38IS) Module Leader/ Dr. Syed Zakir Ali / Dr. Smitha Kumaran Nair Instructor: Semester: Spring 2016 Programme: MBA(IT)