Homework 1

Instructions

Goal

Design and build a small, realistic database for a real organization, then prove you can create, populate, query, and modify it using MySQL Workbench + SQL.

Real-World Scenario (choose ONE)

Pick a scenario you could put in a portfolio as a mini system such as:

• Clinic Appointment System
• Food Delivery / Restaurant Ordering
• University Advising & Enrollment
• Gym Membership Management
• Small Business Inventory & Sales
• Banking Lite (customers + accounts + transactions)

Give your database a professional name (example: clinic_portfolio_db).

Part A – Data Model (4 tables minimum)

Create at least 4 tables that make sense for your scenario.

Requirements (must include):

• Primary key in every table
• At least 2 foreign keys total across the schema
• At least 3 constraints total, chosen from:
• NOT NULL
• UNIQUE
• sensible DEFAULT
• appropriate data types (INT, VARCHAR, DATE, DECIMAL, etc.)

Example (Clinic scenario):

Patient, Doctor, Appointment, Prescription

(or Visit / Billing / Insurance depending on your idea)

Part B – Build It in MySQL Workbench

1. In MySQL Workbench, create your tables in a model.
2. Confirm relationships are properly connected (FK lines in the diagram).

Submission proof (screenshots)

Create a Word/PDF document containing:

• A screenshot of your EER diagram
• A screenshot of each table showing:
• columns
• data types
• PK/FK
• constraints (as visible in Workbench)

Also upload your .mwb model file.

Part C- Forward Engineer + SQL Script

Use Database Forward Engineer to generate and create the schema.

Run the generated script:

CREATE DATABASE …;

USE your_database;

Save everything you run into one SQL script file named:

HW1_LastName_FirstName.sql

Part D – Populate + Query (make it look real)

Insert data

Insert realistic sample data:

• At least 5 rows per table (20 rows total minimum if you have exactly 4 tables)

Queries (include results)

Write SQL that demonstrates skill:

1. SELECT * from each table (basic check)
2. At least 1 aggregate query using COUNT, SUM, or AVG
3. At least 1 filtering query using WHERE + ORDER BY

Drop one table safely:

• Either drop a child table first (FK dependent)
• Or demonstrate you understand FK constraints (explain briefly in comments)

Include the DROP TABLE …; in your SQL script.

Part E – Export Evidence:

Export one result set from Workbench (CSV preferred):

• Choose one of your meaningful JOIN queries
• Export the results as:
• HW1_LastName_FirstName_Result.csv

Upload:

• the SQL script (.sql)
• the result export (.csv)

Part F –

1) Design Rationale (short reflection)

In your Word/PDF submission, add a section titled Design Rationale answering in your own words:

Why did you choose this scenario?

Why did you pick these 4 tables?

• Name 2 constraints you used and explain why they matter.
• Describe 1 relationship (FK) and what it represents in real life. (610 sentences total is enough.)

2) SQL Comment Signature

At the very top of your SQL file, include:

— HW1 CIS 344

— Name: __________

— Scenario: __________

— I confirm this work reflects my own understanding and was not generated by AI tools.

Deliverables Checklist

Upload these items:

• Word/PDF with screenshots + Design Rationale
• .mwb MySQL Workbench model file
• .sql script file
• .csv exported result set file

Late Submission Policy

Anything submitted after the posted deadline date/time in Brightspace is considered late and receives a flat 10-point deduction (for a 100-point homework), regardless of the score earned.

Grading Rubric (100 pts total 25 pts each file)

1) Word/PDF (screenshots + rationale) (25 pts)

• EER diagram + readable screenshots of all tables (columns/types/keys shown)
• Scenario is clear + short Design Rationale included

2) .mwb model file (25 pts)

• 4+ tables created correctly in Workbench
• PKs on all tables + relationships/FKs defined + appropriate constraints/data types

3) .sql script (25 pts)

• Database + tables created (Forward Engineer output OK)
• Inserts (5 rows per table)
• Queries: SELECT * for each table + at least 3 meaningful WHERE queries
• Includes DROP TABLE (with brief comment if needed)

4) Export (.csv) (25 pts)

• Correct CSV export of one query result (opens properly, matches your database)