Activity 1: Designing and implementing a relational database 29/12/2023

Material
– SQL Data Definition Language [SQL languages cheat sheet]
– MERISE – Relational Model Transformation Rules [PDF]

=========================================================
– Ch 17: Conception des BD (in French, all book chapters available for free)
Lire les sections 1 and 3 for the UML/R transformation rules
– Ch 06: Modèle Relationnel
– Règles passage MCD/R
Material from a BTS program
– Video: Relational Model Explained
=========================================================

Objective: The research question is how to design a relational database (relations and constraints) that represents the mini-world described in the section Context.

Theory background [PDF]

  • Relational databases principle and architecture
  • Relational model: the notion of relation (formal specification and implementation in RDBMS)
  • Relational database schema properties to avoid anomalies

Todo

  1. Model the mini-world of the Olympic Games using MERISE/UML formalisms.
  2. Propose a DB schema representing this information. Express each relationship schema’s intention formally, and indicate the integrity constraints that you think are necessary to complete its semantics. Use transformation rules. [Transformation Rules Example]
    ***Example de modélisation et de spécification de schéma relationnel de BD ****
    [Modèle Conceptuel – Relationnel]
  3. Illustrate the extension of two relationships; one must define a referential constraint concerning the other.
  4. Write a DDL SQL script to implement and test the relational database schema on the RDBMS. [Example de spécification en DDL ]
  5. Explore your schema

Note:
Assume the existence of the predefined domains INTEGER, CHAR, REAL, STRING, DATE, etc.

Deliverables: 

  • Specification of the mini-world using MERISE/UML (document) – 29/12/2023
  • Specification of the translation process from Merise to a relational database schema (document) – 29/12/2023
  • SQL script (data definition language) implementing the relation (code). Identify constraints associated to the definition of the relation and those delegated to the RDBMS and application 05/12/2023
  • Meta-data (script exploring the schema you have defined – code) 05/12/2023