Search for question
Question

MySql DBMS system which comes bundled with software called XAMPP.

1. Create a database of an example company, 'etrade' for managing small business customers,

orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting.

Naming Convention used

Field names are in snake_case.

• primary keys named id.

• foreign keys constrained named as fk_table_name_ reference_table_name.

• All the column data types are valid with most databases.

Complete the following steps to complete this Step 2:

2.1 Downloaded the provided two SQL database scripts for 'etrade' database.

1. schema_extended.sql - DDL Statements (Table creation & Foreign Key

definitions): schema_extended.sql:

2. data_extended.sql - Insert statements to populate with sample data:

data_extended.sql:

2.2 Open XAMPP Control Panel and start MySQL and Apache servers. Both should appear

green. Click Admin in front of MySQL from the same Control Panel

2.3 Check from your browser that you have access to phpMyAdmin interface

2.4 From the Right Panel, select Import and then click on the Browse button under the File to

Import section.

2.5 Select the script named: schema_extended.sql/n2.2 Open XAMPP Control Panel and start MySQL and Apache servers. Both should appear

green. Click Admin in front of MySQL from the same Control Panel

2.3 Check from your browser that you have access to phpMyAdmin interface

2.4 From the Right Panel, select Import and then click on the Browse button under the File to

Import section.

2.5 Select the script named: schema_extended.sql

2.6 Click on Import from the left bottom

2.7 The script should run successfully

2.8 Confirm from the Left panel that 'northwind' database structures are created with tables like

customers, employees, invoices, orders etc. Note that tables are empty with no data by clicking

on any table from the left

2.9 From the Right Panel, select Import and then click on the Browse button under the File to

Import section.

2.10 Select the script named: data_extended.sql

2.11 Click on Import from the left bottom/n2.12 The script should run successfully

2.13 Confirm from the Left panel that 'XYZ_Traders' database is populated with data in tables

like customers, employees, invoices, orders etc.

2.14 Write and run the following queries and submit their output in Only One PDF file:

A. Display the shipping_fee that has been charged by all of the orders in the orders table (10

points)

B. Display the average list price of products (15 points)

C. Display the number of employees working in each city (15 points)

D. Display the price of all products that have a higher list_price than the average unit price?

(20 points)

E. List the product_id, product_name and product_code of the prodcuts which have never

been ordered(20 points)

Fig: 1

Fig: 2

Fig: 3