3.1 Using High Level Conceptual Data Models for Database Design
The first step of the database design process is requirements collection and analysis. During this step, the database designers interview prospective database users to understand and document their data requirements. Once The next step is to create a conceptual schema for the database, using a high level conceptual data base. This step is called conceptual database design. After that, the basic data model operations can be used to specify high-level transactions corresponding to the users-defined operations identified during functional analysis. The next step is the actual implementation of the database, using a commersial DBMS. Finally, the last step is the physical database design phase, during which the internal storage structures and file organizations for the database are specified.
3.3 Entity Relationship Model Concepts
The Entity Relationship (ER) model describes data as entities, relationship, and attributes.
3.3.1 Entities and Attributes
Entities is a “thing” in the real world with an independent existence. Each entity has particular properties, called attributes, that describe it. For example, an employer entity may be described by the employe’s name, age, address, salary, and job.
3.3.2 Entity Types, Value Sets, and Key Attributes
An entity type is represented in ER diagrams as a rectangular box enclosing the entity name. An entity type describes the schema or intension for a set of entities thet share the same structure. An important constraint on the entities of an entity type is the key or uniqueness constraint on attributes. An entity type usually has an attribute values are distinct for each individual entity. Each simple attribute of an entity is associated with a value set (or domain), which specifies the set of value that may be assigned to that attributefor each individual entity.
3.3.3 Relationship, Roles, and Structural Constraints
Informally, each relationship instance r1 in R is an association of entities, where are association includes exactly one entity from each participating entity type. For example, conisder a relationship type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which associates each employee with the department the employee works for. Relationship types are displayed as a diamond-shape boxes, which are connected by straight lines to the rectangular boxes representating the participating entity types.
Each entity type that participates in a relationship types plays a particular role in the relationship. The role name signifies the role that a participating entity from the entity type plays in each relationship instance. Role names are not necessary in a relationship types where all the participating entity type are distinct, since each entity type name can be used as the role name.
The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. There are two types of participation constraints, total and partial . Relationship types can also have attributes, similiar to those of entity types.
3.3.4 Weak Entity Types
Some entity types may not have any key attributes of their own; these are called weak entity types. Entities belonging to a weak entity type are identified by being related to specific entities from another entity type in combination wiyh some of their attribute values. A weaak entity type normally has a partial key, which is the set of attributes that can uniquely identify weak entities related to the same owner entity. Weak entity tpes can sometimes be represented as composite, mutivalued attributes.
3.3.5 Refining the ER Design for the COMPANY Database
From the example on Figure 3.8 :
1. MANAGES, a 1:1 relationship type between EMPLOYEE and DEPARTMENT. EMPLOYEE participation is partial. DEPARTMENT participation is not clear rfom the requirements.
2. WORKS_FOR, a 1:N relationship type between DEPARTMENT and EMPLOYEE. Both participations are total.
3. CONTROLS, a 1:N relationship type between DEPARTMENT and PROJECT. The participation of PROJECT is total, whereas that of DEPARTMENT is determined to be partial.
4. SUPERVISION, a 1:N relationship between EMPLOYEE (supervisor role) and EMPLOYEE (supervisec role). Both perticipations are determined to be partial.
5. WORKS _ON, determined to be an M:N relationship with attributes Hours. Bort participations are determined to be total.
6. DEPENDENTS_OF, a 1:N relationship type between EMPLOYEE and DEPENDENT, which is also the identifying relationship for the weak entity type DEPENDENT. The participation of EMPLOYEE is partial, whereas thet of DEPENDENT is total.
After specifying the above six relatioship types, we remove fron the entity types all attributes thet have been refined into relationship.
3.4 Notation for Enttity-Relationship (ER) Diagrams
In ER diagrams the emphasis is on representating the schemas rather than than the instances. This is more useful because a database schema changes rarely, whereas the extension changes frequently. Weak entity types are distinguished bu being placed in double retangles and by having their identifying relationship placed in double diamonds.
An alternative ER notation for specifying structural constraints involves associating a pair of integer numbers (min, max) with each participation of an entity type E in a relationship type R, where 0 ≤ min ≤ max and max ≥ 1.
3.5 Proper Naming of Schema Contructs
The choice names for entity types, attributes, relationship type, and (particularly) roles is always straightforward. One should choose names that convey, as much as possible, the meanings attached to the different construct in the schema. Use singular names for entity types, rather than plural ones, because the entity type name applies to each individual entity belonging to that entity type.
3.6 Relationship Types of Degree Higher than Two
Some database design tools are based on variations of the ER model that permit binary relationships only. In this case, a ternary relationship such as SUPPLY must be represented as a weak entity type, with no partial key and with three identifying relationship. The schema designer must analyze aech spesific situation to decide which of the binary and ternary relationship types are needed.
