Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2015

    Unanswered: Help for project!

    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!

    Thank you!

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    What have you tried so far?
    Let's start with point #1.
    Home | Blog

  3. #3
    Join Date
    Jan 2015


    I think that by temp table i meant intermediary table and so far, that meaning last night i've managed to succesfully write this:

    create schema theclinic;

    use theclinic;

    delimiter $

    create procedure init()


    drop table if exists Doctors;

    create table Doctors(

    ID int unsigned not null auto_increment primary key,

    Firstname varchar(100),

    Lastname varchar(100),

    Statute varchar(20),

    Speciality varchar(100));

    drop table if exists Pacients;

    create table Pacients(
    ID int unsigned not null auto_increment primary key,
    Firstname varchar(100),
    Lastname varchar(100));

    drop table if exists Medicalwards;

    create table Medicalwards(

    ID int unsigned not null auto_increment primary key,

    Name varchar(100));



    call init();

    I haven't yet got to check on the intermediary table and creating the indexes and keys as requested. That was my next step for tonight! So you think i got it right until now?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts