Course:CICS515/Database

Installing the Database and Configuring SQL

In these steps, replace 8230 with a random number between 8001 and 8999. Also be sure to replace the pathname:

   /ubc/icics/mss/cics515/2011/ 

with the pathname of your home directory. You can get the home path by using the 'pwd' command, by executing:

   cd ~
   pwd

Now to set up sql environment, make sure you have directory called mysql in your top home directory. If you already do, you need to clear out some of the old stuff, or risk errors with the install script. I have tested with my old 505 junk and had issues so I wiped it clean and had no issues. Create the directory if it doesn't exist:

   mkdir mysql 

Change directories, and be sure to stay in this directory for a while:

   cd mysql

Run the script:

   mysql_install_db

Open the EMACS editor, or your favorite editor such as Nano, or Vi. The important part hew is you make a file called: ".my.cnf"

   emacs -nw ~/.my.cnf

Enter the following into your text editor, be sure to use your own port and home directory as described above.

   [mysql] 
   port=8230
   socket=/ubc/icics/mss/cics515/2011/mysql/mysql.sock
   [mysqld]
   port=8230
   socket=/ubc/icics/mss/cics515/2011/mysql/mysql.sock
   datadir=/ubc/icics/mss/cics515/2011/mysql
   [client]
   port=8230
   socket=/ubc/icics/mss/cics515/2011/mysql/mysql.sock

Close your editor, and run the mysql demon:

   mysqld &

Run mysqladmin to create a user:

   mysqladmin -u root password 'password'

Loginto mysql as that user:

   mysql -uroot -ppassword

At this point the mysql prompt will appear and you can input the database described below.

   Happy scripting!

To test this works, insert an author

   INSERT INTO `discussion`.`author`(`authorName` ,`email` ,`password` ,`rating`)
   VALUES('Ben', 'bbertka@mss.icics.ubc.ca', 'password', '0');

Show the authors, notice the auto increment if the authorID works too:

   select * from author;

Now, create a test PHP script to show the data via web interface:

   cd ~
   mkdir public_html
   chmod 755 public_html
   cd public html
   touch discussionTest.php
   chmod 755 discussionTest.php
   nano discussionTest.php

Database Latest Deliverable

Discussion Forum Database Schema

create database Discussion; 

use database Discussion;

CREATE TABLE `Discussion`.`Author` ( 
`authorID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`authorName` VARCHAR( 25 ) NOT NULL ,
`email` VARCHAR( 25 ) NOT NULL ,
`password` VARCHAR( 50 ) NOT NULL ,
`rating` INT NOT NULL DEFAULT '0',
UNIQUE ( `email` ));

CREATE TABLE `Discussion`.`topic` (
`topicID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 50 ) NOT NULL ,
`content` TEXT NOT NULL ,
`rating` INT NOT NULL DEFAULT '0',
`authorID` INT NOT NULL ,
`date_created` DATE NOT NULL ,
`date_edited` DATE NOT NULL,
 FOREIGN KEY (authorID) REFERENCES author(authorID) );

CREATE TABLE `Discussion`.`post`(
`PostID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`postReplyID` INT,
`content` VARCHAR( 1000 ) NOT NULL ,
`rating` INT NOT NULL ,
`authorID` INT NOT NULL ,
`topicID` INT NOT NULL ,
`date_posted` DATE NOT NULL,
FOREIGN KEY (authorID) REFERENCES author(authorID),
FOREIGN KEY (topicID) REFERENCES topic(topicID) ); 

CREATE TABLE `Discussion`.`keywords`(
`keyword` VARCHAR( 25 ) NOT NULL , 
`topicID` INT NOT NULL ,
PRIMARY KEY ( `keyword` , `topicID` ),
FOREIGN KEY (topicID) REFERENCES topic(topicID) );

Ideas

ER diagram

New changes in the database schema explained for Post table

Misc

KEYWORD table, code level business logic implementation suggestions by the team;

follow the below given links to learn

Keyword Search
Category keyword search