Jonathan's Magic Emporium was founded in 1957 in Seattle, Washington. It has grown into an internationally recognized organization in the magical community. The company is in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The company database contains information about employees, departments, projects, and assets. 

 

A department has many employees, but an employee only works for one department. A department may have many projects or no projects, but a project is assigned to only one department. An employee may be assigned more than one project. The asset table currently only contains data about computers. An employee is assigned only one computer and a computer can only be assigned to one employee at a time. 

 

1. Based on your analysis of the sample data below (primary keys are bolded and foreign keys are bold/italicized), use a diagramming software of your choice to create an E-R diagram for Jonathan's Magic Emporium Database. Document it fully, noting primary keys, foreign keys, cardinality, and modality. For a deliverable, insert the diagram into your answer sheet as a picture.   If you choose to use https://app.diagrams.net 

 

2. Recreate the above tables in MySQL, using the sample data below to determine data types and lengths. During table creation, make sure to define primary keys and foreign keys. For deliverables, take screenshots of each Create Table command (5 total). 

 

3. Insert the data below into your newly created tables. Data should match exactly as it is shown (capitalization, punctuation, etc.). For deliverables take a screenshot of a SELECT * command being performed on each table (5 total). 

 

4. Create the following queries. Each query should be completed as a single command. If a column is the result of a calculation or a function, use an alias to assign a more descriptive name. Make sure screenshots show the full SQL command and its results.

 

a. Assume all employees make 18.50 an hour. Create a query that lists the projectID, projectName, MaxHours, and the calculated ProjectCost.

 

b. Jonathan's Magic Emporium is interested in seeing which departments have more than one employee. Create a report listing the department name and the number of employees that work in that department. 

 

c. Using a subquery, list all employees (firstname, lastname) who have worked more than 50 hours on any single assignment. Hint: Use of a distinct in your subquery may be useful. 

 

d. Create a report listing the project name, the first and last name of the employee assigned to the project, and the number of hours the employee has worked. Order by project name.

 

e. Who is currently using which computer? Create a SQL query showing serial Number, Make, and model, along with the employeeID, firstname, lastname, and department of the assigned employee. 

 

 

5. Create a new table, based on the existing assets table. Name it DellComputer and insert all recorders with the Make of DELL into it. Do this in a single SQL command. 

 

6. In the DellComputer table, decrease the purchase price of all records by 10%. 

 

 

7. In the DellComputer table, delete all records whose serial number ends in Q2. 

 

8. Remove the DellComputer table from your database. 

 

 

Department

DepartmentName

BudgetCode

OfficeNumber

Phone

Administration

BC-100-10

BLDG01-300

360-285-8100

Legal

BC-200-10

BLDG01-301

360-285-8200

Accounting

BC-300-10

BLDG01-302

360-285-8300

Finance

BC-400-10

BLDG01-303

360-285-8400

Human R..n@W..e@W..s@W..s@W..y@WPC.com7GeorgeSmithHuman R..h@WPC.com8TomJacksonProduction360-285-8880TomJackson@W..s@WPC.com10KenNumotoMarketing360-285-8101KenNumoto@W..r@WPC.com12RickBrownInfoSystems360-285-8121RickBrown@WPC.com

 

Project

ProjectID	ProjectName	Department	MaxHours	StartDate	EndDate
1000	2023 Q3 Product Plan	Marketing	135	10-May-23	15-Jun-23
1100	2023 Q3 Portfolio Analysis	Finance	120	5-Jul-23	25-Jul-23
1200	2023 Q3 Tax Preparation	Accounting	145	10-Aug-23	15-Oct-23
1300	2023 Q4 Product Plan	Marketing	150	10-Aug-23	15-Sep-23
1400	2023 Q4 Portfolio Analysis	Finance	140	5-Oct-23	31-Dec-23

 

Assignment

AssignmentID	ProjectID	EmployeeNumber	HoursWorked
1	1000	1	30
2	1000	8	75
3	1000	10	55
4	1100	4	40
5	1100	6	45
6	1200	1	25
7	1200	2	20
8	1200	4	45
9	1200	5	40
10	1300	1	35
11	1300	8	80
12	1300	10	50
13	1400	4	15
14	1400	5	10
15	1400	6	27.5

 

 

Assets

Serial Number	Make	Model	PurchaseAmount	EmployeeIssued
2S1K4Q2	DELL	LATITUDE 5590	820.77	1
3MLN4Q2	DELL	LATITUDE 5590	820.77	2
95LLST2	DELL	LATITUDE 5590	1385.05	3
2ZKX2X2	DELL	LATITUDE 5590	974	4
8MXQ4Q2	DELL	LATITUDE 5590	820.77	5
JCCG4Q2	DELL	LATITUDE 5590	820.77	6
GG2H4Q2	DELL	LATITUDE 5590	820.77	7
70LX2X2	DELL	LATITUDE 5590	974	8
JFRX2X2	Apple	MACBOOK PRO 15	3290	9
FBZ15Y2	Apple	MACBOOK PRO 15	3290	10
786D4Q2	Apple	MACBOOK PRO 15	3290	11
3PY33X2	Apple	MACBOOK PRO 15	3290	12

  

Department DepartmentName BudgetCode OfficeNumber Phone Administration BC-100-10 BLDG01-300 360-285-8100 Legal BC-200-10 BLDG01-301 360-285-8200 Accounting BC-300-10 BLDG01-302 360-285-8300 Finance BC-400-10 BLDG01-303 360-285-8400 Human Resources BC-500-10 BLDG01-304 360-285-8500 BC-600-10 BLDG01-305 360-285-8600 BC-700-10 BLDG02-99 360-285-8700 BC-800-10 BLDG02-100 360-285-8800 Production Marketing InfoSystems