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.

REQUIREMENTS – PASS/CREDIT – DATA MDOEL

Create an ER diagram, relational model and any business rules or assumptions made.

REQUIREMENTS – 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

Create Table:

create table person(pid varchar(3) primary key,firstName varchar(20),lastName varchar(40));

Insert Rows:

insert into person values('p01','Alex','Smith');
insert into person values('p02','John','Smith');
insert into person values('p03','Robert','Green');
insert into person values('p04','Peter','Lobo');
insert into person values('p05','James','Anderson');

SELECTION condition query

select * from person where firstName='John';

GROUP BY query

select COUNT(pid),lastName from person group by lastName;

JOIN query

NESTED query

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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