Database Project

This repository contains the implementation for the laboratory project of Databases and Information Systems class at the University of Florence

Table of Contents

1. Getting Started
2. Description
   • Conceptual design
   • E-R Diagram
   • Logical design
   • Restructuring of the E-R diagram
   • Restructure E-R diagram
3. Problem
4. Running instructions
5. Assignment and report
6. License

Getting Started

In order to run this source code, download MySQL Workbench on the relative site page and follow the instructions at the following site for proper configuration. Project tested in MySQL Workbench 8.0.30, installed on Ubuntu Linux 22.04.

Description

University project that aims to create a database with related schemes and alleys. Instances are then created to populate that database, visits and triggers are implemented. Follows the implementation of queries such as: selections, projections, joins, with grouping, nested, with functions for flow control. Finally, procedures and functions are implemented. The idea from which the project takes its cue comes from the course 'Database Systems Concepts & Design' present on Udacity. The aim of the project is therefore to create a database for a social media X like Linkedin with the related queries, procedures and functions.

Conceptual design

The project is structured as follow.

Platform X stores users. There are two types of users. Each user must be a user or an admin. All users have a userId, a password, name and surname. An admin also has the date and time of the last access to the platform. A user also has a gender, date of birth, current city and hometown, and multiple interests. The platform stores employers. Each employer has a unique name. A user can be related to many employers, in turn they can be related to many users. In the relationship between a user and an employer, it's possible to store more professional qualifications. A user can also be a freelancer. The platform stores the names of the schools and their type. Each school has a unique name. All schools must have a typology (university, high school, middle school, primary school), which in turn can be the typology of education for multiple schools. A school can have several types of education within it. A user can be related to many schools, which in turn can be related to many users. In the relationships between user and school, it's possible to archive the end of studies date for each year. Each user can send multiple friend requests. A friendship is determined solely by the userId of the user who requested it and the userId of the user who accepted it. Friendship is not always mutual... just because A is a friend of B, this doesn't imply that B is a friend of A.

E-R Diagram

Logical design

Restructuring of the E-R diagram

Let's replace generalization with associations.

A one-to-one relationship is introduced between the parent entity and each child entity.

You enter the constraint that for each instance of the parent entity can only participate in one bonding relationship with the child entities.

Since the generalization is total, each instance of the parent entity participates in only one of the bonding relationships with the children.

The two subtypes have specific attributes.

We leave the two subtypes with entities and use two relationships to avoid having entities with too many attributes and/or with attributes with null values.

Restructure E-R diagram

Probelm

When using MySQL Workbench 8 there's an error when using the command 'load data local infile', due to restrictions on file access permissions.

The solution for the problem is as follows:

Open MySQL Workbench → go to Settings → Connection → Advanced → Others

Add the string:

 GLOBAL local_infile=1
 OPT_LOCAL_INFILE=1

For completeness, the solution to ERROR code 2068 and ERROR code 3948 are present if you have links indicated on stackoverflow.

Running instructions

Open a MySQL Workbench session and copy the contents of the code-project.sql file.

After copying all the contents of the file it's necessary to scroll the file up to the comment 'Instance creation: database population'.

At this point it's necessary to change for each command 'load data local infile', the path of all csv file in relation to where they have been saved on the computer.

For example for Unix:

load data local infile '~/database-project/res/cvs/nameFile.csv'

'~' stands for /home/userName/

For example for Windows:

load data local infile 'C:/Users/youUserName/database-project/res/cvs/nameFile.csv'

After changing the paths for all the CSV files, all you have to do is press the lightning icon to run the program on the top left.

Assignment and report

You can have a look at the written report, but beware that they have been written in Italian. While all the source code is commented in English.

License

Distributed under the GNU General Public License v3.0 . See LICENSE.txt for more information.