Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Hey I need help with this question. The first part is already done. I need help with the second part 1. Write SQL statements that

Hey I need help with this question. The first part is already done. I need help with the second part

1. Write SQL statements that create the tables according to the given ER diagram. Your code should include constraints that enforce primary key, foreign key, and deletion rule constraints.

create table User(userId int primary key,username varchar(10),userpicture BLOB);

create table Role(roleid int primary key,roletitle varchar(10));

Create table UserhasRole (UserId int,roleid int,primary key(Userid,roleid),foreign key(UserId) references User(userId) on delete cascade,foreign key(roleid) references Role(roleid) on delete cascade);

create table State(stateid int primary key,StateCode varchar(5),statename varchar(10));

create table Picture (pictureid int primary key,businessid int,picture BLOB,foreign key(businesssid) references Business(BusinessId) on delete cascade);

create table Business(BusinessId int primary key,BusinessName varchar(10),BusinessDetail varchar(10),Businesstell int,BusinessUrl varchar(30),Street varchar(10),City varchar(10),zipcode varchar(10),StateId int,Haswifi char(3),Hasparking varchar(3),foreign key(StateId) references State(stateid) on delete cascade)

create table Review(ReviewID int, UserID int,ModeratorID int,BusinessID int,ReviewText varchar(10),ReviewRating int,ReviewStatus varchar(10),foreign key(UserId) references User(UserId) on delete cascade,foreign key(ModeratorId) references User(UserId) on delete cascade,foreign key(BusinessId) references Business(BusinessId) on delete cascade);

create table schedule (scheduleid int primary key,BusinessId int, day date,FromHour time,EndHour time,foreign key(BusinessId) references Business(BusinessId) on delete cascade);

create table Tag (TagId int primary key,Tagtext varchar(10));

create table BusinesshasTag ( BusinessId int,TagId int, foreign key(BusinessId) references Business(BusinessId) on delete cascade,forein key(TagId) references Tag(TagId) on delete cascade);

2. Write SQL statements that populate the tables according to the data mentioned in the description or your own made up data. (Dont populate too much

data, just enough so you can do the questions below). Use your own made up data. Please generate your own made up data

image text in transcribed

Completed ER Diagram Picture PicturelD BusinessID FK Picture User BusinessHasTag BusinessID PK FK UserlD PK Business BusinessID PK BusinessName BusinessDetail BusinessTell TagID PK FK Review ReviewID UserlD ModeratorID FK BusinessID FK ReviewText ReviewRating ReviewStatus Tag sinessURL Street City ZipCode TagID TagText PK UserHasRole UserlD PK FK RolelD PK FK FK tatelD asWifi State HasParking StatelD StateCode StateName Schedule SchedulelD PK BusinessID FK Day FromHour EndHour Role RolelD RoleTitle PK

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started