Search Results

Submission file Please submit a PDF file that, for each query, contains: 1) SQL query: copy and paste your query in the pdf 2) a screenshot executing the query in SQLite Studio, and it should clearly show the output table. EntertainmentAgency.sqlite Query1. What is the percentage of male and female entertainer members whose musical style is Jazz? The query should output the percentages of each gender separately and indicate which is which. (The percentage here means percentage out of all members) Query2. What is the full name (in the form of LastName, FirstName) of the top 3 agents who have the highest average commission per engagement? Print out both the full names and the average commissions. The average commission can be calculated by multiplying the contract price with the agent’s commission rate. Query3. What is the total income of the Jazz entertainers (i.e.,the sum of all Jazz entertainers’ income across all of their engagements) and the total income of the Salsa entertainers? The income of each entertainer for each engagement is the ContractPrice of the engagement minus the agent’s commission. To receive credit you must not use subqueries anywhere (i.e., no nested SELECT clauses at all). Query4. What are the top 5 musical styles that have the highest number of customers, and how many customers each of these styles has? BowlingLeague.sqlite Query5. Which teams have captains with the same last name? Each such team must be listed exactly once, along with the team captain’s full name (in the form of LastName, FirstName). Query6. List the TourneyDate,TourneyLocation,TeamNames and Lanes of the teams that have captains with the same last name. You can use only the team name from the results of the previous question. To receive credit you must not use subqueries anywhere (i.e., no nested SELECT clauses at all). Query7. How many teams have the different players with the same last name? To receive credit you must not use subqueries anywhere (i.e., no nested SELECT clauses at all). SalesOrders.sqlite Query8. Print the states that have a customer, but no vendor. Query9. Print a report of the 5 products that have sold in the greatest total quantity (the most popular products). You should print the name of the product and the total quantity. Be careful to count the quantity in each order, not just the number of times a product was ordered. Query10. Print all the pair of products whose names have the same number of characters. List each pair only once (ie., don't list both A,B and B,A).(SQLite has a LENGTH() function that can be applied to text. https://sqlite.org/lang_corefunc.html#length) You can easily download all the schema for GitHub using below link: https://github.com/CodersArts/database

MongoDB is a document database designed for ease of development and scaling. A record in MongoDB is a document, which is a data structure composed of field and value pairs. MongoDB documents are similar to JSON objects. The values of fields may include other documents, arrays, and arrays of documents. For ease and better understanding of the queries in mongoDB, i’ll be comparing SQL database structure with the database structure of mongoDB(NoSQL). KEY POINTS:- SQL database consists of TABLES and NoSQL database consists of collections. Which are simply different ways of representing and storing datas. Please go through the MongoDB documentation( https://docs.mongodb.com/manual/reference/operator/query/ ) on queries before going through the project to understand the basics . Before writing queries let’s connect mongoDB atlas with our system :- 1) Download mongoDB Atlas from https://www.mongodb.com/try . 2) After installing mongoDB Atlas, open it and connect it with the required PORT or connect it with the default PORT(27017) by clicking on connect. 3) Create a new database and create a new collection inside of that database by selecting the CREATE DATABASE and entering name of database and collection. 4) For this project import an existing dataset by clicking the IMPORT option under collection or you can add data manually. 5) After completing all the steps you are ready to start writing queries in the mongoDB atlas itself and later we’ll show how to write queries on any other terminal such as CMD prompt. 6) Here I have downloaded the dataset from, http://grouplens.org/datasets/movielens/ . Use the small dataset (ml-100) from the category MovieLens Latest Datasets section Getting Started with Queries : 1. Generate a list of movies that have “Adventure” as a genre. Hint: Simple queries like the above one could be written directly on the options bar given above the collection, then click on the find option to run the required query.In the above query we are finding the genres that consist of the adventure word in it. 2. Generate the movies that have been categorized into more than 2 genres. Hint: For solving this query we checking if the genres field of each document consist of “|” in it. If it consist of “|” that shows it has more than one category under genres. 3. Generate a list of movies that have the “Children” genre and a user rating > 3. Hint: In the above query we are using two collections, movies and ratings. So before writing any query we have to join the two tables and then write the required query. Note that for writing multiple queries aggregate keyword is being used (i.e. db.movies.aggregate([ ])), and in mongoDB Atlas you can directly do it using the aggregation tab mentioned on top. And for different queries under aggregation use the boxes given on the left and the result will be displayed on the right. You can select the queries from the dropdown as shown below 4. Generate all users whose average rating across the movies they have rated is greater than 3 5. What are the genres that appear in movies that are rated greater than 4? 6. Which Star Wars movie has the highest average user rating and what is the average rating? 7. A. What is the average rating of all movies with a date of 2008 in the title? 7.B. What is the average rating of all movies with Star Wars in the title? Try to solve this on your own if you face any issue go through the solution given below. Run on CMD NOTE: TO RUN THE FOLLOWING QUERIES ON COMMAND LINE TERMINAL FOLLOW THE GIVEN STEPS, 1) Open CMD prompt. 2) Change the current directory to the directory where you have installed mongoDB using (cd) command. E.g: cd C:\Program Files\MongoDB\Server\4.4\bin After changing the directory run the mongoDB server by typing the keyword (mongo) in terminal and you’ll get the given response Contact Us to Get solution of these queries or other MongoDB Project Related Help at affordable price then you can contact us at: contact@codersarts.com

INTRODUCTION : - Weather forecasting is the application of current technology and science to predict the state of the atmosphere for a future time and a given location. People can download thousands of weather apps on Apple App Store or Android Play Store nowadays. Those apps show about present weather information and weather forecasts, with sleek and gorgeous interfaces. It seems it’s unnecessary to design more weather applications, but I designed new one few days ago. Problems As I told you above, people can get various kinds of weather forecast apps. But those apps are all the same thing, except their design elements like color, icons, etc. Basically, it’s not a big problem. Their main purpose is to inform how the weather like today or this week is. However, research shows that there are still some problems like: 1. Users easily forget to check today’s weather. Imagine you have date at night with your friend, without knowing the rain is coming…. Even if people checked for today’s weather forecast yesterday, It’s too easy to forget that (how’s the weather like today) after they wake up! 3. For sure, those applications are beautiful. But it’s still bothersome to check today’s weather forecast every morning, right after you woke up. 4. Some weather applications notify users about today’s weather forecast, but it’s kind of somewhat annoying thing to get push notification everyday morning when they’re still on bed. Even when you don't want to be notified! More than 85% of people I surveyed experienced to forget to take their umbrella before they go outside, even though they checked the weather forecast already. I guess everybody has an experience like this once before, ‘Do I need to take my umbrella today…?’ in front of his/her front door, just before open the door to go outside, turn on smartphone… launch weather application… check if it’s rainy today… and decide to take it or not. But what if he/she totally forgot to check the weather forecast? When I took this project (to design any weather application) as an assignment, I decided to design a weather “Alarm” application — Weather that notifies users about weather events only they want to be notified, not the same application that people usually can get on store. Let's get started! #1. Observation Goal & Challenge I wanted users not to launch app often. When users launch this app for the first time, they set settings like what weather events to be notified like ‘snowy’ or ‘cloudy’, and what time and days to be notified. That’s it! When setting finished, users don’t need to launch this app again until they want or need to change notification settings. Job Stories Additionally, I wrote some Jobs to be done lists to design more useful product and make it from user’s perspective. When I woke up late for the work, I want to be notified in advance about specific weather events, so I can prepare with that even I don’t have much time to check how the weather like today. When I’m too lazy that it’s bothersome to check the weather with my smartphone on every morning, I want to be notified about today’s weather only it’s rainy outside, so I can prepare my umbrella without forgetting and get additional minutes on my bed. When I set an alarm at specific time like before I go to my work, I want to be notified about the events I want, not every alarm how’s the weather like today, so I can avoid to get notification everyday even if it’s sunny(that I don’t need to be informed). #2. Ideation Main purpose This app’s main purpose is to notify users about the weather information that only they want to be notified, on time they selected. This can save their time, makes them to get ready with specific weather events without checking whole weather forecast. With that way, even if users forgot yesterday's weather forecast or forget to check today's weather forecast, App will notifies users when if there are specific weather event that user's want to be notified. Brainstorming Here’s simple user flow about this application: 1. User downloads and launches this app. 2. User sets an alarm of weather events he/she wants to be notified. 3. User gets notification based on the settings. For example, if users selected that they wants to get notification when it’s rainy or windy, push notification pops up on the time when user sets, only when it’s rainy or windy. I wanted to keep this application as simple as possible with minimum depth, so I focused only for the one process: to set an alarm. No other function was added. Just like the example above, user gets push notification based on his/her weather alarm settings. In the case above, user sets an alarm for 9AM, Monday if it’s rainy. What if it’s not rain on Monday? No alarm pops up! #3. Rapid Prototyping Lo-Fi Prototypes 1. On the main screen, simple introduction and launcher icon pops up before user get started with “Start” button at the bottom of the screen. 2. On “Select weather type” screen, users can select what kind of weather events they want to be notified. Selected weather types are shows up at the top of the screen as weather icons. 3. On “Set time” screen, users can set what time to be notified with time picker and date picker. On the last screen, this app informs users that everything has finished. If users want to change settings of alarm they set, simply tab the “Alarm Settings” button at the bottom of the screen. This makes users to iterate one step from screen 2 to screen 3. On "Select weather type" screen, I did not give many options. Notifications for ordinary (e.g., sunny) weather on a smartphone that already has many push notifications will only interfere with other more important notifications. I only wanted to use push notifications for important weather events that require attention, such as having to pick up an umbrella or wear a thick coat. That is why I restricted the selection of weather events on the "Select weather type" screen. In addition, if there are too many options, users will spend much more time to choose. The choices that come out from those ideas are as follows: · Snow / Rainy · Thunderstorm · Swelter · Gale · Sudden Cold · Fog / Cloudy · ​​​​​​​All Above Improvements Usability Testing Based on the Lo-fi prototypes above, I had some usability tests with my friends who were not designers to prove usability of this application. Examples below are about some pain points I’ve notified during user testing. #4. Validation & Iteration Based on Feedback With improved design above, I had one more user testing with other testers. The table below shows the result of second user testing with redesigned wireframes. If user can perform specific mission (pain points) without any struggle, I recorded it as ‘succeeded’. #5. Output Hi-Fi Prototypes After some usability tests, I fixed and redesigned some screens and details based on the feedback from the tests. Based on the settings that user set on, push notification pops up on user’s smartphone. In this case, user selected that he/she wants to be notified only when it’s [Snow/Rainy], [Swelter] or [Gale]. When it’s [Snow/Rainy], [Swelter] or [Gale] on every Monday, Tuesday and Thursday, push notification will pops up at 9AM. Once users set their alarm, they don’t need to launch this app again until they need to change the alarm’s setting. CONCLUSION : - So, we conclude: in order to create a weather app and succeed, it's important to think through the logic of the program and develop a strategy of distinguishing from competitors. After all, such meteorological services are basic Android & iPhone weather apps, and you should do your best to attract the user. If you want to build your own weather app from scratch, we’re more than happy to help you. Our team employs only experienced and skilled specialists who'll implement your idea with pleasure and inspiration. T H A N K Y O U ! !

File Name rubyElseIf.rb #!/usr/bin/ruby marks = 90 if marks >= 90 puts "Grade A+" elsif marks >= 80 puts "Grade A" elsif marks >= 70 puts "Grade B" elsif marks >= 60 puts "Grade C" else puts "Pass" end How to run: ruby rubyElseIf.rb Output: Grade A+ How to Use Multiple Conditions You can do this by using the && (AND) operator: if name == "David" && country == "India" #statement 1 ----------- #statement 2 ----------- end You can also use the || (OR) operator: if age == 10 || age == 20 #statement 1 ----------- #statement 2 ----------- end

Single Line Ruby Comments # This is a comment line print "Welcome to Ruby!" The '#' character is used for Single line comments in a Ruby script. Comments on Lines of Code print "Welcome to Ruby!" # prints the welcome message Multi Line or Block Ruby Comment =begin This is a comment line it explains that the next line of code displays a welcome message =end =begin and =end comment markers is used to defined as comments in ruby. These are known as the comment block markers Any suggestion and correction is acceptable please do if you have any.

Open Interactive Ruby Open up IRB (which stands for Interactive Ruby). If you’re using macOS open up Terminal and type irb, then hit enter. If you’re using Linux, open up a shell and type irb and hit enter. If you’re using Windows, open Interactive Ruby from the Ruby section of your Start Menu. Open Interactive Ruby in macOS: With the help of irb command you can open up Interactive Ruby in other platforms

In this we will covers two task: Creating an animated Chloropeth  plot using plotly that analyzes a seven-day moving average of cases for some geographic unit and sub-unit (e.g. USA and states) Creating an animated scatter plot of Covid-19  using Plotly that analyzes COVID cases and deaths for some geographic unit and sub-unit (e.g. USA and states). Any data source relevant or related to requirements is accepted (e.g international covid cases). (csv or json files are accepted) suggested data source links: https://covidtracking.com/data/api https://covidtracking.com/data/api/v1/states/daily.csv https://www.census.gov/data/datasets/time-series/demo/popest/2010s-state-total.html Output: Task 1: Objectives: Create an animated choropleth plot using plotly that analyzes a seven-day moving average of cases for some geographic unit and sub-unit (e.g. USA and states) Create a second, non-animated, choropleth plot that shows cumulative cases per 100,000 people for the most recent date in the data file. Requirements: Find appropriate data source that includes new COVID-19 cases per day for the geographic region. (Direct link not downloaded file.) Find a data source that estimates the population for the geographic region. (Direct link not downloaded file) Load both to a pandas dataframe Calculate cumulative cases per 100,000 population for the sub-region (i.e., state) Calculate 7-day moving average if new cases Plot 7-day moving average of cases on Plotly plot and animate by day (older dates on left of slider) Create a separate plot of cumulative cases per 100,000 population. This should be for the maximum date in the dataframe and should not be animated. Plots will include relevant title and hover text. Colors will be continous scale of your choice. Task 2: Objectives: Create an animated scatter plot using plotly that analyzes COVID cases and deaths for some geographic unit and sub-unit (e.g. USA and states) Requirements: Find appropriate data source that includes new COVID-19 cases per day for the geographic region. (Direct link not downloaded file.) Load to a pandas dataframe Perform any necessary transformations to conduct analysis and plotting. Plot cumulative cases as size of bubble (older dates on left of slider). Color of bubble should be continous scale and represent the cumulative number of deaths for that geographic region. Plots will include relevant title and hover text. Colors will be continous scale of your choice. Looking Any Data Visualization Help related to Python Machine Learning, R Programming, D3.js, then you can contact us at contact@codersarts.com

Implement a hexadecimal calculator for iOS. Requirements: The calculator should support 4 basic arithmetic operations: + - * and / The calculator will operate on hexadecimal numbers, not decimal numbers The calculator only needs to operate on unsigned integers (i.e. UInt). You do not need to consider negative numbers or fractions. The calculator should support the 16-digit hexadecimal numbers (i.e. The range of the numbers is from 0 to FFFF FFFF FFFF FFFF). Prevent the user from entering a number that is greater than FFFF FFFF FFFF FFFF. The calculator should handle overflow and underflow gracefully. The app must not crash. The calculator should handle division-by-zero error gracefully. The app must not crash. The calculator should be able to support most of the devices and orientations. If it does not support the old devices earlier than iPhone 6, it is okay. Hint: To convert a string to a hex number, use “radix: 16” as an argument. For example: var s:String? s = "1A" var intHex:UInt = 0 intHex = UInt(s!, radix: 16)! print(intHex) // shows 26 intHex = 90 s = String(intHex, radix: 16).uppercased() print(s!) // shows 5A It is recommended that you use a UI label instead of a text field, so that the user will not type directly by using a keyboard. You will need to provide a button for each digit. Strings may be concatenated by using + operator. E.g. var s1 = "1234" var s2 = "5" print(s1 + s2) // shows 12345 You may want to do string concatenation in the action of each digit button. To prevent the user from entering a number exceeding the size of 16 digits, you may verify the length of the string associated with the UI label. To handle overflow and underflow, use &+, &-, and &* instead of +, -, and *. To support different devices and orientations, use stack view, scroll view, or both. Design your algorithm first! Think about the status of the calculator: when to take the first operand, when to take the second operand, when to append digits to the current number, and when to refresh the current number, etc. The functionality of your hex calculator worth 60% of the credit, while the appearance of the user interface worth 40%. When you submit the assignment, please compress the entire project folder into a single zip file, and upload it to D2L. In addition, please provide 4 to 5 screenshots of your app in different devices and orientations. If your app doesn’t work on every device/orientation, please specify why. The screenshots of a sample program are shown below. Your UI does not have to be the same as the sample program. As long as it has a pleasing looking, it should be fine. Contact us to get this project source code at affordable prices. Get Help in any Swift Programming project at affordable prices, we are providing app development services using swift.

Balancing a Binary Search Tree Write three rotation functions that help in transforming an unbalanced Binary Tree to a Balanced Binary Tree. Node* rotateLeft(Node *node); Node* rotateRight(Node *node); Node* rotateLeftRight(Node *node); These functions will take a node at which point you should perform the respective rotations for the unbalanced tree and return the new root. The left right rotation should be performed as a left rotation on node's left child, then a right rotation on node. We have defined the following C++ Node class for you.  The name serves as the value. class Node { public: std::string name; Node* left = NULL; Node* right = NULL; }; Test Cases The first line of input in test cases is the number of nodes in the tree. The second line is the nodes of a tree which are inserted into a binary search tree in that order. You don't need to implement insert. You have access to the root of the constructed Binary Search Tree. We will create the tree for you, then call rotateLeftRight on the root of the tree. This is followed by a call to pre, in, and post-order traversal. The output is an pre, in, post-order traversal of the tree after the rotation separated by spaces. Author: Cheryl Resch, Hamish Pierpont, Ori Leibovici and Amanpreet Kapoor, Date Created: May 2018, Last Modified: 26 Sep 2020 Sample Input: 3 2 0 1 Sample Output: 102 012 021 Write a program, test using stdin → stdout #include #include class Node { public: int name; Node* left = NULL; Node* right = NULL; }; Node* insert(Node* root, int key) { if(root==NULL) { Node* temp=new Node(); temp->name=key; return temp; } if (key < root->name) root->left = insert(root->left, key); else if (key > root->name) root->right = insert(root->right, key); return root; } std::string traverse(Node* head) { std::string string1, string2, string3; if (head == NULL) return ""; string1 = traverse(head->left); string2 = std::to_string(head->name); string3 = traverse(head->right); return string1+string2+string3; } Node* rotateLeft(Node *node) { //your code here } Node* rotateRight(Node *node) { //your code here } Node* rotateLeftRight(Node *node) { //your code here } int main() { Node* root=NULL; int x; int num; std::cin >> num; for(int i=0;i<num;i++) { std::cin>>x; root=insert(root,x); } root=rotateLeftRight(root); std::cout << traverse(root); } BST = AVL ? Problem Statement AVL Tree is a Binary Search Tree that allows Balance Factor of each node to be in the range -1 to 1 (n>=0). Implement a function that takes as input a root node of a Binary Search Tree. This function checks if the BST is an AVL Tree or not. We have defined the following C++ Node class for you: class Node {     public:         int name;         Node* left = NULL;         Node* right = NULL; }; Function to code bool isAVL(Node* root); Example Inserted Numbers in a BST: -  5 2 15 16 9 11 14 ; Tree:- 5 / \ 2 15 / \ 9 16 \ 11 \ 14 Balance Factor of tree = left subtree height – right subtree height = -3 Therefore, this BST is not an AVL Tree. Test Cases The input in test cases are nodes of a tree which are inserted in that order. You don't need to implement insert. You have access to the root of the constructed Binary Search Tree. Return true if the BST is a valid AVL Tree and False if not. Hint: Use a Height function to calculate height of a tree at given node. Author: Amanpreet Kapoor, Date Created: May 2018, Last Modified: 31 May 2020 Sample Input 1: 5 2 15 16 9 11 14 Sample Output 1: false Sample Input 2: 1 2 3 Sample Output 2: false Contact Us to get instant help related to C++, Data Structure Algorithms, etc., at: contact@codersarts.com

StopHacking is a start-up incubated in Monash University to develop cloud service to detect and stop computer hackers. Although they have some rule-based service to identify certain hacks, they would like to add machine learning models which can integrate with their Spark cluster to process large amounts of data and detect any potential hacks. They hired us as the Analytics Engineer to investigate the open data from the Cyber Range Labs of UNSW Canberra and build models based on the data to identify abnormal system behaviour. In addition, they want us to help them integrate the machine learning models into the streaming platform using Apache Kafka and Apache Spark Streaming to detect any real-time threats, in order to stop the hacking. In this part A of the assignment, we would only need to process the static data and train machine learning models based on them. What you are provided with Four data files: Linux_memory_1.csv Linux_memory_2.csv Linux_process_1.csv Linux_process_2.csv These files are available in Moodle in the Assessment section in the Assignment Data folder. A Metadata file is included which contains the information about the dataset. Information on Dataset Four data files recorded from Linux systems are provided, which captures information relating to the memory activity, and process activity. They are a subset of the Internet of Things dataset collected by Dr. Nour Moustafa, from IoT devices, Linux systems, Windows systems, and network devices. For more detailed information on the dataset, ​please refer to the Metadata file included in the assignment dataset or from the website below​ https://ieee-dataport.org/documents/toniot-datasets​. In this assignment, the memory activity and the process activity data are providedseparately, without explicit linkage or computer ID to link up memory and process. For each data, there is a binary label and a multi-class label, one for indicating whether the activity is an attack or not, and the other for indicating which kind of attack the activity is under. What you need to achieve The StopHacking company requires us to build separate models for the memory activity and the process activity. Each activity needs a model for a binary classification predicting whether it is an attack or not, as shown in use case 1 & 2 below. To build the binary classification model, use the column “attack” as your label in the model (label 0 as non- attack event, and label 1 as attack event). You can select any columns as features from each activity data, except “attack” or “type”. Process activity - attack or not Binary classification Memory activity - attack or not Binary classification Architecture The overall architecture of the assignment setup is represented by the following figure. ​Part A of the assignment consists of preparing the data, performing data exploration and extracting features, building and persisting the machine learning models. In both parts, for the data pre-processing, the machine learning processes , you are required to implement the solutions using PySpark SQL / MLlib / ML packages. For the data visualisations, please use Matplotlib packages to prepare the plots, and excessive usage of Pandas for data processing is discouraged. Please follow the steps to document the processes and write the codes in Jupyter Notebook. Getting Started Download the datasets from moodle. Create an ​Assignment-2A.ipynb​ file in Jupyter Notebook to write your solution for process data. You will be using Python 3+ and PySpark 3.0 for this assignment. Data preparation and exploration Create a SparkConf object for using as many local cores as possible, for a proper application name, and for changing the max partition byte configuration1 to enable a minimum of 2 partitions2 when reading each file in Spark SQL (so each dataframe should have at least 4 partitions when reading from the given datafiles). Then create a SparkSession using the SparkConf object. Loading the data Load each activity data into a Spark dataframe and cache the data. Then print out the row count of each dataframe. In order to speed up the loading process, please specify the schema before reading the data into dataframes. You may find relevant schema info from the metadata file, however, note that some data may not fully comply with the schema. For those that do not comply with the schema, import them as StringType and further transform them in step 1.2.2. For each column in each dataframe above, Check the null data (if any) and print out the corresponding count in each column Are these columns: MINFLT, MAJFLT, VSTEXT, RSIZE, VGROW RGROW in memory data following the datatype from the metadata file? If not, please transform them into the proper formats. Exploring the data Show the count of attack and non-attack in each activity based on the column “attack”, then show the count of each kind of attack in process activity based on the column “type”. Do you see any class imbalance? Examine and describe what you observe More details of configuration can be found on ​https://spark.apache.org/docs/latest/configuration.html This is a mock scenario considering the data size and the VM setup. 2. For each numeric feature in each activity, show the basic statistics (including count, mean, stddev, min, max); for each non-numeric feature in each activity, display the top-10 values and the corresponding counts. No need to show the labels at “attack” or “type” column 3. For each activity, present two plots3 worthy of presenting to the StopHacking company, describe your plots and discuss the findings from the plots Hint - 1: you can use the basic plots (e.g. histograms, line charts, scatter plots) for relationship between a column and the “attack” label (such as “ts” and “attack”, “PID” and “attack”); or more advanced plots like correlation plots for relationship between each column; 2: if your data is too large for the plotting, consider using sampling before plotting 100 words max for each plot’s description and discussion Feature extraction and ML training Randomly split the dataset into 80% training data and 20% testing data for each use case With the class imbalance observed from 1.3.1, for the binary classification use case 1 & 2, prepare rebalanced training data, with attack events and non-attack events being 1:2 ratio, while using 20%4 attack events data from the training data from 2.1.1. Cache the rebalanced training data, and display the count of each event's data. Hint - you can use undersampling to get the rebalanced training data Preparing features, labels and models Based on data exploration from 1.3.3, which features would you select? Discuss the 5 reason for selecting them and how you plan to further transform them . 400 words max for the discussion Hint - things to consider include whether to scale the numeric data, whether to choose one-hot encoding or string-indexing for a specific model Create Transformers / Estimators for transforming / assembling the features you selected above in 2.2.1 Training and evaluating models For each use case, use the corresponding ML Pipeline from previous step to train the models on the rebalanced training data from 2.1.2 Hint - each model training might take from 1min to 40min, depending on the complexity of the pipeline model, the amount of training data and the VM computing power For each use case, test the models on the testing data from 2.1.1 and display the count of each combination of attack label and prediction label in formats as below. Compute the AUC, accuracy, recall and precision for the attack label from each model testing result using pyspark MLlib / ML APIs. Discuss which metric is more proper for measuring the model performance on identifying attacks. Display the top-5 most important features in each model. Discuss which pipeline model is better, and whether the feature “ts” should be included in the model . And visualise the ROC curve for the better model you selected for each use case. Using the pipeline model you selected in the previous step, re-train the pipeline model using a bigger set of rebalanced training data, with attack events and non-attack events being 1:2 ratio, while using all attack events data from the full data for both use cases. Then persist the better models for each use case. Contact us to get any machine learning project help related to Apache Kafka and Apache Spark, at: contact@codersarts.com

QUESTION 1 This question is set in the context of a small database that stores information about patients, medications, and prescriptions. A partial schema is shown in the figure below: The tables are populated with the following data: Query 1: SELECT PatientLastName, COUNT(*) AS Num FROM Patient pat INNER JOIN Prescription pre ON pat.PatientID = pre.PatientID GROUP BY pat.PatientLastName ORDER BY pat.PatientLastName ASC; Query 2: SELECT PatientLastName, COUNT(*) AS Num FROM Patient pat LEFT OUTER JOIN Prescription pre ON pat.PatientID = pre.PatientID GROUP BY pat.PatientLastName ORDER BY pat.PatientLastName ASC; Query 3: SELECT PatientLastName, COUNT(pre.MedicationID) AS Num FROM Patient pat LEFT OUTER JOIN Prescription pre ON pat.PatientID = pre.PatientID GROUP BY pat.PatientLastName ORDER BY pat.PatientLastName ASC; Query 4: SELECT HourOfDay, COUNT(*) AS NumRegimes FROM AdminTime GROUP BY HourOfDay HAVING COUNT(*) > 1; Query 5: SELECT AVG(x.NumRegimes) AS AvgOfNumRegimes FROM (SELECT HourOfDay, COUNT(*) AS NumRegimes FROM AdminTime GROUP BY HourOfDay HAVING COUNT(*) <2 ) AS x; Query 6: SELECT COUNT(PatientLastName) AS NumPatientNames FROM Prescription pre INNER JOIN Patient pat ON pre.PatientID = pat.PatientID; Query 7: SELECT PatientLastName FROM Patient pat WHERE NOT EXISTS (SELECT * FROM Prescription pre WHERE pre.PatientID = pat.PatientID); Query 8: SELECT PatientLastName, PrescriptionID FROM Patient pat LEFT OUTER JOIN Prescription pre ON pat.PatientID = pre.PatientID WHERE PrescriptionID IS NULL; Question 2: Draw an ERD for the following situation A laboratory collects specimens that may later be analysed. For each specimen collected, the database should record a unique SpecNo. It should also specify SpecArea, and SpecCollMethod A specimen is analysed when a test order is issued. A specimen may not have a test order until after a considerable delay A test order contains a unique test order number (TONo), TOTestName, TOTestType and TOTestResult A test order is created for exactly one specimen The database should keep track of supplies needed for test orders A test order can use a collection of supplies (0 or more) and a supply can be used on a collection of test orders (0 or more). The Supply entity type contains a unique SuppNo, SuppName, SuppLotNo, and SuppNoInStock Notes M:N relationships should be modelled with associative entities Choose appropriate names for all relationships and entity types based on your common knowledge of test orders and supplies Use doubled line relationships and rectangles to represent weak entities. Underline identifiers that are likely to become primary keys QUESTION 3 The following ERD represents a data model for tracking the allocation of laboratory equipment to chemists working on projects. Convert the ERD into a set of relational schemas. Indicate the functional dependencies, and the PK-FK relationships with arrows. Convert all relations into 3NF. Use this sort of format to represent relations. Contact us to get any help related to SQL Databases, SQL Queries or other database database related help like: Database Homework Help SQL Assignment Help Database Project Help contact@codersarts.com

BASIC Question 1: Write SQL queries for the following: Create a table with top 5 cards for each day based on the amount spent (use the table provided as reference) Top 5 cards for on the amount spent Top cards on ‘16-09-2018’ Top 5 cards for each day based on the amount spent (Note: All parts of the above question need to be done using windowing functions) Question 2: Provide the outputs of LEFT (Keep table X on Left side), INNER & FULL OUTER joins for the following two tables (NOTE:The outputs should have all three columns; Col1, Col2 & Col3)? Question 3: What will be the output of following Query? SELECT CASE WHEN null=null THEN ‘Milk’ Else ‘Egg’ END from DUAL; Question 4 : Consider the single column table below. Answer the following questions: 1. What is the possible data type of the column ‘COL1’? 2. What will the output of the following SQL statements be? ‘SELECT COUNT(*) AS ENTRIES FROM TABLE;’ ‘SELECT COUNT(COL1) AS ENTRIES FROM TABLE;’ ‘SELECT COUNT(DISTINCT COL1) AS ENTRIES FROM TABLE;’ Write a query to fetch rows with values ranging from 0 to 2 (assuming the data type to be same as mentioned above) Question 5: Consider the below table: 1. Find out the number of orders booked and their total purchased amount for each day. The output should be in the below format: "For 2001-10-10 there are 15 orders and total amount is Rs.100" 2. Find the total purchase amount at a customer salesman level and rank the salesmen based on the total sales done 3. Find the orders with all the field in such a manner that, the oldest order date will come first and the highest purchase amount of same day will come first. Question 6: Write a query in SQL to obtain the name of the physicians who are the head of each department. Use the below two tables. Sample table: department INTERMEDIATE Question 1: Write a SQL query to convert Table T1 into Table T2 Question 2: Write a query to find cumulative sum of amount for every customer, without using windowing function Question 3: Using the data available below, write a SQL query to calculate the balance in a card as of the end of each fiscal quarter (FQ)? In case the card has not done any transaction in a particular quarter, the balance on the card is the same as the balance in the previous quarter. Data available Question 4: Write a SQL query to find the products that contribute to the TOP 80% of the sales. Tip: In the example below, if by combining say 4 products we achieve 78% of the sales and by including the next one we reach 82%, in that case we would want to include the 5th product so that we cross the 80% cut off. (Please write a generic query, not specific to this particular example). Part 3 Advanced Level Question 1: Below is a sample dataset showing TV sessions of each TV set of each household. The timestamps have been converted to integer values for ease of operation. Household “111” switch on their TV “1” at 500 and switch it off at 570. However, this has been captured in the data as 2 separate rows. You will have to write a query to convert this into a single row. Similar modification needs to be made to all other subsequent occurrences. Please note that a single valid TV session can be split into more than 2 rows as well (As shown by rows 5-8). Also note that this is just a sample data. Question 2: For every customer, calculate the average visit frequency and average basket size - 1 month after registration, 2 months after registration, 3 months after registration and so on for a maximum of 24 months of tenure. Visit frequency at every monthly checkpoint to be calculated based on past 3 months of data, and basket size at every monthly checkpoint to be calculated based on past 3 purchases. Question 3: Input: As you can see, there are duplicate combinations, ie AB and BA. We need to remove these duplicates and retain only one combination, ie either AB or BA. Doesn’t matter which one you choose to retain. The output should look something like this: You can also interchange the combinations and make it look like: Question 4: Generate the below sequence using SQL query. Assume relevant data if required. If you need solution of these questions or looking any other database assignment help then you can contact with us and get instant help. Contact us to get any help related to database assignment: contact@codersarts.com