Question

Topic: Inventory Manangement

Part A. Database Creation Your database should have a minimum of 4 tables and include both one-to-many and many-to-many relationships. Be sure to include some numeric and/or date fields. Define all appropriate constraints, using the proper naming conventions (see Structure Notes below). Populate your database with at least 30 records in the main table(s), and whatever is needed in related tables. Submit the following: • a short description of the purpose of the database and what the data & relationships mean • an Entity Relationship Logical Data Model of your data • the DBDL used to create our database Part B. Queries Create queries that include the following. You may use more than one of the listed conditions in the same query. 1. Retrieve subset of columns. 2. Uses a simple condition. 3. Uses a compound condition. 4. Uses the LIKE, IN, or BETWEEN operator. 5. Uses an aggregate function. 6. Uses the GROUP BY clause. 7. Uses joins to retrieve data from more than one table. 8. Use the IN or EXISTS operator. 9. Use a subquery. 10. Performs an inner or outer join. 11. Uses ALL or ANY operators. For each query, submit the following: STRUCTURE NOTES: • Question the query is trying to answer (e.g. similar to the questions in your book like 'What is the price of the most expensive item?') • The SQL for the query that answer the question • Use the proper naming convention for your constraints: Example: Constraint TableName_FieldName_ConstraintID (Customer_CusNum_PK) • Set up the Primary Keys for each table with Constraints listed. • Add Your Foreign Keys for each table with Constraints listed. • Make the Data Types for all the Primary Keys and their corresponding Foreign Keys Varchar(4).