4pU7x5Yx

1CREATE DATABASE IF NOT EXISTS UABOOK2;
2
3USE UABOOK2;
4
5
6CREATE  TABLE `uabook2`.`profile` (
7	profile_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8	name VARCHAR(40) NOT NULL,
9	email VARCHAR(50) NOT NULL,
10	birth_month numeric(2,0)check(birth_month in('1','2','3','4','5','6','7','8','9','10','11','12')),
11	birth_day numeric(2,0) check(birth_day in ('1','2','3','4','5','6','7')),
12	birth_year numeric(4,0), 
13	sex char(1),
14	hobbies VARCHAR(50),
15	wishlist VARCHAR(50),
16	languages VARCHAR(50) NOT NULL,
17	street_address varchar(60),
18	city varchar(40),
19	state_ varchar(2),
20	zip numeric(5,0),
21	phonenumber numeric(15,0),
22	school VARCHAR(30),
23	university VARCHAR(30),
24	work_ VARCHAR(50)
25	);
26
27
28DROP TABLE IF EXISTS status;
29CREATE TABLE status(
30	status_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
31	status_updates VARCHAR(40) NOT NULL,
32	month_ numeric(2,0)check(month_ in('1','2','3','4','5','6','7','8','9','10','11','12')),
33	day_ numeric(2,0) check(day_ in ('1','2','3','4','5','6','7')),
34	year_ numeric(4,0) 
35);
36
37
38DROP TABLE IF EXISTS notification;
39CREATE TABLE notification(
40	notification_id INT NOT NULL,
41	notification_message VARCHAR(100),
42	notification_type char(3),
43	month_ numeric(2,0)check(month_ in('1','2','3','4','5','6','7','8','9','10','11','12')),
44	day_ numeric(2,0) check(day_ in ('1','2','3','4','5','6','7')),
45	year_ numeric(4,0),
46	notification_is_read char(2)	
47);
48
49
50DROP TABLE IF EXISTS homepage;
51CREATE TABLE homepage;(
52	homepage_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
53	notification INT,
54	status_contents VARCHAR(30),
55	friends_list CHAR NOT NULL,
56	apps_games VARCHAR(30),
57	CONSTRAINT f_status_homepage FOREIGN KEY(status_id) REFERENCES status (status_id) ON DELETE NO ACTION
58);
59DROP TABLE IF EXISTS account;
60CREATE TABLE account(
61	user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
62	username VARCHAR(25) NOT NULL,
63	password VARCHAR(25) NOT NULL
64);