|
In the first module of this course we studied the historical evolution of data access methods from file systems to the database concept and from physical pointer design to logical database models such as the rdbms.
In this module we will describe how an rdb is constructed and, as the first step in rdb construction, how a diagramming technique called entity-relationship modelling can be used to describe the semantic content of data. Described in this way the data model ought to be free of predetermined report structure assumptions.
Steps in database construction
- think of these stages as a series of translations in a communication chain
- conceptual model: erd used to model user's view of data
- both user and designer understand this method of describing data
- each tool in this sequence is a communication tool
- the designer extracts from the erd a normalized set of tables
- programmers implement the tables and design forms and reports
- even when not designing databases and erd is useful in analyzing business functions
Think of this sequence in relation to figure 4.1 of your text which defines the three views of the database
- conceptual view - the end users view point - software and hardware independent
- internal view - how the dbms or db designer views the data - hardware independent
- physical view - how the data are organized in the dasd - software and hardware dependent
Of many tools used to describe the conceptual view, the ERD is the most common for rdbms. we will use the ERD in this course. In the ERD context the data environment consists of
- entities = objects both real and evanescent which are relevant to the user and the business functions and policies
- relationships = describes how the entities interact with each other.
- attributes = what we need to know about each entity and each relationship
- entity sets = entity types = ET = a group of entities that relate in the same way to other entities and have the same attribute set
- relationship sets=relationship types = RT = a group of relationships that represent the same business function or policy and have the same attribute set
- cardinality = the number of entities that may participate in a relationship as one (1) or many (N). relationships are either (1:1), (1:N), or (N:N)
- participation (optional or mandatory) of an entity in a relationship describes whether the relationship is necessary for the entity to be important to the business function.
- associated entity type = an ET that represents an RT and participates in other relationships. (necessary because RTs cannot participate in relationships in an ERD,
- cardinality and participation are determined by business policy of the firm. remember in making ERD models of business functions in this course: we are not prescribing business policy but simply recording them. However, as business managers, we may use ERDs in other ways.
Kinds of RTs (see figure 4.12)
- recursive RT = an ET has a relationship with itself. example 1: [course requires course] where where the role would be 'prerequisite'. example 2: [employee supervises employee] with role 'manager'.
- binary RT = two ETs participate in one RT. example [customer buys part] most RTs we will run into in this class are binary.
- ternary and many-nary = more than two ETs participate in the same RT. can usually also be modelled as two or more binaries.
Kinds of ETs
- normal
- depends on existence of another ET (parent depends on existence of student)
- depends on the existence of an RT (mandatory participation)
- aggregate ET: entity may contain other entities of the same set. part contains part.
- associated ET
- supertypes and subtypes: the subtype IS A supertype and shares the attributes of the supertype but also has specific attributes not shared. pilot is a subtype of employee.
- strong and weak
Kinds of attributes
- non key attributes
- primary key attributes (simple and composite)
- foreign key attributes (simple and composite)
- derived attributes
- multivalued attributes
|
