Create a gameshop database using the diagram below. Implement the GameShop datab

Question

Create a gameshop database using the diagram below.

Implement the GameShop database, include all DDL commands.
(To create a new database named GameShop you use the command: CREATE DATABASE GameShop;)

Insert at least 3 real or fictional records in each table.

Customers Customer ID(PK) Points Balance FirstName LasName Games. Games ID (PK) Title ESRBRating Genre ReleaseDate FranchiselD(FK) Tags XGames TaglD(FK) Game ID(FK) Tags TagID(PK) TagNae Stores Location Manager (FK) Prodcuts ProdcutID(PK) Title GameID(FK) PlatformID(FK) New Price UsedPercent (Float Default 0.7) BuyBack Percent Franchises FranchiselD(PK Name Employees EmployeeID(PK) FirstName Last Name StorelD (FK) SArctive Plafformns PlateFormID(PK) Name Maker isCurrentGen Release Date Transactions TransactionID(PK) StorelD(FK) CustomerID(FK) EmpolyeelD(FK) Date TIME ProductID(FK) Quantity PaymentTotal Inventory Inventory ID(PK) StorelD(FK Quantity isNew

Explanation / Answer

To create database:

CREATE DATABASE GameShop;

CREATE TABLE Customers(
CustomerID int NOT NULL,
PointsBalance NUMBER,
FirstName VARCHAR2(255),
LastName VARCHAR2(255),
PRIMARY KEY (CustomerID),
);

INSERT INTO Customers VALUES (1,500,'Ravi','kanala');
INSERT INTO Customers VALUES (2,200,'Nani','Kattamuri');
INSERT INTO Customers VALUES (3,500,'Ravindra','James');

CREATE TABLE Stores(
StoreID int NOT NULL,
Location VARCHAR2(255),
Manager VARCHAR2(255),
PRIMARY KEY (StoreID),

);

INSERT INTO Stores VALUES (10,'Yendada','Ravi');
INSERT INTO Stores VALUES (20,'Vizag','Musthafa');
INSERT INTO Stores VALUES (30,'Maddelapalem','Hari');

CREATE TABLE Employees(
EmployeeID int NOT NULL,
FirstName VARCHAR2(255),
LastName VARCHAR2(255),
StoreID int NOT NULL,
isArchive VARCHAR2(255),
PRIMARY KEY ( EmployeeID),
FOREIGN KEY (StoreID) REFERENCES Stores(StoreID)

);

INSERT INTO Employees VALUES (101,'Ravi','kanala',1,'yes');
INSERT INTO Employees VALUES (201,'Nani','Kattamuri',1,'no');
INSERT INTO Employees VALUES (301,'Ravindra','James',2,'yes');

CREATE TABLE Games(
GamesID int NOT NULL,
Title VARCHAR2(255),
ESRBRating float,
ReleaseDate DATE,
Genre VARCHAR2(255),
FranchiseID int NOT NULL,
PRIMARY KEY (GamesID),
FOREIGN KEY (FranchiseID) REFERENCES Franchises(FranchiseID)

);

INSERT INTO Games VALUES (1101,'TempleRun',3.8,'01-Apr-1999','Games',2001);
INSERT INTO Games VALUES (1201,'8BAllPool',4.8,'01-Mar-1999','Games',2002);
INSERT INTO Games VALUES (1301,'Cricbuzz' ,2.8,'01-Mar-1999','Apps',2002);

CREATE TABLE Products(
ProductID int NOT NULL,
Title VARCHAR2(255),
GamesID int NOT NULL,
PlatformID int NOT NULL,
NewPrice float,
Usedpercent Float Defaullt 0.7,
PRIMARY KEY (ProductID ),
FOREIGN KEY (GamesID) REFERENCES Games(GamesID)
FOREIGN KEY (PlatformID) REFERENCES Platforms(PlatformID)
);

INSERT INTO Products VALUES (11101,'Keyboard',1101,12001,200.00,5.5);
INSERT INTO Products VALUES (11201,'mouce',1201,12001,250.00,8.8);
INSERT INTO Products VALUES (11301,'ScreenGuard',1201,12002,300.00,9.2);

CREATE TABLE Platforms(
PlatformID int NOT NULL,
Name VARCHAR2(255),
Marker VARCHAR2(255),
isCurrentGenre VARCHAR2(255),
ReleaseDate DATE,
PRIMARY KEY (PlatformID),

);

INSERT INTO Platforms VALUES (12001,'Adventure','Highend','games','01-Apr-1988');
INSERT INTO Platforms VALUES (12002,'Classical','excellent','apps','01-Jan-1999');
INSERT INTO Platforms VALUES (12003,'puzzle','super','puzzles','01-Feb-1972');

CREATE TABLE TagsXGames(
TagID int NOT NULL,
GameID int NOT NULL,
PRIMARY KEY (TagID),
FOREIGN KEY (GameID) REFERENCES Games(GamesID)
);

INSERT INTO TagsXGames VALUES (13001,1101);
INSERT INTO TagsXGames VALUES (13002,1201);
INSERT INTO TagsXGames VALUES (13003,1201);

CREATE TABLE Franchises(
FranchiseID int NOT NULL,
Name VARCHAR2(255),
PRIMARY KEY (FranchiseID),

);

INSERT INTO Franchises VALUES (2001,'Nellam Labs');
INSERT INTO Franchises VALUES (2002,'TechM');
INSERT INTO Franchises VALUES (2003,'Verizona');

CREATE TABLE Tags(
TagID int NOT NULL,
Name VARCHAR2(255),
PRIMARY KEY (TagID),

);

INSERT INTO Tags VALUES (3001,'Temple');
INSERT INTO Tags VALUES (3002,'Buchi');
INSERT INTO Tags VALUES (3003,'Jabrica');

CREATE TABLE Inventory(
InventoryID int NOT NULL,
StoreID int NOT NULL,
ProductID int NOT NULL,
Quantity NUMBER,
isNew VARCHAR2(255)
PRIMARY KEY (InventoryID ),
FOREIGN KEY (StoreID) REFERENCES Stores(StoreID)
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

INSERT INTO Inventory VALUES (4001,10,11101,700,'yes');
INSERT INTO Inventory VALUES (4002,10,11201,100,'no');
INSERT INTO Inventory VALUES (4003,20,11101,400,'yes');

CREATE TABLE Transactions(
TransactionID int NOT NULL,
StoreID int NOT NULL,
CustomerID int NOT NULL,
EmployeeID int NOT NULL,
DATE_TIME DATE,
ProductID int NOT NULL,
Quantity NUMBER,
PaymentTotal Float,
PRIMARY KEY (InventoryID ),
FOREIGN KEY (StoreID) REFERENCES Stores(StoreID)
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

INSERT INTO Transactions VALUES (5001,10,1,103,11101,01-Apr-2017:5:00700,11103,200,50000.00);
INSERT INTO Transactions VALUES (5002,10,1,101,11201,100,1-Apr-2017:7:00,11102,500,1000000.00);
INSERT INTO Transactions VALUES (5003,20,3,102,11101,400,1-Apr-2017:8:00,11103,100,110000.00);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.