Share
Explore BrainMass

Creating a Database with Specific Queries

Create a database with the given E-Diagram and the given queries.

Emmanuel Akpan

Given the schema that we decided to adopt as a standard, create the database in Oracle (or other database if you can't get Oracle to run on your machine).

The data to be entered into your database is listed below.

Department: Departmentnumber Departmentname Manager
1 Consumer 1
2 Commercial 2
3 Emerging 3

Employee: Employeenumber Firstname Lastname Salary Deptnum
1 John Jones 30 1
2 James Smith 30 2
3 Susan Johnson 30 3
4 Linda Gold 30 1
5 Michael Silver 30 2
6 George Diamond 30 3

Widget: Widgetnum Widgetname Widgetmanager Departmentnumber
1 GPS 4 1
2 RFID 2 2
3 Ubiquitous 6 3

Parts: Partnumber Partname
1 Nut
2 Bolt
3 Washer
4 Gasket
5 Button
6 Display

Supplier: Suppliernumber Suppliername Suppliercity
1 Acme New York
2 PartsRUs Newark
3 QuickParts Chicago

WorksOn: Employeenumber Widgetnumber
1 1
2 2
3 3
4 1
5 2
6 3
1 2
2 3
4 3
5 1
6 2

Contains: Widgetnumber Partnumber Quantity
1 1 1
1 2 2
1 3 3
2 3 4
2 4 5
2 5 6
3 2 7
3 5 8
3 6 9

SuppliedBy: Partnumber Suppliernumber
1 1
1 2
2 2
2 3
3 3
3 1
4 1
4 2
5 2
5 3
6 3
6 1

When your database is created, create and run the following queries on the database:

1) The names of all widget managers who also work on widgets in other departments.
2) The names of all suppliers who supply all the parts of any widget, and the name of the widget.

I would like to have the following emailed to me, in text form:

1) The schema script that you used to create the database.
2) The queries that you wrote.
3) The results of the queries when run on the database.

Attachments

Solution Preview

See attachments

1) The schema script that you used to create the database.

The Access Db will be part of the answer

2) The queries that you wrote.

a) The names of all widget managers who also work on widgets in other departments.

Version 1 (Query name in db: WM2)
SELECT Employee.FirstName, Employee.LastName FROM Widget, Employee, WorksOn, Widget AS Widget_1 WHERE ((Widget.WidgetManager = Employee.EmpNum and Employee.EmpNum = WorksOn.EmpNum and WorksOn.WidgetNum = Widget_1.WidgetNum and Not (Widget_1.DeptNum)=[Widget]![DeptNum]));

or ...

Solution Summary

The solution contains the database made in Microsoft Access as well as a helpful word document showing input and output as a result of inputting the queries given by the asker,.

$2.19