PROVIDE SQL CODE FOR THE CONSTRAINTS, PRIMARY KEY, BULK INSERT ETC. also how to load a file so we can bulk insert from them. Assignment $3\mathrm{.}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\mathrm{.}2$

Insert the sample data provided on CourSys into the tables that you have created. The zip file

$$assignment$3.$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 $\text{'}$d:$\backslash$userdata$\backslash ...\backslash$business$.$csv$\text{'}$ WITH $($fieldterminator$=\text{'},\text{'},$

rowterminator$=\text{'}$

$\text{'},$ 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.