Game Store Database.

1. Game Store Database. • To start off, the purpose of this particular database design is to regulate the basic inner workings of a game store. • This design Includes the purchasing of products in the store as well as orders that will be shipped. • The database also includes important information about the products being sold as well as the departments they belong to and the employees that work in those respective departments.

2. The Solution The Problem • In the past all purchases were done in the store only and the business kept physical records of purchases instead of digital records. • During the upgrades the game store decided that they wanted to not only start using digital records but they also wanted to start mailing products to customers who placed orders. • Design the database so that it efficiently keeps track of all customers in a single table. • Create separate tables for online purchases as well as in store purchases • Link all products to the customers who purchased/ordered them in their respective tables.

3. ER Diagram

4. Relational Schema

5. Data Dictionary

6. Data Dictionary Cont.

7. SQL statements for creating tables and inserting records into the tables SQL statements SQL statements SQL statements SQL statements SQL statements SQL statements SQL statements SQL statements SQL statements SQL statements SQL statements ...

8. Tables • Products • Games • Accessories • Consoles

9. Tables Cont. • Orders  • Customers • Customer_Purchases

10. Tables Cont. • Departments • Employees

11. Queries • A query designed to get the total price of a specific orders • select c.Customer_First as first, c.Customer_Last as last, • p.product_Price*o.Quantity as Order_Price • from customers c, products p, orders o • where c.customer_ID=o.Customer_ID AND o.Product_ID=p.Product_ID • order by c.customer_first; • A query designed to get the total price of a specific Purchase • select distinct c.Customer_First as first, c.Customer_Last as last, • p.product_Price*cp.Quantity as Purchase_Price • from customers c, products p, customer_Purchases cp • where c.customer_ID=cp.Customer_ID AND cp.Product_ID=p.Product_ID • order by c.customer_first;

12. Conclusion • The database was functional and works quite well with Oracle. • Overall not many flaws in the data. • The solution to the problem was also a success and the relational system is quite easy to work with and very functional.