Phase two
1. I did a very quick edit on a template wix website. The main thing Is that the customer should be
able to shop for cards and other games fairly easily. The draft of the website has news on
current nerd culture. The top bar having home, the about tab pertaining to the store, creatures
can be chaged to events, landscape can be turned into current news, and shop would take you
to the main shop page. The other media pages should link to the corresponding buttons
2. Creating the tables for the relations
a. Create table EMP ( emp_id int primary key, ename varchar(10));
b. Create table Customer (Cus_ID int primary key, C_name vrchar(10), email varchar (25),
balance dec(5,2));
c. Create cards ( Card_name string, set string, rarity string, foil string, price dec (5,2);
d. Create table inventory (item_id int, Item_name string, price dec (5,2);
e. Create table tournament (cus_Id int foreign key, format string, date date, table_num
int)
f. Create table workdays (emp_id int foreign key, day_work string);
3. Populate tables
a. Insert into employees values (‘Parker vi’, 1223)
b. Insert into employees values (‘Joey Jackson’, 2334)
c. Insert into customers values (135, ,kalen, ‘
[email protected]’, 23.12)
d. Insert into customers values (165, ‘ivy’, ‘
[email protected]’, 42.76)
e. Insert into cards values ( ‘thoughtcast’, ‘mirrodin’, ‘common’, ‘non-foil’,.35)
f. Insert into cards values (‘Teferi,timerbender’, ‘Dominaria’, ‘mythic’, ‘foil’, 1.36)
g. Insert into inventory values(76, ‘purple dragon shield sleeves’, 10.67)
h. Insert into inventory values(109, ‘chessex sunburst dice’, 7.29)
i. Insert into tournament values ( 135, ‘standard’, 19-MAR-2022, 2)
j. Insert into tournament values (165, ‘standard’, 19-MAR-2022, 2)
k. Insert into tournament values (165, ‘draft’, 20-MAR-2022, 4)
4. Implement sql statements
a. insert into customers values (198, ‘rowen’, ‘
[email protected]’,00.00);
i. insert into inventory ( item_id, name, price) values (132, ‘ss metal dice 7set’,
23.54), (154, ‘castle playmat’, 20.99);
ii. update cards set price-1.66 where card_name =’Teferi,timebender’ and
set=’Dominaria’;
iii. delete from customers where cus_id = 198;
b. select ename from EMP;
c. select card_name, set form cards where price>(select price from cards where
rarity=’mythic’);
d. select count(cus_id ) from tournament group by date order by date;
e. create view expensive_item as select Item_name from inventory where price>= 50.00
f. select Item_name from expensive_item where max(price)
g. select c.cus_id, c.C_name, t.cus_id, t.format, t.date, t.table_num from customers c
fullouter join tournament t on c.cus_id = t.cus_id;
h. select item_name, price from inventory where Item_name = any (select item_name
from inventory where item_name like (‘%sleeves%’))
i. select * from tournament where table =( select * from inventory where date in (select *
from tournament where format = ‘standard’
j. alter customers add constraint unique (email)
k. Create table tourney_matches (cus_id int, wins int, loses int, ties int, current_wins int,
current_loss)constraint tourney_matches_cus_id_fk foreign key(cus_id) references
customers(cus_id);
l. Create or replace trigger tourney_matchs after update of table_num on tournament
declare match_winner varchar(10); begin –find winner of person who won match and
update their the cus_stats table.
m. Create or replace procedure login is begin dbms_output.put_line(‘Please
type your employee number in’); end; / exec login; output Please type your employee
number in
Phase two report
I’m not sure what kind of constraints that are needed for this report so I’m just going to go
through them and explain the best I can.
The first part of the report is the e-r diagram. My diagram I submitted didn’t meet all the criteria of the
first phase of the projecet but works for what I need it to. The main thing with the e-r diagram was
establishing things that a card shop that holds events would need to run effectily. This includes info on
their customers and staff, which customers are currently playing in events, which customers are signed
up or already payed for up coming events, the current inventory of the shop, and the in store balences
of the customers. I got about half of that on the diagram because I didn’t know the bestg way to put it
on paper.
Chapter 2
With the transforimg of e-r iagram to relations there really weren’t any problems other then as im doing
this second phase that I wish I would have included more columens in each of the realtaions. I did some
normilazation having to do with the employees and the day they. I split the days they worked into
another table.
Employees (Name: string, Employee_ID: integer, days working: string)
Customer (cus_ID: integer, Name: string, Email: string, balance: integer)
cards (name: string, set: string, rarity: string(common, uncommon, rare, mythic rare), foil: string(non,
foil, full art, full art foil), price: integer)
Inventory ( item_ID: integer, name: string, price: integer)
Tournament (cus_ID: string, format: string, date: string, table_num: integer(1-12))
Chapter 3
For identify the keys and constraints I mainly stuck to keeping a identification of most tables with a
primary key. Each person whether employee or customer and an id number. The cards were
distinguishable by their name and what set they were printed in. the items in the inventory all had a
specific id number. Other then that the work day had a foreign key for employees and tournament for
customers so you could know who was participating in the events.
Chapter 4
Basically the point of the subqueries I did was to the prices of the mythic rarity cards in stock, find card
sleeves in stock, and find out who was playing against one another in the tournaments.
Chapter 5
Basically on the mock up website earlier in this document is the main page of the website. Should they
want to buy, sell, sign up for events, and a couple of things would all involve databases. Data on current
inventory, inventory that needs to be bought, inventory that can be aquaried, cards in stock, cards the
store is loocking to buy, customers information , customers store balance. Im not well versed in sql but if
there are links to social medias then clicking them would take you there .
