Question
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
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started