Relational databases

Teach: OUBDA Nicolas

RELATIONAL DATABASE PROJECT

I) Introduction

The purpose of this project is to allow you to create your own database. This will enable
to cover several stages of creating a database from design to
achievement. However, you will have three objectives:

1. Conception: Entity-Relationship Model (ER)

Normalization: Relational Model (RM)

3. Implementation: Setup with MySQL

The project accounts for 60% of the final grade in BDR

General Constraints
Determination of the Project Subject

➢ You must determine the subject of your project yourself.

➢ You have a free hand regarding the subject addressed. Nevertheless, it is clear that you must
remain respectful without addressing topics of questionable morals.
➢ Try to identify a project that excites you. For example, you can set up:
• a solution for a problem that is particularly close to your heart;
• a project related to a student club;
• a project related to the company where you are interning;
• a business project that you want to start;
• the prototype of a concept that you have;
• a database related to a subject you love (music, sports, art, science,
techno, history ...

NB: Please have your topic validated.

Team project

➢ The project is done in a team of 4 people;

➢ You cannot change teams.
➢ No work should be done alone.

The student presenting an individual work will automatically receive a grade of 0.

1
Note: The groups have already been formed, please check the file to determine the number and the
members of your group.

Technical constraints.

You must use the mandated technological tools. Mainly:

• The entity-relationship model;

• The relational model;
• The use of MySQL as a DBMS.

Accountability

Throughout the project, you are asked to take on these two distinct roles:

➢ The client who has a specific need (you will do the identification yourself)
requirements).
➢ The consultant in the field of databases who develops the solution.

Global objectives

The aim of the project is to allow the student to become familiar with the various concepts related to
databases through a real project by going through several stages of implementation:

➢ Objective 1
• Identification of a need.
• Design and modeling of an external data model (EA model).
• Documentation.

➢ Objective 2
• Design of an internal data model (transformation process of the EA model
towards the MR model).
• Normalization.

➢ Objective 3
• Implementation of the MR in a relational database using SQL language.
• Creation of all objects (table, constraints, procedures, etc.).
• Writing relevant queries.
• Presentation.

2
Objective 1: Entity-relationship model

This first part of the project is focused on design and more specifically on the model.
entity-relationship (ER). You are asked to:

➢ introduce the problem;

➢ write a functional description of the problem;
➢ define the tasks that each system user can or must accomplish;
➢ identify the entities and provide a data dictionary;
➢ provide the EA model by specifying all relevant constraints;
➢ provide examples of queries used in system tasks

Deliverables.

This part of the final report will contain:

An introduction:

➢ who is the client;

➢ What is the project?
➢ what is it for?
➢ any other detail that you consider relevant.

2. A functional description:

➢ it is a concise explanation, in text and images, of the desired features for the database
of data;
➢ you must use non-technical terms in everyday language that can be understood by
a novice reader about databases: your client;

3. A definition of the system users and what their permissions are:

➢ ordinary user
➢ manager
➢ administrator
➢ etc.
4. The entity-relationship model including all relevant constraints. In this scheme, you must
attach the following additional information:

➢ for each entity of the model:

• definition
• description
• approximate number of records expected in the system (this will be a
indication of the potential size of the database;
➢ a data dictionary that will describe each attribute of all entities
(including associations):
• data type (character, string, boolean, integer, decimal number, date, ...)
• all the constraints;

3
5. Five examples of desired requests:

➢ Constraints on the queries to be made:

a. 2 queries using at least 2 entities;

b. 1 query using at least 3 entities;

c. 1 query using at least 4 entities;

➢ For each request, you must include a clear text aimed at describing the objective of the
request.
➢ It is not appropriate here to provide the SQL code for the query, but only what
Information must be obtained and which entities are concerned.
➢ At least one of your queries must use a date type field.

4
Objective 2: Relational model

This part of the project involves carrying out the second design step: the relational model.
asks you to :

➢ make the transformation from the entity-relationship model to the relational model;
➢ perform the normalization of your relational model up to the third normal form
Deliverables

This part of the final report will contain:

1. A section 'Transformation of the EA model to the relational model' explaining step by step the
transformation of the entity-relationship model to the relational model for the three most important entities
complexes of your EA model. In fact, you are asked to justify the transformation of each
attribute but also the transformation of all related associations. For example, you must
Specify for each association why you chose such primary keys and such keys.
foreigners and why you placed the association attributes in such a location.

2. A 'Normalization' section explaining step by step the normalization of the three relations (tables) the
more complex than your relational model.

3. A 'Diagram' section presenting a complete instance diagram of your model

final relational (after complete normalization).

Specific constraints

➢ Even if you are asked to explain only three elements of the transformation and
from normalization, you need to perform a complete transformation and normalization
complete your models.
➢ For the diagram, you need to use the graphical notation presented in the slides.
"Relational model". You must have at least three examples of tuples for each.
relationships (tables).

5
Objective 3: SQL Development.

This part of the project involves implementing your project in a DBMS. You must
produce all the scripts necessary for the implementation of your project with MySQL. You are asked to:
familiarize yourself with the MySQL DBMS;

➢ respect the essence of your design efforts while adding the essential elements
in the realization
• Correction of your model according to the latest indications from your diagram
relational;
• adjustment of primary and foreign keys if necessary;
• add database objects when necessary (indexes, views, queries
prepared, stored procedures, triggers, etc.
➢ create all the necessary SQL scripts for the implementation of your project (clear and well-structured scripts
documented);

Deliverables

You must produce a ZIP file that must contain the following three files:

1. file [Link]: this file must contain the script to create ALL elements
of your project (don't forget that you must first erase any existence of objects
existing beforehand) :
tables;
attributes;
constraints, etc.

2. [Link] file: this file must contain the script for all insertions of
test data of your project.
3. [Link] file: this file must contain all the requested queries.

Specific constraints

You are asked to populate your database. This involves making the necessary insertions.
allowing to perform tests on the database. You are asked to insert at least 5
records per table (3 records per link table for n to n associations). It
It is essential that the added data is consistent with the project and with each other.
The goal is to provide a concrete example of what your database might look like.

➢ You are asked to add a script containing the queries. This script must include the 10
following requests:
• the 5 queries that you described in the first objective;
• an additional relevant request performing a calculation on time;
• an additional relevant query using the 2 clauses of the SQL language;