THE ART EFFECTS Team Data Busters Nikita Tripathi

THE ART EFFECTS Team Data Busters, Nikita Tripathi Aditya Kulkarni

Introduction About The Art Effects: • • The Art Effects is a fictitious home décor store, owned by Mr. John Doe, located at Mid-Hudson market on 2585 South Road, Poughkeepsie, NY. It is a one stop shop for fresh and new home décor items. The Art Effects store specializes mainly in Home Décor; and is spread over massive 3500 square feet. With exclusive range of Artifacts, Vases, Sculptures, Picture Frames, Artificial Flowers, Candles, and many more products in the offering at affordable prices.

Introduction (cont. ) Business Need for Project: • • • Idea incepted from the fact that the sales were not as expected even after two years of its establishment. The way to increase sales was to expand the business and reach out to wider audience and potential target customers, thus generating more revenue in return. The best possible and cheap way identified to accomplish this was by developing an ‘e-commerce’ website for the store. We had a meeting with the client and decided on how to proceed with the website, what features the website shall have, etc.

Introduction (cont. ) Business opportunities: • • • Online Brochure More Customers 24/7 Availability Communication with customers Social media marketing Customer support

Project Vision Statement: “Our vision is to be the America’s most customer centric online home décor retail company; to build a place where customers can come to find and discover any home décor items they might want to buy online and at affordable prices. ”

Project Vision (cont. ) Project Features: • • • The website will have an Administrator Control Panel feature, which can be used only by the store admin. Using this control panel, the admin can add, update, and delete various products (based on their category) and their information in the database. All this products related information stored in the database would be displayed to the users on the website. Users can create their account, by registering on the website.

Project Vision (cont. ) Project Features (cont. ): • • Users can add products to the shopping cart and wish-list. Users can live chat with store representative (as per availability). Users can contact the store by sending any relevant inquiry through Contact us page. FAQ page will have answers to all the basic customer queries.

Project Vision (cont. ) Project Scope: • • The initial release of the website won’t have any third-party payment gateway integrated within the website. The delivery of the products is limited to the United States only.

Business Rules and Cardinality of relationship between entities

Business Rules • Business Rules: For The Art Effects Store Rule No Business Rule Cardinality 1) The STORE is owned by only one PERSON (Mr. (1, M) John Doe); the PERSON may one or more STOREs. 2) The STORE employs many EMPLOYEEs; each EMPLOYEE is employed by one STORE only. (1, M) 3) The STORE is managed by only one EMPLOYEE; only one EMPLOYEE manages the STORE. (1, 1)

Business Rules (cont. ) • Business Rules: Store Admin and the store database (website’s control panel) Rule No Business Rule Cardinality 1) The CONTROL_PANEL can be accessed by store ADMIN only; only the ADMIN has privilege to access the CONTROL_PANEL. (1, 1) 2) The LOGIN_DETAILS for the control panel can be updated by ADMIN only; ADMIN alone has rights to update the control panel LOGIN_DETAILS (password). (1, 1) 3) The ADMIN can insert, update, and delete one or more PRODUCT_CATEGORIES; each PRODUCT_CATEGORY can be inserted, updated, and deleted to/from database by the ADMIN only. (1, M) 4) The ADMIN can insert, update, and delete one or more PRODUCT_MATERIALs; each PRODUCT_MATERIAL can be inserted, updated, and deleted to/from database by the ADMIN only. (1, M) 5) The ADMIN can insert, update, and delete one or more PRODUCTs, along with their details, belonging to particular PRODUCT_CATEGORY and made up of specific PRODUCT_MATERIAL; each PRODUCT can be inserted, updated, and deleted by the ADMIN only (1, M)

Business Rules • Business Rules: Stating Relationship between Products Rule No Business Rule Cardinality 1) One PRODUCT_CATEGORY can contain one or more PRODUCTS; each PRODUCT can belong to only one PRODUCT_CATEGORY. (1, M) 2) One PRODUCT can be made up of only one PRODUCT_MATERIAL; each PRODUCT_MATERIAL can have multiple products. (1, M)

Business Rules (cont. ) • Business Rules: For Customers Rule No Business Rule Cardinality 1) A CUSTOMER can have one or more ACCOUNTs (using multiple email ids) on the website; each ACCOUNT belongs to one CUSTOMER only. (1, 1) 2) A CUSTOMER may send zero or more ENQURIEs to the store via contact-us page; each ENQUIRY is sent by one CUSTOMER only. (0, M) 3) A CUSTOMER is provided with one WISHLIST to save his/her favorite items; each WISHLIST belongs to one CUSTOMER account only. (1, 1) 4) A CUSTOMER is provided with one SHOPPING_CART to add the items he/she wants to purchase; each SHOPPING_CART belongs to one CUSTOMER account only. (1, 1) 5) A CUSTOMER can place one or more ORDERs at a time; each ORDER is placed by one CUSTOMER only. (1, M)

Business Rules (cont. ) • Business Rules: For Customers (cont. ) Rule No Business Rule Cardinality 6) A CUSTOMER can insert, update, and delete multiple PRODUCTs from the wish-list; each PRODUCT can be inserted, updated, and deleted from the wish -list by one CUSTOMER only (the account holder). (1, M) 7) A CUSTOMER can insert, update, and delete multiple PRODUCTs from the shopping cart; each PRODUCT can be inserted, updated, and deleted from the shopping cart by one CUSTOMER only (the account holder). (1, M) 8) A ORDER can consist of one or more products from SHOPPING_CART; one or more products in SHOPPING_CART can make up one ORDER only. (1, M)

Entities, ER- Diagrams and RELATIONSHIPS

Entities 1) 2) 3) 4) ADMIN (A_ID, A_NAME, A_PASSWORD, A_STATUS, A_TIMESTAMP) : responsible for inserting, deleting, and updating the products via website control panel into the database. PRODUCT_CATEGORY (CAT_ID, CAT_NAME, CAT_IMAGE, CAT_TIMESTAMP) : different product categories would be Artifacts, Vases, Wall Clocks, Antiques, Name plates, etc. PRODUCT_MATERIAL (MAT_ID, PROD_MAT, MAT_STATUS, MAT_DATE): different product materials would be wooden, glass, acrylic, bronze, etc. PRODUCT (PROD_ID, CAT_ID (FK), PROD_NAME, PROD_CODE, PROD_DESC, PROD_LENGTH, PROD_WIDTH, PROD_HEIGHT, PROD_DIAMETER, PROD_WEIGHT, PROD_QUANTITY, PROD_MAT, PROD_IMAGE, PROD_PRICE, PROD_TIMESTAMP)

Entities (cont. ) 1) 2) 3) 4) CUSTOMER (USER_NUM, USER_FNAME, USER_LNAME, USER_EMAIL, USER_PASS, USER_REPASS, USER_AREACODE, USER_PHONE, USER_ADDR, USER_CITY, USER_STATE, USER_ZIP, USER_DOR) SHOPPING_CART (CART_ID, USER_NUM, PROD_ID, PROD_QUANTITY, DATE) WISHLIST (WISH_ID, USER_NUM, PROD_ID, DATE) ORDER (ORDER_NUM, USER_NUM, PROD_ID, ORDER_TOTAL, ORDER_DATE)

Entity Representation

Entity Representation (cont. )

ERD 1 : Crow’s Foot Notation

ERD 2 : Crow’s Foot Notation

Table Structure (DB Schema)

Sample Queries • • • create database db_artstore use db_artstore create table tbl_login(id int primary key identity(1, 1), a_name varchar(50), a_password varchar(30), a_status varchar(10), a_timestamp datetime) • insert into tbl_login values('Admin', '1234', 'Active', GETDATE()) • create procedure change. Password ( @a_name varchar(50), @a_password varchar(30) ) as update tbl_login set a_password=@a_password where a_name=@a_name

