Iteration 1 2 3 Previous Sol'n. guess solution Improved Sol'n. Max. Approx. Error * * productlines productLine textDescription htmlDescription image employees employeeNumber lastName firstName extension email office Code reports To job Title offices * officeCode city phone addressLine1 addressLine2 state country postalCode territory HH Ho products productCode productName productLine productScale productVendor product Description quantityInStock buyPrice MSRP customers * customerNumber customerName contactLastName contactFirstName city state phone addressLine1 addressLine2 postalCode country salesRepEmployeeNumber creditLimit HHH |+ HH OH OH orderdetails * orderNumber * product Code quantityOrdered priceEach orderLineNumber # orders orderNumber orderDate requiredDate shipped Date status comments customerNumber payments customerNumber * checkNumber paymentDate amount Database: classicmodels 1. Who is the contact and their phone number for Microscale Inc.? 2. What is the status of all ordered by Petit Auto? 3. What is the status of order 10258 when was it ordered and when was it shipped? 4. What was the last payment for CAF Imports? 5. List the customers who are more that 30 days behind on a payment? A payment is considered behind if it is after the shipped date. 6. What product line has the most sales? 7. What product line has the least sales? 8. What customers have not made an order in the last 60 days? 9. What product have not been ordered in the last 60 dates? 10. What is our most popular product? Number of units sold 3. (35 Points) Chapter 3 - Graphs. (a) (10 Points) DFS vs. BFS : Given a connected graph G = (V, E), and a specific node u EV. You run BFS from u, and you find the BFS tree T rooted at u. You run DFS from u, and you find the same DFS tree T rooted at u. Prove that G = T, i.e., G cannot contain any edges that do not belong to T. (b) (10 Points) Connectivity: Consider an undirected graph G = (V, E) with V = n nodes. Assume that there are two nodes s and t with distance between them strictly greater than 2. . Prove that there must exist some node v, different from s, t, such that deleting v from G disconnects s from t. Draw one example of G, s, t, v. Provide an algorithm that finds such a node v in 0(m + n) time. (e) (5 points) Robotics Motion Planning In autonomous robot navigation problem, you want to design a controller that can nav- igate the robot within a bounded space. Assume that you have a localization algorithm running. Basically the robot can localize itself within this room. Assume you can dis- critize the space of your room into discrete number of squares. The robot does not know the structure of the room. Hence, you can not just draw a line between the initial po- sition and the destination that the robot can follow. The visual capability of the robot can only let it see the neighboring discrete locations. Can you design a controller on the robot that can take the robot from one initial position to a destination position? i. (2 points) Specify one of the algorithms that you learnt in the class to plan the motion of the robot. ii. (2 points) Draw the route that the robot will take to reach its destination. iii. (1 points) After the robot reached its destination (From C to F) as indicated in the discretized space below, the robot wants to go back to its initial location. Draw the path that the robot will take. What is the distance that the robot had to cover (in terms of discretized blocks) to return to its initial location? Initial location Destination Room with finite edges A B D E F G Space can be discretized (c) (5 points) You are visiting Irvine and you want to explore the city of Irvine and visit as much places as possible. You start your special tour from one place and you need to end your special tour at the same place. You want to minimize the effort so you don't want to visit the same place twice (except for the start point). Assume that the city of Irvine is represented as a directed graph with N node and M edges. Where a node is one place to visit and an edge is the route to take to this place. Your special tour in this graph starts at node u is a simple path that begins and ends at the same node u. Formally, a special tour is path u, V, V2, ..., Vi, ..., u where vi are distinct and not equal to u for all i. For every node in the given graph, tell whether it is possible for you to do your special tour starting at that node. Given: N 5, M = 6 Graph represented as adjacency list as follows: 1 2 3 4 5 2 3 4 1 2 5 (d) (5 points) Indicate which one of these two graphs is bipartite. Justify your answer. (c) (5 points) You are visiting Irvine and you want to explore the city of Irvine and visit as much places as possible. You start your special tour from one place and you need to end your special tour at the same place. You want to minimize the effort so you don't want to visit the same place twice (except for the start point). Assume that the city of Irvine is represented as a directed graph with N node and M edges. Where a node is one place to visit and an edge is the route to take to this place. Your special tour in this graph starts at node u is a simple path that begins and ends at the same node u. Formally, a special tour is path u, V, V2, ..., Vi, ..., u where vi are distinct and not equal to u for all i. For every node in the given graph, tell whether it is possible for you to do your special tour starting at that node. Given: N 5, M = 6 Graph represented as adjacency list as follows: 1 2 3 4 5 2 3 4 1 2 5 (d) (5 points) Indicate which one of these two graphs is bipartite. Justify your answer. 1. 2. 3. (20 points) Formulate the Pacman Game with two different goals. Pacman can start at any position in the maze. The starting position is also called birth location. It then can freely turn its direction to face up, down, left, or right. a. Formulate this problem with Goal 1: Find a path from the birth location to a certain point in the maze. Formulate this problem with Goal 2: Eat all the food in the maze. b. C. Suppose there are 180 distinct Pacman (x,y) positions. Only one Pacman and three ghosts are in the same maze. Each ghost can be in 8 distinct (x,y) positions. There are 40 food pellets, each of which can be eaten or not eaten. Please answer the following questions. What is the size of the state space? Explain your answer. a. b. (20 points, 10 points each) Consider a state space where the start state is number 1 and the successor function for state n returns two states, numbers 2n and 2n + 1. How many stating states for pathing? Explain your answer. How many states for the Goal 2 (eat-all-food)? Explain your answer. BFS: DFS: IDS: Draw the portion of the state space for states 1 to 15 in a tree. Suppose the root state has a depth of 0 and the goal state is 11 and. List the order in which nodes will be visited for breadth-first search, depth-first search, depth-limited search with limit 2, and iterative deepening search with limit 3? (20 points, 4 points each) Consider the search graph below. The h value of a node is given adjacent to that node. The actual cost of traversing an arc is given adjacent to the arc. Node S is the start/initial state. Nodes K and L are goals. Leaf states/nodes have no successors. BFS(f=h): A* search: D A E 6 8J Pacman Game 8 S F Give the order in which nodes are visited (i.e., checked for goalless) by each of the following strategies. In the case of two or more nodes with the same evaluation score, break the tie alphabetically; checking the nodes from left-to-right as the nodes appear in the graph above. H 1. (25 Points) Chapter 1 - Stable Marriage. Consider the following preference lists for men and women: Man #Preference List 1 2 3 4 2 1 4 3 4 1 2 3 2 1 answer. 3 4 1 2 3 4 Woman #Preference List 1 4 3 2 1 2 3 4 1 2 2 1 4 3 1 2 3 4 3 4 (a) (5 Points) Find the man optimal stable matching. Show some intermediate steps of your (b) (5 Points) Find the woman optimal stable matching. Justify your answer (c) (5 Points) Suppose that in another problem setting all men have identical preferences. How many steps does it take for the algorithm to converge? Justify your answer. (d) (10 Points) Truthfulness in Stable Marriage. The classic stable matching problem assumes that all men and women state their true preferences. Let us now consider a version of the problem in which people can lie about their preference. More specifically, let us consider the following restricted scenario. Consider three men m, m2, m3 and three women w, W2, W3. Everybody states their true preferences except for woman w3, who can lie to some extent: she can look at her true preference list and switch the place of two men in her list with each other (she can only flip a pair). Woman w3 has no control over the true preference lists (her own or other people's). The only decisions she can make are: (1) whether to lie or not (in the way mentioned above, i.e., switching a pair of men in her list); and (2) if she decides to lie, she can also choose which pair (among all three pairs of men) to switch in her list. The GS algorithm then runs with input the declared preference lists and produces as output a stable matching. The question is: can woman w3 get a better match by lying about her preferences? Resolve this question by doing one of the following: i. (5 Points) Prove that, for any set of preference lists, switching the order of a pair on the list cannot improve a woman's partner in the GS algorithm; or ii. (5 Points) Give an example of a set of preference lists for which, there is a switch that would improve the partner of a woman who switched preferences. And if she decides to lie, which pair of men should she switch in her preference list? 2. (40 Points) Chapter 2 - Running Time Analysis. (a) (20 Points) Growth Rates Order the functions in ascending order of growth rate. That is, if function g(n) immediately follows function f(n) in your list, then it should be the case that f(n) is O(g(n)). 91 (n) = 2logn ,92(n) = 2", 93(n) = n(logn), 94(n) = n3,95 (n) = nlogn, nlogn, ge(n) = 22,97(n) = 2n (b) (20 Points) Consider the problem below: You're given an array A consisting of n integers A[1], A[2], ...., A[n]. You'd like to output a two-dimensional n-by-n array B in which B[i, j] (for i 5 4 3 2 1 1 2 3 -1000 4 +100 5 Data Dictionary Mechanic Attribute name Attribute data type Mechanic. Integer Mechanic First Name CHAR Mechanic Last Name CHAR Mechanic. Phone Mechanic. Email CHAR CHAR Attribute description Given unique ID for each Mechanic (Primary Key) Integer First name of the Mechanic Last name of the Mechanic Mobile number of the Mechanic Attribute values Email address of Mechanic Alphabetic String Alphabetic String Alphanumeric String Alphanumeric String Data Dictionary Car Attribute name Car R Serial Number Make Year Model Attribute name Invoice Ja Card Customer R Sales Person Invoice Num Date Attribute data type Attribute description Integer Unique ID for each car (Primary key) Serial Number given by the vehicle manufacturer Alphanumeric String Manufacturer / Brand of the Car Alphanumeric String Manufactured date of the Car Alphanumeric String Format: YYYY Alphabetic String CHAR Data Dictionary Sales Invoice Attribute name CHAR CHAR CHAR Attribute data type Attribute description Integer Integer Integer CHAR Integer Date Data Dictionary Salespersons Attribute data type Sales Person ID Integer Salesperson First Name CHAR Salesperson Last Name CHAR Sales Person Phone Car Model CHAR Unique ID for Sales Invoice Unique ID for each car Unique ID for each Customer Name of Salesperson Invoice Number assigned to invoices Date issued of Sales Invoice Attribute description Attribute values Integer Attribute values Integer Integer Integer Alphabetic String Integer Alphanumeric String Format: MM-DD-YYYY Attribute values Given unique ID for each Customer (Primary Key) Integer First name of the Salesperson Last name of the Salesperson Mobile number of the Salesperson Alphabetic String Alphabetic String Alphanumeric String Data Dictionary Service Attribute name Service JD Service June Service Description CHAR Hourly Bate Attribute name MechService. JR Data Dictionary Mechanic Service Mechanic. Service JD Ticket JD Hour Attribute data type Integer CHAR Rate Comment DECIMAL Attribute data type Integer Integer Integer Integer Decimal Decimal VARCHAR(800) Data Dictionary Mechanic Attribute name Attribute data type Mechanic. Integer Mechanic First Name CHAR Mechanic Last Name CHAR Mechanic Phone CHAR Attribute description Unique Service ID for each Services Type/Category of Service or task to be given Full Description of Service Hourly rate of Service mechanic Attribute description Unique mechanic Service ID for each Services (Primary Key) Unique ID for each mechanic (Foreign Key) Unique Service ID for each Services (Foreign Key) unique ID for each Service Ticket (Foreign Key) Number of Working hours per service Rate of Mechanic Remarks and comments of the mechanic on the service Attribute values Integer Mobile number of the Mechanic Alphanumeric String Alphanumeric String Numeric Attribute values Integer Integer Integer Integer Numeric Numeric Alphanumeric String Attribute description Given unique ID for each Mechanic (Primary Key) Integer First name of the Mechanic Last name of the Mechanic Attribute values Alphabetic String Alphabetic String Alphanumeric String Mechanic Service PK MechService_ID FK1 Mechanic_ID FK2 Service ID FK3 Ticket_ID Hours Rate Comment Service Ticket PK Ticket_ID FK1 Car_ID FK2 Customer_ID Ticket_Num Date_Recorded Date_Returned Ticket Comments Mechanic PK Mechanic_ID Mech_First Name Mech_Last Name Mech_Phone Mech Email Service PK Service_ID Service Type Service Description Hourly Rate PK Car_ID Year Serial Num Make Car Model Customer PK Customer_ID Customer_First_Name Customer_Last Name Customer_Phone Customer_Email Customer_Address Customer_City Sales Person PK SalesPerson_ID Sales Person_First_Name Sales Person_Last Name Sales Person_Phone Sales Invoice PK Invoice_ID Car_ID Customer_ID Sales Person_ID Invoice_Num Date Data Dictionary Customer Attribute name Customer R Customer First Name Customer last Name Customer Phone Customer Email Customer Address Customer City Attribute name Ticket JD Car R Custumer.D Ticket Num Date Recorded Date Returned Attribute data type Integer Ticket Comments CHAR Data Dictionary Service Ticket CHAR CHAR CHAR VARCHAR(255) CHAR Attribute data type Integer Integer Integer Integer Datetime Datetime VARCHAR(800) Attribute description Given unique ID for each Customer (Primary Key) First name of the Customer Last name of the Customer Mobile number of the customer Email address of the customer The current location of the customer's residence. The maximum Length is 255 City where the customer residents belong Attribute description unique ID for each Service Ticket (Primary Key) Unique ID for each car (Foreign Key) Given unique ID for each Customer (Foreign Key) Unique Service Ticket Number Issued/created date of Ticket Returned date of Ticket Comments indicated in the issued Ticket Attribute values N/A Alphabetic String Alphabetic String Alphanumeric String Alphanumeric String Alphanumeric String Alphanumeric String Attribute values Integer Integer Integer Integer Alphanumeric String Format: MM-DD-YYYY, HH:MM Alphanumeric String Format: MM-DD-YYYY, HH:MM Alphabetic String Units of Measure Attribute Diameter Mass Density Inclination Rotation Aphelion Perihelion Eccentricity Period_Orbit Radius Orbit X,Y, and Z All Composition Attributes Water and Rock Unit of Measure Meters Kilograms Kilograms per Cubic Meter Degrees Hours Astronomical Units Astronomical Units Ratio Years Astronomical Units Number (Ordinate) Percentages of Mass Percentages Relations registry Designation AType Country DDate spatial Coord Designation X specifications Designation Diameter Mass Density Inclination Rotation orbit Designation Aphelion Perihelion Eccentricity Period_Orbit Y Z surface feature Designation Surface Water Radius_Orbit 4 composition_simple Designation Content Rock Content Metal Designation Nickel Molybdenum Iron Zinc composition_precious composition_common Designation Gold Silver Platinum Palladium Rhodium Ruthenium Iridium Osmium composition_strategic Designation Chromium Cobalt Tungsten Uranium Relationships - Relationships registry 1 1 HAS 1 Surface_feature spatialCoord orbit specifications 1 3 Composition_simple Composition_precious Composition_common Composition_strategic Stored Procedure 1 6 Create the Stored Procedure showType which accepts an Asteroid Type (T) and an integer (C)as its parameters and returns the data about the specified number (C) of asteroids of that type (T) in the format shown below. Hyx MySQL 8.0 Command Line Client | Designation | AType | C-a1872-1 | Carboneous | US | Carboneous | UK | Carboneous | UK C-a2151-m | C-a2440-j 3 rows in set (0.00 sec) | Country | DDate 2007-09-02 1994-08-08 | 1991-10-27 US = 'United States' UK = United Kingdom' RUSSIA = 'Russian Federation' CHINA = People's Republic of China Carboneous = 'CARBON_BASED' Metallic = 'METAL_BASED' Silicaceous = 'SILICON_BASED' X Hyx MySQL 8.0 Command Line Client F Data | C-a1872-1 Type: CARBON_BASED Asteroid Country: United States Season: Fall 2007 1 row in set (0.00 sec) | F_Data | C-a2151-m Type: CARBON_BASED Asteroid Country: United Kingdom Season: Summer 1994 | +--- 1 row in set (0.00 sec) | F_Data | C-a2440-j Type: CARBON_BASED Asteroid Country: United Kingdom Season: Fall 1991 | November - March = 'Winter' April - May = 'Spring' June - August = 'Summer' September - October = 'Fall' nits of Measure Attribute Klograms