databases-week3-Exercise

DESIGN.md

LISHAN GEBREMARIAM

Friend Finder Database Design Overview

This databases is designed to help persons find friends when they move to a new city.

Entities and Attributes

1.Person: id, name, email, city_id

2.City: id, name, state, country

3.Event: id, name, date, city_id

4.Connection: id, person1_id, person2_id, status

Relationships:

-A person belongs to a city.

-A City can have more Event

-An event is hosted in a city.

-A connection is a relationship between two people

-

schema.sql

CREATE TABLE City (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100)
);
CREATE TABLE Person (
     id INT PRIMARY KEY,
     name VARCHAR(100),
     email VARCHAR(100),
     city_id INT,
     FOREIGN KEY (city_id) REFERENCES City(id)
);
CREATE TABLE EVENT (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
city_id INT,
FOREIGN KEY (city_id) REFERENCES City(id)
);
CREATE TABLE Connection (
    id INT PRIMARY KEY,
    person1_id INT,
    person2_id INT,
    status VARCHAR(50),
    FOREIGN KEY (person1_id) REFERENCES Person(id),
    FOREIGN KEY (person2_id) REFERENCES Person(id)
);