London Metropolitan University CC6001 Advanced Database Systems Development 1 Sharjeel Aslam CC6001 Advanced Database Systems Development Coursework Assignment 2016-17 Contents 1 - Introduction .......................................................................................................................................2 2 - Case Study - Online Course Management System.............................................................................2 3 -Requirements of the Coursework.......................................................................................................3 3.1 - Contents Page.............................................................................................................................3 3.2 - Normalisation .............................................................................................................................3 3.3 - E-R Model....................................................................................................................................3 3.4 - Data Dictionary ...........................................................................................................................3 3.5 - Generation of the Database .......................................................................................................3 3.6 - Implementation of the Web-based Database Application.........................................................4 3.7 - Documentation of the developed system ..................................................................................4 3.8 - Further Discussion ......................................................................................................................5 4 - Marking Scheme ................................................................................................................................5London Metropolitan University CC6001 Advanced Database Systems Development 2 Sharjeel Aslam 1 - Introduction The coursework assignment is an individual assessment weighted 40% of the marks for the module. It is designed mainly to assess students' practical problem-solving skills and critical thinking/evaluation on the design and development of database systems. It requires the student to analyse, design and implement a web-based database application based on a given business case study. You are asked to provide a software solution as well as appropriate documentation detailing the design and implementation of the system. 2 - Case Study - Online Course Management System EasyLearn is a medium sized limited company which provides education in different subjects (computing, business etc) .They have decided to offer distance learning of BSc computing and professional certifications e.g. Oracle and Cisco. They are planning to develop a online learning system. Business Analyst has suggested minimum requirements of a new online systems as follows:  New learners can view all offered courses online and can choose a course which they would like to enrol.  After successful enrolment learner can view their timetable (time , tutor etc.)  Tutors can view their timetable as well which shows all details (enrolled students, time etc)  Admin can view all details related to students and tutors. Example of learners record list and timetable for a course are shown in the following figure 1and 2. You have been sub-contracted by EasyLearn as a Junior Database Developer to design and implement a prototype of the web-based database system for the EasyLearn. On completion of the system, you are required to provide a full set of documentation of the system to EasyLearn. Your prototype of the system will be developed using Oracle SQL Developer Data Modeler and ASP.NET with C# at London Met.London Metropolitan University CC6001 Advanced Database Systems Development 3 Sharjeel Aslam Figure 1. Example of Learners list LearnerName DOB Address Course Enrolment RefNo Ken Dodge 09/08/89 11 High Street , Folgate E1 9B BSc Active KD201 Cyril Aliened 07/06/91 345 Red Hill, Folgate , E1 U7 Oracle Active CA202 Peter Tim 08/03/76 34 Ivanhoe, Hounslow, TW4 7JH Cisco D-Active PT203 Figure2. Example of Tutor's time table list TutorName Address ContactDetails Course Module Day Time Duration Sharjeel Hounslow 123456XXX BSc Database Tuesday 2pm 3 hrs Hays Feltham 123ZZZZ Oracle SQL Monday 9am 3 hrs Adeel London 567NNNN Cisco Cisco Wednesday 3pm 3hrs 3 -Requirements of the Coursework Marks are awarded for producing a working and properly documented system that meets the requirements specified below as deliverables: 3.1 - Contents Page A list of sections/subsections of the document, including page numbers. 3.2 - Normalisation Produce a set of fully normalised tables for the system:  You may use Figure 1 and Figure 2 as a starting point for normalisation.  You may also add additional attributes where appropriate.  Show clearly all the steps of normalisation, up to the 3rd normal form. 3.3 - E-R Model Use Oracle SQL Developer Data Modeler to produce an Entity Relationship Diagram. The final ERD should be consistent with the outcome of your normalisation. Submit a copy of the ERD. 3.4 - Data Dictionary Use Oracle SQL Developer Data Modeler to produce a list of attributes for each entity. Submit a print-out copy of these lists. 3.5 - Generation of the Database  Use Oracle SQL Developer Data Modeler to convert the E-R diagram into a set of database tables. Provide a print-out of the DDL script for generating the tables (relevant 'CREATE statements only).  Use Oracle SQL Developer to populate these tables with suitable data values (using 'INSERT' statements).  Provide a print-out of contents for all the tables (using 'SELECT' statements).London Metropolitan University CC6001 Advanced Database Systems Development 4 Sharjeel Aslam 3.6 - Implementation of the Web-based Database Application Implementation of a web-based database application which includes the following web forms (web pages) using ASP.NET with C#: Basic Web forms:  Course Details  Course schedule details  Tutor Details Complex Forms:  Tutor's timetabling form (Here tutor can see allocated course details - day , time etc.)  Learner's timetabling form (Learner can see their timetable - day , time ,tutor etc).  Admin form (Admin can view/update/delete learners/tutors details). The completed website connected with the Oracle database must be uploaded onto the designated server, focserver. 3.7 - Documentation of the developed system FOR EACH FORM Implementation document  Provide a set of screen dumps for all the web pages (web forms) you have produced. Testing Document  Provide a copy of the initial data (table contents) in your system.  For each form implemented, list the individual tests that have been carried out together with their results. FOR THE APPLICATION URL address for the uploaded website (connected with the Oracle database) User Manual (up to 5 pages)  The User Manual should have a contents page and separate sections for each form providedLondon Metropolitan University CC6001 Advanced Database Systems Development 5 Sharjeel Aslam  The User Manual should contain clear instructions on how to use the system and how to run each of the forms available to it. 3.8 - Further Discussion Your discussion should summarise your experience in undertaking this coursework. Your work must be submitted in a single word document, with all output including diagrams, tables, forms, SQL scripts clearly labelled and presented. 4 - Marking Scheme Marks will be awarded as follows Specification of database relations  Normalisation based on Figures 1 & 2 [ 15 ]  ER diagram, consistent with normalisation outcome [ 10 ]  Data dictionary (print-out of attribute list for each table) [ 5 ] Generation of Database  Use SQL developer Data modeler to convert ER to DB tables [ 3 ]  Input data into DB tables (INSERT) [ 4 ]  Display data in the tables (SELECT) [ 3 ] Implementation of Database Application  Basic Forms X 3 @5 [ 15 ]  Complex Forms X 3 @5 [ 15 ]  Home Page [ 5 ]  Testing [ 5 ]  User Manual [ 5 ] Further discussionLondon Metropolitan University CC6001 Advanced Database Systems Development 6 Sharjeel Aslam  Discussion of own experience [ 5 ] Professional documentation (overall packaging)  Clarity of presentation, including clear labelling [ 5 ]  Report structure & format, including contents page and description [ 5 ] END