\documentclass[10pt,twocolumn]{article}
\usepackage[french]{babel}
\usepackage[latin1]{inputenc}
\pagestyle{plain}

\begin{document}
\title{RDBMS EFREI M1 TD5 }
\author{Yann Thierry-Mieg}
\maketitle

\section{Introduction}

A company specialized in horse breeding based in Troyes has decided
to automate its information system.

This company owns several stables in the region of Troyes, handled
on a daily basis by managers.

These stables offer several services:
\begin{itemize}
\item Horse raising, sale of the horses bred in the stable, and rental of male partners for reproduction.
\item Participation of horses in races and official horse shows.
\item Temporary accommodation of horses, within the limit of available stalls, fixed by the manager.
\end{itemize}

The information system will allow :
\begin{itemize}
\item Handling of breeding
\item Handling of horse accommodations.
\item Administrative management of the horses that belong to the stable.
\item The issuance of genealogical data sheets and / or curriculum honorum (list of awards);
\item Monthly billing
\end{itemize}

\section{Current situation}

\subsection{Billing}

Monthly invoices concern successful breeding by a rented stallion,
sales of horses and accommodations (An invoice per month per
customer and by type of service).
\begin{itemize}
\item Accommodation billed by the week, depending on the class of horses (5 categories). A discount or a supplement for additional services can be applied by the manager.
\item For breeding, the price is individually fixed for each stallion.
\item Similarly the sell price is fixed individually for each horse.
\end{itemize}
Any bill that is not settled within one month gives rise to a
reminder. A bill late by two months is forwarded to the legal
department.

\subsection{Breeding}
The client makes a request that specifies the dates and place the
breeding should take place, his administrative data, and the
characteristics of his mare (female horse).

If at least one possible stallion exists (same race and already at
least one descendant):
\begin{itemize}
\item We register the client, the breeding request, and all the characteristics of the mare (pedigree and cursus honorum).
\item We produce of maximum 5 likely candidates that we put on hold
for the projected breeding date. Preference will be given to the
stallion whose last breeding date is the least recent.
\end{itemize}

If the client answer is positive (and accompanied by a check
covering at least 10\% of the breeding cost, we free the candidates
which have not been selected, and we produce a breeding document
that mentions the money already paid. The client then has 15 days to
confirm or the breeding is canceled.

If the breeding fails (no offspring) the client is refunded in
totality.

\subsection{Accommodation}

The client makes a request specifying the dates desired and
specifics of his horse (special diet, frequency of exercises ...).
If a stall is available for those dates, accommodation is recorded
and a price estimation based on the going rate established by the
manager of the stable is sent to the client.

If the client response is positive it should be accompanied by a
check for at least 10\% of the cost. The client has 15 days to
confirm or the accommodation is canceled.

We do not wish to store the horse specifics (cursus honorum,
pedigree) of client horses accommodated in the stable.

\subsection{Delivery of pedigree and cursus honorum}

Any client wishing to buy a horse can ask for:

\begin{itemize}
\item The genealogic ascendancy tree of the horse over $n$ generations.
\item The cursus honorum of any ascendant of the horse.
\end{itemize}

\subsection{Administrative management}

The system must allow :

\begin{itemize}
\item Recording of a birth, sale or death, with corresponding update of available stalls.
\item Recording of the results of a horse in a horse show.
\item Recording of the results of a breeding.
\item Update or modification of prices.
\item Change of manager for a stable.
\item Week by week knowledge of available stalls.
\item Recording of client payments.
\item Creation of a new stable
\end{itemize}

\section{Data collected}

The study of current operations of the company has allowed to
determine the following (non-exhaustive) list of information to be
 stored in the system:
\begin{itemize}
\item Description of a horse :
\begin{itemize}
\item Pedigree number, name, race, sex, specific characteristics, birth date, date of death, color,
 eye color, sell price, breeding price.
\end{itemize}
\item Description of a client :
\begin{itemize}
\item Client number, address, phone, name
\end{itemize}
\item Description of a breeeding:
\begin{itemize}
\item Number, place and date
\end{itemize}
\item Description of a horse show or race and results :
\begin{itemize}
\item code of the show, name of the show type (beauty, trot, galop...), code of the show type, place, ranking, date
\end{itemize}
\item Description of a stable :
\begin{itemize}
\item Stable number, name, address, phone, name of manager, total capacity, available capacity week per week.
\end{itemize}
\item Description of an accommodation :
\begin{itemize}
\item start and end date, specific characteristics, price category, base price, discount or supplement.
\end{itemize}
\end{itemize}

\section{Questions}

\begin{enumerate}
\item Elaborate a conceptual E/R schema, with comments and a
dictionary of additional identified constraints that are not modeled
yet.

\item Define the data dictionary.

\item Give the resulting logical/relational schema.

\item Give the table creation scripts for tables Horse and Breeding or
their equivalent in your model.

\item Produce requests allowing to manage a breeding, from the client
request, the search for candidates, etc... up to the delivery of a
pedigree for the offspring.

\item Formulate requests allowing to establish monthly invoices.

\item Formulate requests allowing to track week per week the available
stalls in a given stable.
\end{enumerate}

\end{document}
