aQFJJjhW

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