VPC Report-Applications of Computer Sciences-Project Report, Study Guides, Projects, Research of Applications of Computer Sciences

Download VPC Report-Applications of Computer Sciences-Project Report and more Study Guides, Projects, Research Applications of Computer Sciences in PDF only on Docsity! Table of Contents Table of Contents _________________________________________________ i List of Figures and Tables __________________________________________ii ABSTRACT_____________________________________________________ iii SCHEDULE______________________________________________________v Chapter 1 Introduction________________________________________________1 1.1 Purpose_______________________________________________________1 1.2 Previous work done_____________________________________________1 Chapter 2 Business Logic Design________________________________________5 2.1 Business Rules _________________________________________________5 Chapter 3 Relational Database Model ___________________________________9 3.1 Mapping of E-R Model to Relational Model ________________________9 3.2 Relational Schema______________________________________________9 3.3 Normalized Relational Schema __________________________________11 Chapter 4 Physical Database Design____________________________________13 4.1 Domain of Attributes __________________________________________13 4.2 SQL Scripts __________________________________________________15 References _________________________________________________________24 i docsity.com List of Figures and Tables Figure 1.1 Customized Life-Cycle Model .......................................................................... 1 Figure 2 Pre-Mature Architecture....................................................................................... 2 Figure 3 E-R Model of VPC ............................................................................................... 3 Table 4. 1 Domain of Attributes ....................................................................................... 15 Figure 4.1 Database Diagram showing Detailed Evaluation ............................................ 19 Figure 4. 2 Database Diagram showing Evaluation Relationship .................................... 20 Figure 4. 3 Database Diagram showing Execution Relationship ..................................... 20 Figure 4. 4 Database Diagram showing Proj-Artifact Relationship ................................. 21 Figure 4. 5 Database Diagram showing Proj-Faculty Relationship.................................. 21 Figure 4. 6 Database Diagram showing Revision Relationship ....................................... 22 Figure 4. 7 Database Diagram showingSubmission Relationship .................................... 22 Figure 4. 8 Database Diagram showing all tables............................................................. 23 ii docsity.com SCHEDULE The Project work was divided into several different phases and each task was scheduled to be completed in a certain specific time as specified in SMPM. The following Gantt chart depicts the whole schedule of the project execution. The following tasks were completed before the start of summer vacations. • Project Idea Exploration • Software Requirements The following tasks were completed until 7th semester midterm. • Proof of concept prototype • Premature Architectural Design • Logical Database Design According to the proposed Schedule, following tasks were to be completed by the end of 7th semester. • Business Logic Design. • Relational Model of VPC Database • Physical database Design • Physical Database Implementation v docsity.com Chapter 1 Introduction 1.1 Purpose The purpose of this document is to specify the work done on the project, Virtual Projects Coordinator (VPC) during the period of 7th semester midterm to 7th semester final i.e. December 2006 to February 2007. VPC is an Internet-based application, which is intended to manage, and automate the overall process of the final BS (CIS) seminar project coordination at DCIS, PIEAS. This document provides the details of work done on the project according to the schedule as mentioned in the SPMP. 1.2 Previous work done During the period of summer vacations until the midterm of 7th semester, following work was done on the project. 1.2.1 Proof of Concept Prototype As mentioned in the SPMP, A rapid prototype was to be developed which was suppose to obey the Rapid Prototyping Life-Cycle Model [1]. However, Rapid Prototyping Life-Cycle model was customized for our project and which involved development of a Proof of Concept Prototype [2]. Proof of concept prototype is different from a Rapid Prototype, in that it is not rapid i.e. it does not get developed very quickly, rather it takes time but is more efficient in describing the functionality of the system than a Rapid Prototype. The figure below shows the customized Project Development Life Cycle Model [3] involving the proof of concept prototype. Figure 1.1 Customized Life-Cycle Model 1 docsity.com 1.2.2 Premature Architecture The Proof of concept Prototype had helped a great deal in development of a pre mature architecture of the whole system. This Architecture is shown in the figure below. Figure 2 Pre-Mature Architecture 1.2.3 Logical Database Design A database is required to handle all the data manipulations involved with the VPC. The development of Database for VPC was divided into two parts; Logical Database Design[4] and Physical Database Design[5]. Logical database Development involved 2 docsity.com Chapter 2 Business Logic Design Business Logic design incorporates the business logic into the system and should be designed prior to the development of Physical Database Design, Physical Database Implementation and Application Software Design and Implementation. However, there is no harm in development of Logical Models such as Logical database design, prior to this stage. Business Logic Design comprises of a set of business rules which the system is supposed to obey. 2.1 Business Rules Following Business rules are identified and Physical Database and Application software should obey these rule. 1. All users need to be authenticated before using the functionalities of VPC. 2. Administrator account shall exist at the time of deployment of VPC. 3. Administrator shall create accounts of Students and faculty members by entering the specific details as provided to him by the department. On creation of user accounts, concerned user should be notified via email. 4. User name for Administrator should be ‘administrator’. 5. User name for student should be a six-character string with a prefix of ‘st’ and postfix of a number ranging in 0000-9999, for example st0001. 6. User name for Faculty should be a six-character string with a prefix of ‘fac’ and postfix of a number ranging in 000-999, for example fac001. 7. User Names should be case insensitive. 8. Passwords should be case sensitive. 9. Once authenticated, every user must be shown and given access to what he has right for. 10. On authentication, each user shall be shown a welcome page from where he can perform the tasks that are concerned to him. 11. The complete project title should be saved in the database and complete project title or its abbreviation may be used while showing it to users. 12. Degree program would represent which degree program the Student(s) is in. For example BS(CIS), MS(SE), MS(NE), MS(PE) etc 5 docsity.com 13. Number of students undertaking a project should be specified at the time of project registration. The concerned department specifies limit to this number and it varies with different Degree Programs. 14. Registration number should be a character string with maximum of 15 characters and with a special format, which is visible in this sample registration number 03-3-1-038-2003. 15. All possible email addresses with maximum of 20 characters can be specified as email address. 16. In case of external supervisor, name of relevant organization/ industry must be specified when project is registered. 17. A project must have one supervisor and two examiners. 18. A project can have a co-supervisor and an additional examiner if specified by the department. 19. On every evaluation Examiners and Supervisors should be able to write their remarks about the evaluation. 20. Technical Consultation with supervisor carries 20 marks in 6th semester and 10 marks each in 7th and 8th semesters. 21. Literature Survey carries 15 marks in 6th semester and 10 marks in 7th semester. 22. Presentation and comprehension of the project carries 15 marks in 6th and 7th semesters and 10 marks in 8th semester. 23. Q/A session at the end of each evaluation presentation carries 10 marks in 6th semester and 15 marks each in 7th and 8th semesters. 24. Development of SRS Document carries 40 marks in 6th semester. 25. Development of Design Description Document or Research Findings Description Document carries 40 marks in 7th semester. 26. Project Progress Report carries 10 marks in 7th semester. 27. Project Report Work carries 10 marks in 8th semester. 28. Implementation of Software or Research Work carries 40 marks in 8th semester. 29. Software Testing or research Conclusions carries 15 marks in 8th semester. 30. Both examiners as well as supervisor for each evaluation and for each student should enter project grades separately. Evaluation Criteria are different for each semester. Evaluation should be done according to the semester of the students in question. 6 docsity.com 31. External supervisors can only evaluate Development of SRS Document in 6th semester, Development of Design Description Document or Research Findings Description Document and Project Progress Report in 7th semester, Presentation and comprehension of project and Q/A sessions. 32. In case of a mistake in data entry or a changed detail(s) by the department, only the administrator shall be able to do project modification. 33. Project modification summary should be shown when project details are entered/modified successfully. 34. Administrator shall enter the evaluation schedules as specified by the department. 35. In case of a mistake in data entry or a changed detail(s) by the department, only the administrator shall be able to do evaluation schedule modification. 36. Evaluation schedule modification summary should be shown after evaluation schedule is entered/modified successfully. 37. If student’s work is not of required standard and it is felt that he/she should work more on the project, then he/she may be asked to work further and reappear again to present his/her work to the evaluation committee after some specified time as suggested by the supervisor/examiners. Administrator should enter the evaluation schedule accordingly. 38. Administrator can delete unnecessary artifacts. Only those Artifacts shall be available for deletion which are more than a year old and whose projects are not under execution. 39. When a user is changing his password, old password must be confirmed twice before setting the proposed new password as current password. 40. Password changed summary should be shown after password is changed successfully. 41. Faculty members shall be able to select any project under their supervision/examination and then view its evaluation schedule. 42. Faculty member shall not be able to view projects and their evaluations, which are not under their supervision/examination. 43. On writing comments/suggestions, faculty members should be able to view the comments/suggestions history for every artifact of the projects under their supervision/examination. 7 docsity.com PRIMARY KEY=STUID, FOREIGN KEY=PROJTITLE Referencing PROJECT FACULTY (FACID, FACFNAME, FACLNAME, EMAIL, PASS, RANK) PRIMARY KEY=FACID PROJECT (PROJTITLE, PROJDESC, PROJTYPE) PRIMARY KEY=PROJTITLE ARTIFACT (ARTID, ARTTITLE, BASELINE, VERSION, CONTENTS, DATE/TIME OF SUBMISSION, PROJTITLE, STUID) PRIMARY KEY=ARTID, FOREIGN KEY=PROJTITLE Referencing PROJECT and STUID Referencing STUDENT EVALUATION (FACID, STUID, SCHED, EVAID) PRIMARY KEY=FACID, STUID, SCHED FOREIGN KEY=FACID Referencing FACULTY , STUID Referencing STUDENT and EVAID Referencing DETEVALUATION DETEVALUATION (EVAID) PRIMARY KEY= EVAID DET-EVA-6TH-SEM(EVAID, EVAREMARKS, TECH-CON-SUP, LISTSUR, SRS, PRE, Q/A) PRIMARY KEY=EVAID FOREIGN KEY = EVAID REFERENCES DETEVALUATION DET-EVA-7TH-SEM(EVAID EVAREMARKS, , TECH-CON-SUP, LISTSUR, PRE, Q/A, DES/RES, PPR ) PRIMARY KEY=EVAID FOREIGN KEY = EVAIDREFERENCES DETEVALUATION DET-EVA-8TH-SEM(EVAID EVAREMARKS, , TECH-CON-SUP, PRE, Q/A, PRW, IMP-SOF-RES, STRC) PRIMARY KEY=EVAID FOREIGN KEY = EVAIDREFERENCES DETEVALUATION REVISION (ARTID, FACID, DATE/TIME OF REVISION, REMARKS, DETREMARKS) PRIMARY KEY=ARTID, FACID, FOREIGN KEY=ATRID Referencing ARTIFACT and FACID Referencing FACULTY PROJ-FACULTY (PROJTITLE, FACID, ROLE) PRIMARY KEY=PROJTITLE, FACID, FOREIGN KEY=PROJTITLE Referencing PROJECT and FACID Referencing FACULTY 10 docsity.com 3.3 Normalized Relational Schema Normalization involves techniques of identifying and removing flaws in the Logical Database Design, which result in redundancy and several insert, update and delete anomalies. Normalization involves several normal forms [7]. Normalization has been done upto Boyce-Codd Normal Form. After applying Normalization to the previously mentioned Relational Schema, Normalized Rational Schema is shown below. The Student Relation which is : STUDENT (STUID, STUFNAME, STULNAME, PASS, EMAIL, ADDRESS, REGNO, PROJTITLE) PRIMARY KEY=STUID, FOREIGN KEY=PROJTITLE Referencing PROJECT is in 2nd Normal form as nonkey attributes are fully functionally dependent on the key, however it is not in the 3rd normal form as there were attributes which is transitively dependent on the key attribute because the nonkey attribute REGNO was also determining them. In order to remove this, Student Relation was broken into: STUDENT (STUID, STUFNAME, STULNAME, PASS, EMAIL) REGISTRATION (REGNO, ADDRESS, PHONE) But this is not a lossless projection, so it was reverted back and normalized schema is as follows: The rest of the schema is normalized up to BCNF. STUDENT (STUID, STUFNAME, STULNAME, PASS, EMAIL, ADDRESS, REGNO, PROJTITLE) PRIMARY KEY=STUID, FOREIGN KEY=PROJTITLE Referencing PROJECT STUPHONE (STUID, PHONE) FOREIGN KEY=STUID Referencing STUDENT FACULTY (FACID, FACFNAME, FACLNAME, EMAIL, PASS, RANK) PRIMARY KEY=FACID PROJECT (PROJTITLE, PROJDESC, PROJTYPE) PRIMARY KEY=PROJTITLE ARTIFACT (ARTID, ARTTITLE, BASELINE, VERSION, CONTENTS, DATE/TIME OF SUBMISSION, PROJTITLE, STUID) PRIMARY KEY=ARTID, FOREIGN KEY=PROJTITLE Referencing PROJECT and STUID Referencing STUDENT EVALUATION (FACID, STUID, SCHED, EVAID) PRIMARY KEY=FACID, STUID, SCHED FOREIGN KEY=FACID Referencing FACULTY , STUID Referencing STUDENT and EVAID Referencing DETEVALUATION 11 docsity.com DETEVALUATION (EVAID) PRIMARY KEY= EVAID DET-EVA-6TH-SEM(EVAID, EVAREMARKS, TECH-CON-SUP, LISTSUR, SRS, PRE, Q/A) PRIMARY KEY=EVAID FOREIGN KEY = EVAID REFERENCES DETEVALUATION DET-EVA-7TH-SEM(EVAID EVAREMARKS, , TECH-CON-SUP, LISTSUR, PRE, Q/A, DES/RES, PPR ) PRIMARY KEY=EVAID FOREIGN KEY = EVAIDREFERENCES DETEVALUATION DET-EVA-8TH-SEM(EVAID EVAREMARKS, , TECH-CON-SUP, PRE, Q/A, PRW, IMP-SOF-RES, STRC) PRIMARY KEY=EVAID FOREIGN KEY = EVAIDREFERENCES DETEVALUATION REVISION (ARTID, FACID, DATE/TIME OF REVISION, REMARKS, DETREMARKS) PRIMARY KEY=ARTID, FACID, FOREIGN KEY=ATRID Referencing ARTIFACT and FACID Referencing FACULTY PROJ-FACULTY (PROJTITLE, FACID, ROLE) PRIMARY KEY=PROJTITLE, FACID, FOREIGN KEY=PROJTITLE Referencing PROJECT and FACID Referencing FACULTY 12 docsity.com IMP-SUF-RES Tiny integer with values in range of 0-40 STRC Tiny integer with values in range of 0-10 Table 4. 1 Domain of Attributes 4.2 SQL Scripts In this section the SQL Scripts for creating the database as well as the tables is given. Create Database.sql CREATE DATABASE VPCDB ON PRIMARY ( NAME=VPCDB_dat, FILENAME='F:\Microsoft SQL Server\Data Files\VPCDB\VPCDB_dat.mdf', SIZE=100, MAXSIZE=1024, FILEGROWTH=15% ) LOG ON ( NAME=VPCDB_log, FILENAME='F:\Microsoft SQL Server\Data Files\VPCDB\AIOUDB_log.ldf', SIZE=10, MAXSIZE=100, FILEGROWTH=10% ) Create STUDENT Table.sql CREATE TABLE STUDENT ( STUID CHAR(5) NOT NULL, STUFNAME VARCHAR(20) NOT NULL, STULNAME VARCHAR(20) NOT NULL, PASS VARCHAR(16), EMAIL VARCHAR(20), REGNO CHAR(15), STADDRESS VARCHAR(70), CITYADDRESS VARCHAR(20), COUNTRYADDRESS VARCHAR(20), PROJTITLE VARCHAR(50) NOT NULL, PRIMARY KEY(STUID), FOREIGN KEY(PROJTITLE) REFERENCES PROJECT ) Create STUPHONE Table.sql 15 docsity.com CREATE TABLE STUPHONE ( STUID CHAR(5) NOT NULL, PHONECODE VARCHAR(5), PHONE VARCHAR(7), PRIMARY KEY(STUID) ) Create Faculty Table.sql CREATE TABLE FACULTY ( FACID CHAR(6) NOT NULL, FACFNAME VARCHAR(20) NOT NULL, FACFLAME VARCHAR(20) NOT NULL, RANK VARCHAR(3) NOT NULL, PASS VARCHAR(16), EMAIL VARCHAR(20), PRIMARY KEY(FACID) ) Create PROJECT Table.sql CREATE TABLE PROJECT ( PROJTITLE VARCHAR(50) NOT NULL, PROJDESC TEXT NOT NULL, PROJTYPE VARCHAR(3) NOT NULL, PRIMARY KEY(PROJTITLE) ) Create ARTIFACT Table.sql CREATE TABLE ARTIFACT ( ART_ID CHAR(7) NOT NULL, ARTTITLE VARCHAR(20) NOT NULL, PROJTITLE VARCHAR(50) NOT NULL, STUID CHAR(5) NOT NULL, SUBMISSION_TIME SMALLDATETIME NOT NULL, VERSION CHAR(11) NOT NULL, BASELINE BIT NOT NULL, CONTENTS BINARY NOT NULL, 16 docsity.com PRIMARY KEY(ART_ID), FOREIGN KEY(PROJTITLE) REFERENCES PROJECT, FOREIGN KEY(STUID) REFERENCES STUDENT ) Create EVALUATION Table.sql CREATE TABLE EVALUATION ( FACID CHAR(6) NOT NULL, STUID CHAR(5) NOT NULL, SCHED CHAR(19) NOT NULL, EVAID CHAR(7) NOT NULL, PRIMARY KEY(FACID,STUID,SCHED), FOREIGN KEY(STUID) REFERENCES STUDENT, FOREIGN KEY(FACID) REFERENCES FACULTY, FOREIGN KEY(EVAID) REFERENCES DETEVALUATION ) Create DETEVALUATION Table.sql CREATE TABLE DETEVALUATION ( EVAID CHAR(7) NOT NULL, PRIMARY KEY(EVAID) ) Create DET-EVA-6TH-SEM Table.sql CREATE TABLE DET_EVA_6TH_SEM ( EVAREMARKS TEXT, EVAID CHAR(7) NOT NULL, TECH_CON_SUP TINYINT NOT NULL, LISTSUR TINYINT NOT NULL, SRS TINYINT NOT NULL, PRE TINYINT NOT NULL, Q_A TINYINT NOT NULL, PRIMARY KEY(EVAID), FOREIGN KEY(EVAID) REFERENCES DETEVALUATION ) Create DET-EVA-7TH-SEM Table.sql CREATE TABLE DET_EVA_7TH_SEM ( EVAREMARKS TEXT, EVAID CHAR(7) NOT NULL, 17 docsity.com The following Diagram depicts the physical relationship among the tables representing the Evaluation Relationship. Figure 4. 2 Database Diagram showing Evaluation Relationship The following Diagram depicts the physical relationship among the tables representing the Execution Relationship. Figure 4. 3 Database Diagram showing Execution Relationship 20 docsity.com The following Diagram depicts the physical relationship among the tables representing the Proj-Artifact Relationship. Figure 4. 4 Database Diagram showing Proj-Artifact Relationship The following Diagram depicts the physical relationship among the tables representing the Proj-Faculty Relationship. Figure 4. 5 Database Diagram showing Proj-Faculty Relationship 21 docsity.com The following Diagram depicts the physical relationship among the tables representing the Revision Relationship. Figure 4. 6 Database Diagram showing Revision Relationship The following Diagram depicts the physical relationship among the tables representing the Submission Relationship. Figure 4. 7 Database Diagram showingSubmission Relationship 22 docsity.com