---
# System prepended metadata

title: CS322 REPORT OF DELIVERABLE ONE

---

# CS322 REPORT OF DELIVERABLE ONE

#### Part 0: Data Exploration
The data given is a subset of the famous Yelp data which consists on the feebacks of clinets on business.The 
We have 4 csv files:
- tip.csv
- review.csv
- user.csv
- business.csv

Each of these csv files contains various columns which represent to some extent the attributes or characteristics of the entity. As we only want our attributes values to be atomic, we decide to separate the columns of each csv files into two kinds: atomic, non-atomic.

Here we list all fields which are not atomic because they're the more difficult to treat with.

- __user.csv__ contains the following non-atomic fileds:
        - elite
        - friends
- __Business.csv__ contains the following non-atomic fileds:
        - attributes 
        - categories
        - hours
- __tip.csv__ contains the following non-atomic fields:
        - Null
- __review.csv__ contains the following non-atomic fileds:
        - Null

#### Part 1: ER Diagram
An ER diagram should contain entity set, relation set, attributes , and also the link between entity set and relation set.

We first consider business, tips, reviews and users as four entities sets and all the atomic fields contained in the corresponding csv file are considered as attributes linked to the entity.

As for non-atomic fields, we discuss them one by one:
- __elite__ : 
    - a list of years
    - one user can have many years for elite
    - represent it by a weak entity set for user
    - contain each corresponding elite year for each users as separate rows
-  __friends__:
    -  a list of user_id of friends (we call them invited users of a particular inviting users)
    -  inviting users can have many or no friends, and one user can become invited users of many or no users
    -  represent it by a relation set
    -  contain user_id of both inviting users and invited users 
-  __attributes__:
    -  have 6 catogaries: Music,GoodForMeal,BusinessParking,Ambience,DietaryRestrictions, Noise
    -  all of them (except noise which is atomic) have its own (fixed) set of keys and corresponding atomic values (of boolean)
    -  represent noise as attribute of business
    -  represent others as individual weak entity sets for business, containing their keys as attributes
-  __categories__:
    -  list of words with over 1000 unique value of words
    -  impossible to have columns for each unique value of words
    -  represent as attribute of business (of long string)
-  __hours__:
    -  have 7 atomic values (time) for each weekdays
    -  represent by a weak entity sets for business, with each weekdays as attribute


### Constraints:

#### Entity set:
- user has user_id as __primary key__ 
- review has review_id as __primary key__ 
- business has business_id as __primary key__
- tip has no primary key. It's a __weak entity__. It has two owner sets: user and business. __User_id, business_id__ and __tip.date__ all together form a __primary key__ for tip set.
- elite is a __weak entity__ whose owner set is user. User_id and elite_year together form a __primary key__ for elite set.
- each non-atomic catagory of attributes (Music,GoodForMeal,BusinessParking,Ambience,DietaryRestrictions) are __weak entity__ whose owner set is business. business_id is both the __foreign key__ and __primary key__
- hours is a __weak entity__ whose owner set is business. business_id is both __foreign key__ and __primary key__.




| Entity set                      | Primary Key                       | Foreign Key          | NOT NULL           | UNIQUE |
| -------------------             | ----------------------------------| ------------------   | ------------------ | ------ |
| User                            | user_id                           | None                 | None               | None   |
| Review                          | review_id                         | user_id, business_id | user_id, business_id | None   |
| Business                        | business_id                       | None                 | None               | None   |
| Tip   (weak entity)             | (user_id, business_id, date)      | user_id, business_id | user_id, business_id | None   |
| Hours(weak entity)              | business_id                       | business_id          | business_id        | None   |
| Music(weak entity)              | business_id                       | business_id          | business_id        | None   |
| GoodForMeal(weak entity)        | business_id                       | business_id          | business_id        | None   |
| BusinessParking(weak entity)    | business_id                       | business_id          | business_id        | None   |
| Ambience(weak entity)           | business_id                       | business_id          | business_id        | None   |
| DietaryRestrictions(weak entity)| business_id                       | business_id          | business_id        | None   |
| Elite(weak entity)              | (user_id,year)                    | user_id              | user_id,year       | None   |

| Relation set | Primary Key        | Foreign Key     | NOT NULL        | UNIQUE |
| ------------ | ------------------ | --------------- | --------------- | ------ |
| friends      | (userID1,userID2 ) | userID1,userID2 | userID1,userID2 | None   |






## DDL code

