Can you help me revise this code? I think its accurate so far, but I need a 2nd perspective. Would you look over this code with the given information to see if everything is fine. If anything needs revision, would you revise it? Thank you.

Question:

1.Data Definition Language (DDL)/Data Manipulation Language (DML) (25%):

Create tables statements and insert statements. Named it wrong

Code:

create database City_Boomers;

Go

USE City_Boomers;

go

create table SalesPerson (

SalesPerson_ID int not null IDENTITY,

SalesPerson_Name varchar (50) not null,

CONSTRAINT SalesPerson_ID_PK primary key(SalesPerson_ID)

);

insert into salesperson

([salesperson_name])

values ('Mallory');

insert into salesperson

([salesperson_name])

values ('Patrick');

Create table Agents (

Agent_ID int not null IDENTITY,

AgentName varchar (50) not null

CONSTRAINT Agent_ID_PK PRIMARY KEY(Agent_ID)

);

Insert into Agents

([AgentName])

values ('Bryce');

Insert into Agents

([AgentName])

values ('Olivia');

Create table Merchandise (

Merchendise_ID int not null IDENTITY,

MerchandiseType varchar (50) not null,

MerchandisePrice DECIMAL not null

CONSTRAINT Merchandise_ID_PK PRIMARY KEY(Merchendise_ID)

);

Insert into Merchandise

([MerchandiseType], [MerchandisePrice])

values ('Jersey', 100.00)

Insert into Merchandise

([MerchandiseType], [MerchandisePrice])

values ('Souvenir Ball', 40.00)

Insert into Merchandise

([MerchandiseType], [MerchandisePrice])

values ('Water Bottle',15.00)

Insert into Merchandise

([MerchandiseType], [MerchandisePrice])

values ('T-Shirt', 25.00)

Insert into Merchandise

([MerchandiseType], [MerchandisePrice])

values ('Autographed Picture', 75.00)

Create Table SalesTransaction (

SalesTransaction_ID int not null IDENTITY,

SalesTransactionPaymentMethod varchar (50) not null,

CONSTRAINT SalesTransaction_ID_PK PRIMARY KEY(SalesTransaction_ID)

);

Insert into SalesTransaction

([SalesTransactionPaymentMethod])

values ('Credit')

Insert into SalesTransaction

([SalesTransactionPaymentMethod])

values ('Cash')

Create Table Advertisers (

Advertisers_ID int not null IDENTITY,

AdvertisersCompany varchar (50) not null,

CONSTRAINT Advertisers_ID_PK PRIMARY KEY(Advertisers_ID)

)

Insert into Advertisers

([AdvertisersCompany])

values ('Koma Cola')

Insert into Advertisers

([AdvertisersCompany])

values ('City Hardware')

Insert into Advertisers

([AdvertisersCompany])

values ('Showtime Grill')

Insert into Advertisers

([AdvertisersCompany])

values ('NAC Beverages')

Insert into Advertisers

([AdvertisersCompany])

values ('Daytime Diner')

Create Table Game (

Game_ID int not null IDENTITY,

GameOpponent varchar (50) not null,

GameDate date not NULL

CONSTRAINT Game_ID_PK PRIMARY KEY(Game_ID)

)

Insert into Game

([GameOpponent], [GameDate])

values ('Loomers','07-Mar-20' )

Insert into Game

([GameOpponent], [GameDate])

values ('Zoomers','14-Mar-20')

Create Table Tickets (

Ticket_ID int not null IDENTITY,

TicketSeatType varchar (50) not null,

TicketPrice DECIMAL not null,

TicketPayment varchar (50) not null,

GameDate date not null,

Agent varchar (50) null

CONSTRAINT Ticket_ID_PK PRIMARY KEY(Ticket_ID)

)

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate],[Agent])

values ('Sideline', 150.00,'Credit Card', '07-Mar-20', 'Bryce')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate],[Agent])

values ('Lower Bowl', 100.00, 'Cash', '07-Mar-20', 'Bryce')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate])

values ('Upper Deck', 50.00, 'Credit Card', '07-Mar-20')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate])

values ('Upper Deck', 50.00,'Credit Card', '07-Mar-20')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate],[Agent])

