Pharmacy Database Design
Introduction:
This was a project for my college course: MIS 331--Database Management Systems. My team and I were tasked with choosing an industry and then creating a database with multiple tables from scratch. I led this team and ensured everyone did an equal amount of work. There were 11 components to this project: Introduction, Mission Statement, System Description, UML Class Diagram, Relational Translation, User Permissions, SQL Creating Tables, SQL Inserting Data, SQL Strategic Questions, SQL Strategic Queries, and Conclusion. The main point of this project was to develop a database from beginning to end to better understand the process passed the theoretical concepts taught in class.
Description:
We first started by choosing the pharmaceutical industry because we agreed that was the most interesting out of all our options. Then we wrote a simple introduction and mission statement for our hypothetical company. The system description was next, where we outlined detailed information about how the database will work, including the relationships and cardinalities. Then we designed the UML class diagram which included 10 tables with one-to-many and many-to-many relationships. Then we wrote the relational translation in which we converted the conceptual diagram into a set of tables with their foreign keys. Next we defined the user permissions to ensure who had read and/or write access to the database. Then we created the actual tables using SQL syntax in the Oracle database management system. Then we inserted 4 rows of fake data into each of the 10 tables. Next we developed 4 strategic questions and answered them using SQL queries in Oracle. These SQL queries included these clauses: SELECT, FROM, WHERE, LIKE, INNER JOIN, ORDER BY. Lastly, we wrote the conclusion which wrapped up our report.
Results:
The end result of this project was very rewarding. On top of getting an A on the project and being recognized as one of the best projects in the class, I learned so much past the theoretical material taught throughout this course. Being able to design a database from start to finish was very practical. Moreover, working with a team of 4 including myself was very helpful due to the different perspectives and knowledge levels. After completing this project, I feel much more confident working with databases, SQL, and database management system in a work environment.