OPIM 3103 009 Final Project 2023

docx

School

University Of Connecticut *

*We aren’t endorsed by this school

Course

Subject

Computer Science

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by lilrentfeatures

OPIM 3103 – 009 Shaikh, Mariam Corey Hernas 12/8/23 Total Points: 100 Read the instructions carefully for each section and answer accordingly. Ensure if a screenshot is required that all relevant items are being displayed. Part 1: Excel (34 pts) Open the Social_Media_Dataset.xlsx and rename it to Social_Media_FirstName_LastName respective to your own name to answer the following excel questions. After answering each question, include a screenshot in your response of your excel page along with the title where it shows your renamed version. You will not receive credit if the top part is missing. Here is an example: 1) What is the highest number of social media followers in the data? Include a screenshot of the excel sheet with the proper formula that can be used to obtain this information (4.25 pts) =MAX(F2:F998)

2) Create a pivot table from the dataset that shows a sum of the followers, the location as rows, and the status as columns. Include a screenshot of the excel sheet. (4.25 pts) 3) Create a chart of your choice from the dataset and customize the colors. Elaborate how this chart could be useful to a social media company? Include a screenshot of the excel sheet. (4.25 pts) 4) Assume the social media company wants to remove all accounts that are not active. Using an IF formula, create a new column titled “Delete”. If the account is inactive or has been suspended based on the status column, then the cell should show “Remove” otherwise it should say “Keep”. Include a screenshot of the excel sheet with the formula and the corresponding values for the new column. At least 10 rows should be showing in the screenshot. (4.25 pts)

5) Assume the social media company wants to understand who the accounts are that have MORE than 100,000 followers and follow LESS than 100,000 accounts. Create a new column called “User Type”. If the user is a top user and fulfills the criteria listed above, the cell should read “Top User”. If the user does not fulfill that criteria, the cell should read “Regular User”. Include a screenshot of the excel sheet showing the formula and at least 10 rows. (4.25 pts) 6) Create the following to the right of the dataset: In the cell to the right of Followers, use a VLOOKUP function to search the dataset for the full_name Krissy Pardie to bring up the number of Followers she has listed in the respective column. Include a screenshot of the excel sheet that shows both the number of followers this person has and the formula used to obtain this information. (4.25 pts)

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

7) Describe the difference between a VLOOKUP, HLOOKUP, and XLOOKUP. Explain what each of the three do. (4.25 pts) VLOOKUP finds a value in the first column of a table and returns a corresponding value from the same row in a specific column. HLOOKUP finds a value in the first row of a table and returns the corresponding value from the same column but in a specific row. XLOOKUP has the ability to search for a value from either one row or column depending on specification and returns the corresponding value in the same position from another row or column. 8) What does the CONCATENATE function do in excel? When would you use this and how is it useful? (4.25 pts) The CONCATENATE function combines multiple cell contents or joins multiple cell contents into one single string. You could use this function for instance when you have the values “Greetings,” in B2 and “can we” in B3 and “chat?” in B4 by using CONCATENATE it will merge the text to result “Greetings, can we chat?”. The usefulness of this function is that we can merge text, manipulate text, improve organization and make things more efficient. Part 2: Database (34 pts) Using the Student_data.xlsx file provided, answer the following questions: 1) Write an insert into statement for the entirety of the student data provided. The table name is Student_Table. (4.25 pts)

2) Write a SQL Query that gives the maximum and the minimum grade from the Student_Table. (4.25 pts) 3) Write a SQL Query that will display ALL of the fields within the Student_Table. Remember to use proper syntax. (4.25 pts)

4) Review the following SQL statement: SELECT student_id, country, email, FROM Student_Table WHERE country = ‘China’; Rewrite and correct the above SQL so that it will not error: (4.25 pts) 5) Between the two queries below, which one will give you an error? Query A Query B SELECT Fundraisers.FundraiserID, Members.FirstName, Members.LastName, Fundraisers.DonationSource, Fundraisers.DonationsTotalAmount, Fundraisers.DonationGoal from Fundraisers JOIN Members ON Fundraisers.FKMemberID = Members.MemberID SELECT Fundraisers.FundraiserID, Members.FirstName, Members.LastName, Fundraisers.DonationSource, Fundraisers.DonationsTotalAmount, Fundraisers.DonationGoal from Fundraisers JOIN Members ON Fundraisers.FKMemberID

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

