So...i got this project that i have to do, not in order to make money but for an exam. I have to do a db for a clinic. The
clinic registered on paper details about doctors, pacients and visits. For each pacient there is a record with the name,
doctor's name, date and hour of the visit and the cabinet. I have 2 documents provided: a somekind of CSV with the pacients
and visits and another one with the doctors(first and last name, speciality, specialist or primary) which also has a list
of fresh doctors that have no visits record.
1) I have to do a db theclinic in which i must define a procedure called init() which does this:
- erases and recreates the following tables: doctors(first and last name, ranking-specialist or primary and speciality);
pacients(first name and last name); medical wards(name); visits(one column for date and hour and external keys for doctors,
pacients and medical wards); temp table in which the informations about the visits will be imported before being
distributed in the designated tables. The structure of the table must be chosen taking in consideration the file content
type so that the import will be smooth.
- create indexes: primary keys(auto_increment type); unique indexes for the combo(first and lastname) from doctors and
pacients tables; unique index for the name of the medical ward in the medical wards.
2) Import the data from the doctors file in the Doctors table.
3) Import the informations regarding the Visits in the temp table.
4) The information from the temp table must be distributed in the main tables so that the correct realtions can be
established between recors: the list of unique pacients will be introduced in Pacients; the list of unique medical wards
in Medical Wards; populating the Visits table based on the temp table, meaning that names of doctors and pacients will be
replaced by ID's etc., so that every record from Visits will contain referencies to records from other tables(Pacients,
Medical Wards, Doctors). This way any future report can be obtained from joins.
5) After all the tables are populated with the correct datas, the temp table will be deleted. Later reports will be
generated exclusively from data inside the main tables(Doctors, Pacients, Medical Wards and Visits).
6) The administration of the clinic will want some extra info in the future about pacients and visits: DoB of the pacient
and Duration of the visit. Therefore: Duration must be added in the Visits table and DoB in Pacients table.
Those columns will be filled with random data(Dob no more than 20 years ago and Duration between 5-30 min).
7) A procedure that generates the next reports must be created:
- extracting info from one table:
- the oldest 20 pacients (fullname and age);
- how many pacients with age between 60 and 70;
- the list of doctors specialities together with the number of doctors for each;
- extracting info from 2 tables:
- the speciality which had the biggest number of pacients;
- the pacient with the most visits to the clinic;
- doctors who had less than 250 pacients in 2009;
- doctors with no visit;
- pacients who have at least 20 visits during weekends;
- one randoms pacient visit history(chronologically) by giving the name of the pacient(only date and hour of the
- extracting info from 3 or more tables:
- average age of pacients for each speciality except pediatry;
- one randoms pacient visit history(chronologically) by giving the name of the pacient(for each visit these will
be listed: date, hour, fullname of the doctor and pacient, medical ward, speciality and duration).
8) A second implementing of point 4) will be executed using a trigger for populating the main tables when importing data in
the temp table. The trigger attached to the temp table will roll automatically when new records are being added and will
run the next operations:
- calls a function to format to current date. The function will have as argument two types of characters (date and hour)
and will return a value DATETIME which represents date+hour as in Visits.
- adds the corresponding records in the tables Pacients and Medical Wards by determining the value of the external key from
the table Visits using LAST_INSERT_ID() or SELECT.
- adds the corresponding record in the table Visits using the values calculated before.
9) A third method of implementing of point 4) will be made creating a procedure that uses a cursor to go through the
records from the temp table. The function used in the previous point for the formatting of the date may be used.
All of the above must be written in a script. I don't need anyone to write it for me, just need guidance!