I have two homeworks that need to be done although my major one is the final paper. This is due today by 7.m EST. It’s involves SQL creating…

I have two homeworks that need to be done although my major one is the final paper. This is due today by 7.p.m EST. It’s involves SQL creating databases , inserting databases, altering, modifying . I have all the attachments to share upon request.

Database management plays an integral role in nearly every area of business. Databases house customer, accounting, employee, and other types of data. These different data sets must all be efficiently stored in order to make the information accessible. Companies rely on database engineers and administrators to ensure that their records are accurate, updated, and available at all times. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final project, you will apply the skills you learned in order to demonstrate your mastery of the key concepts necessary for effective database management. These are skills you will be able to apply to your future courses as well as your career in managing information systems. demonstrate your mastery of the following course outcomes:  Communicate concepts and applications of relational database management systems and structured query language effectively to internal stakeholders  Create databases for meeting specifications using appropriate structured query language statements  Manipulate data for single and multiple tables within a database for meeting specifications using proper structured query language statements  Develop queries to extract requested information from databases using appropriate structured query language statements Prompt Imagine yourself as a newly hired database engineer for a social media messaging application startup. The initial release of the messaging app was a success, and the management team would like for you to enhance the database to support two new features in the app. The first feature is to enhance the user profile with one new property to capture more information about the users. This will allow the organization to provide more targeted advertisements based on the users’ profiles. The second feature is to allow users to send one or more pictures in messages. The current database schema does not support this feature, so you will need to create tables, keys, and constraints to support attaching pictures to messages. These modifications to the database can have a profound impact on the user experience, so you must test the changes before the new features can be released to the public. You will populate the database with new records to test new user property, and you will add new message records that include zero, one, or more images. Finally, you will create some queries that will provide the organization with useful insights on how the new features are used. The management team has requested a written report detailing the changes that you made, the SQL statements that you executed, and the analysis Specifically, the following critical elements must be addressed: I. Database Management: In this section, you will modify the database schema to support the new features for the application. The database specifications are provided in an accompanying document. A. Create tables for the database using appropriate SQL statements. Provide screenshots of the statements and the results of their execution. B. Assign primary keys and required foreign keys for the new tables using appropriate SQL statements. Provide screenshots of the statements and the results of their execution. C. Alter the table by adding a new column using appropriate SQL statements. Provide screenshots of the statements and the results of their execution. D. For all of your screenshots, effectively explain each part of the associated SQL statements and their results to internal stakeholders. Be sure your explanations are logically organized and clearly communicated to meet the needs of the internal stakeholders. II. Data Manipulation: In this section, you will add, update, and delete records to test the changes that you made to the database. The data set changes are provided in an accompanying document. A. Insert new records into tables using appropriate SQL statements. Provide screenshots of the statements and the results of their execution. B. Update existing records using appropriate SQL statements. Provide screenshots of the statements and the results of their execution. C. Delete existing records using appropriate SQL statements. Provide screenshots of the statements and the results of their execution. D. For all your screenshots, provide explanations of each part of the associated SQL statements and their results. Be sure your explanations are logically organized and clearly communicated to meet the needs of the internal stakeholders. III. Data Retrieval: In this section, you will query the database to retrieve information about the application. The query descriptions are provided in an accompanying document. A. Retrieve data from multiple tables by using the appropriate WHERE clauses. Provide screenshots of the statements and the results of their execution. B. Retrieve data from multiple tables by using the appropriate JOIN statement. Provide screenshots of the statements and the results of their execution. C. create a query that aggregates results, group results, and includes appropriate column aliases. Provide screenshots of the statements and the results of their execution. D. For all of your screenshots, provide explanations of each part of the associated SQL statements and their results. Be sure your explanations are logically organized and clearly communicated to meet the needs of the internal stakeholders. 

*************************************************************************************************************************************************************************************************************************************************************************************************************************************************************

