Amys Bikes is a new bike shop located in a suburb in Ontario, offering a wide range of bicycles and related accessories. Amy, the shops owner, has been conducting her daily business mostly on paper. She records sales on preprinted forms. Employee and customer information is maintained on sheets of paper. For each employee, this includes his/her social insurance number, first and last name, and home phone number. For each customer, Amy records the first, middle (if any) and last name, as well as at least one phone number and home address (consisting of street number and post code). To keep track of the product, Amy uses a spreadsheet program to record the number, name, price and quantity of the products in stock. For each product, a range of services is offered. The spreadsheet program is also used to list the type (e.g., repair, exchange) and charge for each service. Multiple types of service may be offered to one product (e.g., repair and exchange for bikes) and a service may be offered to multiple products (e.g., repair for bikes and accessories) at different prices. The service is optional and is offered only if the customers opts for it. Each employee is connected to the customer it attends to.

Amy spends a lot of time maintaining this information. Recently, she has decided to use database to manage all this data. Now help Amy to design an ER diagram that better captures the requirements. It should contain all relevant concepts in the requirements. Use any notation you like (chens, crow) and any software (Lucid chart would be the easiest to use).

INSERT THE CHART WITH THE FOLLOWING CONSTRAINTS

NORMALIZE THE ATTRIBUTES FOR THE CUSTOMER WHERE REQUIRED.

WRITE THE FOREIGN AND COMPOSITE KEYS FOR THE ENTITIES WHERE APPLICABLE.

Consider the Tables below and write the SQL queries. Answer each question and paste the answer as asked in the blanks

Create Table employee and incentives. Make sure to define the respective PRIMARY and FOREIGN keys wherever applicable.

Paste create table Query in text format for Table employee

Paste create table Query in text format for Table incentives

From the csv files in Moodle, import the data into both tables

After importing the data successfully into employee run SELECT * FROM employee and paste the resulting screenshot here.

After importing the data successfully into incentives run SELECT * FROM incentives and paste the resulting screenshot here.

Get First_Name and Last_Name as single column from employee table separated by a '_' .

Write the query below in the first box in text format. Then paste the screenshot in the second box of the query.

Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000.

Write the query below in the first box in text format. Then paste the screenshot in the second box of the query.

Select 2nd Highest salary from employee table.

Write the query below in the first box in text format. Then paste the screenshot in the second box of the query.

Alter Table incentives to add another column to add employee names as firstname_lastname (concatenate the 2 names) only for the employees that received the incentives.

Write the query below in the first box in text format. Then paste the screenshot in the second box of the query. There may be multiple queries required for this question. Write each query in the order they will be executed with space between each new query.

Add a column named employee_type in the employee table. Use case and select to update the column data according to the criteria below: [10 Marks]

Salary > 700000, employee_type is Executive.

Salary > 600000, employee_type is Manager.

else all others are Standard.

Write the query below in the first box in text format. Then paste the screenshot in the second box of the query. There may be multiple queries required for this question. Write each query in the order they will be executed with space between each new query.

Write SQL queries once it is imported for the questions asked below.

Department

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

		TEAM LEADER
		TEAM LEADER
		TEAM LEADER

List the employees who are either CLERK or ANALYST in the Desc order.

List the employees who are senior to BLAKE working at CHICAGO & BOSTON. [seniority is in terms of the hire date]

Concatenate the employee name and their respective managers name with the phrase works for [ex. Smith works for Ford]. Write the SQL query that displays this information for everyone except the president.