Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE ACCOUNT(
- UserID Number NOT NULL,
- ScreenName VARCHAR2,
- Email VARCHAR2,
- Address VARCHAR2,
- DOB Date,
- BALANCE DECIMAL(6,2) DEFAULT 0,
- CONSTRAINT ACCOUNT_PK PRIMARY KEY (UserID));
- CREATE TABLE FRIENDSHIP(
- UserID1 NUMBER,
- UserID2 NUMBER,
- FriendshipDate DATE,
- CONSTRAINT FRIENDSHIP_PK PRIMARY KEY (UserID1, UserID2));
- CREATE TABLE STEAMGROUP(
- GroupID NUMBER NOT NULL,
- Name VARCHAR2,
- Description VARCHAR2,
- CONSTRAINT GROUP_PK PRIMARY KEY (GroupID));
- CREATE TABLE MEMBERSHIP(
- DateJoined DATE,
- UserID NUMBER,
- GroupID NUMBER,
- CONSTRAINT MEMBERSHIP_FK1 FOREIGN KEY (UserID) REFERENCES ACCOUNT(UserID),
- CONSTRAINT MEMBERSHIP_FK2 FOREIGN KEY (GroupID) REFERENCES STEAMGROUP(GroupID));
- CREATE TABLE EMPLOYEE(
- EmployeeID NUMBER,
- Name VARCHAR2,
- HireDate DATE,
- TerminationDate DATE,
- Skill VARCHAR2,
- CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EmployeeID));
- CREATE TABLE SUPPORT_TICKET(
- SupportTicketID NUMBER,
- DateFiled DATE,
- DateResolved DATE,
- Reason VARCHAR2,
- EmployeeID NUMBER,
- UserID NUMBER,
- CONSTRAINT SUPPORT_TICKET_PK PRIMARY KEY (SupportTicketID),
- CONSTRAINT SUPPORT_TICKET_FK1 FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
- CONSTRAINT SUPPORT_TICKET_FK2 FOREIGN KEY (UserID) REFERENCES ACCOUNT(UserID));
- CREATE TABLE ITEM(
- ItemID NUMBER,
- ItemType VARCHAR2,
- ItemPrice DECIMAL(6,2),
- CONSTRAINT ITEM_PK PRIMARY KEY (ItemID));
- CREATE TABLE INVENTORY(
- InventoryID NUMBER,
- DateAcquired DATE,
- UserID NUMBER,
- ItemID NUMBER,
- CONSTRAINT INVENTORY_PK PRIMARY KEY (InventoryID),
- CONSTRAINT INVENTORY_FK1 FOREIGN KEY (UserID) REFERENCES ACCOUNT(UserID),
- CONSTRAINT INVENTORY_FK2 FOREIGN KEY (ItemID) REFERENCES ITEM(ItemID));
- CREATE TABLE PURCHASE(
- ProductID Number NOT NULL,
- UserID Number NOT NULL,
- DateOfPurchase TIMESTAMP,
- PurchaseAmount DECIMAL(6,2) DEFAULT 0,
- CONSTRAINT PURCHASE_PK PRIMARY KEY (ProductID, UserID));
- CREATE TABLE PRODUCT(
- ProductID NUMBER NOT NULL,
- Title VARCHAR2 NOT NULL,
- Description VARCHAR2,
- Price DECIMAL(5,2) DEFAULT 0,
- ReleaseDate DATE,
- ProductType VARCHAR2 NOT NULL,
- CONSTRAINT PRODUCT_PK PRIMARY KEY (ProductID));
- CREATE TABLE PUBLISHER(
- PublisherID NUMBER NOT NULL,
- PublisherName VARCHAR2,
- YearEstablished NUMBER,
- CONSTRAINT PUBLISHER_PK PRIMARY KEY (PublisherID));
- CREATE TABLE PROGRAM(
- ProductID NUMBER NOT NULL,
- ProgramType VARCHAR2,
- PublisherID NUMBER,
- CONSTRAINT PROGRAM_FK FOREIGN KEY (PublisherID) REFERENCES PUBLISHER(PublisherID)
- CONSTRAINT PROGRAM_PK PRIMARY KEY (ProductID));
- CREATE TABLE GAME(
- ProductID NUMBER NOT NULL,
- Genre VARCHAR2,
- Rating VARCHAR2,
- CONSTRAINT GAME_PK PRIMARY KEY (ProductID));
- CREATE TABLE SOFTWARE(
- ProductID NUMBER NOT NULL,
- Category VARCHAR2,
- CONSTRAINT SOFTWARE_PK PRIMARY KEY (ProductID));
- CREATE TABLE MOVIE(
- ProductID NUMBER NOT NULL,
- RunTime TIME,
- Genre VARCHAR2,
- Rating VARCHAR2,
- CONSTRAINT ITEM_PK PRIMARY KEY (ProductID));
- INSERT INTO ACCOUNT VALUES
- (100001, 'Gamer1', 'gamer1@gmail.com', '1234 Gamer Ln Ames, IA 50010', '1995-01-01', 0),
- (100002, 'Gamer2', 'gamer3@gmail.com', '1235 Gamer Ln Ames, IA 50010', '1995-01-01', -10.00),
- (100003, 'Gamer3', 'gamer3@gmail.com', '1236 Gamer Ln Ames, IA 50010', '1995-01-01', 10.00),
- (100004, 'Gamer4', 'gamer4@gmail.com', '1237 Gamer Ln Ames, IA 50010', '1995-01-01', 9999.99),
- (100005, 'Gamer5', 'gamer5@gmail.com', '1238 Gamer Ln Ames, IA 50010', '1995-01-01', 100.00);
- INSERT INTO FRIENDSHIP VALUES
- (100001,100002, '2015-04-13'),
- (100002,100003, '2015-04-13'),
- (100005,100003, '2015-04-15'),
- (100003,100001, '2015-04-13');
- INSERT INTO STEAMGROUP VALUES
- (1000, 'Gamers Unite', 'Place for gamers to make friends and play games'),
- (1001, 'MIS320', 'Group for students taking MIS320 to unwind and play some games');
- INSERT INTO MEMBERSHIP VALUES
- ('2015-04-13', 100001, 1000),
- ('2015-04-15', 100002, 1000),
- ('2016-04-19', 100004, 1001),
- ('2015-04-28', 100005, 1001);
- INSERT INTO EMPLOYEE VALUES
- (100, 'Employee1', '2008-11-05', NULL, 'Customer Service') ,
- (101, 'Employee2', '2008-11-05', NULL, 'Public Relations'),
- (102, 'Employee3', '2008-11-22', NULL, 'Publisher Relations'),
- (103, 'Employee4', '2008-05-11', '2016-01-01', 'Customer Service');
- INSERT INTO SUPPORT_TICKET VALUES
- (10000, '2016-09-10', '2016-09-10', 'Refund needed on No Man"s Sky', 100, 100001),
- (10001, '2016-04-11', '2016-05-11', 'Refund needed for Call of Duty: Infinite Warfare', 101, 100002),
- (10002, '2017-04-13', NULL, 'Request for Battletoads', 101, 100003);
- INSERT INTO ITEM VALUES
- (100, 'CSGOSKIN', 0.99),
- (101, 'TF2HAT', 10.00),
- (102, 'PROFILEBACKGROUND', .50),
- (103, 'CSGOSKIN', 500.00);
- INSERT INTO INVENTORY VALUES
- (10000, '2017-01-01', 100001, 100),
- (10001, '2017-04-11', 100002, 101),
- (10002, '2017-04-13', 100001, 102);
- INSERT INTO PURCHASE VALUES
- (100001, 60001, '2016-03-01 08:24:01', 18.99),
- (100001, 60003, '2016-03-01 08:24:01', 5.99),
- (100002, 60002, '2016-03-01 08:24:29', 59.99),
- (100003, 60002, '2016-03-01 08:25:16', 59.99),
- (100003, 60004, '2016-03-01 08:25:16', 29.99);
- INSERT INTO PRODUCT VALUES
- (60001, 'BattleToads', 'Fight as toads', 18.99, '2012-08-08', 'P'),
- (60002, 'Call of Booty: Pirate Warfare', 59.99, 'Fast paced naval shooter', '2015-11-15', 'P'),
- (60003, "Harold Bowler and the Magician's Rock", 'Young magician performs geological tricks', 5.99, '2014-08-14', 'M'),
- (60004, 'Abode PhotoCrop', "World's most advanced image cropping tool", 29.99, '2014-01-13', 'P');
- INSERT INTO PUBLISHER VALUES
- (90001, 'Inactivision', 2003),
- (90002, 'Abode', 1995),
- (90003, 'Common', 1985);
- INSERT INTO PROGRAM VALUES
- (60001, 'G', 90003),
- (60002, 'G', 90001),
- (60004, 'S', 90002);
- INSERT INTO GAME VALUES
- (60001, 'Sidescroller', 'T'),
- (60002, 'First Person Shooter', 'M');
- INSERT INTO SOFTWARE VALUES
- (60004, 'Image-Editing');
- INSERT INTO MOVIE VALUES
- (60003, '1:20:59', 'Fantasy', 'PG-13');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement