Entity relationship diagram of stock maintenance

Stock Management System ER Diagram | FreeProjectz

entity relationship diagram of stock maintenance

Jun 27, Entity-relationship diagrams were first proposed as a means of quickly example, such attributes as stock number, description, and stock on hand some relationships are more stable and easier to maintain than others. Sep 24, ER diagram of a book stock management system is given below. Books are written by authors & published by the publishers. Shopping basket. forms, user security and system maintenance. . Entity-Relationship Diagram. .. as entity-relationship diagrams, referential integrity, and normalisation.

In our example, we can assume that no two customers have the same email address, so the email address can be the key.


However, we need to think carefully about the implications of our choices. For example, if we decide to identify customers by their email address, it would be hard to allow a customer to have multiple email addresses. Any applications we build to use this database might treat each email address as a separate person, and it might be hard to adapt everything to allow people to have multiple email addresses.

Clearly, there may be several possible keys that could be used to identify an entity; we choose one of the alternative, or candidate, keys to be our main, or primary, key.

You usually make this choice based on how confident you are that the attribute will be non-empty and unique for each individual entity, and on how small the key is shorter keys are faster to maintain and use.

entity relationship diagram of stock maintenance

Attributes comprising the primary key are shown underlined. The parts of any composite attributes are drawn connected to the oval of the composite attribute, and multivalued attributes are shown as double-lined ovals. Similarly, a product price could be a positive rational number.

Attributes can be empty; for example, some customers may not provide their telephone numbers. You should think carefully when classifying an attribute as multivalued: The sales database requirements may specify that a product has a name and a price. To distinguish between products, we can assign a unique product ID number to each item we stock; this would be the primary key. Each product entity would have name, price, and product ID attributes. The ER diagram representation of the product entity Representing Relationships Entities can participate in relationships with other entities.

For example, a customer can buy a product, a student can take a course, an artist can record an album, and so on. Like entities, relationships can have attributes: Our database could then record each sale and tell us, for example, that at 3: For example, each customer can buy any number of products, and each product can be bought by any number of customers.

This is known as a many-to-many relationship. We can also have one-to-many relationships. For example, one person can have several credit cards, but each credit card belongs to just one person. Looking at it the other way, a one-to-many relationship becomes a many-to-one relationship; for example, many credit cards belong to a single person.

Finally, the serial number on a car engine is an example of a one-to-one relationship; each engine has just one serial number, and each serial number belongs to just one engine. We often use the shorthand terms 1: N for one-to-one, one-to-many, and many-to-many relationships, respectively. The number of entities on either side of a relationship the cardinality of the relationship define the key constraints of the relationship.

entity relationship diagram of stock maintenance

There are many relationships that may at first seem to be one-to-one, but turn out to be more complex. For example, people sometimes change their names; in some applications, such as police databases, this is of particular interest, and so it may be necessary to model a many-to-many relationship between a person entity and a name entity.

Redesigning a database can be time-consuming if you assume a relationship is simpler than it really is. In an ER diagram, we represent a relationship set with a named diamond.

entity relationship diagram of stock maintenance

The cardinality of the relationship is often indicated alongside the relationship diamond; this is the style we use in this book. The ER diagram representation of the customer and product entities, and the sale relationship between them. Partial and Total Participation Relationships between entities can be optional or compulsory.

In our example, we could decide that a person is considered to be a customer only if they have bought a product. On the other hand, we could say that a customer is a person whom we know about and whom we hope might buy something—that is, we can have people listed as customers in our database who never buy a product.

Entity Relationship Diagram Example: Inventory System

These are referred to as the participation constraints of the relationship. In an ER diagram, we indicate total participation with a double line between the entity box and the relationship diamond. From time to time, we encounter cases where we wonder whether an item should be an attribute or an entity on its own.

For example, an email address could be modeled as an entity in its own right. When in doubt, consider these rules of thumb: Is the item of direct interest to the database? Objects of direct interest should be entities, and information that describes them should be stored in attributes. Our inventory and sales database is really interested in customers, and not their email addresses, so the email address would be best modeled as an attribute of the customer entity.

Does the item have components of its own? If so, we must find a way of representing these components; a separate entity might be the best solution.

entity relationship diagram of stock maintenance

In the student grades example at the start of the chapter, we stored the course name, year, and semester for each course that a student takes. It is neither a complete data model depicting every necessary relational database table, nor is it meant to be a proscriptive design for implementations of resource management systems.

Alternate models may capture the necessary attributes to relationships and represent the schemas or the overall organization of the system. In order to begin constructing the basic model, the modeler must analyze the information gathered during the requirement analysis for the purpose of: An entity is an object generally correspond to persons, objects, group, locations, place, events, an activity, etc. Examples are employee, vendor, supplier, materials, warehouse, delivery, etc.

An occurrence is a single instance of an entity; for example, a particular inch color TV set is a single occurrence of the entity called Inventory. An attribute is a property of an entity; for example, such attributes as stock number, description, and stock on hand might be associated with Inventory.

Generally, the same set of attributes is associated with each occurrence of an entity, so every part in Inventory can be expected to have a stock number, a description, and a stock on hand. The set of attributes associated with an entity can be visualized as a table or a record: It represents a collection of objects or things in the real world whose individual members or instances have the following characteristics: Each can be identified uniquely in some fashion.

Each plays a necessary role in the system we are building.

entity relationship diagram of stock maintenance

Each can be described by one or more data elements attributes. A specific inch color TV set is an example of a single occurrence of that entity, but Inventory might hold or more virtually identical TV sets. For Inventory control purposes, tracking TV sets a class of occurrences is probably good enough.

However, a Customer purchases a specific TV set identified, perhaps, by concatenating the serial number to the stock number. Thus a given Item sold lists one and only one occurrence of Inventory.