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
- INTRODUCTION
- 1.1 Purpose
- 2.1 Choosing database type
- 2.2 Choosing data type - 2.2.1 User
- Discipline 3 2.2.
- Community 4 2.2.
- 2.2.5 Community_related_to_university
- 2.2.6 Community_related_to_discipline
- 2.2.7 Forum
- 2.2.8 Poll
- 2.2.9 Donor
- Event 5 2.2.
- 2.2.11 News
- 2.2.12 Articles
- 2.2.13 Industry
- 2.2.14 Forum_reply
- 2.2.15 Vote
- 2.2.16 Community_membership
- Event_registration 6 2.2.
- 2.2.18 University_enrollment
- 2.2.19 Course
- 2.2.20 Skill
- 2.2.21 Passion
- 2.2.22 Discipline_of_interest
- 2.2.23 User_add_list
- 2.2.24 User_discipline_view
- 2.2.25 Discipline_article
- Discipline_news 7 2.2.
- 2.3 Default values
- 2.4 Range control
- 2.5 Null value control
- 2.6 Choosing the table type
- SQL CODE
- PHP CODE
- DATABASE DIAGRAMS
- 5.1 Database schema
ii
ii
Database server screenshot 20 5.
References 20
Table of figures
19 ...................................................................................................... Figure 1 Database Schema 20 ......................................................................................................... Figure 2 Database server
2 Designing fields
There are six major steps in designing of a data field. These steps are listed below with detail.
2.1 Choosing database type
As the database implementation will be using MySQL DBMS, I have to consider which type of character set is to be used for the database. Infact character set can be specified individually for each column. Character set determines which code is used to represent various characters. The most important character set for the implementation in English language is latin1 with sort orders general1, german, Swedish, Danish and Spanish. Among the available character sets in MySQL the latin character sets have the most widespread use. Latin character sets are one byte and contain characters of European languages. Both character set and sort order can be defined by Collation. I have used the collation latin_general_ci which has the character set of latin1, has the general sort order and does not take country specific issues. If need be, different character set can be used for each column.
2.2 Choosing data type
The following is the list and description of the data types used in the database. a) VARCHAR(n) It is a character string with variable length. Maximum limit is n < 65,535. b) SMALLINT(m) It is a 16 bit integer. It takes 2 bytes of space. Has a range of +32768 to -32768. The optional value of m gives the desired width of SELECT results. c) DATE This data type is for storing date values in the form of 2009-01-31. It has range from 1000- 01-01 to 9999-12-31. It takes three bytes. d) TINYINT(m) It is a 8 bit integer. It takes 1 byte of space. It has a range of +127 to -128. e) BLOB It contains binary data with variable length which can be upto 2^16-1 bytes.
The following are the attributes with entities and the data type associated with each attribute.
2.2.1 User
UserID, SMALLINT(15), Primary key. Username, VARCHAR(15) Donor_ID, TINYINT(m) Email, VARCHAR(30) Password, VARCHAR(12) Profile_Picture, BLOB
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)
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)
2.2.17 Event_registration
Registration_number, SMALLINT(m) User_ID, SMALLINT(m) Event_ID, SMALLINT(m)
2.2.18 University_enrollment
Enrollment_date, DATE University_ID, TINYINT(m) User_ID, SMALLINT(m)
2.2.19 Course
Course_title, VARCHAR(30) University_ID, TINYINT(m) Discipline_ID, SMALLINT(m)
2.2.20 Skill
Skill_name, VARCHAR(50) User_ID, SMALLINT(m)
2.2.21 Passion
Passion, VARCHAR(50) User_ID, SMALLINT(m)
2.2.22 Discipline_of_interest
Discipline_of_interest, VARCHAR(50) User_ID, SMALLINT(m)
2.2.23 User_add_list
User_ID, SMALLINT(m) User_added, SMALLINT(m)
2.2.24 User_discipline_view
User_ID, SMALLINT(m) Discipline_ID, TINYINT(m)
2.2.25 Discipline_article
Article_ID, SMALLINT(m) Discipline_ID, SMALLINT(m)
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)
) 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; --
-- Table structure for table 'donor'
CREATE TABLE donor ( Donor_ID smallint(6) NOT NULL auto_increment, Donor_name varchar(40) NOT NULL, Donor_weblink varchar(25) default NULL, Dono_contactnumber varchar(20) default NULL, PRIMARY KEY (Donor_ID), UNIQUE KEY Donor_name (Donor_name) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- -- Table structure for table 'event' -- CREATE TABLE event ( Event_ID smallint(6) NOT NULL auto_increment, Event_name varchar(30) NOT NULL, Event_weblink varchar(25) default NULL, Event_date date NOT NULL, Event_location varchar(50) default NULL, User_ID smallint(6) NOT NULL, PRIMARY KEY (Event_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; --
-- -- Table structure for table 'event_registration' -- CREATE TABLE event_registration ( Registration_number varchar(30) default NULL, User_ID smallint(6) NOT NULL, Event_ID smallint(6) NOT NULL, PRIMARY KEY (User_ID,Event_ID), KEY Event_ID (Event_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; --
-- Table structure for table 'forum'
CREATE TABLE forum ( Forum_ID smallint(6) NOT NULL, Forum_name varchar(30) NOT NULL, Forum_moderator varchar(20) NOT NULL, Forum_description varchar(50) default NULL, Community_ID smallint(6) NOT NULL, PRIMARY KEY (Forum_ID), UNIQUE KEY Forum_name (Forum_name), UNIQUE KEY Community_ID (Community_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; --
-- Table structure for table 'forum_reply'
CREATE TABLE forum_reply ( Reply_text varchar(100) NOT NULL, User_ID smallint(6) NOT NULL, Forum_ID smallint(6) NOT NULL, PRIMARY KEY (Reply_text,User_ID,Forum_ID), KEY User_ID (User_ID), KEY Forum_ID (Forum_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; --
-- -- Table structure for table 'industry' -- CREATE TABLE industry ( Industry_ID smallint(6) NOT NULL auto_increment, Industry_name varchar(40) NOT NULL, Industry_weblink varchar(25) default NULL, PRIMARY KEY (Industry_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Table structure for table 'news'
CREATE TABLE news ( News_ID smallint(6) NOT NULL auto_increment, News_headline varchar(40) NOT NULL, News_content varchar(1000) NOT NULL, News_source varchar(30) NOT NULL, PRIMARY KEY (News_ID), UNIQUE KEY News_headline (News_headline) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- -- Table structure for table 'passion' -- CREATE TABLE passion ( Passion_answer varchar(50) NOT NULL default '', User_ID smallint(6) NOT NULL, PRIMARY KEY (Passion_answer,User_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- -- Table structure for table 'poll' -- CREATE TABLE poll ( Poll_ID smallint(6) NOT NULL auto_increment, Poll_question varchar(100) NOT NULL,
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 (
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,
ALTER TABLE poll ADD CONSTRAINT poll_fk_1 FOREIGN KEY (Community_ID) REFERENCES community (Community_ID) ON DELETE CASCADE ON UPDATE CASCADE;
-- -- Constraints for table university_enrollment -- ALTER TABLE university_enrollment ADD CONSTRAINT university_enrollment_fk_2 FOREIGN KEY (University_ID) REFERENCES university (University_ID) ON DELETE NO ACTION ON UPDATE CASCADE, ADD CONSTRAINT university_enrollment_fk_1 FOREIGN KEY (User_ID) REFERENCES user (User_ID) ON DELETE CASCADE ON UPDATE CASCADE;
-- -- Constraints for table user_add_list -- ALTER TABLE user_add_list ADD CONSTRAINT user_add_list_fk_2 FOREIGN KEY (User_added) REFERENCES user (User_ID) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT user_add_list_fk_1 FOREIGN KEY (User_ID) REFERENCES user (User_ID) ON DELETE CASCADE ON UPDATE CASCADE;
-- -- Constraints for table user_disciple_view -- ALTER TABLE user_disciple_view ADD CONSTRAINT user_disciple_view_fk_2 FOREIGN KEY (Discipline_ID) REFERENCES discipline (Discipline_ID) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT user_disciple_view_fk_1 FOREIGN KEY (User_ID) REFERENCES user (User_ID) ON DELETE CASCADE ON UPDATE CASCADE;
-- -- Constraints for table vote -- ALTER TABLE vote ADD CONSTRAINT vote_fk_2 FOREIGN KEY (Poll_ID) REFERENCES poll (Poll_ID) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT vote_fk_1 FOREIGN KEY (User_ID) REFERENCES user (User_ID) ON DELETE CASCADE ON UPDATE CASCADE;
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';
