Student prescription medicine abuse schema

Abstract

# Student Prescription Database A university database project on prescription patterns related to non-medical study-drug use among students. It combines a normalized prescription model, fictional teaching records and real FDA product and recall data. ## Purpose and scope Our societal topic is study-drug use and academic pressure. We examine how a database can organize relevant information and answer clear questions for students, university support staff and healthcare providers. The [Week 1 report](docs/week1/Week1.pdf) describes the problem and the supporting literature. The prescription model answers the following questions: - Which drugs has a student received, and which doctor prescribed them? - What dates, doses and tablet quantities are recorded? - Which drugs appear most often in the sample? - Which students have no prescription recorded? - What is each student's latest prescription? **A prescription does not prove misuse.** The database does not establish medical appropriateness, actual consumption, motivation, or effects on wellbeing and grades. All student prescription results use fictional data. ## Project deliverables | Week | Deliverable | | --- | --- | | 1 | [Societal problem and scientific literature](docs/week1/Week1.pdf) | | 2 | [Crow's foot ERD and normalization report](docs/week2/Week2.pdf), including before-and-after examples for 1NF, 2NF and 3NF | | 3 | [Relational schema](docs/week3/SCHEMA.md), constraints, mock data, basic operations and four advanced queries in [SQL files 01–04](sql/) | | 4 | [Stakeholder video](docs/week4/Week4.mp4), explaining questions, limitations and future work in plain language | | 5 | [Real-world data integration report](docs/week5/Week5.pdf), [real-data import](sql/05_real_world_data.sql) and [four adapted queries](sql/06_real_world_queries.sql) | ## Stakeholder video The video introduces the problem, presents results from the mock database and outlines next steps. https://github.com/user-attachments/assets/6ab59ed9-3a44-402c-9038-be4cdf5bed6b [Download the video](docs/week4/Week4.mp4). ## Database design The four core tables are **Student**, **Doctor**, **StudyDrug** and **Prescription**. Each prescription links exactly one student, doctor and drug; each of these can have zero or many prescriptions. A surrogate prescription ID allows repeat prescriptions of the same drug to the same student. The schema enforces primary and foreign keys, required fields, unique drug name–producer pairs, and CHECK constraints on ages, study years, doses and quantities. Reports are generated by queries, not stored. The [Week 2 report](docs/week2/Week2.pdf) explains the normalization choices and assumptions. Two additional tables, **FDA_Product** and **FDA_Recall**, store real product information and recall events. They are kept separate from the fictional prescription records because the sources provide no verified link between them. ## Real-world data | Source | Saved records | Source last updated | | --- | ---: | --- | | [openFDA NDC Directory](https://open.fda.gov/apis/drug/ndc/) | 100 unique methylphenidate product listings | 7 October 2026 | | [openFDA drug enforcement records](https://open.fda.gov/apis/drug/enforcement/) | 66 unique methylphenidate recall records | 30 September 2026 | Both snapshots were accessed on 7 October 2026 and are available under [openFDA's CC0 licence](https://open.fda.gov/license/) without payment or registration. Product listings and recall events describe complementary facts; neither dataset is a subset of the other. The import converts dates to the SQL DATE type, preserves source names and checks unique identifiers. The Week 5 report documents missing-data checks, constraints, normalization and query results. The adapted queries show product listings, counts by dosage form, recall-classification counts and the latest recall for each recalling firm, as named in the source. The catalogue data is a 100-record sample. An NDC listing does not imply FDA approval, and recall counts do not measure student use or general clinical risk. Company-name variants are treated as separate firms. These US datasets provide product context rather than evidence of student misuse. ## Run the database Requires **MySQL 8.0.16 or later**. Create and select an empty database: ```sql CREATE DATABASE student_prescriptions; USE student_prescriptions; ``` Then run the files in order: | File | Purpose | | --- | --- | | [01_schema.sql](sql/01_schema.sql) | Create the four prescription tables and constraints | | [02_mock_data.sql](sql/02_mock_data.sql) | Add 8 students, 4 doctors, 4 drugs and 13 fictional prescriptions | | [03_basic_operations.sql](sql/03_basic_operations.sql) | Demonstrate create, read, update and delete operations; all changes are rolled back | | [04_advanced_queries.sql](sql/04_advanced_queries.sql) | Run prescription history, frequency, absence and latest-record queries | | [05_real_world_data.sql](sql/05_real_world_data.sql) | Create and populate the two FDA tables from the embedded data | | [06_real_world_queries.sql](sql/06_real_world_queries.sql) | Run four queries on the real FDA records | After importing the FDA data, rerun files 03 and 04 to confirm the original operations and queries still work. File 01 must be run on a fresh database, whereas file 05 can be rerun to refresh only the FDA tables. No live API connection or separate import program is needed. **Validation:** tested on MySQL 8.4.0. All 166 imported records matched the selected source fields, repeat imports produced the same counts, and duplicate product keys and invalid recall classifications were rejected. The original queries still returned the expected mock results. ## Query work distribution Some work was done jointly on one machine during video calls, so the commit history does not show who designed each query. The table below outlines the distribution for the most complex queries, in files 06 and 07: | User | Queries | | --- | --- | | patrykkolosovski-lab | 6.2, 7.4 | | szstefanczak | 6.1, 7.3 | | Mjedlin | 6.3, 6.4 | | Skindel | 7.1, 7.2 | ## Progress, limitations and future work As planned in the video, in Week 5 we tested additional examples and verified the reports. Query 7.3 in [file 07](sql/07_additional_queries.sql) is a first step towards comparing changes over time, though only for recalls. Feedback from students and staff has not yet been collected. ### Limitations - The database only records prescriptions, so it does not capture pills that students obtain from friends or online. It also contains no data on grades or wellbeing. - The mock data is small and only covers January to March 2026. Each drug is always prescribed by the same doctor, so drug and doctor effects cannot be separated. - Real prescription data is sensitive, and the university normally cannot access it. With only two students per programme, results could identify individual students, so privacy protection must improve before real data is used. ### Future work - Create a larger mock dataset covering a full academic year. - Add an active-ingredient field to StudyDrug to link it to the FDA data, and import all 262 product listings. - Save the reports as views so staff can use them without writing SQL. - Later: run anonymous surveys on motives and outcomes, and use real student data only with consent. Personal data will never be published in this repository.