teaching machines

Database Script

October 3, 2012 by millerhj. Filed under
Deprecated: Creation of dynamic property WP_Term::$cat_ID is deprecated in /var/www/twodee/blog/wp-includes/category.php on line 378

Deprecated: Creation of dynamic property WP_Term::$category_count is deprecated in /var/www/twodee/blog/wp-includes/category.php on line 379

Deprecated: Creation of dynamic property WP_Term::$category_description is deprecated in /var/www/twodee/blog/wp-includes/category.php on line 380

Deprecated: Creation of dynamic property WP_Term::$cat_name is deprecated in /var/www/twodee/blog/wp-includes/category.php on line 381

Deprecated: Creation of dynamic property WP_Term::$category_nicename is deprecated in /var/www/twodee/blog/wp-includes/category.php on line 382

Deprecated: Creation of dynamic property WP_Term::$category_parent is deprecated in /var/www/twodee/blog/wp-includes/category.php on line 383
aphasia.

I made a database script for an Oracle database for the model I produced, but I don’t know the credentials to log in and test it, so it isn’t tested. I’m also a little rusty on my database skills, so I apologize if there are any obvious errors in my script!

DROP TABLE aphasia_comment CASCADE CONSTRAINTS;

DROP TABLE aphasia_post CASCADE CONSTRAINTS;

DROP TABLE aphasia_category CASCADE CONSTRAINTS;

DROP TABLE aphasia_user CASCADE CONSTRAINTS;

CREATE TABLE aphasia_user

(user_id NUMBER(9) PRIMARY KEY,

username VARCHAR(30) NOT NULL,

password VARCHAR(30) NOT NULL,

user_first_name VARCHAR(30) NOT NULL,

user_last_name VARCHAR(30)) NOT NULL;

CREATE TABLE aphasia_category

(category_id NUMBER(4) PRIMARY KEY,

category_name VARCHAR(30) NOT NULL);

CREATE TABLE aphasia_post

(post_id NUMBER(9) PRIMARY KEY,

user_id NUMBER(9) NOT NULL,

post_title VARCHAR(50) NOT NULL,

post_date DATE NOT NULL,

category_id NUMBER(4) NOT NULL,

post_image_address VARCHAR(100) NULL,

post_video_address VARCHAR(100) NULL,

post_text VARCHAR(100) NULL,

CONSTRAINT aphasia_user_id_fk FOREIGN KEY (user_id) REFERENCES aphasia_user(user_id),

CONSTRAINT aphasia_category_id_fk FOREIGN KEY (category_id) REFERENCES aphasia_category(category_id));

CREATE TABLE aphasia_comment

(comment_id NUMBER(9) PRIMARY KEY,

post_id NUMBER(9) NOT NULL,

user_id NUMBER(9) NOT NULL,

comment_text VARCHAR(100) NOT NULL,

CONSTRAINT aphasia_post_id_fk FOREIGN KEY (post_id) REFERENCES aphasia_post(post_id),

CONSTRAINT aphasia_user_id_fk FOREIGN KEY (user_id) REFERENCES aphasia_user(user_id));

INSERT INTO aphasia_category (category_id, category_name) VALUES(1, ‘My Event’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(2, ‘Living with Aphasia’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(3, ‘Family and Friends’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(4, ‘Photos’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(5, ‘Videos’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(6, ‘Hobbies’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(7, ‘Travel’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(8, ‘Pets’);

INSERT INTO aphasia_category (category_id, category_name) VALUES(9, ‘Question’);

INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)

VALUES(1, ‘millerhj’, ‘bleep’, ‘Hannah’, ‘Miller’);

INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)

VALUES(2, ‘buhrhr’, ‘bleep’, ‘Heather’, ‘Buhr’);

INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)

VALUES(3, ‘johnch’, ‘bleep’, ‘Chris’, ‘Johnson’);

INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)

VALUES(4, ‘morriscm’, ‘bleep’, ‘Mike’, ‘Morrison’);

NOW We are using a MySQL Database, so I updated the script for MySQL and added the tables/attributes/properties we talked about in our meeting:

DROP TABLE IF EXISTS aphasia_comment_read;
DROP TABLE IF EXISTS aphasia_post_read;
DROP TABLE IF EXISTS aphasia_comment;
DROP TABLE IF EXISTS aphasia_post;
DROP TABLE IF EXISTS aphasia_category;
DROP TABLE IF EXISTS aphasia_user;

CREATE TABLE aphasia_user
(user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_is_locked BOOLEAN DEFAULT FALSE NOT NULL,
user_image_address VARCHAR(100) NULL);

CREATE TABLE aphasia_category
(category_id BIGINT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(30) NOT NULL,
category_is_public BOOLEAN DEFAULT TRUE NOT NULL);

CREATE TABLE aphasia_post
(post_id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
post_title VARCHAR(50) NOT NULL,
post_date DATE NOT NULL,
category_id BIGINT NOT NULL,
post_image_address VARCHAR(100) NULL,
post_video_address VARCHAR(100) NULL,
post_text VARCHAR(100) NULL,
CONSTRAINT aphasia_user_id_fk FOREIGN KEY (user_id) REFERENCES aphasia_user(user_id),
CONSTRAINT aphasia_category_id_fk FOREIGN KEY (category_id) REFERENCES aphasia_category(category_id));

CREATE TABLE aphasia_comment
(comment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
post_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
comment_text VARCHAR(100) NOT NULL,
CONSTRAINT aphasia_comment_post_id_fk FOREIGN KEY (post_id) REFERENCES aphasia_post(post_id),
CONSTRAINT aphasia_comment_user_id_fk FOREIGN KEY (user_id) REFERENCES aphasia_user(user_id));

CREATE TABLE aphasia_comment_read
(comment_read_id BIGINT AUTO_INCREMENT PRIMARY KEY,
comment_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
CONSTRAINT aphasia_comread_comment_id_fk FOREIGN KEY (comment_id) REFERENCES aphasia_comment(comment_id),
CONSTRAINT aphasia_comread_user_id_fk FOREIGN KEY (user_id) REFERENCES aphasia_user(user_id));

CREATE TABLE aphasia_post_read
(post_read_id BIGINT AUTO_INCREMENT PRIMARY KEY,
post_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
CONSTRAINT aphasia_postread_post_id_fk FOREIGN KEY (post_id) REFERENCES aphasia_post(post_id),
CONSTRAINT aphasia_postread_user_id_fk FOREIGN KEY (user_id) REFERENCES aphasia_user(user_id));

INSERT INTO aphasia_category (category_id, category_name)
VALUES(1, ‘My Event’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(2, ‘Living with Aphasia’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(3, ‘Family and Friends’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(4, ‘Photos’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(5, ‘Videos’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(6, ‘Hobbies’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(7, ‘Travel’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(8, ‘Pets’);
INSERT INTO aphasia_category (category_id, category_name)
VALUES(9, ‘Question’);

INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)
VALUES(1, ‘millerhj’, ‘bleep’, ‘Hannah’, ‘Miller’);
INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)
VALUES(2, ‘buhrhr’, ‘bleep’, ‘Heather’, ‘Buhr’);
INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)
VALUES(3, ‘johnch’, ‘bleep’, ‘Chris’, ‘Johnson’);
INSERT INTO aphasia_user (user_id, username, password, user_first_name, user_last_name)
VALUES(4, ‘morriscm’, ‘bleep’, ‘Mike’, ‘Morrison’);