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.
Submission
Create the tables and constraints in your assigned database on the Cypress server and import the
data provided. Your database must contain an exact copy of the provided data without
modifications (insertions, deletions, updates).
Submit a pdf-file Assignment3.pdf with
- all your SQL statements to create tables,
- all your SQL statements to create constraints,
- and the order of tables when inserting the data
to CourSys.