ADMN 873, Structured Data Relational Database Structure & SQL Queries <Edit this document for your submission. Instructions provided with the questions on what to submit.> This assignment uses the classicmodels database, a database maintained by a company that sells scale-model replicas of classic cars. You will be examining this database, addressing some questions, and then writing SQL queries to obtain information from the database. Brief descriptions of the tables follow: ● Customers: stores customer's data. ● Products: stores a list of scale model cars. ProductLines: stores a list of product line categories. Orders: stores sales orders placed by customers. OrderDetails: stores sales order line items for each sales order. ● ● ● ● ● Name: ● Payments: stores payments made by customers based on their accounts. Employees: stores all employee information as well as the organization structure such as who reports to whom. Offices: stores sales office data. Figure 1 shows the ERD; Figure 2 lists the tables. Preparations Go to http://myisba.org/admn873/ and log in using the username and password you were provided via email. This assignment uses the myisba44_rbg_classicmodels database. Submit ● Submit this edited Word file. Please rename to be HW06_lastname_firstname.docx. productlines *productLine textDescription htmlDescription image employees * employeeNumber lastName firstName extension email officeCode reports To job Title offices * officeCode city phone addressLine1 addressLine2 state country postalCode territory Figure 1. ERD for classicmodels Filters Containing the word: Table customers employees offices orderdetails orders payments productlines products 8 tables Action HHH Sum Check all Query With selected: Server: localhost: 3306 » Database: rogerg81_classicmodels_01 Structure SQL Search *productCode productName productLine productScale productVendor product Description quantityInStock buyPrice MSRP Print Data dictionary Figure 2. Listing of tables for classicmodels products customerNumber customerName contactLastName contactFirstName city state phone addressLine1 addressLine2 customers postalCode country salesRepEmployeeNumber creditLimit Export Browse Structure Search Insert Empty Drop Browse Structure Search Insert Empty Drop Browse Structure Search Insert Empty → Drop Browse Structure Search Insert Empty Drop Browse Structure Search Insert Empty Drop Browse Structure Search Insert Empty Drop Browse Structure Search Insert Empty Drop Browse Structure Search Insert Empty Drop Import Operations HH +H +H OH eH Routines orderdetails orderNumber *productCode quantityOrdered priceEach orderLineNumber orders orderNumber orderDate required Date shippedDate status comments customerNumber payments customerNumber * checkNumber paymentDate amount Events Triggers Rows Type Collation Size Overhead 122 InnoDB latin1_swedish_ci 32 KiB 23 InnoDB latin1_swedish_ci 48 KiB 7 InnoDB latin1_swedish_ci 16 KiB 2,996 InnoDB latin1_swedish_ci 32 KiB 326 InnoDB latin1_swedish_ci 64 KiB 273 InnoDB latin1 swedish_ci 16 KiB 7 InnoDB latin1_swedish_ci 16 KiB 110 InnoDB latin1_swedish_ci se KiB 3,864 InnoDB utf8_general_ci 304 KiB 0 B Designer Part 1. Database Structure (assess navigation of database, relational database concepts) Question 1.1. (answer by filling in the table) How many unique products are there? How many units are in inventory of the "1968 Dodge Charger?" What country is the customer "Baane Mini Imports" in? Question 1.2. (answer by filling in the table) What is the cardinality of the productlines to products relationship? Question 1.3. Complete the information about the primary and foreign key(s) of the customers and orderdetails table. Note that a table can have more than one foreign key. You may not need all the space in the table. If you need more space, insert/add rows as required. Primary Key Table customer orderdetails Foreign Key (FK) FK Related Table Part 2. Database (SQL) Queries Instructions: Please add your responses to these questions. For each question, include a) SQL code (as text, not an image); b) total number of rows in the result set (not just what shows on the first screen); and c) a screen shot of the phpMyAdmin window. Keep the question text and the full response on the same page (repaginate as necessary, and note that Ctrl-Enter inserts a page break in Word). Make sure the screen shots include the database name, the SQL query, the number of total rows, and the actual results. For queries resulting in more than 25 results (the default shown on the screen), just take a screen shot of the first page of results and include that). An example is provided. General hints: Keep a text file of your SQL statements separate from MySQL, and copy/paste between the text editor and MySQL. Start each query clause (e.g., SELECT, FROM, WHERE, ...) on a separate line, IN ALL CAPS, and end the query with “;”. When you have finalized the query, copy/paste the correct query back to the text editor and to the place provided in this document. Refer to the ERD often to understand the logical relationships. The question may ask for several conditions. Start out with a basic query, and then add clauses and conditions to the query to match what is being asked for. This incremental approach tends to make building and debugging queries easier, and helps you to verify that what the query is providing as a result is indeed what is being asked. EXAMPLE (to show format for submission) Question 0: List the first name, last name, email address, and extension (in this order of fields) for all employees. Note: I have circled some items on this screen shot; you don't need to do that on yours. SELECT firstName, lastName, email, extension FROM employees; 23 Query Number Rows in Result Server: localhost:3300 Database: rogerg81_classicmodels_01 > Table: employees Browse SQL Search ✓ Showing rows 0-22 (23 total, Query took 0.0051 seconds.) SELECT firstName, lastName, email, extension FROM employees Show all Number of rows: 25 + Options firstName lastName email Diane Murphy Mary Patterson Firrelli Jeff Jeff William Patterson Gerard Bondur Anthony Bow Leslie Jennings Leslie Julie Thompson Firrelli Patterson Tseng Vanauf Bondur Steve Can Foon Yue A Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. George Dow Loui Gerard Pamela Structure Castillo Bott Bott Larry Barry Jones Andy Fixter Peter Tom extension X5406 X5420 dmurphy@classicmodelcars.com x5800 mpatterso @classicmodelcars.com x4611 jfirrelli@classicmodelcars.com x9273 wpatterson@classicmodelcars.com x4871 gbondur@classicmodelcars.com x5408 abow@classicmodelcars.com x5428 ljennings@classicmodelcars.com x3291 X3291 Ithompson@classicmodelcars.com x4065 jfirrelli@classicmodelcars.com x2173 spatterson@classicmodelcars.com x4334 ftseng@classicmodelcars.com x2248 gvanauf@classicmodelcars.com x4102 6402 Ibondur@classicmodelcars. x6493 Hernandez_ghernande@classicmodelcars.com x2028 il classicmodelcars.com pcastillo@classicmodelcars.com x2759 Ibott@classicmodelcars.com 2311 biones@classicmodelcars.com ✓102 X102 afixter@classicmodelcars.com x101 2249 4402 2759 Marsh pmarsh@classicmodelcars.com X102 x101 x102 tking@classicmodelcars.com x103 mnishi@classicmodelcars.com x101 ykato@classicmodelcars.com x102 mgerard@classicmodelcars.com x2312 King Nishi Mami foshimi Kato Martin Gerard Insert Show all Number of rows: 25 Y Export Filter rows: Search this table Import Filter rows: Search this table Operations 28 Triggers Sort by key: None Sort by key: None One-Table Queries Question 2.1. Similar to above (Question 0), but also show the jobTitle. Name the columns (in this order) "First Name," "Last Name", "Title," "Email", and "Phone Ext". Only show the employees in office number (code) 1. Hint: Use the AS clause to create the aliases. Order the list alphabetically by last name, then first name. Query Number Rows in Result Screenshot (paste below):