Data Modelling and E-R Diagrams
We can subdivide the process of designing a database into three separate phases: Data Analysis, System Design, and Technical Design.
User Environment: The ‘real world’ environment about which the organisation wishes to store data for the purposes of some application (the application might be, for example, a computerised stock control and accounting system).
Data Analysis: In this phase the ‘real world’ environment, in the context of the required application, is modelled to produce (i) an abstract data model which focuses on the data that needs to be stored and the relationships between the data entities and (ii) the functional model which deals with how the data will be processed (e.g. the design of queries to the database). The abstract data and functional models produced are independent of the specific DBMS that will eventually be used to implement the database. Note that this phase is also sometimes called conceptual design.
System Design: The data and functional models feed into the system design phase which produces the database specification. The database specification details how the abstract data model from the previous design phase will map onto the specific data model of the chosen DBMS (this activity is sometimes called logical design). The database specification also describes information such as statistics on how frequently data will be accessed and access patterns across the datasets etc. The aim of these specifications is to ensure that the implementation will be efficient and that sufficient capacity (storage, memory, processing) is provisioned.
Technical Design: Taking the database specification as input, the technical design phase specifies exactly how the database will be physically implemented using a DMBS, so that a certain set of criteria are met. This phase is sometimes called physical design. Examples of the criteria are:
Database: Finally the database is implemented according to the technical design requirements using a chosen DBMS.
In this course, we are most interested in the high level design process of Conceptual
Design (Data Analysis) and the relational theories on which it builds.
As mentioned above, the data model is an abstraction of the data structures required by a database. By ‘abstraction’, we mean that data model represents the data as a user would observe it in the ‘real world’ rather than how it is stored on disk or how software manipulates it. This means that the data model is independent of the particular DBMS that will eventually be used to implement the database, a useful property.
The data model is composed of data entities, relationships between different data entities and also rules which govern operations on the data. Note that the data model does not specify the operations themselves that will be performed on the data (these are specified in the functional model), but rather just the restrictions that apply when any operation is performed on the data structures.
An E-R model is a particular type of data model suited to designing relational databases. The main component of the model is the Entity-Relationship Diagram. The E-R diagram is a simple way of representing the data entities being modelled and the relationships between these data entities. It is easy to transform E-R diagrams to the Relational Model (data entities correspond to relations and relationships correspond to the implied associations created by keys and foreign keys of relations).
Entities are analogous to relations in the relational model. They represent the principle data objects about which information is to be collected.
