Information Technology Skills and Digital Literacy

Computing Applied Science, Computing and Engineering BSc (Hons) Computing Level: 6 Module: COM636 Distributed Data Assignment: 2 Issue Date: Review Date: Tutorial Sessions Submission Dates: 25 th April 2021 before 10.30AM Estimated Completion time: 72 Hours Lecturer: Bindu Jose Verified by: Denise Oram To be completed by student: I certify that, other than where collaboration has been explicitly permitted, this work is the result of my individual effort and that all sources for materials have been acknowledged. I also confirm that I have read and understood the codes of practice on plagiarism contained within the Glyndŵr Academic Regulations and that, by signing this printed for m or typing my name on an electronically submitted version, I am agreeing to be dealt with accordingly in any case of suspected unfair practice. I also certify that my attendance for the module has been at least 70% Name: ............................... ........................... Student Number: ......................................... Date Submitted: ......................................... Student Signature: .......................................... Are extenuating circumstances being claimed? YES / NO If YES, give reference number: -------------------------------------- To be completed by lecturer Comments: Grade / Mark (Indicative: may change when moderated) COM617: Assignment 2 The assignment will entail working with PL/SQL procedures, triggers and reading a range of academic papers, journals and books. You will be writing reports evaluating the issues and benefits of using modern database technologies. Please Note:  In order to complete Task 1 you must carry out required res earch in the field of data analysis and modern database technologies and write a report. You must follow IEEE referencing standards.  In order to complete Task 2 o You must create ‘student management’ tables and insert data using the scripts ( createStudent.sql) provided on VLE (Moodle). o You may have to insert appropriate additional data (details) to test the functionalities of your procedures. o You must create required log tables with appropriate attributes to record the necessary details for enrolment amendments, withdrawals and suspensions . o You must create Procedures, Triggers (and the necessary tables) in University Oracle Server and must documen t the PL/SQL code used to create the Procedures and Triggers. o Procedures and Triggers must be tes ted and test results must be documented. o PL/SQL Code and Test Results must be Word Processed and must be submitted along with Task 1. Task 1: Business Intelligence 50% “We live in a world where vast amounts of data are collected daily. Analysing such dat a is an important need.” [Han, Kamber & Pei] In the light of the above statement, research the field of data analysis and modern database technologies that support business intelligence and write a report which critically evaluates the benefits and issues of adopting any one modern data analysis technology. For example Big Data, Data Warehouse, Data Mining etc. Support your work with relevant references (using IEEE referencing standards) from books, journals and other relevant qualit y information resources. (Max 1500 words +/ -10%) Task 2: Procedure, Functions Triggers and Active Rules in Oracle 50% Recently you have been appointed as a Database Administrator (DBA) in a University. As part of your job you have to manage, monitor and log the database activities in their Student Management Database. You have to create appropriate log details and create active rules etc. to control and monitor database activities. It is important for the University to record the details of t he students, courses, enrolment, grades, staff and sessions (sections). You have to create procedures to manage data centric operations like enrolment, withdrawal, grades, grade reports etc. and must restrict data updates to working hours to protect the da ta. You will have to create some new tables to log/enter the details related to log the database activities, manage students’ withdrawal, course changes etc. The details of the student who withdraw from a course or suspend from a course must be recorded in corresponding log tables along with the staff details (staff ID), date of withdrawal (suspension), reason, and appropriate status message etc. and also may have to remove from the original table (STUDENT, ENROLLMENT etc. ). You must create log tables with appropriate attributes and make sure you add all required attributes to record the necessary details. You may have to add more sample data to test the functionalities of your procedures and to produce meaningful test reports. Task 2.a: Changing Enrolmen t Details After enrolling for a course the students may wish to make some changes. For example: change the course, withdraw from the course or suspend the course for a given time period etc. Depending on their status in a course students are allowed to ma ke certain amendments as listed below. a. Changing Course (section) details: Students who are enrolled on a course are allowed to transfer/change courses (enrol for the appropriate sections). These changes are only allowed if they make the decision at the beginning of the section, before they undergo any type of assessment. For completing a course change following database tasks must completed: i. Enrolment table mu st be amended (updated) with the new section details (section ID) ii. Enrolment log table must have entries detailing the sections they were enrolled on and the new sections with appropriate details and messages to indicate the changes. b. Withdraw from a Course: Students who had not undergone any assessment are allowed to withdraw from a course. If they are withdrawing from a course i. Enrolment details must be removed from the original enrolment table. ii. Student details must be removed from the Student table if they are not enrolled for any other courses. iii. Student and Enrolment log table must have the necessary details with appropriate details and messages to indicate the changes. c. Suspension of Studies: Students who are enrolled and had taken any assessment can suspend the studies for a given period (for example one academic year or two academic year). Students can suspend one course or all courses they are enrolled on which still have some more assessments to be completed. Students can suspend stu dies once for two consecutive academic years or can suspend twice, each time for a single academic year. i. Student details and Enrolment details must be removed from the original tables depending on whether they have any other courses and whether they have awarded any Grades. ii. Student and Enrolment log table must have the necessary details with appropriate details and messages to indicate the changes. iii. Start date and end date of the suspension and the course/section they are suspending must be recorded Create PL/SQL Procedure/s to handle the above listed changes/amendments to the student enrolment. Procedure should accept required parameter/s (e.g. student ID, section ID, suspension start date and end date etc.) . The procedure/s should incorporate the required tasks and satisfy the criterions listed above. Task 2.b: Grade Report : The University needs to produce different reports related to the course, students and their performance, results etc. I. University has to produce the Final Mark sheet for students. This should display student details, description of the courses that a student has enrolled on, Final Mark they have received for that course. Final marks must be calculated considering different types of assessment (quiz, examination, mid -term etc.), number o f assessment of the given type (3 quizzes) and their percentages towards the final grade. II. The University likes to get statistical reports on regarding courses which include the description of the course, sections start date, name of the instr uctor who manage the section, number of students enrolled for section, highest mark, lowest mark, average mark, standard deviation, number of 1 st (Final mark: 80 -100), 2 nd (Final mark: 70 – 79), 3 rd (Final mark: 60 – 69) number of failures (Final mark: 1 -59) etc. Create appropriate PL/SQL Procedure to produce the above listed reports. Please ensure that all necessary details are included and displayed in the report. Task 2.c: Active Rules Using Triggers I. The University student data centre that is responsible for entering student details and enrolment details, works from Monday 9.00 am – Friday 5.00 pm. It is important that the system is protected (don’t allow any changes on appropriate tables) and only allow minimum operations from Friday 5.00 pm to Monday 8:00 am. For example students’ details or enrolment details are only added or amended during office hours. Database activities are monitored all the time, create an active rule to stop such activities and display appropriate messages. II. Uni versity can only accommodate a limited number of students for each section. Create an active rule to display an appropriate message on the screen to warn the DBA while the enrolment is nearing to its capacity and also when it has reached the capacity limit . Guidance Students will get assistance to complete the tasks through the tutorial sessions. Drafts will be reviewed and formative feedback will be given in the tutorial sessions . So you are much less likely to obtain a good grade if you don’t attend the tutorial sessions. Support your work with relevant references from books, journals and other quality information resources using the IEEE Referencing System. Both Tasks are equally weighted (Task 1 is weighted as 50%, Task 2 is weighted as 50% of Assignme nt 2). Failure to complete any of the above tasks or sub tasks in time will result in a loss of marks Submission The assignment should be word -processed. The front sheet must be filled with your Name, Student Number, Date and Signature and must be attached with the assignment work. Work must be submitted as a single file via Turnitin link provided through VLE . The Glyndwr policy on assignment submission will be rigidly adhered to (see your Student Handbook). Submit a single word processed document consisting both Task 1 and Task2. Task 1: Report – Word processed Task 2:  PL/SQL Scripts for procedures and Triggers  Test scripts (execution blocks for Procedures, SQL statements for Triggers) to test the procedures and triggers.  Test results, tak e screen shots where necessary. Also for Task 2 ensure the required tables, data, procedures and triggers are all created in the University Oracle server . Learning Outcomes 1. Critically assess some of the more advanced developments in database technology, e.g. Distributed databases, Multidimensional Database, Multimedia Databases, Mobile Databases 2. Evaluate the current issues associated with theory to practical implementations in database research. 3. Explore advanced aspects of data warehousing, distributed da ta, data intensive computing, remote access and personalised data; encompassing the principles, research results and commercial application of the technologies. 4. Critically evaluate the adoption/use of data warehouse systems and business intelligence practi ces for achieving organisational benefits Key skills for employability: 1. Written, oral and media communication skills 2. Leadership, team working and networking skills 3. Opportunity, creativity and problem solving skills 4. Information technology skills and digita l literacy 5. Information management skills 6. Research skills 7. Intercultural and sustainability skills 8. Career management skills 9. Learning to learn (managing personal and professional development, self - management) 10. Numeracy Assessment Criteria In order to achieve an A grade, the work must be excellent in almost all respects, only very minor limitations. In order to achieve a B+ grade, the work should show strength in most respects, but perhaps has limitations in one or two areas. A good piece of work nevertheless. In order to achieve a B grade, the work should be of a satisfactory standard, showing strength in some areas, but perhaps let down by poor presentation, poor practical work, or poor written explanations where required. In order to achieve a C grade, the work should be of a satisfactory standard but may have significant shortcomings in some areas. Nevertheless shows at least a basic understanding of the concepts and a basic practical ability. A Refer grade will be given t o work that is just unsatisfactory