Elaborate on your answer. Why will one give an error while the other will not? (4.25 pts) Query B will give an error due to the fact that JOIN section of the code is incomplete and it will lack the specification on how the tables are joined. The JOIN section should be like Query A since it shows a clear relationship between the tables by using the JOIN function and the completed condition unlike Query B. 6) What is the difference between a relational database and a NoSQL database? Provide at least one difference / example. (4.25 pts) The difference between a relational database and a NoSQL database is that their data structure, data storage models are different. In relational databases data structure is organized in table formatting consisting of rows and columns following a structured schema. In NoSQL databases the data structure is modeled document-based with columnar, graph-based structures and doesn’t generally follow a fixed schema and can handle unstructured or somewhat structured data. They have more flexible data models while relational databases have more structured data that’s more organized. 7) What is the difference between a database and a DBMS? (4.25 pts) A Database is an organization of structured data that is stored and accessed and managed. Databases can include sensitive information that are organized into tables, columns, and rows. The purpose of a database is to organize and hold data that is created for efficiency. A DBMS is a software that provides a set of tools to interact with databases by managing the organization, retrieval of data, and creation from the database. It also provides the functionality of security and recovery. Essentially a database is needed for DBMS to exist. Also DBMS stands for Database Management System. 8) List the 4 V’s of big data. Include a brief explanation of each one. (4.25 pts) First V is Volume which refers to the scale of data being generated. Second, Velocity which refers to the rate of data generated. Third, Variety which refers to diverse types and sources of data. Lastly, Veracity refers to the reliability, trustworthiness and accuracy of the data. Part 3: BIS Concepts (32 pts) 1) What is a cybersecurity breach? Provide an example of one. (6 pts) A cybersecurity breach is when there is unauthorized access to a network, computer system, server, database which results in criminal activity such as theft, data leak, damage. An example of one is the attacks on the Las Vegas casinos MGM Grand and Caesars Palace. All games, bookings, credit card info, rewards info, restaurants, and the entirety of the systems they use were shut down making the casinos lose money and putting the casino’s client’s information at risk. 2) We discussed various methods of social engineering in class. Describe what social engineering is. (6 pts) Social engineering is manipulation psychologically and deception used by unauthorized people such as attackers which have the goal of gaining access to something they are not supposed to. This is done by deceiving authorized people with the use of confidential information or certain actions that trick others

into trusting attackers and from that point the attacker gains access to their system. This is more formally called manipulation, exploitation of psychological thinking, phishing. 3) What is the CIA triad? What do each of the letters mean? (5 pts) CIA triad is a common model for the basis of the development of security systems. The CIA triad stands for Confidentiality meaning information that is sensitive is accessed only by authorized members, Integrity means assurance that the data is accurate and trustworthy, Availability means that data is readily accessible by authorized users. 4) What does LAN stand for? How does this connect to the internet? (5 pts) LAN stands for Local Area Network. This connects to the internet by connecting with a router or modem which this router or modem serves as a bridge between LAN and the internet being the external network. LAN network is local and connects devices within a limited area with the connection being facilitated through a router or modem. 5) Describe at least two benefits of the cloud and why companies would want to shift to this? (5 pts) One benefit is the ease of access for authorized users at the company. When searching for certain files or the use file sharing it can be easily accessed via the cloud or uploaded to the cloud. The second benefit of utilizing the cloud is that. It reduces costs in the sense that physical hard drives such as HDDs or SSDs would be minimized since when uploading to the cloud it takes up significantly less local storage on the company’s computers which in return means less costs in hardware and relieving the worries of hard drives corrupting or physical damage/loss. 6) Name an AI tool or website that is open to the public that you find interesting. What problem is this resource solving? What could be a potential consequence of this tool? (5 pts) There’s ChatGPT and there is also HaasOnline which is a software company that specializes in providing traders with automation in trading by using bots that act as Artificial Intelligence when it comes to trading cryptocurrencies. On the site there are various bots to choose from such as Binance Trading Bot or SMMDH v3.3 that trades various cryptos. This is solving the problem of people not knowing the best trading strategies and risk management as the bot is trained in these specifications. A potential consequence to this tool is losing a user’s invested fiat currency. With ChatGPT it’s solving any information you could think of as it provides tons of info at the ease of typing a question. A problem to this is cheating or plagiarism due to the fact that this is not a user’s work.

OPIM 3103 009 Final Project 2023

Related Documents