Sample Queries • create table tbl_Product. Category (Cat_Id int primary key identity(1, 1), Category_Name varchar(20), Cat_Image varchar(max), Time. Stamp datetime) • create table tbl_Product. Material (Mat_Id int primary key identity(1, 1), Prod_Material varchar(20), Status varchar(10), Time. Stamp datetime) • create table tbl_userreg(user_num int primary key identity(1, 1), user_fname varchar(20), user_lname varchar(30), user_email varchar(20), user_pass varchar(30), user_repass varchar(30), user_areacode int, user_phone varchar(15), user_staddress varchar(30), user_city varchar(30), user_state varchar(30), user_zipcode varchar(20), user_DOR date) • create table tbl_Product. Details (Prod_id int primary key identity (1, 1), Cat_Id int foreign key references tbl_Product. Category (Cat_Id), Prod_Name varchar(100), Prod_Code varchar(30), Prod_Desc varchar(max), Prod_Length decimal(18, 2), Prod_Width decimal(18, 2), Prod_Height decimal(18, 2), Prod_Diameter decimal(18, 2), Prod_Weight decimal(18, 2), Prod_Quantity decimal(18, 2), Prod_Mat varchar(50), Prod_Image varchar(max), Prod_Price decimal(18, 2), Prod_Timestamp datetime)

Sample Queries create procedure Insert_Product. Details ( @Cat_Id int, @Prod_Name varchar(max), @Prod_Code varchar(max), @Prod_Desc varchar(max), @Prod_Mat varchar(max), @Prod_Length decimal(18, 2), @Prod_Width decimal(18, 2), @Prod_Height decimal(18, 2), @Prod_Weight decimal(18, 2), @Prod_Quantity decimal(18, 2), @Prod_Diameter decimal(18, 2), @Prod_Price decimal(18, 2), @Prod_Image varchar(max) ) as insert into tbl_Product. Details ( Cat_Id, Prod_Name, Prod_Code, Prod_Desc, Prod_Mat, Prod_Length, Prod_Width, Prod_Height, Prod_Weight, Prod_Quantity, Prod_Diameter, Prod_Price, Prod_Image, Prod_Timestamp) values (@Cat_Id, @Prod_Name, @Prod_Code, @Prod_Desc, @Prod_Mat, @Prod_Length, @Prod_Width, @Prod_Height, @Prod_Weight, @Prod_Quantity, @Prod_Diameter, @Prod_Price, @Prod_Image, GETDATE())

Sample Queries create procedure Delete_Product. Details ( @Prod_id int ) as delete tbl_Product. Details where Prod_id=@Prod_id create procedure Delete_User ( @User_num int ) as delete tbl_userreg where user_num=@User_num

Application Screenshots:

Team Member Bios Name: Ms. Nikita Tripathi Role: Project Manager Description: arrange meetings with the client, create and maintain project plan, assign tasks to members, take weekly follow-up to keep check on the deadlines, and report project status to client. Name: Mr. Aditya Kulkarni Role: Web and Database Developer Description: To develop the dynamic web-application in ASP. NET with C# as code-behind language and develop the database in MS SQL Server 2008 as backend for the project.

Team Member Experience • Nikita Tripathi: A project manager who is the primary cause of a project to be carried out in a desired way has the greatest role and I got to learn a lot about project management with a small group of my team members, it was pleasure working with all of them. • Aditya Kulkarni: I had wonderful experience, throughout the semester, working with my team-mates. Everyone was co-operative and completed their assigned tasks on time.

Acknowledgement: We would like to express our special thanks of gratitude to Prof. Alex Macur who gave us the golden opportunity to do this wonderful project on the topic ‘The Art. Effects’, which also helped us in doing a lot of research and we came to know about so many new things. We are really thankful to him. Secondly, we would also like to thank each other and our friends who helped us in finalizing this project within the limited time frame.

THANK YOU. !!