2  Analyzis

Initial Columns :

['ID' 'URL' 'Titre' 'Chapeau' 'Description' 'Date de début' 'Date de fin'
 'Occurrences' 'Description de la date' "URL de l'image"
 "Texte alternatif de l'image" "Crédit de l'image" 'Mots clés'
 'Nom du lieu' 'Adresse du lieu' 'Code postal' 'Ville'
 'Coordonnées géographiques' 'Accès PMR' 'Accès mal voyant'
 'Accès mal entendant' 'Transport' 'Url de contact' 'Téléphone de contact'
 'Email de contact' 'URL Facebook associée' 'URL Twitter associée'
 'Type de prix' 'Détail du prix' "Type d'accès" 'URL de réservation'
 'URL de réservation - Texte' 'Date de mise à jour' 'Image de couverture'
 'Programmes' 'En ligne - address_url' 'En ligne - address_url_text'
 'En ligne - address_text' 'title_event' 'audience' 'childrens' 'group']

2.1 Redudant Attributes

We will remove the following attributes : description_de_la_date

2.2 First Normal Form

Non atomic attributes :

• List
  • Occurences : separated by '_' (underscore)
  • Tags : separated by ','
  • “Childrens” : separated by ','
  • Transport : separated by '\n'
• Multiple attributes
  • Transport : transport_type, transport_line, station, distance
  • Geographic_Coordinates : longitude, latitude

2.3 Functional dependencies :

• id \(\to\) url
  • For simplicty sake, we consider that we cannot deduce id from url. We may use this property to identify sub_events_id in “childrens”
• id \(\to\) titre, chapeau, description, date_de_debut, date_de_fin, occurences, url_de_l_image, texte_alternatif_de_l_image, credit_de_l_image, mots_clefs, nom_du_lieu, adresse_du_lieu, code_postale, ville, url_du_contact, telephone_de_contact, email_de_contact, url_facebook_associee, url_twitter_associe, type_de_prix, detail_du_prix, type_d_acces, url_de_reservation, url_de_reservation_texte, date_de_mise_a_jour, image_de_couverture, programmes, en_ligne_address_url, en_ligne_address_url_text, en_ligne_address_text, title_event, audience, childrens, groupe, transport_type, transport_line, station, distance
• date_de_debut, date_de_fin \(\to\) description_de_la_date
  • Removed attribute
• adresse_du_lieu, ville, code_postale \(\to\) coordonnees_geographiques
• nom_du_lieu, adresse_du_lieu, ville, code_postale -> acces_pmr, acces_mal_voyant, acces_mal_entendant

2.3.1 Non used functional dependencies

• In 1NF : transport_station \(\to\) ville
  • It’s true, but for simplicity sake, we’ll ignore it.
• ville \(\to\) cp and cp \(\to\) ville
  • It’s neither true in France and Ile de France
• (titre, date_de_debut, date_de_fin, nom_du_lieu, adresse_du_lieu, code_postale, ville) \(\to\) id
  • We cannot use those attributes as a primary key, because they are often NULL, thus we’ll ignore this functionnal dependency.

2.4 Miscellaneous

We decide to translate everything into English.

2.5 Constraints

• Realistic implementation expectations
  • date_end > date_start
  • NOT NULL :
    • id, url
    • (date_start AND date_end) OR (ocurrences)
    • title
• Unrealistic implementation expections
  • url must finish with id
  • all urls must be valid
  • address must be valid
  • phone_number must be valid
  • every event should have an address, a geographic coordinate and a contact
  • NOT NULL
    • a non null lead text, description, date_start, date_end, title_event, price_type

2.6 Triggers

• event_table.parent_event_id : ON DELETE CASCADE
• transport.event_id : ON DELETE CASCADE

2.7 Functional dependencies after decomposition

• Relation : geographic_correspondance
  • address_street, address_zipcode, address_city \(\to\) lat, lon
• Relation : address_table
  • address_name, address_street, address_zipcode, address_city \(\to\) pmr, blind, deaf
• Relation : event_table
  • event_id \(\to\) event_url, title, lead_text, event_description, date_start, date_end, cover_url, cover_alt, cover_credit, address_name, address_street, address_zipcode, address_city, price_type, price_detail, access_type, access_link, access_link_text, updated_at, image_couverture, programs, title_event, audience, contact_url, contact_phone, contact_mail, contact_facebook, contact_twitter, address_url, address_url_text, address_text, keyword, group_name, parent_event_id
• Relation : occurence
  • event_id, occurence_date (no functional dependancy)
• Relation : tag
  • event_id, keyword (no functional dependancy)
• Relation : transport
  • event_id, transport_type, transport_line, station \(\to\) distance

2.7.1 Boyce-Codd verification

• 1NF : Atomicity and no list attributs
  • Checked
• 2NF : Attributes cannot depend only on a subpart of the primary key
  • Checked
• 3NF : No transitive dependances
  • Checked
• Boyce-Codd : \(X \to Y\) \(\implies\) \(X\) is a superkey keyword
  • Checked

2.8 Indexes

We often want to identify events using :

• the title
  • event_table (title)
  • event_table (title_event)
    
• the period
  • event_table (date_begin)
  • event_table (date_end)
• the arrondissement of Paris
  • event_table (address_zipcode)
• using the url
  • event_table (event_url)
• event_table (parent_event_id)

We also want to do agregations on keywords :

• tag (keyword)