DEMO S.Q.L : PROJECT TWO.

🚀 PROJECT 2 – PostgreSQL DatingDB Database Analysis 💻 Welcome to Project 2 of my SQL and PostgreSQL database development journey. In this project, I designed and built a complete relational database system called DatingDB using PostgreSQL. The goal of this project was to simulate a real-world dating application database while demonstrating professional database design principles, normalization, relational modeling, constraints, indexing, and advanced SQL techniques. This database was carefully structured to manage contact profiles, professions, relationship statuses, interests, locations, and dating preferences. Every table was connected using primary keys and foreign keys to maintain strong data integrity and efficient relationships between records. The project also includes bridge tables for many-to-many relationships, SQL views for reporting, and multiple validation rules to protect data consistency. 📌 TABLES INCLUDED IN THE PROJECT ✔ profession Stores unique professions such as Software Engineer, Teacher, Doctor, Designer, Entrepreneur, Nurse, Lawyer, Architect, and Marketing Manager. A UNIQUE constraint prevents duplicate profession names from being inserted into the database. ✔ zip_code Stores South African zip codes, cities, and provinces. The table contains all 9 provinces with 2 cities each, giving a total of 18 rows of location data. A CHECK constraint ensures that zip codes remain within the valid 4-digit range. ✔ status Stores relationship statuses including Single, Divorced, Widowed, and Separated. ✔ my_contacts The main table in the database. It stores user information including first names, last names, birthdays, gender, phone numbers, emails, professions, locations, and relationship status. ✔ interests Stores hobbies and interests such as Hiking, Gaming, Reading, Music, Fitness, Photography, Dancing, Painting, Cooking, and Travel. ✔ seeking Stores relationship goals including Friendship, Marriage, Casual Dating, Long-term Relationships, and Short-term Relationships. ✔ contact_interest A bridge table connecting contacts to their interests using a many-to-many relationship. Every contact in the project has at least 3 interests as required by the project guidelines. ✔ contact_seeking Another bridge table connecting contacts to their relationship preferences and dating goals. 📌 ADVANCED SQL FEATURES USED ✅ Primary Keys ✅ Foreign Keys ✅ UNIQUE Constraints ✅ CHECK Constraints ✅ Many-to-Many Relationships ✅ Composite Primary Keys ✅ Cascading Updates and Deletes ✅ SQL Views ✅ Indexes for Query Optimization ✅ Aggregate Functions ✅ STRING_AGG() ✅ Referential Integrity ✅ Data Validation Rules 📌 SQL VIEWS CREATED 🔹 vw_contact_profile Combines contact details with professions, provinces, cities, zip codes, and relationship statuses into a single easy-to-query profile view. 🔹 vw_contact_interests Uses STRING_AGG() to display all interests connected to each contact in one row. 🔹 vw_contact_seeking Displays relationship goals and dating preferences for every contact. 📌 SAMPLE DATA INCLUDED The project contains: ✔ 16 contacts ✔ 10 professions ✔ 10 interests ✔ 5 seeking categories ✔ 18 South African city and province combinations ✔ Multiple many-to-many relationship records 📌 CONSTRAINT VIOLATION TESTS This project also demonstrates how PostgreSQL handles invalid data using intentional error tests: ❌ Duplicate profession insertion ❌ Invalid zip code insertion ❌ Foreign key violations ❌ Duplicate email violations These tests prove that the database correctly enforces integrity and validation rules. 📌 WHAT I LEARNED Through this project I improved my understanding of: 🔹 Relational database design 🔹 PostgreSQL development 🔹 Data normalization 🔹 Query optimization 🔹 Constraint management 🔹 Many-to-many relationships 🔹 SQL reporting views 🔹 Real-world database architecture 📌 TOOLS USED 🛠 PostgreSQL 🛠 SQL 🛠 pgAdmin 🛠 Relational Database Modeling This project helped me strengthen my backend development and database engineering skills while building a scalable and realistic SQL system from scratch. If you enjoy SQL, PostgreSQL, data analysis, and database development content, make sure to like, comment, and subscribe for more programming projects and tutorials. This project was created to demonstrate how professional databases are planned, structured, and tested in real-world environments. The system was designed with scalability, readability, and maintainability in mind. Every relationship was carefully connected to reduce redundancy and improve consistency across the database. By completing this project, I gained practical experience with PostgreSQL development, schema design, data validation, and relational modeling techniques used in modern software engineering careers. 📌 PLEASE LINK: If you enjoy SQL, PostgreSQL, database, software engineering, #PostgreSQL #SQL #DatabaseDesign #Programming #BackendDevelopment #DataAnalysis #SoftwareEngineering #LearnSQL #pgAdmin #DatabaseProject