Database Script
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’);