Explore BrainMass

Explore BrainMass

    Creating a Database with Specific Queries

    Not what you're looking for? Search our solutions OR ask your own Custom question.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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.

    © BrainMass Inc. brainmass.com November 24, 2021, 11:16 am ad1c9bdddf
    https://brainmass.com/engineering/electrical-engineering/creating-database-specific-queries-18553

    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.49

    ADVERTISEMENT