```sql
CREATE TABLE Ambiance(
	CASUCAL BOOLEAN,
	CLASSY BOOLEAN,
	DIVEY BOOLEAN,
	HIPSTER BOOLEAN,
	INTIMATE BOOLEAN,
	ROMANTIC BOOLEAN,
	TOURISTY BOOLEAN,
	TRENDY BOOLEAN,
	UPSCALE BOOLEAN,

	PRIMARY KEY (BUSINESS_ID),
	FOREIGN KEY (BUSINESS_ID) REFERENCE BUSINESS,
)




CREATE TABLE HaveBusinessParking(
	GARAGE BOOLEAN,
	LOT BOOLEAN,
	STREET BOOLEAN,
	VALET BOOLEAN,
	VALIDATED BOOLEAN,

	PRIMARY KEY (BUSINESS_ID),
	FOREIGN KEY (BUSINESS_ID) REFERENCE BUSINESS,

)




CREATE TABLE HaveDietaryRestrictions(
	DIARY_FREE BOOLEAN,
	GULTEN_FREE BOOLEAN,
	HALAL BOOLEAN,
	KOSHER BOOLEAN,
	SOY_FREE BOOLEAN,
	VEGAN BOOLEAN,
	VEGETARIAN BOOLEAN

	PRIMARY KEY (BUSINESS_ID),
	FOREIGN KEY (BUSINESS_ID) REFERENCE BUSINESS,

)



CREATE TABLE HaveGoodForMeal(
	BREAKFAST BOOLEAN,
	BRUNCH BOOLEAN,
	DESSERT BOOLEAN,
	DINNER BOOLEAN,
	LATENIGHT BOOLEAN,
	LUNCH BOOLEAN,

	PRIMARY KEY (BUSINESS_ID),
	FOREIGN KEY (BUSINESS_ID) REFERENCE BUSINESS,

)



CREATE TABLE HaveMusic(
	BACKGROUND_MUSIC BOOLEAN,
	DJ BOOLEAN,
	JUKEBOX BOOLEAN,
	KARAOKE BOOLEAN,
	LIVE BOOLEAN,
	NO_MUSIC BOOLEAN,
	VIDEO BOOLEAN,
	BUSINESS_ID CHAR [22]
	PRIMARY KEY (BUSINESS_ID),
	FOREIGN KEY (BUSINESS_ID) REFERENCE BUSINESS,
)


CREATE TABLE Open_in_Hours(
	MONDAY CHAR[11],  # e.g. 8:00-19:00
	TUESDAY CHAR[11]
	WEDNESDAY CHAR[11]
	THURSDAY CHAR[11]
	FRIDAY CHAR[11]
	SATURDAY CHAR[11]
	SUNDAY   CHAR[11]

	PRIMARY KEY (BUSINESS_ID),
	FOREIGN KEY (BUSINESS_ID) REFERENCE BUSINESS,

)

CREATE TABLE BUSINESS(
	BUSINESS_ID  CHAR [22] 
	POSTE_CODE VARCHAR[16] # max poste code lenth in data=8
	ADDRESS VARCHAR[128] # max address lenth in data=118
	CITY VARCHAR[64] # max name lenth in data =50
	LATITUDE FLOAT,
	LONGITUDE FLOAT,  
	IS_OPEN BOOLEAN,
	NAME VARCHAR(128),   #MAX IN DATA =64
	REVIEW_COUNT INT.
	STARS INT,
	STATE CHAR[2], # AZ,ON...
	NOISE_LEVEL  CHAR [10],#{'None', 'average', 'loud', 'quiet', 'very_loud'}
	CATEGORIES   TEXT, # max of lenth of categories in data =550

)

CREATE TABLE YELP_USER (
    average_stars FLOAT,
    compliment_cool  INTEGER,
    compliment_cute       INTEGER,
    compliment_funny      INTEGER,
    compliment_hot        INTEGER,
    compliment_list       INTEGER,
    compliment_more       INTEGER,
    compliment_note       INTEGER,
    compliment_photos     INTEGER,
    compliment_plain      INTEGER,
    compliment_profile    INTEGER,
    compliment_writer     INTEGER,
    cool                  INTEGER,
    fans                  INTEGER,
    funny                 INTEGER,
    name                  varchar(64),
    review_count          INTEGER,
    useful                INTEGER,
    user_id               char(22),
    yelping_since         DATETIME,
    PRIMARY KEY (user_id)
);

CREATE TABLE ELITE(
    awardee         char(22),
    year           INTEGER,
    PRIMARY KEY (awardee,year),
    FOREIGN KEY (awardee) REFERENCES YELP_USER(user_id)
)

CREATE TABLE FRIENDS(
    inviting    char(22),
    invited     char(22),
    PRIMARY KEY (inviting,invited),
    FOREIGN KEY (inviting) REFERENCES YELP_USER(user_id),
    FOREIGN KEY (invited) REFERENCES YELP_USER(user_id)
)

create table TIP (
    business_id         char(22),
    compliment_count   INTEGER,
    date                DATETIME,
    text                varchar(1024),
    user_id             char(22),
    PRIMARY key (business_id,date,user_id),
    FOREIGN KEY (business_id) REFERENCES BUSINESS(business_id),
    FOREIGN KEY (user_id) REFERENCES YELP_USER(user_id),

);

create TABLE REVIEW(
    business_id    char(22),
    cool           INTEGER,
    date           DATETIME,
    funny          FLOAT,
    review_id      char(22),
    stars          FLOAT,
    text           varchar(1024),
    useful         FLOAT,
    user_id        char(22),  
    PRIMARY key (review_id),
    FOREIGN KEY (business_id) REFERENCES BUSINESS(business_id),
    FOREIGN KEY (user_id) REFERENCES YELP_USER(user_id),
)




```

