Question

Pad 2:49 PM @ * 51%- 2 protile cam 3 dell pin hole PPIO bar stock 2 plas T100 PP125 PP12 PPI2S dell mounting 1 cut round stock 4 turn round stock M 15 20 25 15 25 1 eut round stock M 2 turn round stack M 1ut round stock M T210 PP250 12 finish gnind 1 out plate 2mill sufac dell holes T250 (a) Create the database and tables in Access DBMS (b) Write the SQL. code that will generate answers to the following questions: 1) List all the entries from the parts table. (2) List the instances of parts that use raw material RM302 (3) List the instances of all parts, alphabetically ordered by part description. 4) List the instances of all schedules with lot sizes greater than 15 (5) List the instances of all parts that require machine MI (6) List the machines and their average, minimum, and maximum unit machining (7) For each schodule, list the schedule ID. part number, operation number, machine ID setup time, total machining time, and total time (setup plus machining). Be sure that each attribute column has a title The Relational Database Maiel (8) List the tooling that is used with each raw material without repetition in the tuple. (9) List the schedule release date, part namber that will be machined, and machine ID (10) List the part number and machine ID that will be used in production on schedule ) List just the machine ID (without duplication) that will be used by schedules released (12) Using a query/subquery, list all the part numbers that have operations with a setup (13) List the material ID and the number of pieces of raw material that will be required 2.5 Bikes-R-US maintains a database of products sold to customers, and the invoices for those that will be used by schedule release date descending release date 4/1/06 on 4/IN06, time greater than 8 minutes for all the scheduled job releases on 406 sales are used to bill customers. The Access database can be downloaded from the web sitc supporting this book. The following tables are kept in the database:

Answer 1

Access DBMS:

a.) Create database and tables

Open MS Access

Opt for Blank database and then enter the name and click on ‘Create’.

Then blank table is opened, where user can add new fields

or

CREATE TABLE parts (

process_planid int,

OP_no varchar(80)

....

);

b.)SQL queries

_____________

1. SELECT * FROM parts; //to list all data in parts

2. SELECT * FROM parts WHERE raw_material = RM302;//parts with a condition

3. SELECT * FROM parts ORDER BY op_desc ASC;//ordered by Ascending order

4. SELECT * FROM schedules WHERE lot_sizes > 15;// parts with a condition

5. SELECT * FROM parts WHERE machine_id = M1; ;// parts with a condition

6. SELECT AVG(column_name)
FROM table_name
WHERE condition;

SELECT MIN(column_name)
FROM table_name
WHERE condition; SELECT MAX(column_name)
FROM table_name
WHERE condition;

SELECT machine_id, AVG(std_unit_mch_min)
FROM parts
WHERE condition;

SELECT machine_id, MIN(std_unit_mch_min)

FROM parts

WHERE condition;

SELECT machine_id, MAX(std_unit_mch_min)

FROM parts

WHERE condition;