In order to develop an entity relationship diagram, different aspects are put in place. To start with, we define the major business that needs to be automated. This forms the entities. Attributes are defined by the various storage fields and tables in the database. In the case of Celtic Analytics we are going to implement a data warehouse that bears the resemblance to a relational database system.
The process will involve the translation of ideas into sophisticated plan that will permit the development team to put together a relational data warehouse schema in lieu of the key functionality of the warehouse.
ERDs are used to represent logical relationships between entities. An entity can take an object, products, place, and orders. Information systems recognize relationships between entities. For instance a customer entity can reveal several instances of an order, sales representative, and location, among others.
Entity relation diagrams are essential in the design of relational databases. Using an entity relationship data model, abstract schemas are designed and denoted with various entities. Likewise, in object-oriented data model, objects are denoted with different classes with a provision that segregate functional primary data from processes that are involved in creation modification of such data. The functional areas are primarily mapped out during the problem definition stage and should be independent of other processes. This is one crucial stage that should not be overlooked by the design team in order to produce a functional database.
There are numerous ways to represent a ERD using CASE tools but the popular method utilized in this design involve the use of rectangular blocks to represent entities and crows foot to represent relationships. Relationships in an entity relation diagram are defined using a technique known as cardinality. Cardinality describes the relationship between two entities in a numeric manner and illustrates how the instances of one entity relate to another.
An entity comprise of primary keys and foreign keys. A primary key is used to access a particular entity. It is constrained such that no two values are equal in a table. In the ERD above the primary keys includes the following. Examples of primary keys derived from the ERD include Last Name, Order Number, Representative ID, Order Number and Order Line Number. Foreign keys, on the other hand, are pieces of identifiers that describe entities in two tables. It enforces a link between the data in one table and another. In the ERD table below, there is a link between Orders and Order line derived from the existence of Order Number. Order Number qualifies as a foreign key. Likewise, Street, Zip, and State are foreign keys. Attributes are the special characteristics of each entity. These includes; first name, rate, credit limit, and class.
One to one relationship: (1:1)
The relationship exists when of the second entity occurs for each instance of the first entity. For instance an sales representative writes orders.
One to many relationship: (1: M)
This kind of relationship occurs when one occurrence of the first entity can relate to many occurrences of the second entity but each occurrence of the second entity can relate to one occurrence of the first entity. In the case of the diagram, an order can have zero, one or many parts.
Many to many (M:N)
Many to many relationships occur when one instance of the first entity can relate to many instances of the second entity, and one instance of the second entity can relate to many instances of the first entity. This is manifested in the relationship between rep and customer. A rep can sell to zero; one or many customers and customers can have one or many representatives selling to them.