Tackling SQL Projects by Analyzing the Fakebook Assignment Structure

1. Entity-Relationship (ER) Diagram Design

   This is where you interpret a verbose real-world scenario (e.g., users, events, friendships) and translate it into a logical database model.

   In Fakebook, for example:

   • A User has personal data, lives in a City, and attends Programs.
   • Users form friendships (a symmetric relationship).
   • Users create albums that contain photos where they can be tagged.
   • Events exist that users can join.

   Your ER diagram must capture entities, attributes, primary keys, foreign keys, and cardinality. But more subtly, you must also capture constraints that SQL can’t enforce directly — like “a photo must belong to one album” or “an album must have at least one photo.”

2. Schema Implementation via DDL

   Next comes creating actual tables (createTables.sql) and dropping them (dropTables.sql) using Data Definition Language (DDL). You’ll be expected to reflect your ER diagram precisely in SQL code.

   The catch? The project may give you a fixed schema for testing purposes, like in Fakebook. You’ll have to reverse-engineer the given schema into an ER diagram and reconcile it with your own.

3. Data Loading via DML

   You’ll use loadData.sql to read from a public (often poorly designed) dataset and insert into your well-designed tables.

   This is where students usually underestimate complexity. Mismatches in field naming, normalization, duplicates (like redundant friendship pairs), and NULLs in the public data are common tripwires.

4. View Creation

   To wrap things up, you’ll write createViews.sql to expose your tables in the same schema as the original public dataset (and dropViews.sql to clean up). These views act as proof that your schema can reproduce the original structure while conforming to best practices.

Why Projects Like Fakebook Are Intentionally Frustrating:

These projects aren’t just about checking boxes. They simulate real-world database refactoring — dealing with poor data design, data transformation, schema enforcement, and logical consistency. They test:

• Your modeling skills
• Your ability to translate models into code
• Your attention to constraints
• Your SQL debugging stamina

From Prompt to ER Diagram: Decoding Business Logic

Creating the ER diagram is not just about boxes and lines — it’s where 50% of your project success is born. Let’s dissect how to do this effectively with Fakebook as a case study.

Parsing the Project Spec Like a Pro

1. Look for Entities, Not Just Nouns

   Don’t treat every noun as a new table. Instead, ask:

   • Is this thing identifiable? (→ Entity)
   • Is this thing a description of something else? (→ Attribute)
   • Is this thing a relationship between two entities? (→ Relationship)

   Example:

   • User is an entity.
   • City is an entity.
   • Current City is a relationship between a user and a city.
2. Map Relationships Carefully

   Fakebook's friendships are bidirectional — (1, 2) = (2, 1). So a simple Friends table won’t cut it without a trigger or sorted pair enforcement.

   Similarly, messages go from one user to another. This is a unidirectional relationship, and “sender” and “receiver” are distinct roles.

3. Constraints Are Gold

   Constraints in the prompt often determine:

   • Whether you need composite primary keys
   • If participation is total or partial
   • Whether to create weak entities (e.g., Tags)

   Key insight from Fakebook: The prompt explicitly says not to represent constraints that are impossible to show in ER diagrams — like mutual exclusivity. Focus on those you can show, like "one photo must belong to exactly one album."

Translating the Model to SQL: Building and Populating the Schema

Once your ER diagram is stable, it’s time to transition from theory to SQL files. This is where the rubber meets the road.

Writing SQL DDL (createTables.sql / dropTables.sql)

1. Match the Schema Exactly

   In Fakebook, the testing schema is rigid — column names, types, and order must match exactly.

   Pro tip: Use the ER diagram to write your CREATE TABLE statements, but before finalizing them, adapt their structure to match the testing schema. This is reverse engineering in action.

2. Handle Circular Dependencies

   In Fakebook, Albums reference Photos (via cover_photo_id) and Photos reference Albums (via album_id). You can’t create both simultaneously with foreign keys.

   Solution: Use ALTER TABLE after creation to insert foreign keys:

   ALTERTABLE Albums ADDCONSTRAINTfk_cover_photoFOREIGN KEY (cover_photo_id) REFERENCES Photos(photo_id)INITIALLY DEFERRED DEFERRABLE;
3. Create Sequences and Triggers

   For ID fields that aren't supplied in the public dataset (like city_id), you’ll need to:

   • Create a SEQUENCE
   • Use a TRIGGER to auto-generate IDs on insert

   Pro tip from Fakebook: Keep trigger/sequence names short or the Autograder will break.

Data Loading Is Where Things Break

Once your schema is ready, loadData.sql brings in actual data — often from an ugly legacy table.

1. De-duplicate Friendships

   Fakebook public data includes both (1, 2) and (2, 1). Your job is to SELECT only one using:

   SELECTLEAST(user1_id, user2_id), GREATEST(user1_id, user2_id)FROM project1.Public_Are_Friends
2. Normalize Programs

   Fakebook uses (institution, concentration, degree) to define a unique program. But the public dataset repeats this combo per user.

   You’ll need a DISTINCT selection into a Programs table, then join it to populate Education.

3. Watch for NULLs and Multi-step Joins

   For fields like hometown_city, program_year, or tag_coordinates, missing values may force you to insert conditionally or skip rows entirely.

Creating Views: The Final Act

Your schema is done, your data is in — now comes the proof.

🔍 Rebuilding Public Tables Using Views

Fakebook requires you to write views (View_User_Information, View_Photo_Information, etc.) that match the public dataset format exactly. That means:

• Column names must match
• Column order must match
• Field types must match

1. Use MINUS Queries for Testing

   To ensure your view replicates the original table:

   SELECT * FROM project1.Public_User_Information
   MINUS
   SELECT * FROM View_User_Information;

   This should return no rows. If it does — you’re missing or misaligning data.

2. Handle Special Cases (e.g., View_Are_Friends)

   Because you normalized friendships, your view must de-normalize them again — showing both (1,2) and (2,1). Use a UNION:

   SELECT user1_id, user2_id FROM FriendsUNIONSELECT user2_id, user1_id FROM Friends;

Final Tips and Gotchas from Fakebook’s Blueprint

Expect Interdependent Failures

If one table fails to create, others depending on it will also break. Always test creation in sequence. Start with DROP, then CREATE, then LOAD.

Use Autograder Wisely

Fakebook allows only 3 feedback submissions per day. Test your scripts locally before burning submissions.

Clean and Modular Code

• Use @createTables, @loadData, etc. for modular SQL execution.
• Comment your scripts clearly.
• Use aliases and avoid hardcoding.

Conclusion: From Fakebook to Facebook

Assignments like Fakebook aren’t just practice — they’re miniaturized versions of real-world data engineering problems. If you can conquer this, you’re ready for production systems, migrations, and schema refactoring tasks in industry.

To succeed:

• Model carefully
• Match schemas exactly
• Normalize public data logically
• Validate using queries
• Always anticipate side effects

Treat each part like a component of a larger machine — because in databases, one broken gear can crash the whole engine.