values ('Suite', 250.00, 'Cash', '14-Mar-20', 'Bryce')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate],[Agent])

values ('Sideline', 150.00,'Credit Card', '14-Mar-20', 'Olivia')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate])

values ('Lower Bowl', 100.00,'Credit Card', '14-Mar-20')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate])

values ('Lower Bowl', 100.00,'Cash', '14-Mar-20')

Insert into Tickets

([TicketSeatType],[TicketPrice],[TicketPayment],[GameDate],[Agent])

values ('Upper Deck', 50.00,'Credit Card', '14-Mar-20', 'Olivia')

Data:

City Boomers is a professional soccer team. City Boomers Database will keep track of the following:

For each game, gameid (unique), as well as game date and game opponent

For each sales transaction, stid (unique) and the payment method

For each sales person, spid (unique) and spname

For each merchandise, mid (unique), mtype and mprice

For each ticket, ticketid (unique), price, seat type, and payment method

For each agent, aid (unique), and agent name

For each advertiser, companyid (unique), and company name

Each sales transaction involves one sales person

Each sales person participates in between zero and many sales transactions

Each sales transaction includes one or more merchandise

Each merchandise is included in one or more transactions

For every instance of a merchandise included in a transaction, we keep track of the quantity of that merchandise in that transaction

Each sale transaction occurs in one game

Each game has one or more sales transactions occurring during it

Each ticket is sold for exactly one game

Each game has between zero and many tickets sold

We keep track of the number of tickets sold for each game

Each ticket is sold by one or none agents

Each agent sells between zero and many tickets

Each game has between zero and many advertisers promoting at it

Each advertiser promotes at between zero and many games

For each instance of an advertiser promoting at a game, we keep track of the fee charged to that advertiser to promote at that game

Each advertiser is a competitor with between zero and many other advertisers

Each advertiser has between zero and many other advertisers as competitors

Data for the City Boomers Database:

Two salespersons:

Mallory

Patrick

Two agents:

Bryce

Olivia

Five different types of merchandise for sale:

Jersey, cost: $100.00 each

Souvenir Ball, cost: $40.00 each

Water Bottle, cost: $15.00 each

T-Shirt, cost: $25.00 each

Autographed Picture, cost: $75.00 each

Five different advertisers:

Koma Cola, pays $200.00 per game

City Hardware, pays $250.00 per game

Showtime Grill, pays $150.00 per game

NAC Beverages, pays $300.00 per game

Day Street Diner, pays $100.00 per game

Sales transaction, two types:

Credit Card

Cash

Two games:

07-Mar-20 the home team Zoomers play the Loomers

oThe following advertisers pay for advertisement at this game:

Koma Cola

Showtime Grill

NAC Beverages

oThe following merchandise and the number of items were sold at this game by Mallory

Souvenir Ball, 2, paid by Credit Card

Water Bottle, 3, paid by Credit Card

Autographed Picture, 1, paid by Credit Card

oThe following merchandise and the number of items were sold at this game by Patrick

Souvenir Ball, 2, paid by Credit Card

14-Mar-20 the home team Loomers play the Zoomers

oThe following advertisers pay for advertisement at this game:

Koma Cola

NAC Beverages

Day Street Diner

oThe following merchandise and the amount were sold at this game by Patrick

T-Shirt, 5, paid by cash

Souvenir Ball, 1, paid by cash

Nine tickets sold:

Sideline for $150.00, Credit Card, Game on the 07-Mar-20, Agent is Bryce

Lower Bowl for $100.00, Cash, Game on the 07-Mar-20

Upper Deck for $50.00, Credit Card, Game on the 07-Mar-20, Agent is Bryce

Upper Deck for $50.00, Credit Card, Game on the 07-Mar-20

Suite for $250.00, Cash, Game on the 14-Mar-20, Agent is Bryce

Sideline for $150.00, Credit Card, Game on the 14-Mar-20, Agent is Olivia

Lower Bowl for $100.00, Credit Card, Game on the 14-Mar-20

Lower Bowl for $100.00, Cash, Game on the 14-Mar-20

Upper Deck for $50.00, Credit Card, Game on the 14-Mar-20, Agent is Olivia