We provide some sample data based on the dataset available on
https://www.yelp.com/dataset.
Using your assigned database on the Cypress server, you will create the tables and constraints
and insert some sample data available in ‘Datasets’ folder on CourSys.
Please, check the following link for instructions on how to use Microsoft SQL Server in CSIL:
http://www.sfu.ca/computing/about/support/csil/windows/how-to-use-sql.html
This link also presents instructions on how to import data from a file into your database:
https://www.sfu.ca/computing/about/support/csil/windows/how-to-use-sql.html#import-export
Assignment 3.1
Use SQL to create the tables and constraints in your SQL Server database according to the
description below.
Your database should have the following tables, please read the Attributes and constraint part
carefully for primary and foreign keys.
You must use the exact names and domains specified. Marks will be deducted if you fail to do so.
Tables
business = (business_id, name, address, city, postal_code, stars, review_count)
checkin = (checkin_id, business_id, date)
tip = (tip_id, user_id, business_id, date, compliment_count)
review = (review_id, user_id, business_id, stars, useful, funny, cool, date)
user_yelp = (user_id, name, review_count, yelping_since, useful, funny, cool, fans, average_stars)
friendship = (user_id, friend)
Attributes
You should use the domains listed below. Consider the described constraints and primary keys.
Table business
● business_id: string(22), primary key.
● name: variable-length string(60), not null.
● address: variable-length string(75).
● city: variable-length string (30), not null.
● postal_code: variable-length string(7).
● stars: decimal(2, 1), in [1,5] interval.
● review_count: integer, default: 0, non-negative.
Table checkin
● checkin_id: integer, primary key.
● business_id: string(22), not null.
● date: date and time, not null, default: current date.
Table tip
● tip_id: integer, primary key.
● user_id: string(22), not null.
● business_id: string(22), not null.
● date: date and time, not null, default: current date.
● compliment_count: integer, default: 0, non-negative.
Table friendship
● user_id: string(22), primary key
● friend: string(22), primary key
Table review
● review_id: string(22), primary key.
● user_id: string(22), not null.
● business_id: string(22), not null.
● stars: integer, not null, between 1 and 5.
● useful: integer, default: 0, non-negative.
● funny: integer, default: 0, non-negative.
● cool: integer, default: 0, non-negative.
● date: date and time, default: current date.
Table user_yelp
● user_id: string(22), primary key.
● name: variable-length string(35), not null.
● review_count: integer, default 0, non-negative.
● yelping_since: date and time, not null, default: current date.
● useful: integer, default: 0, non-negative.
● funny: integer, default: 0, non-negative.
● cool: integer, default: 0, non-negative.
● fans: integer, default: 0, non-negative.
● average_stars: decimal(3, 2), in [1,5] interval.
Note: Consider a fixed-length string where not specified otherwise.
Constraints
Your database should enforce the primary key constraints noted above.
The following foreign key constraints also need to be enforced:
- checkin.business_id references business.business_id
- tip.business_id references business.business_id
- tip.user_id references user_yelp.user_id
- review.business_id references business.business_id
- review.user_id references user_yelp.user_id
- friendship.user_id references user_yelp.user_id
- friendship.friend references user_yelp.user_id
The response to the violation of foreign keys constraints should be to not allow deletion or
update of referenced records.
Assignment 3.2
Insert the sample data provided on CourSys into the tables that you have created. The zip file
“assignment3.zip”, posted on CourSys under Datasets, contains one file for each table.
Use one of the following two options:
1) Command prompt: bcp
bcp "dbName.dbo.tableName" in "ifile" -S SQLserver -b 1 -m 100 -T -c -F 1
2) T-SQL: bulk insert
Example:
"BULK INSERT dbo.business FROM 'd:\userdata\...\business.csv' WITH (fieldterminator=',',
rowterminator='\n', firstrow=2)"
Remember you will need to insert data in a sensible order that respects the foreign key
constraints. Report the order in which you execute the statements.