DAD-220-Module-Three-Lab-CMoore

docx

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

DAD 220

Subject

Computer Science

Date

Dec 6, 2023

Type

docx

Pages

Uploaded by SargentWaterBuffalo9718

Cameron Moore DAD 220 Intro to Struct Database Env 1. Before you begin, type the following commands prior to typing MySQL to set file permissions. This will allow you to perform the file output creation: o chmod +x change_perm.sh o Press Enter. o ./change_perm.sh o Then, enter MySQL and reconnect to the employee information you entered in the previous lab. o Write a SELECT statement for the Employee table to check that you’ve reconnected to the right information.

Cameron Moore DAD 220 Intro to Struct Database Env 2. Update the name of the Branches table that you created in the previous lab to say "Department". a. Use an ALTER statement to successfully RENAME the "Branches" table to "Department". b. Capture these outputs in a screenshot to validate that you’ve successfully completed this step.

Cameron Moore DAD 220 Intro to Struct Database Env 3. Insert fields to the Department table so that you’ll be able to perform joins on them. a. INSERT INTO Department VALUES (1, 'Accounting'), (2, 'Human Resources'), (3, 'Information Systems'), (4, 'Marketing'); b. Write a SELECT statement for this table to prove this step, and validate that it ran correctly with a screenshot. 4. Now, perform joins between the Department and Employee tables and show results for how many employees work in each one of the four departments. This will only provide information on the records that are already there. a. Department 1 = Accounting

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Cameron Moore DAD 220 Intro to Struct Database Env i. Command: SELECT First_Name, Last_Name, Department.Department_Name FROM Employee INNER JOIN Department ON Employee.Department_ID = Department.Department_ID WHERE Employee.Department_ID = 1; b. Using SELECT statements similar to the one above, perform joins to produce results for the following tables: i. Department 2 = Human Resources ii. Department 3 = Information Systems iii. Department 4 = Marketing c. Capture the results of these joins and validate your work by providing a screenshot. You should have the same number of records as you do employees. 5. Populate the Employee table with information for ten new employees.

Cameron Moore DAD 220 Intro to Struct Database Env a. Give them unique names and include attributes for all necessary fields. (Note: Please reference attributes from the lab in Module Two. Department ID values must be between 1 and 4.) 6. Perform a join across the Employee and Department Tables for each of the four departments. New and existing records should be displayed in the results. a. Take a screenshot to capture the updated results that the Employee and Department joins show to validate that they have run correctly. You should have the same number of records as you do employees. 7. Identify the resultant outputs of the commands that you’ve written: a. How many records are returned for employees in each department?

Cameron Moore DAD 220 Intro to Struct Database Env 8. Create a CSV file that contains only the records of employees in Human Resources and Information Systems. If you run this query multiple times, be sure to use a different file name each time. MySQL will not overwrite an existing file. a. Enter the command listed below. i. Command: select First_Name, Last_Name, Department.Department_Name from Employee inner join Department on Employee.Department_ID = Department.Department_ID where Employee.Department_ID = 3 OR Employee.Department_ID = 2 into outfile'/home/codio/workspace/HRandIS-Employees.csv' FIELDS TERMINATED BY',' LINES TERMINATED BY '\r\n'; b. Print the file output to the screen. i. You’ll need to type the word quit after your MySQL prompt and then press Enter to exit to the Linux shell. Do not exit the virtual lab environment itself. ii. Next, print the output of your file to the screen by following these steps:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Cameron Moore DAD 220 Intro to Struct Database Env 1. Type pwd and press Enter, then type ls and press Enter again. This will list your files. 2. Now, type cat HRandIS-Employees.csv and press Enter. 3. Capture these outputs in a screenshot to validate that you’ve successfully completed this step. 9. Reflection: Provide detailed insight on the prompts below by explaining your process along with how and why it ultimately worked. a. Process i. Explain how the joins you used in this assignment worked. This ended up taking a little while for me to understand and I kept getting something wrong while trying to get the code to work. Come to find out I was adding an extra comma. What helped to get this to work however is that the join looked at what department you had requested and then linked up with that table. ii. Describe why the commands you used were able to retrieve the Department table when you selected the Department name. The commands were what I used to retrieve the information from and then was able to pull information from both the first and the second table that were created. b. File creation and extraction

Cameron Moore DAD 220 Intro to Struct Database Env i. Identify how many records are in the file when you write the records of your query to a CSV file. It is showing 4 rows were affected. ii. Explain, in detail, the process of extracting data to a flat file. This consists extracting selected information from the file as in, first name, last name, department, department name. These are being extracted from the employee table. Once this is done we use a inner join and choose (select) which department on the employee table we want to use, this is done by selecting the department ID. Now you have two departments selected, and this is where we output a file from the Codio application. We create a file called HRandIS-Employee.csv. Once the file is created we are able to see a much more user friendly file that can be shared.

Related Documents

Bhavya's MIS Lab Class Assignment Week 6.docx

annotated-Planning%20and%20Organizing%20Intruction%20Essay.docx.pdf

practice.pdf

6362_prereq_12_final.doc

6362_prereq_10.doc

3.3 & 3.4 Comprehension Quiz: ASL 101-50-4238.pdf

IT-140-Moudle-Four-Assignement-CMoore.docx

BIOL1050_Lab Module 3_Poster template (1).pptx

Spring 2022 CECS 274 Lab 6 -- Searching & Sorting (corrected).pdf

NFLab4.pdf

NetworkForensics2lab.pdf

NetworkForensics2lab (1).pdf

Recommended textbooks for you

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:9781337102124

Author:Diane Zak

Publisher:Cengage Learning

EBK JAVA PROGRAMMING

Computer Science

ISBN:9781337671385

Author:FARRELL

Publisher:CENGAGE LEARNING - CONSIGNMENT

Programming Logic & Design Comprehensive

Computer Science

ISBN:9781337669405

Author:FARRELL

Publisher:Cengage

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE

Computer Science

ISBN:9780357392676

Author:FREUND, Steven

Publisher:CENGAGE L

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

SEE MORE TEXTBOOKS

Recommended textbooks for you

Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
EBK JAVA PROGRAMMING
Computer Science
ISBN:9781337671385
Author:FARRELL
Publisher:CENGAGE LEARNING - CONSIGNMENT
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr