CREATE TABLE CUSTOMER_PROFILE (CUST_ID VARCHAR(10),FIRST_NAME VARCHAR(25),LAST_NAME VARCHAR(25),GENDER CHAR(1),STREET VARCHAR(30),APTNO VARCHAR (20),ZIPCODE NUMERIC(7),DOB DATETIME,CONSTRAINT CUSTOMER_PROFILE_PK PRIMARY KEY (CUST_ID)
CREATE TABLE DEPT_REPOSITORY (DEPT_ID VARCHAR(10),ACTIVITY_TYPE VARCHAR(25),ACTIVITY_PLAN VARCHAR(25),ACTIVITY_PRICE NUMERIC(10,2),CONSTRAINT DEPT_REPOSITORY_PK PRIMARY KEY (DEPT_ID)
CREATE TABLE EMPLOYEES(EMPID VARCHAR(10),DEPT_ID VARCHAR(10),EMPLOYEE_NAME VARCHAR(30),DESIGNATION VARCHAR(30),CONSTRAINT EMPLOYEES_PK PRIMARY KEY (EMPID),CONSTRAINT EMPLOYEES_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID)
CREATE TABLE CUSTOMER_DEPT (CUST_ID VARCHAR(10),DEPT_ID VARCHAR(10),CUST_ACTIVITY_TYPE VARCHAR(25),CUST_ACTIVITY_PLAN VARCHAR(25),PLAN_JOIN_DATE DATETIME,PLAN_TERMINATION_DATE DATETIME,TRAINER_ID VARCHAR(10),CONSTRAINT CUSTOMER_DEPT_pk PRIMARY KEY(CUST_ID,DEPT_ID),CONSTRAINT CUSTOMER_DEPT_CUST_ID_fk FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER_PROFILE(CUST_ID),CONSTRAINT CUSTOMER_DEPT_DEPT_ID_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID),CONSTRAINT CUSTOMER_DEPT_TRAINER_ID_fk FOREIGN KEY(TRAINER_ID) REFERENCES EMPLOYEES(EMPID)
CREATE TABLE ACCOUNTS (CUST_ID VARCHAR(10),DEPT_ID VARCHAR(10)CUST_TOT_COST NUMERIC(10,2),CONSTRAINT ACCOUNTS_pk PRIMARY KEY(CUST_ID,DEPT_ID),CONSTRAINT ACCOUNTS_CUSTID_fk FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER_PROFILE(CUST_ID),CONSTRAINT ACCOUNTS_DEPT_ID_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID),
CREATE TABLE INVENTORY(PROD_ID VARCHAR(10),PROD_NAME VARCHAR(25),DEPT_ID VARCHAR(10),PROD_COST NUMERIC(10,2),PROD_QUANT NUMERIC (5),CONSTRAINT INVENTORY_pk PRIMARY KEY(PROD_ID),CONSTRAINT INVENTORY_DEPT_ID_fk FOREIGN KEY(DEPT_ID) REFERENCES DEPT_REPOSITORY(DEPT_ID)
CREATE TABLE CUSTOMER_LOGIN(CUST_USERNAME VARCHAR(20),CUST_ID VARCHAR(10),CUST_PWD VARCHAR (20),CONSTRAINT CUSTOMER_LOGIN_pk PRIMARY KEY(CUST_USERNAME),CONSTRAINT CUSTOMER_LOGIN_CUSTID_fk FOREIGN KEY(CUST_ID) REFERENCES CUSTOMER_PROFILE(CUST_ID));););););););
Wednesday, March 30, 2011
Schema Representation
Customer_Profile(cust_id, first_name, last_name, gender, street, aptno, zipcode, dob);
Dept_Repository(dept_id, activity_type, activity_plan, activity_price);
Customer_Dept(cust_id, dept_id, cust_activity_type, cust_activity_plan, plan_join_date, plan_termination_date, trainer_id);
Accounts(cust_id, dept_id, cust_tot_cost);
Inventory(prod_id, dept_id, prod_name, prod_cost, prod_quant);
Customer_Login(cust_username, cust_id, cust_pwd);
Employees(emp_id, dept_id, employee_name, designation);
Below is the diagrammatical representation of the Schema:
System Modules
Ø User/login Module: Authenticates the user logging in as either an Administrator or Customer. This module is subdivided into the following two:
· Administrative Module: Deals with the management of customers, inventory, staff; through this login he/she can update the membership plans.
· Customer Module: The registered customers can view their profile, facilities he/she has registered for and update the same.
Ø Registration Module: This module covers the first time registration of customers.
Ø Inventory Module: This module is accessible only to the health club administrators. It enables them to track and manage the equipments concerned with each department.
Warm up!!!!
We were all oriented towards thinking along the same lines, we wanted to implement a real world scenario, a system which would be of practical use. While brainstorming we filtered out the systems which according to us were already in place, like online shopping. All of us being fitness freaks, it was not surprising that we came up with the Health Club Management System. This system models a fitness studio administration software which provides the customers with an easy to register interface and helps navigate through the offered facilities at fingertip. The system also touches the managerial aspect, in that it allows the employers to keep a tab, on the inventory like the equipments required.
Project Overview
Our project implements an online registration system for a health club. The functionality encompasses a 2 dimensional perspective which includes Customers and Administrator.
The system provides the customer with a user friendly environment for navigating through the various facilities provided by the health club i.e., online registration for new customers, view and update profile, as well as the plans he/she has registered for.
Administrator is a central governing entity which has access to customer, staff, inventory data as well as the various plans that the health club has to offer. Administrator will maintain the customer, staff related data, keep a track of inventory and will also be responsible for managing the various plans offered by the health club.
The system simulates software used to maintain the required data at a real world health club.
The project aims at projecting the abilities provided by SQL Server 2005 for managing large amounts of data while at the same time providing an efficient way of retrieving. The Health Club Management System requires storage and quick retrieval of various categories of data like customer, inventory etc. The front end provides source data/information which is then segregated into the various departments modelled using SQL Server 2005.
Subscribe to:
Posts (Atom)