Author: JOHN REY ORTIGAS
Course: BSCS 3B
This repository contains multiple database case studies I designed and analyzed as part of my coursework.
Each case focuses on data modeling, entity relationships, and business logic implementation using SQL-based database systems.
- Case Study 1 — Student Enrollment Database
- Case Study 2 — Student Enrollment ERD and Queries
- Case Study 3 — Art Gallery Management System
- Case Study 4 — Student Record Query System
- Case Study 5 — Bakeshop Inventory and Sales Management
Author: JOHN REY ORTIGAS — BSCS 3B
This database is designed to manage student enrollment and academic records efficiently.
- Store student details (ID, first name, last name)
- Maintain staff information (ID, name, birthdate)
- Manage class offerings (class code, name, units)
- Track student enrollments:
- Which student enrolled in which class
- Which staff teaches each class
- Grades received by students
- Enable queries to:
- Filter by grade
- Search students by name
- List students with their classes and grades
- Order grades in ascending or descending order
Business Task:
This case expands on the enrollment database by including Entity Relationship Diagram (ERD) design and analytical SQL queries.
- Maintain student information (ID, first name, last name)
- Store staff details (ID, name, birthdate)
- Manage subject offerings (class code, name, units)
- Track enrollments:
- Students enrolled per class
- Assigned staff per class
- Grades per student
- Provide analytical queries:
- Count available subjects
- Retrieve highest and lowest grades
- Compute GPA and total grades per student
This database manages art gallery operations, tracking artists, artworks, customers, and purchase transactions.
- Maintain artist records (ID, name)
- Store artwork details (art number, title, artist)
- Manage customer records (ID, first name, last name)
- Track purchases:
- Which customer bought which artwork
- Purchased artwork and artist details
- Purchase date and customer purchase count
- Provide analytical queries:
- Count customers buying specific artworks
- Retrieve customer purchase history
- Display purchases with artwork and artist details
- Generate reports on total and frequent buyers
This case study focuses on data querying and reporting for student academic performance.
- Maintain student information (ID, first name, last name)
- Store staff and class details (name, birthdate, code, units)
- Track student enrollments and grades
- Provide analytical queries to:
- List students by specific grades
- Find student details by last name
- Retrieve students enrolled in a given class
- Display grades per class and per student
Author: JOHN REY ORTIGAS — BSCS 3B
This database manages bakeshop operations, covering inventory, sales, suppliers, and financial records.
- Maintain branch information (ID, name, address)
- Manage product categories (cakes, bread, beverages, etc.)
- Track suppliers and supplied products
- Monitor product details: stock, price, cost, and expiration
- Store customer data and purchase orders
- Record employee roles and assignments
- Handle invoices, payments, and order details
- Generate business reports:
- Total sales and revenue
- Profit margins
- Sales by product category or supplier
- Low-stock and expiration alerts
- Employee performance tracking
- Customer purchase patterns
Each case study demonstrates:
- Database planning and modeling
- Entity-Relationship Diagram (ERD) design
- Structured query formulation
- Real-world business logic translation into database systems
These projects collectively highlight practical database management skills applicable to business, education, and retail systems.
John Rey Ortigas
BS Computer Science Student