PUBH 6330: Healthcare Database Design
Course Catalog DescriptionPUBH 6330 provides a practical, hands-on approach to the analysis, design, and development of relational databases with a strong emphasis on healthcare applications. Students begin with requirements gathering from healthcare scenarios (e.g., patient records, clinical trials, population health tracking, billing, or quality reporting), proceed to conceptual and logical database design, and then implement and query the database in a Relational Database Management System (RDBMS). The course uses Microsoft Access as the primary development platform while introducing transferable skills in SQL, normalization, data integrity, and healthcare-specific considerations such as HIPAA privacy/security, data standards (e.g., HL7, ICD-10, SNOMED), and ethical data use. Students complete a semester-long project building a functional healthcare database.Prerequisites: Graduate standing or instructor permission. Basic computer literacy recommended; no prior programming required.
Format: Online or hybrid (with optional synchronous sessions).
Credits: 3Course Learning OutcomesBy the end of the course, students will be able to: Analyze healthcare information requirements and translate them into a conceptual data model.
Design normalized relational databases (up to 3NF/BCNF) using Entity-Relationship (ER) modeling.
Implement a functional database in Microsoft Access, including tables, relationships, forms, queries, and reports.
Write and optimize SQL queries (SELECT, JOIN, aggregate, subqueries) for healthcare data retrieval and reporting.
Address data integrity, security, privacy (HIPAA), and ethical issues in healthcare database design.
Evaluate and document a complete database solution suitable for public health or clinical environments.
Required MaterialsMicrosoft Access (part of Microsoft 365 – free for many university students; desktop version required for full features).
Free supplementary tools: Lucidchart, draw.io, or Visio for ER diagrams; optional MySQL/PostgreSQL for advanced SQL practice.
All readings and datasets provided via LMS (Open Educational Resources, public health de-identified datasets, and instructor notes).
Textbook (recommended): “Database Systems: Design, Implementation, & Management” (any recent edition by Coronel/Morris) + selected healthcare informatics articles.
Grading Breakdown (1000 points total)Weekly Modules & Hands-on Labs: 400 points (40%)
Midterm Database Design Project: 200 points (20%)
Final Comprehensive Database Project + Presentation: 250 points (25%)
Quizzes & Exams: 100 points (10%)
Participation & Peer Reviews: 50 points (5%)
Late policy: 10% per day (up to 3 days max). Extensions granted for documented reasons.Weekly Schedule with Exact Assignment Topics and GuidesWeek 1: Introduction to Databases in Healthcare
Topic: Role of databases in public health and healthcare; relational vs. other models; overview of the database development lifecycle.
Readings: Course intro + “Databases in Healthcare” article.
Assignment 1 (30 pts) – Healthcare Data Needs Analysis
Guide: Identify a real-world healthcare scenario (e.g., immunization tracking, chronic disease registry). Write a 500-word requirements document listing users, data needs, and potential challenges (privacy, volume, reporting). Post to discussion and reply to two peers. Due: Sunday 11:59 pm.Week 2: Conceptual Data Modeling & ER Diagrams
Topic: Entities, attributes, relationships, cardinality; basic ER diagramming.
Readings: ER modeling chapter + healthcare example (patient-provider encounter).
Assignment 2 (30 pts) – Conceptual ER Diagram
Guide: Create an ER diagram (using Lucidchart or draw.io) for your Week 1 scenario with at least 5 entities. Export as PDF/image and submit with a 300-word explanation of relationships. Due: Sunday 11:59 pm.Week 3: Enhanced ER Models & Business Rules
Topic: Weak entities, supertypes/subtypes, recursive relationships, constraints in healthcare contexts.
Assignment 3 (30 pts) – Enhanced ERD Refinement
Guide: Refine your ERD to include supertypes (e.g., Person → Patient/Staff) and business rules. Submit updated diagram + 400-word rationale. Peer review one classmate’s diagram. Due: Sunday 11:59 pm.Week 4: Logical Design & Relational Model
Topic: Mapping ERD to relational tables; primary/foreign keys; data types.
Assignment 4 (40 pts) – Initial Table Design
Guide: Convert your ERD into a relational schema (list tables with attributes, keys, and data types). Implement basic tables in Microsoft Access and submit the .accdb file + screenshot of Relationships view. Due: Sunday 11:59 pm.Week 5: Normalization
Topic: 1NF–3NF (and BCNF); functional dependencies; anomalies and how normalization solves them in healthcare data.
Assignment 5 (40 pts) – Normalization Exercise
Guide: Take an unnormalized healthcare dataset (provided) and normalize it step-by-step to 3NF. Submit before/after schemas + explanation of anomalies removed. Due: Sunday 11:59 pm.Week 6: Implementing the Database in Access – Part 1
Topic: Creating tables, enforcing referential integrity, input masks, validation rules.
Assignment 6 (40 pts) – Database Build Lab
Guide: Build normalized tables in Access for your project. Add sample data (at least 20 records per major table). Submit .accdb file and data dictionary (Excel or Word). Due: Sunday 11:59 pm.Week 7: SQL Basics & Data Retrieval
Topic: SELECT statements, WHERE, ORDER BY, aggregate functions (COUNT, SUM, AVG) using healthcare examples.
Assignment 7 (40 pts) – Basic SQL Queries
Guide: Write and test 8–10 SQL queries in Access (or SQL view) to answer public health questions (e.g., “average age of patients with diabetes”). Submit query screenshots + results. Due: Sunday 11:59 pm.Week 8: Midterm Project – Database Design Proposal
Midterm (200 pts)
Guide: Submit a complete design package for your chosen healthcare database: requirements document, final ERD, normalized relational schema, data dictionary, implemented Access file with sample data, and 5 sample queries. Include a 2-page reflection on design decisions and HIPAA considerations. Recorded 5-minute presentation optional. Due: Sunday 11:59 pm.Week 9: Advanced SQL – Joins & Subqueries
Topic: INNER/OUTER JOINs, multi-table queries, nested queries for complex healthcare reporting.
Assignment 9 (40 pts) – Advanced Query Lab
Guide: Create 6 complex queries (including joins across at least 3 tables). Focus on useful public health outputs (e.g., patient outcomes by provider). Due: Sunday 11:59 pm.Week 10: Forms, Reports & User Interface
Topic: Building user-friendly forms and reports in Access for data entry and output.
Assignment 10 (40 pts) – Interface Development
Guide: Design at least 3 forms (e.g., patient intake) and 2 professional reports (e.g., summary dashboard). Submit updated .accdb and screenshots. Due: Sunday 11:59 pm.Week 11: Database Security, Privacy & HIPAA
Topic: Access control, encryption basics, audit trails, de-identification, HIPAA implications for database design.
Assignment 11 (30 pts) – Security Plan
Guide: Write a 600-word security and privacy plan for your database, including user roles, data masking strategies, and compliance checklist. Due: Sunday 11:59 pm.Week 12: Data Integrity, Indexing & Performance
Topic: Constraints, indexes, query optimization; handling large healthcare datasets.
Assignment 12 (30 pts) – Optimization Lab
Guide: Add indexes and constraints to your database. Test query performance before/after and document improvements. Due: Sunday 11:59 pm.Week 13: Integration with Healthcare Standards
Topic: Brief overview of HL7, FHIR, ICD-10 coding integration; exporting/importing data.
Assignment 13 (30 pts) – Standards Reflection
Guide: Research how your database could incorporate one standard (e.g., ICD-10 codes). Modify one table/query and submit changes + 400-word discussion. Due: Sunday 11:59 pm.Week 14: Final Project Workshop & Peer Review
Assignment 14 (30 pts) – Peer Review
Guide: Share your near-final database with two peers. Provide and receive structured feedback using the rubric. Submit review comments and revision log. Due: Sunday 11:59 pm.Week 15: Final Project & Presentations
Final Project (250 pts)
Guide: Deliver a polished, functional healthcare database in Access including: complete ERD, data dictionary, all tables with realistic data, 15+ tested queries, forms/reports, security plan, and a 1000-word final report addressing learning outcomes. Record a 7–10 minute presentation demonstrating your database. Submit .accdb file, documentation, and video link. Due: Finals week.Study Notes (Key Concepts & Review Guide)Use these for quizzes, project work, and exam prep.Core Vocabulary: Entity, Attribute, Relationship, Cardinality, Primary/Foreign Key, Normalization (1NF–3NF), Functional Dependency, SQL (DDL/DML), Referential Integrity, HIPAA, De-identification.Big Ideas by Week: Weeks 1–3: Good design starts with understanding user needs and healthcare workflows. ERDs are the blueprint.
Weeks 4–6: Normalization eliminates redundancy and prevents anomalies — critical for accurate patient data.
Weeks 7–10: SQL turns data into actionable public health information (counts, trends, alerts).
Weeks 11–13: Security and standards are not add-ons; they are core to ethical healthcare databases.
Final Synthesis: A successful healthcare database balances usability, performance, compliance, and scalability.
Final Review Tip: Revisit your Week 1 requirements document and compare it to your final project. Document how your design evolved and why — this is excellent evidence of learning.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