DAD-220 Final Project Instructions and Specifications Database Schema Instructions and Specifications you need to this work in Codio in the “Personal Playground” unit. If the Terminal does not appear in the “Personal Playground”, select Tools and then Terminal. Once you’re in the terminal, type “mysql” to start the MySQL command line interface. You will need to provide all of the SQL commands that you created and executed to complete the assignment. You can use by copying and saving your SQL commands to a .sql file or .txt file using a plain text editor like NotePad or Sublime Text. Please refer to the “FAQs and Troubleshooting Guide” for details on how to make this file. Alternatively, you can create the SQL statements in the plain text editor first and then copy and paste the SQL statements into the MySQL command line interface. In addition to submitting your work as a .sql file or .txt file, you will need to submit a Word document with screenshots and explanations of your work. For each SQL statement that you construct in the steps below, you must provide the screenshot of your SQL statement, the screenshot of the results of executing the SQL statement, and a written explanation of the SQL statement. The written explanation of the SQL statement should explain the different parts of your SQL statement. The following tables have already been created for you. Each table is already populated with some records. Table Name: person Field Type Notes person_id int(8)  Primary key  Auto-increment value  Required first_name varchar(25)  Required last_name varchar(25)  Required Table Name: contact_list Field Type Notes connection_id int(8)  Primary key  Auto-increment value  Required person_id int(8)  Required contact_id int(8)  Required Table Name: message Field Type Notes message_id int(8)  Primary key  Auto-increment value  Required sender_id int(8)  Required receiver_id int(8)  Required message varchar(255)  Required send_datetime datetime  Required Task 1: Insert Record to the Person Table Construct the SQL statement to add yourself to the Person table. Note: You are required to add yourself as a new record in the “person” table. Use your first name and last name for one of the new records that you are inserting. Task 2: Alter the Person Table Construct the SQL statement to alter the table named “person”. The columns, column data types, and column notes are provided in the previous section. You need to alter the table to include an additional column of your choice. This column should represent some property of a person. You can choose the data type for the column and any constraints on the column. Table Name: person Field Type Notes person_id int(8)  Primary key  Auto-increment value  Required first_name varchar(25)  Required last_name varchar(25)  Required Custom Column Your Choice Your Choice Task 3: Update Records in the Person Table Construct the SQL statement to update the existing record in the “person” table to use the new column that you created. Update your record (the record with your first and last name) in the Person table by setting some value to your new property. Task 4: Delete Records from Person Table Construct the SQL statement to delete the record(s) from the “person” table where the first name is “Diana” and the last name is “Taurasi.” Task 5: Alter the Contact List Table Construct the SQL statement to alter the table named “contact_list”. The columns, column data types, and column notes are provided in the previous section. You need to alter the table to include an additional column named “favorite” with a data type of “varchar(10)”. This column is not required. Table Name: contact_list Field Type Notes connection_id int(8)  Primary key  Auto-increment value  Required person_id int(8)  Required contact_id int(8)  Required favorite varchar(10)  Not required Task 6: Update Records in the Contact List Table Construct the SQL statement to update the existing records in the “contact_list” table to use the new column that you created. Update the record(s) in the table by setting Michael Phelps as everyone’s favorite contact (contact_id = 1). The value for the “favorite” column should be set to “y” for these records. Task 7: Update Records in the Contact List Table Construct the SQL statement to update the existing records in the “contact_list” table to use the new column that you created. Update the remaining record(s) in the table by setting every contact who is NOT Michael Phelps (contact_id <> 1) to not be a favorite. The value for the “favorite” column should be set to “n” for these records. Task 8: Insert Records to Contact List Table Construct the SQL statement to insert at least 3 new records in the “contact_list” table. Make sure that you use the new column that you created in the previous step. Note: You are required to add at least 3 new records with yourself as a new contact in the “contact_list” table. Make sure that you provide a value (y or n) for the new “favorite” column. Task 9: Create the Image Table Construct the SQL statement to make a table named “image”. The columns, column data types, and column notes are provided here. Create the “image” table according to these specifications. Table Name: image Field Type Notes image_id int(8)  Primary key  Auto-increment value  Required image_name varchar(50)  Required image_location varchar(250)  Required Task 10: Create the Message-Image Intersection Table Construct the SQL statement to make an intersection table named “message_image”. The columns, column data types, and column notes are provided here. Create the “message_image” table according to these specifications. Table Name: message_image Field Type Notes message_id int(8)  Primary key  Required image_id int(8)  Primary key  Required Task 11: Insert Records to Image Table Construct the SQL statement to insert 5 new records in the “image” table. Task 12: Insert Records to Message-Image Table Construct the SQL statement to insert 5 new records in the “message_image” intersection table. Note: You are required to add at least one record where at least one of Michael Phelp’s messages includes at least one image. Also, you are required to add at least one message that has multiple images. Task 13: Find All of the Messages that Michael Phelps Sent Construct the SQL statement to find all of the messages that Michael Phelps sent. Note: You must use the WHERE clause to set the conditions for this query. Display the following columns: – Sender’s first name – Sender’s last name – Receiver’s first name – Receiver’s last name – Message ID – Message – Message Timestamp Task 14: Find the Number of Messages Sent for Every Person Construct the SQL statement to find the number of messages sent for every person. Note: You must use the WHERE clause to set the conditions for this query. Display the following columns: – Count of messages – Person ID – First Name – Last Name Task 15: Find All of the Messages that Have At Least One Image Attached Using INNER JOINs Construct the SQL statement to find all of the messages that have at least one image attached using INNER JOINs. Note: For messages with multiple images, display only the first image for the message. Display the following columns: – Message ID – Message – Message Timestamp – First Image Name – First Image Location Verification Verify the results of your SQL statements. Refer to the “FAQs and Troubleshooting Guide” on instructions on how to list the tables in the database and to describe the details for each table.

"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!":

Get started