Physical Database Design-Computer Sciences Applications-Project Report, Study Guides, Projects, Research of Applications of Computer Sciences

Download Physical Database Design-Computer Sciences Applications-Project Report and more Study Guides, Projects, Research Applications of Computer Sciences in PDF only on Docsity! Table of Contents 1 INTRODUCTION 1 1.1 Purpose 1 2 DESIGNING FIELDS 2 2.1 Choosing database type 2 2.2 Choosing data type 2 2.2.1 User 2 2.2.2 Discipline 3 2.2.3 University 3 2.2.4 Community 4 2.2.5 Community_related_to_university 4 2.2.6 Community_related_to_discipline 4 2.2.7 Forum 4 2.2.8 Poll 4 2.2.9 Donor 4 2.2.10 Event 5 2.2.11 News 5 2.2.12 Articles 5 2.2.13 Industry 5 2.2.14 Forum_reply 5 2.2.15 Vote 5 2.2.16 Community_membership 5 2.2.17 Event_registration 6 2.2.18 University_enrollment 6 2.2.19 Course 6 2.2.20 Skill 6 2.2.21 Passion 6 2.2.22 Discipline_of_interest 6 2.2.23 User_add_list 6 2.2.24 User_discipline_view 6 2.2.25 Discipline_article 6 2.2.26 Discipline_news 7 2.3 Default values 7 2.4 Range control 7 2.5 Null value control 7 2.6 Choosing the table type 7 3 SQL CODE 8 4 PHP CODE 18 5 DATABASE DIAGRAMS 19 5.1 Database schema 19 docsity.com ii ii 5.2 Database server screenshot 20 References 20 Table of figures Figure 1 Database Schema ...................................................................................................... 19 Figure 2 Database server ......................................................................................................... 20 docsity.com 3 3  Personal Information o Name  First_name, VARCHAR(15)  Middle_initial, VARCHAR(15)  Last_name, VARCHAR(15) o Address  Country, VARCHAR(15)  Province, VARCHAR(12)  City, VARCHAR(20)  Address_line, VARCHAR(30) o Date_of_birth, DATE o Webpage, VARCHAR(20) o Passions, VARCHAR(50) o Skills, VARCHAR(50)  Educational Information o School, VARCHAR(40)  Year_school_ passed, DATE o Collage, VARCHAR(40)  Year_college_ passed, DATE o University, VARCHAR(40)  Year _university_passed, DATE o Current_education_status, VARCHAR(30) o Disciplines_of_interest, VARCHAR(50) o Future plan  University, VARCHAR(40)  Degree, VARCHAR(10)  Career interest, VARCHAR( 20) 2.2.2 Discipline  Discipline_ID, SMALLINT(m)  Discipline_name, VARCHAR( 30)  Discipline_category, VARCHAR(20)  Discipline_description, VARCHAR(100)  User_ID, SMALLINT(m)  Industry_ID, SMALLINT(m) 2.2.3 University  University_ID, TINYINT(m)  University_name, VARCHAR(40)  Summary_of_information, VARCHAR(200)  University_weblink, VARCHAR( 25) docsity.com 4 4 2.2.4 Community  Community_ID, SMALLINT(m)  Community_name, VARCHAR(30)  Community_purpose, VARCHAR(50)  Community_moderator (Username), VARCHAR(20),  Community_type  User_ID, SMALLINT(m) 2.2.5 Community_related_to_university  Community_ID, SMALLINT(m)  Related_university, VARCHAR(100) 2.2.6 Community_related_to_discipline  Community_ID, SMALLINT(m)  Related_discipline, VARCHAR(100) 2.2.7 Forum  Forum_ID, SMALLINT(m)  Forum_name, VARCHAR(30)  Forum_moderator, VARCHAR(20)  Forum_description, VARCHAR(50)  Community_ID, SMALLINT(m) 2.2.8 Poll  Poll_ID, SMALLINT(m)  Poll_name, VARCHAR(40)  Poll_creator, VARCHAR(20)  Poll_statistics, VARCHAR(40)  Community_ID, SMALLINT(m) 2.2.9 Donor  Donor_ID, TINYINT(m)  Donor_name, VARCHAR(40)  Donor_weblink, VARCHAR(25)  Donor_contactnumber, VARCHAR(20) docsity.com 5 5 2.2.10 Event  Event_ID, SMALLINT(m)  Event_name, VARCHAR(30)  Event_weblink, VARCHAR(25)  Event_date, DATE  Event_location, VARCHAR( 40)  User_ID, SMALLINT(m) 2.2.11 News  News_ID, SMALLINT(m)  News_headline, VARCHAR(40)  News_content, VARCHAR(500)  News_source, VARCHAR(30) 2.2.12 Articles  Article_ID, SMALLINT(m)  Article_name, VARCHAR( 40)  Article_content, VARCHAR(2000)  Article_source, VARCHAR(30) 2.2.13 Industry  Industry_ID, SMALLINT(m)  Industry_name, VARCHAR(40)  Industry_weblink, VARCHAR(25) 2.2.14 Forum_reply  Reply_text, VARCHAR(100)  User_ID, SMALLINT(m)  Forum_ID, SMALLINT(m) 2.2.15 Vote  Selected_optotion, BIT(20)  Vote_comment, VARCHAR(100)  User_ID, SMALLINT(m)  Poll_ID, SMALLINT(m) 2.2.16 Community_membership  Membership_date, DATE  User_ID, SMALLINT(m)  Communtiy_ID, SMALLINT(m) docsity.com 8 8 Foreign Key Constraints: When you define relations between your tables, the InnoDB table Driver automatically ensures that the referential integrity of the table is preserved after DELETE commands. Thus it is impossible, for example, for a record in table A to refer to a no longer existing table B. Crash Recovery: After a crash, InnoDB tables are automatically and very quickly returned to a consistent state (provided that the file system of the computer was not damaged). 3 SQL code Given below is the SQL code for the creation of database “WebSIComm” and all the tables of the database. I wrote this code using phpMyAdmin in XAMPP. ---------------------------------------------------------------------------------------------------------------- CREATE DATABASE `WebSIComm` ; -- Database: 'websicomm' -- -- -------------------------------------------------------- -- -- Table structure for table 'article' -- CREATE TABLE article ( Article_ID smallint(6) NOT NULL auto_increment, Article_name varchar(40) NOT NULL, Article_content varchar(2000) NOT NULL, Article_source varchar(30) default NULL, PRIMARY KEY (Article_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'community' -- CREATE TABLE community ( Community_ID smallint(6) NOT NULL auto_increment, Community_name varchar(30) NOT NULL, Community_purpose varchar(50) NOT NULL, Community_moderator varchar(20) NOT NULL, Community_type varchar(30) NOT NULL, User_ID smallint(6) NOT NULL, PRIMARY KEY (Community_ID), UNIQUE KEY Community_name (Community_name) docsity.com 9 9 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- Table structure for table 'communityrelatedtodiscipline' -- CREATE TABLE communityrelatedtodiscipline ( Community_ID smallint(6) NOT NULL auto_increment, Related_discipline varchar(100) default NULL, PRIMARY KEY (Community_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'communityrelatedtouniversity' -- CREATE TABLE communityrelatedtouniversity ( Community_ID smallint(6) NOT NULL auto_increment, Related_university varchar(200) default NULL, PRIMARY KEY (Community_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'community_membership' -- CREATE TABLE community_membership ( Membership_date date NOT NULL, User_ID smallint(6) NOT NULL, Community_ID smallint(6) NOT NULL, PRIMARY KEY (User_ID,Community_ID), KEY Community_ID (Community_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'course' -- CREATE TABLE course ( Course_title varchar(30) NOT NULL, Discipline_ID tinyint(4) NOT NULL, University_ID tinyint(4) NOT NULL, PRIMARY KEY (Course_title,Discipline_ID,University_ID), KEY University_ID (University_ID), KEY Discipline_ID (Discipline_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- docsity.com 10 10 -- -------------------------------------------------------- -- -- Table structure for table 'discipline' -- CREATE TABLE discipline ( Discipline_ID tinyint(4) NOT NULL auto_increment, Discipline_name varchar(30) NOT NULL, Discipline_category varchar(20) NOT NULL, Discipline_description varchar(200) default NULL, User_ID smallint(6) NOT NULL, Industry_ID smallint(6) NOT NULL, PRIMARY KEY (Discipline_ID), UNIQUE KEY Discipline_name (Discipline_name), UNIQUE KEY Industry_ID (Industry_ID), UNIQUE KEY User_ID (User_ID), UNIQUE KEY User_ID_2 (User_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Table structure for table 'discipline_articles' -- CREATE TABLE discipline_articles ( Discipline_ID tinyint(4) NOT NULL, Article_ID smallint(6) NOT NULL, PRIMARY KEY (Discipline_ID,Article_ID), KEY Article_ID (Article_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'discipline_news' -- CREATE TABLE discipline_news ( News_ID smallint(6) NOT NULL, Discipline_ID tinyint(4) NOT NULL, PRIMARY KEY (News_ID,Discipline_ID), KEY Discipline_ID (Discipline_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'discipline_of_interest' -- CREATE TABLE discipline_of_interest ( Disciplines_of_interest varchar(100) NOT NULL default '', User_ID smallint(6) NOT NULL, PRIMARY KEY (Disciplines_of_interest,User_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- docsity.com 13 13 Poll_creator varchar(20) NOT NULL, Poll_statistics varchar(100) default NULL, Community_ID smallint(6) NOT NULL, PRIMARY KEY (Poll_ID), UNIQUE KEY Poll_question (Poll_question), UNIQUE KEY Community_ID (Community_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Table structure for table 'skill' -- CREATE TABLE skill ( Skill_name varchar(50) NOT NULL default '', User_ID smallint(6) NOT NULL, PRIMARY KEY (Skill_name,User_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'university' -- CREATE TABLE university ( University_ID tinyint(4) NOT NULL auto_increment, University_name varchar(70) NOT NULL, University_weblink varchar(25) default NULL, Summary_of_information varchar(300) default NULL, PRIMARY KEY (University_ID), UNIQUE KEY University_name (University_name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'university_enrollment' -- CREATE TABLE university_enrollment ( Enrollment_date date NOT NULL default '0000-00-00', User_ID smallint(6) NOT NULL, University_ID tinyint(4) NOT NULL, PRIMARY KEY (Enrollment_date,User_ID,University_ID), KEY User_ID (User_ID), KEY University_ID (University_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'user' -- CREATE TABLE `user` ( User_ID smallint(6) NOT NULL, Username varchar(15) NOT NULL, Email varchar(30) NOT NULL, `Password` varchar(12) NOT NULL, docsity.com 14 14 Profile_picture blob, First_name varchar(15) NOT NULL, Middle_initial varchar(15) default NULL, Last_name varchar(15) NOT NULL, Country varchar(15) NOT NULL, Province varchar(12) default NULL, City varchar(20) NOT NULL, Address_line varchar(40) NOT NULL, Date_of_birth date NOT NULL, Webpage varchar(20) default NULL, School varchar(40) NOT NULL, Year_school_passed date default NULL, College varchar(40) default NULL, year_college_passed date default NULL, University_studied varchar(40) default NULL, Year_university_passed date default NULL, Current_educational_status varchar(30) NOT NULL, Desired_university varchar(40) default NULL, Desired_degree varchar(15) default NULL, Donor_ID smallint(6) NOT NULL, PRIMARY KEY (User_ID), UNIQUE KEY Email (Email) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'user_add_list' -- CREATE TABLE user_add_list ( User_ID smallint(6) NOT NULL, User_added smallint(6) NOT NULL default '0', PRIMARY KEY (User_ID,User_added), KEY User_added (User_added) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Table structure for table 'user_disciple_view' -- CREATE TABLE user_disciple_view ( User_ID smallint(6) NOT NULL, Discipline_ID tinyint(4) NOT NULL, PRIMARY KEY (User_ID,Discipline_ID), KEY Discipline_ID (Discipline_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- -------------------------------------------------------- -- -- Table structure for table 'vote' -- CREATE TABLE vote ( docsity.com 15 15 Selected_option bit(20) NOT NULL, Vote_comment varchar(100) default NULL, User_ID smallint(6) NOT NULL, Poll_ID smallint(6) NOT NULL, PRIMARY KEY (User_ID,Poll_ID), KEY Poll_ID (Poll_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- --- -------------------------------------------- -- Constraints for table `communityrelatedtodiscipline` -- ALTER TABLE `communityrelatedtodiscipline` ADD CONSTRAINT communityrelatedtodiscipline_fk_1 FOREIGN KEY (Community_ID) REFERENCES community (Community_ID) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `communityrelatedtouniversity` -- ALTER TABLE `communityrelatedtouniversity` ADD CONSTRAINT communityrelatedtouniversity_fk_1 FOREIGN KEY (Community_ID) REFERENCES community (Community_ID) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `community_membership` -- ALTER TABLE `community_membership` ADD CONSTRAINT community_membership_fk_2 FOREIGN KEY (Community_ID) REFERENCES community (Community_ID) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT community_membership_fk_1 FOREIGN KEY (User_ID) REFERENCES `user` (User_ID) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `course` -- ALTER TABLE `course` ADD CONSTRAINT course_fk_5 FOREIGN KEY (Discipline_ID) REFERENCES discipline (Discipline_ID) ON DELETE NO ACTION ON UPDATE CASCADE, ADD CONSTRAINT course_fk_4 FOREIGN KEY (University_ID) REFERENCES university (University_ID) ON DELETE NO ACTION ON UPDATE CASCADE; -- -- Constraints for table `discipline` -- ALTER TABLE `discipline` ADD CONSTRAINT discipline_fk_2 FOREIGN KEY (User_ID) REFERENCES `user` (User_ID) ON DELETE NO ACTION ON UPDATE CASCADE, docsity.com 18 18 4 PHP code The SQL queries can also be written using PHP. I wrote the following equivalent PHP code for the User table. This code is also written the Query browser of phpMyAdmin. $sql = 'CREATE TABLE `websicomm`.`User` ( `Username` VARCHAR(15) NOT NULL, `Email` VARCHAR(30) NOT NULL, `Password` VARCHAR(12) NOT NULL, `Profile_picture` BLOB NULL, `First_name` VARCHAR(15) NOT NULL, `Middle_initial` VARCHAR(15) NULL, `Last_name` VARCHAR(15) NOT NULL, `Country` VARCHAR(15) NOT NULL, `Province` VARCHAR(12) NULL, `City` VARCHAR(20) NOT NULL, `Address_line` VARCHAR(40) NOT NULL, `Date_of_birth` DATE NOT NULL, `Webpage` VARCHAR(20) NULL, `School` VARCHAR(40) NOT NULL, `Year_school_passed` DATE NULL, `College` VARCHAR(40) NULL, `year_college_passed` DATE NULL, `University_studied` VARCHAR(40) NULL, `Year_university_passed` DATE NULL, `Current_educational_status` VARCHAR(30) NOT NULL, `Desired_university` VARCHAR(40) NULL, `Desired_degree` VARCHAR(15) NULL, PRIMARY KEY (`Username`), UNIQUE (`Email`)) ENGINE = InnoDB'; docsity.com 19 19 5 Database diagrams 5.1 Database schema Figure 1 Database Schema docsity.com 20 20 5.2 Database server screenshot Figure 2 Database server References [1] Modern Database Management by Jeffrey A. Hofer. Seventh edition. [2] A definitive guide to MySQL 5 by Michael Kofler. Third edition. docsity.com