Social media database

Tables

This is a database developed in SQLite simulating a social media like LinkedIn! This database has 17 tables such that 5 of them are main tables and others are either many-to-many relationships or multi-values attributes. The 5 main tables are:

Event

• EventID
• Title
• Duration
• Organizer
• EventDate
• Description
• No. participants

Job

• JobID
• Company name
• Qualifications
• Benefits
• Job description
• Seniority level
• No. applicants
• Employment type

Person

• PersonID
• Gender
• Name (first name + last name)
• BornDate
• RegisterDate
• Nationality
• Education
• Skills
• Favorites
• Age
• Signed-in devices
• Current position

Poll

• PollID
• Question
• Options

Post

• PostId
• PostDate
• Content
• Hashtags
• No. likes
• No. views

and also other tables are as follows:

Person_favorite

• PersonID (FK)
• Favorite

Person_Signed_in_devices

• PersonID (FK)
• DeviceMacAddress

Person_Skill

• PersonID (FK)
• Skill

Poll_Option

• PollID (FK)
• OptionID
• Option

Post_hashtags

• PostID (FK)
• Hashtag

To_apply

• PersonID (FK)
• JobID (FK)
• ApplyID
• ApplyDate

To_comment

• PersonID (FK)
• PostID (FK)
• CommentID
• Content
• CommentDate

To_like

• PersonID (FK)
• PostID (FK)
• LikeDate
• ReactionID

To_follow

• FollowerID (FK)
• FollowedID (FK)
• FollowershipID
• StartDate
• EndDate

To_participate_event

• EventID (FK)
• PersonID (FK)
• ParticipationID

To_participate_poll

• PollID (FK)
• PersonID (FK)
• ParticipateDate
• SelectedOption

To_share

• PostID (FK)
• PersonID (FK)
• IP

Extra information

I implemented this program as my final project for Database course in semester 5 in a short time (this is why it may be a little messy... LOL!!!). To be able to communicate with the database, I developed a Python program that is made of two files:

1. ui.py : that input the command from the user and also add some visualization to terminal
2. socialApp.py : contains and implements 10 selected queries and also some CRUD stuff

How to run?

First of all to have tables( + fake data) you should create a file named SocialApp.db. After creating the file and putting it next to the ui.py and socialApp.py, it's time to initial the database. To do this, in your terminal first type:

sqlite3 SocialApp.db

and then paste the contents of the dumped.sql file into the terminal and then press enter. Finally to run the program just type:

python.ui

and then you will face a chart that is there to guide you!

Note that the commands start with S indicate systematic (or aggregation commands) while commands start with P are mostly related to a single person or post.