You are asked to design the database for the new hospital to

You are asked to design the database for the new hospital to be built only for Surgery in California. Here is the list of information that you gather: Different departments (Heart, plastic, etc.) will be available for different kinds of surgery. Each department can be uniquely identified by a dame. Database also contains information about the address and phone number of the department. Department can have multiple surgeons to deal with different operations and tests. A surgeon has surgeonID, surgeonName, specialization and phone number. Each department is headed by a surgeon from some specific date. A department can offer varied number of operations. An operation has a unique opID, a opName and a description. Different kinds of tests for an operation is performed by one surgeon. There may be possibility of conducting multiple tests for some operation. Each test is uniquely tracked by testID and testName. A patient has an unique ID, name, location (home address or office address), date_admitted and date_discharged. A patient may be examined for multiple tests for some operation. Design an ER diagram describing the database discussed above Do not use the quaternary relationships. Also, specify the cardinality and participation constraints in the relationships. Write your assumptions if any. Transform this ER diagram into relational schema. Describe the relation - attributes and the constraints on the relations thus obtained.

Solution

PRINT NOGEN DBD NAME=HOSPDBD,ACCESS=HDAM,RMNAME=(DFSHDC40,40,100) DATASET DD1=PRIME,DEVICE=3390 SEGM NAME=HOSPITAL,PARENT=0,BYTES=80 FIELD NAME=(HOSPNAME,SEQ,U),BYTES=20,START=1,TYPE=C FIELD NAME=ADMIN,BYTES=20,START=61,TYPE=C SEGM NAME=WARD,PARENT=HOSPITAL,BYTES=31 FIELD NAME=(WARDNO,SEQ,U),BYTES=2,START=1,TYPE=C FIELD NAME=BEDAVAIL,BYTES=3,START=9,TYPE=C FIELD NAME=WARDTYPE,BYTES=20,START=12,TYPE=C SEGM NAME=PATIENT,PARENT=WARD,BYTES=125 FIELD NAME=(BEDIDENT,SEQ,U),BYTES=4,START=61,TYPE=C FIELD NAME=PATNAME,BYTES=20,START=1,TYPE=C FIELD NAME=DATEADMT,BYTES=6,START=65,TYPE=C SEGM NAME=SYMPTOM,PARENT=PATIENT,BYTES=77 FIELD NAME=(SYMPDATE,SEQ),BYTES=6,START=21,TYPE=C FIELD NAME=DIAGNOSE,BYTES=20,START=1,TYPE=C SEGM NAME=TREATMNT,PARENT=PATIENT,BYTES=113 FIELD NAME=(TRDATE,SEQ),BYTES=6,START=21,TYPE=C FIELD NAME=TRTYPE,BYTES=20,START=1,TYPE=C SEGM NAME=DOCTOR,PARENT=PATIENT,BYTES=80 FIELD NAME=DOCTNAME,BYTES=20,START=1,TYPE=C FIELD NAME=SPECIALT,BYTES=20,START=61,TYPE=C SEGM NAME=FACILITY,PARENT=HOSPITAL,BYTES=26 FIELD NAME=FACTYPE,BYTES=20,START=1,TYPE=C FIELD NAME=FACAVAIL,BYTES=3,START=24,TYPE=C DBDGEN FINISH END