Phonebook Database Project

Introduction

As an exercise in the Databases course, I was asked to create a Phonebook.

The professor’s request breaks the work down into these points:

  1. Describe the Mini-world
  2. Draw the Diagrams
  3. Write the SQL script for creating the database
  4. Create the web pages for modifying the database

Description of the Mini-world

First, I was tasked with describing the mini-world starting with “The goal is to create ...”

So, after thinking about it for a bit, I wrote

The goal is to create a Phonebook.
The phonebook must store contacts, and a contact is characterized by:

1. An id_contatto ( NotNull) (int)
2. A first name (Not null) (varchar(30))
3. A last name ( varchar(30))

The primary key of the contact is its ID, which uniquely identifies it.
A contact may have one or more phone numbers, but a phone number can be associated with only one contact.
e.g. ( Marco Rossi -> first_number.type -> home , second_number.type -> work)


The phone number consists of:

1. An Id_numero (Not Null) (int)
2. A Prefix ( varchar(4))
3. A phone number ( not null) ( varchar(12))
4. Contact Type (varchar(30))

The primary key of the Phone Number entity is id_numero since it uniquely identifies it.
To be added to the phonebook, it must be assigned a name; other attributes are optional to provide more information about the contact.

Once this part was completed, I began creating the Diagrams.

Database Diagrams

In the assignment I was asked to first create the ER Diagram for the database, and then derive the Relational Diagram from it to build the SQL script.

The ER Diagram

Diagramma ER

Based on the data defined in the Mini-world description, creating this diagram was very easy.
Once it was done, I was able to proceed to the creation of the Relational Diagram.

The Relational Diagram

Diagramma Relazionale

In this way I have a clear structure of what I need to create.
Time to write some SQL!

SQL Script

Now I can proceed with writing the SQL script that will create the Database.

DROP SCHEMA IF EXISTS RubricaTelefonica;
CREATE SCHEMA IF NOT EXISTS RubricaTelefonica;

USE RubricaTelefonica;

CREATE TABLE CONTATTO (

ID_CONT INT NOT NULL AUTO_INCREMENT,
NOME VARCHAR(30) NOT NULL,
COGNOME VARCHAR(30),

PRIMARY KEY (ID_CONT)

);


CREATE TABLE NUMERO_TELEFONICO (

ID_NUM INT NOT NULL AUTO_INCREMENT,
PREFISSO VARCHAR(4),
NUMERO_INTERNAZIONALE VARCHAR(12) NOT NULL,
TIPO VARCHAR(30),
ID_CONT INT NOT NULL,

PRIMARY KEY (ID_NUM),
FOREIGN KEY (ID_CONT) REFERENCES CONTATTO(ID_CONT) ON DELETE CASCADE

);

The syntax is very simple; I used new KEYWORDS such as ON DELETE CASCADE, which allows me
to delete all phone numbers associated with a contact when it is removed from the Database.

The Development of the Web Environment

At this point I felt I was very close to finishing the project.
I couldn’t have been more wrong — in fact, I had totally underestimated the amount of time I would spend
creating the various web pages that actually manage the Database.

The “fault” (if you can call it that) is partly mine; I could have VERY much simplified the Database
if I had restricted the degree of association between a contact and its phone number from N:1 to 1:1.
This is because with an N:1 association (the blue one in the ER diagram) I give the possibility
for a contact to have more than one phone number.
If instead I had restricted the relationship to 1:1, I could have had a single page for creating the contact and adding
the phone number, reducing the programming time (6H if you’re wondering).
But while chasing perfection, I spent a loooong morning.

Now that everything is done, I’m still happy with the path I chose.
The phonebook is more flexible and also more realistic (not that it really matters)
at the cost of being a bit more complicated to use.

In conclusion, I have to say it was cool and I feel like I learned useful things, even if creating all those pages
was EXTREMELY repetitive.

See you next time!
-Popirex