Information Integration
In this homework you are going to design and implement a simple database application.
This phase is to be implemented by each student individually, without the help of others. You will design and implement an inventory database as follows:
Generic instructions: Your database should have at least 4-5 tables. Each table should have several columns with descriptive names and it should be populated with a small number of records (around 20). You may use primary/foreign keys for your tables.
You need to implement a set of operations for your database. These operations should be implemented in SQL. More details on these operations are found in the description of the Inventory database.
Deliverables:
1. ER Diagram
2. Script with all create commands, and some insert commands
3. Operations in SQL.
a. The SQL queries
b. Screen shots with the execution of all the operations showing that your database application actually works (you can take a screen shot of the current window by pressing Alt+PrintScreen keys together. The screen shot goes into your clipboard which you can paste Cntr+V into a document)
Inventory Database
Your inventory database contains information about books, and music CDs. The items are stored in different warehouses in the country. Each warehouse stocks different quantities of the products that you sell. You should store information with details about all the products, the warehouses, and the availability of each product in each warehouse.
Below is a set of mandatory fields for your database tables. However, they are not enough. You willneed additional columns to make it functional and support the inventory operations. Feel free to create your own complete schemausing the columns below and additional columns of your choice.
- Books: ISBN (unique identifier), title, author(s), price, category (fiction, drama, etc.), keywords (to describe its contents), etc.
- Music CDs: ID(unique identifier), album title, artist(s), recording company, price, genre(classical, jazz, pop, rock, etc.), etc.
- Warehouses: address, manager's name, telephone number, etc.
Operations for the inventory database:
1. List all products in inventory (just a few columns for each product)
2. List all details of a product given its unique identifier
3. List each product(s) of a particular author or artist
4. List each product with at least quantity N
5. List each product that is out of stock
6. List the location of the warehouse(s) which stock a product of a given unique identifier