Some of your friends want to set up a new web site to sell Custom Made T-Shirts

Question

Some of your friends want to set up a new web site to sell Custom Made T-Shirts to the public. Rather than stock a range of pre-made t-shirts, they will make the t-shirts individually to order and hopefully fill a special demand in the clothing market that is not currently met. It will be called myTShirt. They have asked you to design a database to be used by the website to keep details of the products and options available, and also to record orders that are received from customers via the web site. The basic product they will sell will be, obviously, t-shirts. They will stock different styles of t-shirts – some styles will be tight, others loose and they may also sell polo tops as another type (The intend to add more tshirt types in future). As well as different styles of t-shirts there will also be different colours that customers can choose from. The t-shirts come with various type of sleeve, but mainly long or short. They will have a large range of images, logos etc that can be printed on the t-shirts. The database will need to store all these details so customers can be presented with all the choices when placing an order. When customers place an order, the order details will need to be stored into the database, as well as details of the customer. Naturally this will include details such as name, address and phone number. For each order they can order multiple t-shirts. For each t-shirt, they will be allowed to choose: -The style of the t-shirt -The sleeve configuration -The colour of the t-shirt -The size of the t-shirt -The text/image/logo to be printed Customers will be required to pay before the products are sent so details will need to be kept of the payment. Your friends are planning to accept payment by credit card, direct bank deposit and paypal. For credit cards they need to store the credit card number and expiry date, for direct deposit they need a field to tick off that the payment has appeared in their bank account and for paypal they again need a field to tick off plus the paypal user id of the payer.

UIREMENTS – HIGHER LEVEL – SQL IMPLEMENTATION

For additional marks, provide the SQL commands to create the tables and insert a few rows into each table. Also provide several business question and SQL queries to test out the tables, include at least a:

SELECTION condition query

GROUP BY query

JOIN query

NESTED query

Explanation / Answer

Table Creation Statements:-

create table customer(cid int primary key,cname varchar2(20), address varchar2(30),phone int,paymentid int,foreign key(paymentid) references payment(paymentid));

create table Tshirt(tid int primary key, style varchar2(10),sleeve char(3),color varchar2(10),size int,price int,logotype varchar2(10))

create table order (oid int primary key,amount int,orderdate date,tid int,cid int, foreign key(tid) references Tshirt(tid),foreign key(cid) references customer(cid));

create table payment(paymentid int primary key,type varchar2(10),cardno int,expdate date,payeruserid int,paymentstatus varchar2(7),oid int, foreign key(oid) references order(oid));

Insert statements:-
Customer Table:-
insert into customer values(01,'john','toronto',098765,111);
insert into customer values(02,'jonathan','mexico',635445,112);
insert into customer values(03,'jamie','mishigan',123456,113);

Tshirt Table:-
insert into Tshirt values (201,'long','Yes','Red',39,1000,'Text');
insert into Tshirt values (202,'short','No','Yellow',40,800,'Logo');
insert into Tshirt values (203,'loose','No','Green',42,1200,'Image');

Order Table:-
insert into order values(1,1000,'SYSDATE',201,01);
insert into order values(2,800,'SYSDATE',203,02);
insert into order values(3,1200,'SYSDATE',202,03);

Payment Table:-

insert into payment values(111,'Credit Card',123456789,'12-2-2019',' ','Done',1);
insert into payment values(111,'Cash',Null ,' ',' ','Done',2);
insert into payment values(111,'Paypall',Null ,'12-2-2019','user1@pp ','Done',3);

Business Queries :-
Display the username who has done payment by credit card?
select cname from customer c,payment p where c.pid=p.pid and p.type='Credit Card';

Display users count information group by the Tshirt type?
select count(c.cid), o.type from customer c,order o where c.cid=o.cid group by o.type;

Display the orders and their respective Tshirt information
select o.oid,t.tid,t.type from Tshirt t join Order o on o.tid=t.id;

Display all the orders paid using Credit card.
select o.oid,p.paymentid,p.paymenttype from order o,payment p where o.oid in(select oid from payment where paymenttype='Credit Card');

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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