Statement of Work (SOW): Adeoye Gym Fitness Tracking System Overview: The goal of the project is to design and develop a database system for Adeoye Gym that will help keep track of a customer’s overall fitness journey. Adeoye Gym currently has one gym location. The gym is looking to expand operations by launching 10 facilities in Milwaukee, WI. In order, to keep up with competition, Adeoye Gym wants to create a tailored fitness experience for its customers. The gym will offer nutrition advice, fitness classes, fitness exercises, fitness coaching, supplements, mental exercises, and fitness equipment. In addition, the customer will be able to interact with coaches virtually. In order, for customers to maximize the full benefits of the services offered by the gym, customers will be required to create online accounts. Purpose and Objective: Adeoye Gym needs clients to enter health, fitness, and personal information so the gym can take a tailored approach in tracking a specific clients fitness journey. The database will collect and store information on dates of exercise, specific exercise done, type of exercise, weight, type of diet, repetition of specific exercise for instance, 10 repetitions of push-ups. The information gathered will enable fitness coaches to inspect and adapt to the client's fitness journey. For instance, a coach will have to adjust and refine a client's diet and exercise after a week of no improvements. Project Scope: The new fitness tracking system will be created for use of all Adeoye Gyms. Features of the new system must be able to provide virtual coaching between instructor and client, nutritional information on various foods, calorie counter, body measurements, fitness timeline, names of exercises, videos of how to do various exercises. Out of scope: The new system will provide a reward program in the future. A program for members that achieve their fitness goals that will give rewards to members who achieve and maintain their fitness goals. Also, video journals of each client's fitness journey will be captured and shared with other members for informational, educational, and motivational purposes. Database Goals, Expectations, and Deliverables (10 points) Upon completion of the system, the tracking system will give Adeoye Gym a competitive edge over the competition. Customers will be able to log into the mobile application or website and in real time monitor metrics, track performance, and adjust goals. The database is expected to have a high degree of functionality, quality, and security with a low tolerance for downtime. The system is expected to be delivered on December 12, 2021, it will take 6 months to complete the project. Database Benefits The business wants to keep the customer happy. Connecting with the customer from a tailored perspective shows care and passion for fitness. Keeping the customer happy will in turn generate revenue and create expansion opportunities once customers testify to the value the new tracking system will provide. For the customer, ease of use, convenience, opportunity to quickly adapt fitness goals are all merits for the customer. Overall, the tracking system is providing a customized way to tackle obesity in the USA. Diagram Tool: The data modeling tool that will be used to create and provide an entity relation diagram is ER assistant. The ERD will show how the relationships and cardinality that connects them. Also, the ERD will provide a visual representation of the fitness tracking database and show the rational structure of the database. Database: Oracle 11g database will be accessed using virtual desktop applications. Hardware and Software: Virtual Desktop Application- This is a server that allows a connection into a personal computer. The Virtual machine provided by UMGC will be used. Citrix Receiver will be the link between the virtual application and client computer (MAC OS) ER assistant is the software that will be used for creating the ERD DDL and DML: Structured query language (SQL) will be used for the data definition language and data manipulation language. SQL will be used in building the tables in the tracking system. Also, SQL will be used to translate information like entities, relationships, and attributes in the ERD into tables. Project 2: Requirements Definition Document Business Rules 1) A gym may have a member enrolled at one gym location exclusively or have membership access to several other gym locations. 2) Members can only be assigned to one type of coach 3) Members must be assigned to a nutritionist 4) Entities: GYMS, MEMBERS, NUTRITIONIST, EXERCISE, AND COACH. Entity Name: GYMS Description: Gym Primary key: GYM ID Attributes of GYM ID: GYM1, GYM2, GYM3, GYM MANAGER Entity Name: MEMBERS Description: Members can be enrolled exclusively in one gym location or have access to other locations Primary Key: MEMBER ID Attributes of MEMBER ID: First Name, Last Name, DOB, EMAIL, PHONE NUTRITIONIST Description: Each member will be assigned a nutritionist who will prescribe a special type of diet to aid fitness Primary Key: Nutritionist ID Attributes: First Name, Last Name, Keto, Low carb, and vegan EXERCISE Description: Many members can participate in one or many types of exercises Primary Key: Exercise ID Attributes: Exercise ID Swimming, Boxing, Running, Weightlifting, Cycling COACH Description: One specialty coach can coach multiple members. Each member must have a coach. Multiple members can have the same coach Primary Key: COACH ID Attributes: First Name, Last Name, Strength Coach, Cardio Coach Relationships and Cardinality Relationship 1: M- GYM and MEMBER Cardinality/ Business Rule a gym shall provide service to one or many members Relationship 1: M- MEMBERS and MEMBER Cardinality/ Business Rule a gym shall provide service to one or many members Data Definition Language (DDL) SQL Script 1: Drop Statements for All Objects as needed (5 points) at the beginning of your script, ensure that all objects that are required to be dropped are properly dropped. Answer: In this section we will drop the table which is not useful for our database and we use the Drop key word using in My SQL with table name. Using the database DLD we have drop the persons table for add some useful tables for our future work 2: 1. Create/Alter Statements for All Tables and Constraints (30 points) Database: `dld` --- -------------------------------------------------------- --- Table structure for table `classes` -CREATE TABLE `classes` ( `cls_id` int(11) NOT NULL, `cls_name` varchar(255) NOT NULL, `cls_add` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------- Table structure for table `coaching` -CREATE TABLE `coaching` ( `c_id` int(11) NOT NULL, `c_name` varchar(255) NOT NULL, `c_mobile` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------- Table structure for table `fitness` -- CREATE TABLE `fitness` ( `f_id` int(11) NOT NULL, `f_desc` varchar(255) NOT NULL, `f_type` int(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------- Table structure for table `gyms` -CREATE TABLE `gyms` ( `g_id` int(11) NOT NULL, `g_desc` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------- Table structure for table `members` -CREATE TABLE `members` ( `m_id` int(11) NOT NULL, `m_add` varchar(255) NOT NULL, `m_name` varchar(255) NOT NULL, `m_email` varchar(255) NOT NULL, `m_pass` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------------------------------------------------------- Table structure for table `nutrition` -CREATE TABLE `nutrition` ( `n_name` int(255) NOT NULL, `n_desc` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --- Indexes for dumped tables ---- Indexes for table `classes` -ALTER TABLE `classes` ADD PRIMARY KEY (`cls_id`); --- Indexes for table `coaching` -ALTER TABLE `coaching` ADD PRIMARY KEY (`c_id`); --- Indexes for table `fitness` -ALTER TABLE `fitness` ADD PRIMARY KEY (`f_id`); --- Indexes for table `gyms` -ALTER TABLE `gyms` ADD PRIMARY KEY (`g_id`); --- Indexes for table `members` -ALTER TABLE `members` ADD PRIMARY KEY (`m_id`); --- Indexes for table `nutrition` -ALTER TABLE `nutrition` ADD KEY `n_name` (`n_name`); --- Constraints for dumped tables ---- Constraints for table `gyms` -ALTER TABLE `gyms` ADD CONSTRAINT `gyms_ibfk_1` FOREIGN KEY (`g_id`) REFERENCES `classes` (`cls_id`); --- Constraints for table `nutrition` -ALTER TABLE `nutrition` ADD CONSTRAINT `nutrition_ibfk_1` FOREIGN KEY (`n_name`) REFERENCES `fitness` (`f_id`); 3: 1. Create Indexes for Natural, Foreign Key, and Frequently Queried Columns (10 points) Answer: List of indexes for all choices: 4: 1. Create a Minimum of Two Views (10 points) 5: 1. Create a Minimum of Two Sequences (10 points) 2. Answer: 3. 4. 5. 6. 6: 1. 2. Create a Minimum of Two Triggers (10 points) 7: 1. Describe the Business Purpose of your Views and Triggers (5 points) Business purpose of Views: No need to apply same query again and again No need to create separate table for view Views are auto updated Provides better view to business table Business purpose of Triggers: To insert automate To make auto operations Fast business query operations 2. 8: 1. Database Catalog/Data Dictionary Queries (5 points) 9: 1. 2. 1: 2: 3: 4: Output Report (5 points) 5: 6: 7: Business purpose of Views: No need to apply same query again and again No need to create separate table for view Views are auto updated Provides better view to business table Business purpose of Triggers: To insert automate To make auto operations Fast business query operations 8: 10: Executable, Error-Free Script (10 points) -- Database: `dld` --- ---------------------------------------------------------- Stand-in structure for view `business1` -- (See below for the actual view) -CREATE TABLE `business1` ( `m_id` int(11) ,`m_add` varchar(255) ,`m_name` varchar(255) ,`m_email` varchar(255) ,`m_pass` varchar(255) ); -- ---------------------------------------------------------- Stand-in structure for view `business2` -- (See below for the actual view) -CREATE TABLE `business2` ( `c_id` int(11) ,`c_name` varchar(255) ,`c_mobile` varchar(255) ); -- ---------------------------------------------------------- Table structure for table `classes` -CREATE TABLE `classes` ( `cls_id` int(11) NOT NULL, `cls_name` varchar(255) NOT NULL, `cls_add` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='data dictionaries'; --- Dumping data for table `classes` -INSERT INTO `classes` (`cls_id`, `cls_name`, `cls_add`) VALUES (1, 'msc', 'cs department'); -- ---------------------------------------------------------- Table structure for table `coaching` -CREATE TABLE `coaching` ( `c_id` int(11) NOT NULL, `c_name` varchar(255) NOT NULL, `c_mobile` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --- Dumping data for table `coaching` -INSERT INTO `coaching` (`c_id`, `c_name`, `c_mobile`) VALUES (1, 'msc', '225235'); -- ---------------------------------------------------------- Table structure for table `fitness` -CREATE TABLE `fitness` ( `f_id` int(11) NOT NULL, `f_desc` varchar(255) NOT NULL, `f_type` int(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `fitness` -INSERT INTO `fitness` (`f_id`, `f_desc`, `f_type`) VALUES (452, 'fit', 1); -- ---------------------------------------------------------- Table structure for table `gyms` -CREATE TABLE `gyms` ( `g_id` int(11) NOT NULL, `g_desc` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --- Dumping data for table `gyms` -INSERT INTO `gyms` (`g_id`, `g_desc`) VALUES (1, 'xyz'); --- Triggers `gyms` -DELIMITER $$ CREATE TRIGGER `test2` BEFORE INSERT ON `gyms` FOR EACH ROW set @sum = @sum+g_desc $$ DELIMITER ; -- ---------------------------------------------------------- Table structure for table `members` -CREATE TABLE `members` ( `m_id` int(11) NOT NULL, `m_add` varchar(255) NOT NULL, `m_name` varchar(255) NOT NULL, `m_email` varchar(255) NOT NULL, `m_pass` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --- Dumping data for table `members` -INSERT INTO `members` (`m_id`, `m_add`, `m_name`, `m_email`, `m_pass`) VALUES (444, 'usa', 'david', 'davic@gmail.com', 'qwer'); --- Triggers `members` -- DELIMITER $$ CREATE TRIGGER `test` BEFORE INSERT ON `members` FOR EACH ROW set @sum = @sum+new.m_pass $$ DELIMITER ; -- ---------------------------------------------------------- Table structure for table `nutrition` -CREATE TABLE `nutrition` ( `n_name` int(255) NOT NULL, `n_desc` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --- Dumping data for table `nutrition` -INSERT INTO `nutrition` (`n_name`, `n_desc`) VALUES (452, 'full'); -- ---------------------------------------------------------- Structure for view `business1` -- DROP TABLE IF EXISTS `business1`; CREATE ALGORITHM=UNDEFINED DEFINER=`view1`@`%` SQL SECURITY DEFINER VIEW `business1` AS SELECT `members`.`m_id` AS `m_id`, `members`.`m_add` AS `m_add`, `members`.`m_name` AS `m_name`, `members`.`m_email` AS `m_email`, `members`.`m_pass` AS `m_pass` FROM `members` WHERE 1 ; -- ---------------------------------------------------------- Structure for view `business2` -DROP TABLE IF EXISTS `business2`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `business2` AS SELECT `coaching`.`c_id` AS `c_id`, `coaching`.`c_name` AS `c_name`, `coaching`.`c_mobile` AS `c_mobile` FROM `coaching` WHERE `coaching`.`c_name` = 'david' ; --- Indexes for dumped tables ---- Indexes for table `classes` -ALTER TABLE `classes` ADD PRIMARY KEY (`cls_id`), ADD UNIQUE KEY `cls_add` (`cls_id`,`cls_name`,`cls_add`); --- Indexes for table `coaching` -ALTER TABLE `coaching` ADD PRIMARY KEY (`c_id`); --- Indexes for table `fitness` -ALTER TABLE `fitness` ADD PRIMARY KEY (`f_id`); --- Indexes for table `gyms` -ALTER TABLE `gyms` ADD PRIMARY KEY (`g_id`), ADD UNIQUE KEY `test` (`g_desc`), ADD KEY `g_desc` (`g_desc`); --- Indexes for table `members` -ALTER TABLE `members` ADD PRIMARY KEY (`m_id`); --- Indexes for table `nutrition` -ALTER TABLE `nutrition` ADD KEY `n_name` (`n_name`); --- AUTO_INCREMENT for dumped tables ---- AUTO_INCREMENT for table `members` -ALTER TABLE `members` MODIFY `m_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=445; --- Constraints for dumped tables ---- Constraints for table `gyms` -ALTER TABLE `gyms` ADD CONSTRAINT `gyms_ibfk_1` FOREIGN KEY (`g_id`) REFERENCES `classes` (`cls_id`); --- Constraints for table `nutrition` -ALTER TABLE `nutrition` ADD CONSTRAINT `nutrition_ibfk_1` FOREIGN KEY (`n_name`) REFERENCES `fitness` (`f_id`); COMMIT;
Purchase answer to see full attachment