Opti-Mystic

Problem Statement:

We see that if someone has issues with his/her vision, first gets it checked by an ophthalmologist and then the ophthalmologist refers the use of some spectacles. Then the person visits an optician and gets the required spectacles. The process is quite hectic for people who wear spectacles regularly. We observe that usually databases are designed either for the hospital or for the Optical Centre’s. The problem which we target here is the creation of such a database which manages a complete eye care Centre. This includes the record of customers buying the spectacles as well as that of the patients having several eye diseases.

Requirements:

There are some doctors hired by our centre. There is a stock of products. These products include the Frames and glasses, contact lenses and medicines which may include eye drops. There are several people who visit the eye centre. They can either be a patient or a customer or both. A patient is treated by a doctor. And a doctor recommends the medicine to the patient. A customer can buy a list of products.

Functional Requirements:

• An interface which allows the user to enter either as an admin or as a receptionist.
• Admin is given more rights as compared to the receptionist.

How it is done in the software:

• The receptionist can add details of customer and generate his/her total bill.
• Can add a new patient.
• Can update an already registered patient.
• Can add a new doctor.

The software view is:

• The admin can have access to some other details as well.
• Admin can view the patient and doctors record.
• Admin also has the rights to add another receptionist.

As done in our software:

Non-Functional Requirements:

• Security:
• There is a proper login ID for the receptionist.
• And the data is secured from the receptionist by creating a login separate for admin to provide sensitive details.
• Maintainability:
• The system can be upgraded easily. However, there will be a need of database professional in order to add extra tables in the schema and upgrade the software functionality.

Step by step ERD:

Several changes were made on the ERD’s when we started to practically work on our software. There were several mistakes which were then corrected after consultation with the instructor. Some of these mistakes were the creation of entity eye centre for no reason. And then creation of entities shop and clinic. We also removed the category entity. Since there was no need to create a separate entity, for the purpose of subcategory we added the category name as an attribute in the glasses table.

The final ERD is displayed below:

SCHEMA:

Product (Sr, price) 
Glasses (GSr, quality, color, categoryName)
Medicine (MedSr,MedPrice,MedName,MedType)  
Frames (FSr, size,type,colour,gender) 
Contact lens (CLSr,price,type,color) 
ProductOrders (OrderNo,Sr) 
LoginUser (ID,Password,Username) 
Patients (PatientId, name, address, phone, DocCNIC, left CYL, right CYL, Addition, left sph, rigt sph) 
Orders (OrderNo, name, address, phone, payed,change, OrderDateOrderType) 
Orderpayment ( orderNo, TotalPrice)
Doctor (DocCnic, Name, sex,address,phone#, checkupfee)  

SUB-Queries:

Triggers:

Linking of MySQL and C#:

The schema was created in MySQL.

The interface is made in C#

The database is linked to C# using MySQLClient.

Limitations:

• Extensive product dealing capabilities could be added.

• Generating a receipt was quite difficult hence instead of that we added a button “Generate SUM” to display the total bill for the customer.

• Change of admin to be done manually.

• There is supposed to be only one admin of the management system.

Conclusion:

In the end we were able to implement a complete database that enables the person using database to maintain a complete record of products, buyers, patients, doctors and the order details. We were able to link our database with an interactive interface that enables its users to effectively use the software and actually implement it in daily life.