Words: 459
Assignment of DBMS Draw the ER Diagram for the following case studies using any case tool like VISIO or ERWING or MSWORD. Case Study 1: You are assigned to design the database of General Hospital. Their requirements are as under: Record of doctors is kept along-with designation like Professor, Associate Professor, Assistant Professor, Medical Officer etc. Each patient is allotted a patient number along-with his/ her complete record. Each visit of patient is recorded with reference to patient number allotted. A list is kept for doctors a patient is under treatment on each visit.
Doctors has fixed monthly salary + fees for private outdoor patients + fees for operations. Fees for each type of operation is predefined. Fees for outdoor patient of each doctor is different. Bill for each visit of patient is made and stored. Management is interested in following reports: Following reports are required on date-bases (i. e. , between from-date and to-date) List of all the patients List of all the patients treated by a particular doctor List of operations performed by a doctor List of all the outdoor patients checked by a doctor
List of all the doctors a patient is under treated by Complete record of each patient Record of any patient for a particular visit Entities: Doctor Patient PatientVisit Operation OperationType OutdoorPatient Bill Case Study 2: Golden company wants to computerize its sales system. Golden Co. has its network across the country. Country is distributed in zones. Each zone covers one or more cities. Each city has one or more distributors. Golden company has number of products. Some products have more than one packing size. The sales process is: Delivery order is made for each approved order.
One delivery order can be served in more than one supplies depending on the availability of material in the store. For each supply Invoice is made. Invoice should be made from the delivery order. Therefore we have to save the record of pending items in delivery order. Golden company has dealing with number of transporters to send their goods to distributors. Golden company is maintaining record of each transporter. Invoice also contains the name of transporter. Golden company receives payments from the distributors. We have to save the record of each distributor.
Golden company is interested in following reports: Delivery orders between dates (zone wise) Delivery orders for any zone between dates (distributor wise) Delivery orders for any distributors between dates Sale between dates (zone wise) Sale for any zone (distributor wise) Sale for any distributor between dates Sale between dates (product wise) Sale for any product between dates (packing wise) Sale for any product for any particular packing Entities: Zone City Distributor Product Range Product Delivery Order Pending Delivery Order Invoice Transporter