Database of Scriptum

The first step in the E-R approach is to select the entities that will be used to model the enterprise. An entity is quite similar to an object, except that an entity does not have methods. It might be a concrete object in the real world, such as Nurse Paula, the Pajero Sport parked at 123 Main Street, or the Foreign Affair Building, or it might be an abstract object, such as the patient medical record account 123456789, the database course MK305, or the Engineering Faculty at UGM Yogyakarta.

Similar entities are aggregated into entity types. For instance, Kevin Cruise, Mary Hart, Joe Fitzgerald, and Giselle White might be aggregated into the entity type Person based on the fact that these entities represent humans. Kevin Cruise and Joe Fitzgerald might also belong to the entity type Patient because in our sample database these objects presumably represented patients. Similarly, Mary Hart and Giselle White might be classified as members of the entity type Nurse.

Attributes for describing entities

As with relations and objects, entities are described using attributes. Every attribute of an entity specifies a particular property of that entity. For instance, the Name attribute of a Person entity normally specifies a string of characters that denotes the real-world name of the person represented by that database entity. Similarly, the Age attribute specifies the number of times the Earth had circled around the Sun since the moment that real-world person was born.

Domains as the set of values

As in the relational model, the domain of an attribute specifies the set from which its value can be drawn. Unlike the relational model, however, E-R attributes can be set-valued. This means that the value of an attribute can be a set of values from the corresponding domain rather than a single value. For example, an entity type Person might have set-valued attributes ChildrenNames and Hobbies.

Key constraints
As with the relational model, it is useful to introduce the key constraints associated with entity types. A key constraint on an entity type, S, is a set of attributes, A, of S such that

1. No two entities in S have the same values for every attribute in A (for instance, two different Patients entities cannot agree on both the Name and the MedicalNumber attributes).
2. No proper subset of the attributes in A has property 1 (i.e., the set A is minimal with respect to this property).

This concept of entity keys is analogous to that of candidate keys in the relational model. One subtle point, though, is that attributes in the E-R approach can be set-valued and, in principle, such an attribute can be part of a key. However, in practice set-valued attributes that occur in keys are not very natural and often indicate poor design.
As with the relational model, we define the schema of an entity type to consist of the name of that type, the collection of the attributes, and the key constraints.

E-R diagram representation
Entity types are represented in E-R diagrams as rectangles, and their attributes are represented as ovals. Set-valued attributes are represented as double ovals. Figure below depicts one possible representation of the Person entity type. Note that in this picture Hobbies is specified as a set-valued attribute and IDCard is underlined to indicate that it is a key.

This simplicity might seem suspicious in view of the fact that entities can have set-valued attributes while relations cannot. How, then, can a set-valued attribute of an entity be turned into a single-valued attribute of the corresponding relation without violating the property of data atomicity of the relational model?
The answer is that each entity that has a set-valued attribute is represented in the translation by a set of tuples, one for each element in the attribute value.
The set-valued attribute Hobby is the trouble maker. To obtain a key of the relation in question, we must include the offending attribute. Thus, the key of the Person relation is {IDCard, Hobby}.
The following CREATE TABLE statement defines the schema for the Person relation.
   CREATE TABLE Person (
       IDCard INTEGER,
       Name CHAR(20),
       Address CHAR(50),
       Hobby CHAR(10),
       PRIMARY KEY (IDCard, Hobby) )

The preceding example is the first indication that the E-R approach alone is not a sufficient tool for guaranteeing good relational design. Next article will provide a host of objective criteria that can help the database designer evaluate the relational schema obtained by converting E-R diagrams into relations. In particular, the problem with the relation is that it is not in a certain “normal form” defined next article. That article proceeds to develop algorithms that can automatically rectify the problem by splitting such offending relations into smaller relations that are in a desired normal form.

The E-R approach is not a relative, a derivative, or a generalization of the relational data model, it is not a data model at all but a design methodology, which can be applied (but is not limited) to the relational model.

Main components of the E-R approach are the concepts of entity and relationship. Entities model the objects that are involved in an enterprise—for example, the patients, physicians, and procedures in a hospital. Relationships model the connections among the entities—for example, professors teach courses. In addition,
integrity constraints on the entities and relationships form an important part of an E-R specification, much as they do in the relational model. For example, A physician/ a doctor can only perform a surgery at a given time on a given day.

An entity-relationship (E-R) diagram is a graphical representation of the entities, relationships, and constraints that make up a given design. It provides a graphical summary of the design that is extremely useful to the designer, not only in validating the correctness of the design but also in discussing it with colleagues and in explaining it to the programmers who will be using it. But unfortunately, there is no standard drawing convention for E-R diagrams, and hence there is a good deal of variation among database texts in many aspects of this approach.

After the enterprise has been modeled with E-R diagrams, there are ways of converting these diagrams into sets of CREATE TABLE statements. But unfortunately, this conversion process does not yield a unique schema, especially in the presence of constraints, because some constraints that can be indicated in the E-R diagrams have no direct counterparts in SQL.

An important advantage of this methodology is that the designer can focus on complete and accurate modeling of the enterprise, without worrying about efficiently executing the required queries and updates against the final database. Later, when the E-R diagrams are to be converted to CREATE TABLE statements, the designer can add efficiency considerations to the final table designs using normalization theory and other techniques.

In the ER diagram of a Hospital-Based Database System, we can view the entities are Patient, Doctor, Receptionist, Department, Medicine, Test, etc. Among these entities, relationships exist which connect all the entities in the diagram. For example, Patient, Doctor and Receptionist are connected via the relationship Appointment. In other words, a receptionist will set up a doctor’s appointment for a patient. Similarly, Doctor, Patient and Medicine are connected via the relationship Prescription. Here, a doctor may prescribe one or more medicine to a patient. In a similar way, other entities are connected via relationships in a meaningful way.

In this article will be explained about entities or tables that consist in the sub system personnel/ employee of the hospital database.
This sub system maintain some functionalities such as user login, user permission on accessing the system, etc. These tables are created by using DDL syntax in MySql Workbench for MySql database management system. We assume that you are familiar with SQL syntax.

Person
CREATE TABLE Person(
  `personID` varchar(18) NOT NULL,
  `Name` varchar(45) DEFAULT NULL,
  `Is_Active` bit(1) DEFAULT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table will store all personnel/ employee of the hospital

Specialists
CREATE TABLE `specialists` (
  `specialistID` varchar(2) NOT NULL,
  `Title` varchar(15) DEFAULT NULL,
  `Specialist` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`specialistID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table is for look up table used by Doctor/ Physician table.

Doctor/ Physician
CREATE TABLE `dokter` (
  `doctorID` varchar(4) NOT NULL,
  `personID` varchar(18) DEFAULT NULL,
  `specialistID` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`doctorID`),
  UNIQUE KEY `personID_UNIQUE` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table stores the identity of each doctor, and will be used in another sub system too such as emergency room sub system, ambulatory sub system, inpatient sub system, etc

User.
CREATE TABLE `tuser` (
  `user_id` varchar(20) NOT NULL,
  `encrypted_pass` varbinary(100) DEFAULT NULL,
  `personID` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `personID_UNIQUE` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
This table stores the user id and password for each person that use the hospital information system entirely. When aplication of each sub system is launched, user will be prompted to input user id and password, if user id and password is match then system will query to table that store every user permition to the system, this table may be "user_modul" or something else like that. Of course this happen at the application level/ user interface level.
May be we need more tables such as Nurse, Officials, etc. It will be presented in next articles.