Case study with SQL and ERD

User Generated

fnjlrew

Computer Science

Description

With the given case study:

After reviewing the case study, create at least 3 queries, 1 report, and final database documentation.

Items to be included with your submission

Your submission should contain a Microsoft Word document of at least 10 - 12 pages, containing all components from Modules 01 through 05, (this does not include the cover page, table of contents, and references), you must include ALL items below for your final project submission, with headers or page breaks separating each item.

Your submission should include the following:

  • Title Page containing the project title, your name, and date of the assignment.
  • Table of Contents:
  • Project Review Document
    • Functional Requirements
    • Database System Description
    • Flowcharts or graphics illustrating the current and future processes
  • Data Gathering Document
    • At least one sample report. This should include column headers and descriptions, and sample data.
    • Entity relationship diagram (ERD), explanation of the entities, attributes, and data types.
    • Sample SQL code (must be saved in *.txt or *.sql format).
  • Data Modeling and ERD
    • Final entity relationship diagram (ERD), explanation of the entities, attributes, and data types.
    • Updated SQL code (must be saved in *.txt or *.sql format).
  • Relational Tables
    • Updated SQL code (must be saved in *.txt or *.sql format) showing primary keys and related tables.
    • SQL code based on your ERD that will be used to begin creating your database and tables. Demonstrate your ability to make sophisticated use of information including the case study document, text materials, and other resources. You will utilize file transfer protocols (FTP) to both download and upload files to a website, database, or repository to begin building your database in the next module. SQL code must be saved in *.txt or *.sql format.
  • Your code must also include comments that describes the purpose of the code. For example:

    /*Builds [TABLE NAME] which contains customer information.*/PHP code used to create a web interface to your database. Each PHP web page needs to be in *.php format.
  • References Page - Include references and links to your research sources. Use APA documentation style consistently to cite any sources in your research.

Did you include the merchandise table and monetary transactions? Did you include the boys and girls exercises/events, which are different (parallel bars or uneven bars)? Did you include the parents table? If not, then add them to the ERD and SQL. It is not required to input values into your tables. Also, it is not required to generate a query, but you may add those if you like.

Lastly, the instructions say your code must also include comments that describes the purpose of the code. For example:


  • /*Builds [TABLE NAME] which contains customer information.*/

PART 2:

Now that you've completed your database project and documentation, discuss at least 3 lessons you've learned during this process. These lessons can relate to the technical portion of the project or the documentation you've developed. What did you find most difficult or challenging? Were there specific helpful resources that you've used? What were some methods of troubleshooting any technical issues you've experienced?

Your document should be at least 2-3 pages in length.

Unformatted Attachment Preview

