MIS 385
Database Design Assignment II
Submission instructions
What to submit:
- For part A: one PDF containing the database design outline and ER diagram.
- For part B: the Access file (.accdb) with the database.
Questions
An independent online retailer of vinyl music records, uRock, has built its business on offering the widest selection of new and rare vinyl music records. Lately, uRock has become concerned with its inventory management methods. The company does not use a formal, consistent inventory tracking system, instead, periodically, an employee visually checks to see what items are in stock. The lack of a formal inventory tracking system has led to overstocking some items and understocking other items. On occasion, a customer will request an item, and it is only then that uRock realizes that the item is out of stock. If an item is not available, the company risks losing a customer to a competitor.
Part A.
Design a relational database that will allow uRock to automate the inventory checking and ordering process. Hand in a database design outline and an ER diagram.
The details of the database are explained next.
Suppliers table:
● Create a table to keep data about the company’s records suppliers: supplier’s name, address, phone number, and email.
Products table:
● We need a table to keep data about the vinyl music records sold by the company.
● Search the internet for the attributes you believe are required to have in such a table (certainly, the album name, the band, and the year will be there… and what else?).
● Do not forget the primary key!
In addition, this table needs to track the inventory levels for each product. Add an attribute for the units in stock and another for the reorder level.
Relationship between Products and Suppliers:
● A product can only be stocked by one supplier, but a supplier can provide more than one product.
Categories table:
● This table is available in Excel (link to file) and corresponds to the different record categories that uRock considers. There are two attributes: CategoryID and Name. Add this table to your database design. Later, when you create the DB in Access, import the data from Excel to the Categories table in Access. (Feel free to add more categories to this table).
Relationship between Products and Categories:
● A category may contain many products. A product can belong to multiple categories simultaneously (e.g. an album may be in both the Rock and Vintage/Rare categories).
Customers table:
● Create a table to keep data about the company’s customers: name, address, phone number, email.
Orders:
● Create a table to keep data about orders: order ID, date, and payment method.
Relationship between Customers and Orders:
● An order is placed by only one customer. A customer may place multiple orders.
Relationship between Products and Orders:
● An order must have at least one product but can contain more than one product.
● A product can have one or more orders but need not have any orders.
Do not forget to add the quantity of each product order to the database (which table should it go?...).
The only relationships you need to create are the ones I mentioned above. If you want, you may use a zip codes table as studied in class, but it is not mandatory.
Part B
You will implement the database you design in Part A in Ms Access, which means:
● creating the tables;
● choosing reasonable datatypes for each attribute;
● creating relationships between the tables and enforcing referential integrity;
● populating each table with at least 3 records.