write mySQL code for this ER diagram if possible make a relational schema admin
ID: 3600789 • Letter: W
Question
write mySQL code for this ER diagram if possible make a relational schema
admin usernamePassword werProblemDDescription Username ADMINISTRATOR FIXES SELLER PROBLEMS FOXES BUYER PROBLEMS Password Comparty Password Ratine GIVES SELLER FName FName Name LName GMES BUYER Name FEEDBACK Commet Rating Street SELLER BUYER Street AptNo SELLER AptNo MailingAddress MallngAddress City BUYER MUNICATE Message Emal POSTS shipPrice .nrle: ShipDate SHIPS ArrivalDate 8idAmount Descript ption ITEM B DS Condition Saline WinStatus Photo AccountType Category StartBidAmount)| AuctionStart AuctionEndExplanation / Answer
Below are MySQL create statement for the given ER Diagram.
Total 13 tables are created. Primary Key and Foreign key are clearly mentioned in each create statement.
CREATE TABLE Administrator
(
AdminID CHAR(6),
UserName VARCHAR(10),
Password VARCHAR(16),
PRIMARY KEY (AdminID)
);
CREATE TABLE Buyer
(
BuyerID CHAR(10),
UserName VARCHAR(16),
Password VARCHAR(16),
FName VARCHAR(20),
LName VARCHAR(20),
Street VARCHAR(10),
AptNo VARCHAR(6),
City VARCHAR(10),
State VARCHAR(10),
ZipCode VARCHAR(5),
Email VARCHAR(25),
PhoneNo VARCHAR(10)
PRIMARY KEY (BuyerID)
);
CREATE TABLE Seller
(
SellerID CHAR(10),
UserName VARCHAR(16),
Password VARCHAR(16),
CompanyName VARCHAR(25),
FName VARCHAR(20),
LName VARCHAR(20),
Street VARCHAR(10),
AptNo VARCHAR(6),
City VARCHAR(10),
State VARCHAR(10),
ZipCode VARCHAR(5),
Email VARCHAR(25),
PhoneNo VARCHAR(10)
PRIMARY KEY (SellerID)
);
CREATE TABLE Item
(
ItemID VARCHAR(16),
Title VARCHAR(50),
Description VARCHAR(100),
Condition VARCHAR(20),
Photo BLOB,
Category VARCHAR(20),
StartBidAmount DOUBLE,
AuctionStart DATE,
AuctionEnd DATE,
SellerID CHAR(10),
PRIMARY KEY (ItemID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID)
);
CREATE TABLE buyerBidsItem
(
BidID VARCHAR(5),
ItemID VARCHAR(16),
BuyerID CHAR(10),
BudAmount DOUBLE,
BidTime TIME,
WinStatus CHAR(6),
PRIMARY KEY (BidID),
FOREIGN KEY (ItemID) REFERENCES Item (ItemID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID)
);
CREATE TABLE BuyerItem
(
BuyerID CHAR(10),
ItemID VARCHAR(16),
AccountType VARCHAR(16),
AccountNo VARCHAR(25),
PRIMARY KEY (BuyerID, ItemID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID),
FOREIGN KEY (ItemID) REFERENCES Item (ItemD)
);
CREATE TABLE Ships
(
SellerID CHAR(10),
ItemID VARCHAR(16),
ShipPrice DOUBLE,
ShipDate DATE,
ArrivalDate DATE,
PRIMARY KEY (SellerID, ItemID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID),
FOREIGN KEY (ItemID) REFERENCES Item (ItemD)
);
CREATE TABLE SellerFeedback
(
SellerFBID VARCHAR(10),
BuyerID CHAR(10),
SellerID CHAR(10),
Rating CHAR(2),
RatingDate DATE,
Comment TEXT,
PRIMARY KEY (SellerFBID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID)
);
CREATE TABLE BuyerFeedback
(
BuyerFBID VARCHAR(10),
BuyerID CHAR(10),
SellerID CHAR(10),
Rating CHAR(2),
RatingDate DATE,
Comment TEXT,
PRIMARY KEY (BuyerFBID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID)
);
CREATE TABLE sellerComm
(
SellerCommID VARCHAR(10),
BuyerID CHAR(10),
SellerID CHAR(10),
CommDate DATE,
Message TEXT,
PRIMARY KEY (SellerCommID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID)
);
CREATE TABLE buyerComm
(
BuyerCommID VARCHAR(10),
BuyerID CHAR(10),
SellerID CHAR(10),
CommDate DATE,
Message TEXT,
PRIMARY KEY (BuyerCommID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID)
);
CREATE TABLE SellerProblem
(
SellerProblemID VARCHAR(10),
AdminID CHAR(16),
SellerID CHAR(10),
problemDate DATE,
Status VARCHAR(10),
PRIMARY KEY (SellerProblemID),
FOREIGN KEY (SellerID) REFERENCES Seller (SellerID),
FOREIGN KEY (AdminID) REFERENCES Administrator (AdminID)
);
CREATE TABLE BuyerProblem
(
BuyerProblemID VARCHAR(10),
AdminID CHAR(16),
BuyerID CHAR(10),
problemDate DATE,
Status VARCHAR(10),
PRIMARY KEY (BuyerProblemID),
FOREIGN KEY (BuyerID) REFERENCES Buyer (BuyerID),
FOREIGN KEY (AdminID) REFERENCES Administrator (AdminID)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.