Business Logic Design and Relational Database Design for a Project, Slides of Applications of Computer Sciences

Download Business Logic Design and Relational Database Design for a Project and more Slides Applications of Computer Sciences in PDF only on Docsity! 7/7/2012 1 Layout  Introduction  Project Schedule  Summary of previous work done  Business Logic Design  E-R Model to Relational Model  Normalization  Physical Database Design  Future Work for 8th semester docsity.com 7/7/2012 2 Introduction VPC, an Internet-based application intended to manage and automate the coordination process of Seminar Project at PIEAS, would provide the Faculty Members and Students, an easy to use system that automatically coordinates their projects and helps them interact in a more efficient and reliable manner. Figure VPC Architecture docsity.com 7/7/2012 5 Pre-mature Architecture (previous work) Pre-mature Architectural Design on basis of Proof of concept prototype docsity.com 7/7/2012 6 E-R Model (Previous Work) Fig: Entity Relationship Model for VPC Database docsity.com 7/7/2012 7 Business Logic Design •Business Logic design incorporates the business logic into the system •Designed prior to the development of Physical Database Design, Physical Database Implementation and Application Software Design and Implementation. •More than 50 business rules were identified. •Some of the business rules are presented here •Complete list present in Project Progress Report docsity.com 7/7/2012 10 Business Logic Design Marks Distribution for evaluation in 6th semester should be: Feature No. 1 2 3 4 5 Feature to Evaluate Technical consultati on with supervisor Literature Survey Developm ent of SRS Document Presentati on and Comprehe nsion of Project Q/A Session Max. Marks 20 15 40 15 10 docsity.com 7/7/2012 11 Business Logic Design Marks Distribution for evaluation in 7th semester should be: Feature No. 1 2 3 4 5 6 Feature to Evaluate Technical consultatio n with supervisor Literature Survey Developme nt of Design Description Document OR Research Findings Description Project Progress Report Presentati on and Comprehe nsion of Project Q/A Sess ion Max. Marks 10 10 40 10 15 15 docsity.com 7/7/2012 12 Business Logic Design Marks Distribution for evaluation in 8th semester should be: Feature No. 1 2 3 4 5 6 Feature to Evaluate Technical consultatio n with supervisor Implement ation of Software OR Research Work Software testing or research conclusions Project Progress Work Presentati on and Comprehe nsion of Project Q/A Sessi on Max. Marks 10 10 40 15 10 15 docsity.com 7/7/2012 15 Business Logic Design  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. docsity.com 7/7/2012 16 E-R Model to Relational Model  Relational Database Model [1] makes understanding of the database easy on an intuitive level, in that it allows separation of Logical and Physical levels, so that logical design can be performed without considering the storage structures.  The entity sets represented by rectangles become relations represented by tables.  The attributes represented by ovals become attributes of the relation, or column heading of the table. docsity.com 7/7/2012 17 Relational Schema STUDENT (STUID, STUFNAME, STULNAME, PASS, EMAIL, REGNO, ADDRESS, PHONE, PROJTITLE) 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 Initial Relational schema for VPC as obtained by mapping E-R Model to Relational Model docsity.com 7/7/2012 20 Normalization  Normalization involves techniques of identifying and removing flaws in the Logical Database Design, which result in redundancy and several insert, update and delete anomalies [2] .  Involves several normal forms.  Normalization has been done up to Boyce-Codd Normal Form. docsity.com 7/7/2012 21 Normalization  STUDENT Table Broken into STUDENT and STUPHONE tables so that student’s phone number is in a separate table and Address attribute broken into Street Address, City and Country in order to ensure First Normal Form.  STUDENT table is not in the 3rd normal form as there are attributes which are transitively dependent on the key attribute because the non key attribute REGNO is also determining them. STUDENT (STUID, STUFNAME, STULNAME, PASS, EMAIL, REGNO, ADDRESS, PHONE, PROJTITLE) PRIMARY KEY=STUID, FOREIGN KEY=PROJTITLE Referencing PROJECT docsity.com 7/7/2012 22 Normalization  Student Relation was broken into: STUDENT (STUID, STUFNAME, STULNAME, PASS, EMAIL) REGISTRATION (REGNO, ADDRESS, PHONE)  But this is not a loss less projection, so it is reverted back  Rest of the schema already in Boyce–Codd Normal Form.  Most of schema already Normalized because a lot of time and effort was put on the ER model. docsity.com 7/7/2012 25 Normalized Relational Schema contd... DET-EVA-7TH-SEM(EVAID EVAREMARKS, , TECH-CON-SUP, LISTSUR, PRE, Q/A, DES/RES, PPR ) PRIMARY KEY=EVAID FOREIGN KEY = EVAID Referencing 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 docsity.com 7/7/2012 26 Physical Database Design 2005 [3]  VPC Database created in MS SQL Server 2005 [4].  Use of SQL Script.  Advantage over wizard-driven implementation docsity.com 7/7/2012 27 Database Creation PSUEDOCODE: Create VPC Database With Database File name as VPCDB_dat At Physical location 'F:\Microsoft SQL Server\Data Files\VPCDB\VPCDB_dat.mdf‘ with initial size of 100 MB maximum size of 1 GB at growth rate of 15 % With Database Log Name as VPCDB_log At Physical location 'F:\Microsoft SQL Server\Data Files\VPCDB\VPCDB_dat.mdf‘ with initial size of 10 MB maximum size of 100 MB at growth rate of 10 % docsity.com 7/7/2012 30 Tables Creation - Project Tables Created with the help of SQL Script CREATE TABLE PROJECT ( PROJTITLE VARCHAR(50) NOT NULL, PROJDESC TEXT NOT NULL, PROJTYPE VARCHAR(3) NOT NULL, PRIMARY KEY(PROJTITLE) ) docsity.com 7/7/2012 31 Tables Creation Student 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 ) docsity.com 7/7/2012 32 Tables Creation - STUPHONE CREATE TABLE STUPHONE ( STUID CHAR(5) NOT NULL, PHONECODE VARCHAR(5), PHONE VARCHAR(7), PRIMARY KEY(STUID) ) docsity.com 7/7/2012 35 Tables Creation - Evaluation 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 ) docsity.com 7/7/2012 36 Tables Creation - DETEVALUATION CREATE TABLE DETEVALUATION ( EVAID CHAR(7) NOT NULL, PRIMARY KEY(EVAID) ) docsity.com 7/7/2012 37 Tables Creation - DET_EVA_6TH_SEM 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 ) docsity.com 7/7/2012 40 Tables Creation - Revision CREATE TABLE REVISION ( ART_ID CHAR(7) NOT NULL, FACID CHAR(6) NOT NULL, REV_TIME SMALLDATETIME NOT NULL, REMARKS VARCHAR(14) NOT NULL, DETREMARKS TEXT NOT NULL, PRIMARY KEY(ART_ID, FACID, REV_TIME), FOREIGN KEY (ART_ID) REFERENCES ARTIFACT, FOREIGN KEY (FACID) REFERENCES FACULTY ) docsity.com 7/7/2012 41 Tables Creation – PROJ_FACULTY CREATE TABLE PROJ_FACULTY ( PROJTITLE VARCHAR(50) NOT NULL, FACID CHAR(6) NOT NULL, ROLE VARCHAR(13) NOT NULL, PRIMARY KEY(PROJTITLE, FACID), FOREIGN KEY(FACID) REFERENCES FACULTY, FOREIGN KEY(PROJTITLE) REFERENCES PROJECT ) docsity.com 7/7/2012 42 Physical Database Implementation – Database Diagrams Physical Implementation of Execution Relationship docsity.com 7/7/2012 45 Physical Database Implementation – Database Diagrams Physical Implementation of Revision Relationship docsity.com 7/7/2012 46 Physical Database Implementation – Database Diagrams Physical Implementation of Submission Relationship docsity.com 7/7/2012 47 Physical Database Implementation – Database Diagrams Physical Implementation of Evaluation Relationship docsity.com 7/7/2012 50 Future Work for 8th semester  On the basis of this work, following tasks would done by the end of 8th Semester.  Complete Architectural Design  User Interface Design  User Interface Implementation  Alpha Testing  Deployment docsity.com 7/7/2012 51 References [1] Relational Databases. [Online] http://computing-dictionary.thefreedictionary.com/relational database [2] Catherine Ricardo, Database Systems Principles, Design and Implementation, pp. 222, Iona College, New Rochelle, New York: Maxwell Macmillan. [3] Catherine Ricardo, Database Systems Principles, Design and Implementation, pp. 263, Iona College, New Rochelle, New York: Maxwell Macmillan. [4] Microsoft SQL Server: What is SQL Server 2005? [Online] http://www.microsoft.com/sql/prodinfo/overview/what-is-sql-server.mspx docsity.com ee Thank You ! Questions ? ® docsity.com