• ERD Model
  • Based on the specification received create an ERD in MySQL Workbench.
  • Sign the ERD by writing your name in a text object.
  • Take a picture of your ERD.
  • Include in the report file the following:
    • A heading: Task 1 - ERD Model
    • The signed screenshot(s) (image) of the ERD
• Task 2 - Create a database
  • Start writing the SQL script in MySQL Workbench to perform the following operations:
    • Include a comment: Task 2 Create a database by ... (your name)
    • If there is a database with the provided name, delete that database.
    • Add a database with that name.
    • Mark the created database as the default database for the following operations.
    • Show all the databases, find the created one in the list
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 2 - Create database
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen
• Task 3 - Create tables
  • Continue writing the SQL script in MySQL Workbench, adding the following operations:
    • Include a comment: Task 3 - Create tables by ... (your name)
    • List all the tables in the database (no table expected).
    • Create the tables with the required specification.
    • List all the tables in the database (again - the new table must appear).
    • Show the structure of the created tables.
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 3 - Create tables
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen
    • Screenshots with the generated tables by the above commands, included in the order of the commands- middle of the screen in different tabs
• Task 4 - Add data
  • Continue writing the SQL script in MySQL Workbench, adding the following operations:
    • Include a comment: Task 4 - Add data by ... (your name)
    • Show all the data in your tables (no data expected).
    • Add at least 5 rows of data in each of the tables, start with the provided data items in the order they are provided, and imagine new data for the other ones. Be sure you follow the constraints in your problem.
    • Show all the data in your table (at least five rows expected in each table).
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 4 - Add data
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen
    • Screenshots with the generated tables by the above commands, included in the order of the commands - middle of the screen in different tabs
• Task 5 - Query the data
  • Continue writing the SQL script in MySQL Workbench, adding the following operations:
    • Include a comment: Task 5 - Query the data by ... (your name)
    • Perform the queries from your specification.
  • Execute and debug the script in MySQL Workbench until runs without error and performs the above operations.
  • Include in the report file the following:
    • A heading: Task 5 - Query the data
    • A screenshot (image) of the source code for this task only (crop the image if necessary) - top of the screen
    • A screenshot (image) of the results for the performed operations - bottom of the screen
    • Screenshots with the generated tables by the above commands, included in the order of the commands - middle of the screen in different tabs
    •  

Transcribed Image Text:

Requirements Specification (this is a fictional scenario) A fitness company stores the current assignments of trainers to customers in a document file like this: • Trainer: Jones, William; phone (175) 2219988; hired in: 2019; Customer: Garcia, Robert; phone (175) 7518822; year of birth: 1988; • Goal: building endurance; ● A trainer is always assigned to one customer. We do not keep trainers without customers. After it is registered, a customer may be assigned to one or more trainers. The customer may decide to change the trainer, but previous assignments are not kept. A trainer has at most 1 customer. There is no middle name stored with the trainers or customers. Both must have a phone number. For some trainers, the most recent hiring year is stored. For some clients the year of birth is stored. All customers must have a goal specified. The first and last name have at most 24 characters each. The phone number will be stored with the following pattern: (ppp) ppppppp. The hiring year and year of birth are numbers with 4 digits. One may want to search based on first name or last name, phone, goal, hiring year and year of birth. Data Entry Constraints for Task 4 • The fourth customer must have at least two trainers in the data entered. • You must have at least two trainers with the first name Mike. • At least two trainers must be hired after 2020 and two before. Database Questions for Task 5 • For each assignment, list the first and last names for both the trainer and the customer. Use suggestive names for the columns in the result. • Pick the ID for the fourth customer you defined in the database. List the full names (FIRST SPACE LAST) for all the trainers of the selected costumer (in one column named "Trainer Name"). • List the full names (FIRST SPACE LAST) for the trainer and customers (in two columns named "Trainer Name" and "Trainee Name"), for which the trainer first name is "Mike" • List the names for the trainers in the format (LAST COMMA SPACE FIRST) hired after 2020 and the full names of their customers. Show the result in two columns named "Trainer Name" and "Trainee Name". Requirements Specification (this is a fictional scenario) A fitness company stores the current assignments of trainers to customers in a document file like this: • Trainer: Jones, William; phone (175) 2219988; hired in: 2019; Customer: Garcia, Robert; phone (175) 7518822; year of birth: 1988; • Goal: building endurance; ● A trainer is always assigned to one customer. We do not keep trainers without customers. After it is registered, a customer may be assigned to one or more trainers. The customer may decide to change the trainer, but previous assignments are not kept. A trainer has at most 1 customer. There is no middle name stored with the trainers or customers. Both must have a phone number. For some trainers, the most recent hiring year is stored. For some clients the year of birth is stored. All customers must have a goal specified. The first and last name have at most 24 characters each. The phone number will be stored with the following pattern: (ppp) ppppppp. The hiring year and year of birth are numbers with 4 digits. One may want to search based on first name or last name, phone, goal, hiring year and year of birth. Data Entry Constraints for Task 4 • The fourth customer must have at least two trainers in the data entered. • You must have at least two trainers with the first name Mike. • At least two trainers must be hired after 2020 and two before. Database Questions for Task 5 • For each assignment, list the first and last names for both the trainer and the customer. Use suggestive names for the columns in the result. • Pick the ID for the fourth customer you defined in the database. List the full names (FIRST SPACE LAST) for all the trainers of the selected costumer (in one column named "Trainer Name"). • List the full names (FIRST SPACE LAST) for the trainer and customers (in two columns named "Trainer Name" and "Trainee Name"), for which the trainer first name is "Mike" • List the names for the trainers in the format (LAST COMMA SPACE FIRST) hired after 2020 and the full names of their customers. Show the result in two columns named "Trainer Name" and "Trainee Name".

Answer rating: 100% (QA)

Task 1 ERD Model Open MySQL Workbench Create an ERD based on the specifications received including e... View the full answer