stats170ab-2018: TwitterSchema.sql

File TwitterSchema.sql, 1.0 KB (added by mjcarey, 3 years ago)

HW4 solution schema

Line 
1
2DROP TABLE IF EXISTS Hashtags;
3DROP TABLE IF EXISTS UserStats;
4DROP TABLE IF EXISTS RawTweets;
5DROP TABLE IF EXISTS Tweets;
6DROP TABLE IF EXISTS Users;
7
8CREATE TABLE Users (
9  id varchar,
10  name varchar,
11  screen_name varchar,
12  description varchar,
13  created_at timestamp,
14  PRIMARY KEY (id)
15);
16
17CREATE TABLE Tweets (
18  created_at timestamp,
19  id varchar,
20  text varchar,
21  user_id varchar,
22  place varchar,
23  in_reply_to_status_id varchar,
24  in_reply_to_screen_name varchar,
25  PRIMARY KEY (id),
26  FOREIGN KEY (user_id) REFERENCES Users(id)
27);
28
29CREATE TABLE RawTweets (
30  tweet_id varchar,
31  rawtweet varchar,
32  PRIMARY KEY (tweet_id),
33  FOREIGN KEY (tweet_id) REFERENCES Tweets(id)
34);
35
36CREATE TABLE UserStats (
37  tweet_id varchar,
38  user_id varchar,
39  statuses_count int,
40  followers_count int,
41  friends_count int,
42  PRIMARY KEY (user_id),
43  FOREIGN KEY (user_id) REFERENCES Users(id),
44  FOREIGN KEY (tweet_id) REFERENCES Tweets(id)
45);
46
47CREATE TABLE Hashtags (
48  tweet_id varchar,
49  text varchar,
50  FOREIGN KEY (tweet_id) REFERENCES Tweets(id)
51);
52