Journal of Information Systems Education, Vol. 16(4) Teaching Case Gr eta's Gym: A Teaching Case for Ter m-Long Database Pr ojects Gina C. Gr een Department of Information Systems Baylor University Waco, TX 76798-8005 USA Gina_Green@baylor.edu ABSTRACT The following case supports a term-long team project for a senior-level or graduate-level Database course. It describes a gymnastics facility whose business processes are primarily manual. Students design and implement a database supporting the automation of competitive gymnastics team processes. In the analysis and design phase, students gather data from real gyms, produce a conceptual data model, design and normalize relational tables, document field definitions, and calculate storage space requirements. In the implementation phase, students write instructions for installing and using their tables, implement the tables, and write queries, triggers, and stored procedures that manipulate data stored in their tables. This case contributes to IS teaching in that it provides a fuller integration of database tasks than many existing cases, particularly in its inclusion of data gathering from actual facilities, installation instructions, storage space calculations, and both stored procedure and trigger requirements. Information on when and how the project is introduced and managed, and grading tips, is provided to potential adopters of this case. Proposed solutions are provided for most project deliverables. Feedback from students indicates that the case provides a satisfying challenge to them, and that skills gained from the project have been invaluable in their subsequent MIS jobs. Keywor ds: Data Modeling, Database Design, SQL, Team Project 1. CASE SUMMARY Greta's Gym is a local gymnastics facility owned by John and Greta Jones. Greta's Gym has been in operation for 15 years and has grown from two employees (John and Greta) to 20 employees. In this same timeframe, the gym's offerings have expanded from girls gymnastics classes only, to both girls and boys gymnastics and cheerleading classes, as well as girls and boys competitive team gymnastics. In addition, the gym sells a full line of gymnastics and cheerleading clothing and equipment. While Greta's Gym has experienced tremendous success in training young gymnasts and cheerleaders, John and Greta have not been as successful in efficiently managing their business processes. Major business processes such as registering gymnasts and cheerleading students, processing merchandise sales, and managing competitive team operations are mostly done manually. John and Greta are considering opening a new gym soon in a nearby town. With this upcoming expansion, John and Greta realize they can no longer continue with manual processes; thus, they have called on your consulting group for help in automating their business processes. In the planning stage of your project, your team, along with John and Greta, determined that automating the gym's competitive team operations would be the most beneficial process to implement first. Therefore, your remaining analysis, design, and implementation tasks will focus on providing a database solution to support the automation of Greta's Gym competitive team operations only. 2. CASE TEXT: Gr eta’s Gym Greta's Gym, a local gymnastics facility, was opened in 1989 by John and Greta Jones. Initially the gym focused on gymnastics classes for girls. The gym has since expanded its programs to include gymnastics classes for boys, a wellrespected competitive team program for girls and boys, and cheerleading classes. In addition to these services, the gym sells a full line of gymnastics clothing and equipment. Initially, John and Greta were the only employees and every business process was handled manually. New employees have been added and the number of students has grown 387 Journal of Information Systems Education, Vol. 16(4) tremendously, but they have not changed most of their manual procedures. The Jones' are considering opening another gym in Templetown, which is 45 miles south of the current gym. They know it will be impossible to manually monitor activities if they have two gyms to manage. Automating competitive team administrative activities will help greatly in managing the two gyms. Competitive Teams When gymnastics students reach a certain skill level in the regular class program, John or Greta may choose to invite the student to participate in the competitive team program. If a student accepts the invitation to join the competitive team, an enrollment sheet is filled out by the gymnast’s parent and kept on file at the gym. There are currently 125 gymnasts involved in Greta's Gym Competitive Team Program. There are 10 team levels for boys and girls; some team levels have minimum age requirements; others do not. For example, level 10 is the highest (i.e. most skilled) level and has a minimum age requirement of 14 years. Level 1 is the lowest skill level and has no minimum age requirement. Level 6 has a minimum age requirement of 8 years. Gymnasts on each team pay a fixed monthly fee for team practices; this team fee varies by level. Each team level also has a different practice schedule, as shown in Table 1. Team Level 1 2 3 4 5 6 7 8 Min Age Requirement n/a n/a n/a 6 7 8 10 12 9 12 10 14 Monthly Fee $130 $150 $150 $200 $230 $230 $260 $275 Practice Schedule T Th, 5p - 6p T Th, 5p - 7p T Th, 5p - 7p T - Th, 5p - 7p T Th F, 5p - 7:30p T Th F, 5p - 7:30p M T Th F, 4p - 7p M 4:15p - 8:15p, T - Th 3:30p - 8p $325 M 4:15p - 8:15p, T - F 3:30p - 8p $325 M 4:15p - 8:15p, T - F 3:30p - 8p Table 1. Team Infor mation Greta’s Gym Coaching Staff John, a full-time employee, is the gym's co-owner and head coach of the boys levels 1-7 competitive teams. Greta, coowner and also a full-time employee, serves as head coach of the girls' levels 2-5 competitive teams, and teaches a few preschool classes periodically at the gym. There are two other full-time employees: Kristin is the girls' levels 8-10 head coach, and Annie serves as the gym’s office manager. There is only one head coach per team. If there are no boys or girls competing at a given team level, there is no head coach assigned to that team level. The gym also employs ten part-time class instructors, and 6 part-time team coaches. There is no need to track which team a part-time coach is assigned to. Greta's Gym's full-time coaches receive a fixed monthly salary, while part-time coaches are paid on an hourly basis. Each part-time coach can have a different hourly rate, depending on their experience. Coaches offer private lessons to competitive team gymnasts; parents pay $25 per hour for these lessons. Less than half the gymnasts take private lessons each year, but for those who do, the private lesson costs are added to the bill the gymnast receives each month. Gymnastics Meets Competitive team gymnasts compete in gymnastics meets approximately twice per month. Meets are held in different locations throughout the state. Not all gymnasts compete at every meet. Gymnasts pay for each meet they compete in. Meet fees vary by meet. Coaches would like to be able to enter individual scores for each gymnast after each meet they compete in. The coaches and parents of team gymnasts would like to access the system to get gymnasts' performance data and do their own analyses. Each girl performs on 4 events at each meet: vault, uneven bars, beam, and floor. Each boy performs on 6 events at each meet: parallel bars, high bars, floor, vault, rings, and pommel horse. Gymnast scores on individual events range from 0.000 to 10.000. In addition, each gymnast earns an all-around score for each meet. The allaround score for a gymnast is the sum of the gymnasts’ individual event scores for that meet. Currently, Greta collects all individual scores on all the events from the coaches after each meet. She then types them into Excel and posts them (along with the all-around scores) on the team bulletin board. Coaches would like to analyze team member scores to see if, for example, scores are generally lower on a particular event, if scores for boys or girls at a particular level are lower than boys and girls at other levels. They could also use this data to better understand how well they have to perform at upcoming meets in order to qualify for state and national competitions. Gymnasts must achieve a certain average all-around score to qualify for state and national competitions. Merchandise Sales Finally, merchandise, including leotards, grips, leggings, jackets, mats, t-shirts, shorts, bags and gymnastics pins, is available for sale to all Greta’s Gym customers, including competitive team gymnasts. The gym also sells bar chalk, bandages, wraps, anti-bacterial creams, and other supplies. All these products have a set reorder point, so that when the amount on hand reaches that point, the item is reordered from one of the gym's suppliers. Competitive Team Billing Bills are sent to team members monthly. The bill lists the total amount due from the gymnast for that month, which includes expenses incurred by the gymnast for that month, minus any payments made during that month. Expenses that a gymnast can incur each month include their regular monthly team fee, private lessons taken that month, merchandise purchases for the month, and/or meet fees for meets the gymnast competed in that month. Parents typically give payments to the office manager, who records the payments in a ledger. The gym accepts checks, 388 Journal of Information Systems Education, Vol. 16(4) cash, or money orders as forms of payment. The office manager gives a receipt to the parent after a payment is received. 2.1 Team Assignment 1 Deliver ables Team Assignment 1 focuses on the analysis and design phases of the SDLC. You should include ALL items below in a soft binder, with TABS separating each item. ALL items should be clearly labeled. EVERYTHING must be computer-generated (i.e., no hand-written information). The front of the binder should have a cover page which includes: ̇ ̇ ̇ ̇ Project title Date turned in Team number and Section number Team member names in alphabetical order 1. Assignment 1 Project Overview This 1-2 page over view should (1) clearly and briefly describe your database system, and (2) clearly describe each specific deliverable you are providing at this stage of the project. This information should be described in a way that leaves no doubt that your team is highly organized, skilled, professional, and understands Greta’s Gym team processes. Your customer is not an IS person, so do not confuse him with technical jargon that you do not explain in clear, understandable terms. 2. Data Gathering Visit a local gymnastics facility, health and fitness club, dance studio, or local sports league and find a for m, r epor t, or other document related to team/class enrollment, meet/game schedules, meet/game scores, or merchandise sales. You must have my approval on the facility you choose prior to turning in your project. The document you submit must indicate the name and location of the facility that uses it. Incorporate applicable data from this document on your ERD. Include the document in your project binder, and use a Highlighter to highlight the data elements on the document that you incorporate in your ERD. 3. Conceptual Data Model: ERD Use Visio to create an ERD that documents the data required to support Greta’s Gym competitive team processes, as documented in the case. Also include highlighted data from the document you gathered above. Document any assumptions as appropriate. The ERD must be clearly labeled, and should not contain any M:M relationships or multi-valued attributes. Follow all rules and practices discussed in class. 4. Logical Data Design: Relational Tables Use Access to design nor malized tables based on your ERD. Ensure all primary keys, foreign keys, and referential integrity constraints are clearly labeled using the convention discussed in class. Populate all tables with at least 5 rows of sample data. To print the populated tables, copy and paste them into Word; ensure you include table names. Try to fit as many tables as you can on a page. Readability is important (i.e., graded), so you may show your referential integrity constraints on a separate page as discussed in class, if desired. 5. Physical Database Design: Fields, Records (a) Using Visio, produce a Physical Design ERD that depicts your physical database design. The ERD should have information on estimated volumes, data types/lengths, and for eign keys added to each entity. Label the ERD appropriately. If this ERD differs from your logical database design (i.e., if you denormalize any tables), include a brief write-up explaining why there is a difference. Document assumptions on volumes where required. (b) Using Excel or Word, type a 1-2 page document stating estimated exter nal stor age r equir ements for each table. Assume that records cannot span pages. Be sure to explain how you arrived at the numbers. You must state how many bytes of storage will be required to store each table, and then a total number of bytes for the entire database. Remember, leave your customer with no questions as to how you arrived at these numbers, while at the same time not overly bombarding him with jargon or calculations. 2.2 Team Assignment 2 Deliver ables Team Assignment 2 focuses on the implementation phase of the SDLC. You should include ALL items below in the binder, with TABS separating each item. ALL items should be clearly labeled. EVERYTHING must be computergenerated (ie, no hand-written information). The front of the binder should have a cover page which includes: ̇ ̇ ̇ ̇ Project title Date turned in Team number and Section number Team member names in alphabetical order ALL scripts as well as your installation sheet must also be stored on a floppy disk or CD; the disk must be labeled with your team number and section number, and SECURLY ATTACHED inside your project binder. 1. Assignment 2 Project Overview This 1-2 page over view should (1) remind your customer of the functionality that your database system is providing, and (2) clearly describe each specific deliverable of this phase of the project. This information should be described in a way that leaves no doubt that your team is highly organized, skilled, professional, and understands your customer's business. Your customer is not an IS person, so do not confuse him with technical jargon that you do not explain in clear, understandable terms. 2. Assignment 2 Installation Instructions Include a one-page installation sheet that tells your customer how to install and access project components including filenames for the scripts. Specifically, your installation sheet should list the filenames for the (1) DB Implementation script, (2) SQL Queries Script, and (3) 389 Journal of Information Systems Education, Vol. 16(4) DB Removal Script. Ensure you use full path names for your filenames. Include any special instructions or examples needed to run your scripts and queries. Make your instructions clear, concise, and easy to follow. 3. Database Implementation (SQL DDL and DML, PL/SQL) Write a well-commented script creating three the three types of objects described below. All script output should be saved to a file. (a) QL statements that create and load the tables you designed in assignment 1. Load the sample data you identified in team assignment 1. Include SELECT statements in the script to show the contents of the tables that have been created/loaded. Ensure appropriate check constraints are included. (b) In this same script, create two database triggers. The first trigger should calculate and store the allaround score for a gymnast whenever his/her individual meet scores are initially entered. The second trigger you create should create a record in an "audit table" (you must create this table) whenever any meet score is changed. This record should include the gymnast's ID, the date the score was changed, the old value of the allaround score, and the userid of the user that performed the update. Include in the script SQL statements that cause the triggers you created to "fire", and verifies the triggers worked. (c) Also in this same script, create a database stored procedure that produces the Bill for a given gymnast in a given month and year, as described in the case scenario. The project you turn in should contain this entire DB implementation scr ipt, as well as the printed output file from executing the script. the entire SQL Quer ies scr ipt, as well as the printed output file from executing the script. 5. Database Removal Create a short script to remove all objects created for this project. Additional Project Tips: ‚ Place a space after beginning comment delimiters and before ending comment delimiters. ‚ Store all your dates and times using the DATE data type. ‚ Use the SHOW ERRORS command if you get the message that your trigger or procedure was created with compilation errors. ‚ Watch the order in which you create and/or drop tables—remember that tables with foreign keys in them should be created last and dropped first. AUTHOR BIOGRAPHY Gina C. Gr een is an Assistant Professor of Information Systems at Baylor University. She holds a Ph.D. in management information systems from the University of South Florida, and an M.S.E. and B.S. in computer science. She has worked as a systems engineer, database administrator, and software development manager. Her research interests include the diffusion of process innovations, data modeling, project management, and cognitive complexity in computer-supported work. Her work has been published in IEEE Software, the Journal of High Technology Management, Information and Software Technology Journal, Information Resources Management Journal, and others. She is a member of ACM, SIGMIS, and DSI. 4. Queries, Reports (SQL Queries) Write a well-commented script containing five SQL queries that are useful to Greta's Gym. These queries should include the following four: (1) for each team level, show the name of the gymnast who has the highest allaround score, (2) print the scores for a given gymnast at a given meet, (3) list the gymnasts on each team, in descending order of their average all-around score, showing only those gymnasts with an average all-around score greater than 33.250, and (4) produce a bill for a given gymnast in a given month and year. This fourth query should simply invoke the stored procedure that contains this functionality. For all queries (including the fifth query that you create), more points will be given to queries that are directly relevant to the case (i.e., useful to Greta’s Gym), AND demonstrate reasonable complexity (i.e., includes boolean and other operators, expressions, functions, categorizing/sorting, subqueries, joins, substitution variables, and prompts). Add SQL*Plus statements to script to save results of queries in a file. The project you turn in should contain 390
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Please let me know if there is anything needs to be changed or added. I will be also appreciated that you can let me know if there is any problem or you have not received the work Good luck in your study and if you need any further help in your assignments, please let me know Can you please confirm if you have received the work? Once again, thanks for allowing me to help you R P.S: Studypool is facing high level of traffic which may delay the downloading process. MESSAGE TO STUDYPOOL NO OUTLINE IS NEEDED AS IT IS A DISCUSSION

Filename: sql statements (1).txt Date: 2018-06-07 23:49 UTC
Results of plagiarism analysis from 2018-06-07...


Anonymous
Goes above and beyond expectations!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags