There are conceptual differences between these terms that should help you visualize the data and also possible differences in the generated schema that should be fully understood. Mostly the difference is one of perspective though. In a one-to-many relationship, the local table has one row that may be associated with many rows in another table. In the example from SQL for beginners, one Customer may be associated to many Orders. In the opposite many-to-one relationship, the local table may have many rows that are associated with one row in another table. In our example, many Orders may be associated to one Customer. This conceptual difference is important for mental representation. In addition, the schema which supports the relationship may be represented differently in the Customer and Order tables. For example, if the customer has columns id and name: id,name 1,Bill Smith 2,Jim KenshawThen for a Order to be associated with a Customer, many SQL implementations add to the Order table a column which stores the id of the associated Customer (in this schema customer_id: id,date,amount,customer_id 10,20160620,12.34,1 11,20160620,7.58,1 12,20160621,158.01,2In the above data rows, if we look at the customer_id id column, we see that Bill Smith (customer-id #1) has 2 orders associated with him: one for $12.34 and one for $7.58. Jim Kenshaw (customer-id #2) has only 1 order for $158.01. What is important to realize is that typically the one-to-many relationship doesn't actually add any columns to the table that is the "one". The Customer has no extra columns which describe the relationship with Order. In fact the Customer might also have a one-to-many relationship with ShippingAddress and SalesCall tables and yet have no additional columns added to the Customer table. However, for a many-to-one relationship to be described, often an id column is added to the "many" table which is a foreign-key to the "one" table -- in this case a customer_id column is added to the Order. To associated order #10 for $12.34 to Bill Smith, we assign the customer_id column to Bill Smith's id 1. However, it is also possible for there to be another table that describes the Customer and Order relationship, so that no additional fields need to be added to the Order table. Instead of adding a customer_id field to the Order table, there could be Customer_Order table that contains keys for both the Customer and Order. customer_id,order_id 1,10 1,11 2,12In this case, the one-to-many and many-to-one is all conceptual since there are no schema changes between them. Which mechanism depends on your schema and SQL implementation. Hope this helps. Main Body Adrienne Watt The entity relationship (ER) data model has existed for over 35 years. It is well suited to data modelling for use with databases because it is fairly abstract and is easy to discuss and explain. ER models are readily translated to relations. ER models, also called an ER schema, are represented by ER diagrams. ER modelling is based on two concepts:
Here is an example of how these two concepts might be combined in an ER data model: Prof. Ba (entity) teaches (relationship) the Database Systems course (entity). For the rest of this chapter, we will use a sample database called the COMPANY database to illustrate the concepts of the ER model. This database contains information about employees, departments and projects. Important points to note include:
Entity, Entity Set and Entity TypeAn entity is an object in the real world with an independent existence that can be differentiated from other objects. An entity might be
Entities can be classified based on their strength. An entity is considered weak if its tables are existence dependent.
An entity is considered strong if it can exist apart from all of its related entities.
Another term to know is entity type which defines a collection of similar entities. An entity set is a collection of entities of an entity type at a particular point of time. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Figure 8.1, the entity type is EMPLOYEE. Figure 8.1. ERD with entity type EMPLOYEE.Existence dependencyAn entity’s existence is dependent on the existence of the related entity. It is existence-dependent if it has a mandatory foreign key (i.e., a foreign key attribute that cannot be null). For example, in the COMPANY database, a Spouse entity is existence -dependent on the Employee entity. Kinds of EntitiesYou should also be familiar with different kinds of entities including independent entities, dependent entities and characteristic entities. These are described below. Independent entitiesIndependent entities, also referred to as kernels, are the backbone of the database. They are what other tables are based on. Kernels have the following characteristics:
If we refer back to our COMPANY database, examples of an independent entity include the Customer table, Employee table or Product table. Dependent entitiesDependent entities, also referred to as derived entities, depend on other tables for their meaning. These entities have the following characteristics:
Characteristic entitiesCharacteristic entities provide more information about another table. These entities have the following characteristics:
AttributesEach entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary). Each attribute has a name, and is associated with an entity and a domain of legal values. However, the information about attribute domain is not presented on the ERD. In the entity relationship diagram, shown in Figure 8.2, each attribute is represented by an oval with a name inside. Figure 8.2. How attributes are represented in an ERD.Types of AttributesThere are a few types of attributes you need to be familiar with. Some of these are to be left as is, but some need to be adjusted to facilitate representation in the relational model. This first section will discuss the types of attributes. Later on we will discuss fixing the attributes to fit correctly into the relational model. Simple attributesSimple attributes are those drawn from the atomic value domains; they are also called single-valued attributes. In the COMPANY database, an example of this would be: Name = {John} ; Age = {23} Composite attributesComposite attributes are those that consist of a hierarchy of attributes. Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’} Figure 8.3. An example of composite attributes.Multivalued attributesMultivalued attributes are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database, as seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD. Figure 8.4. Example of a multivalued attribute.Derived attributesDerived attributes are attributes that contain values calculated from other attributes. An example of this can be seen in Figure 8.5. Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically saved to the database. Figure 8.5. Example of a derived attribute.KeysAn important constraint on an entity is the key. The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set. Types of KeysThere are several types of keys. These are described below. Candidate keyA candidate key is a simple or composite key that is unique and minimal. It is unique because no two rows in a table may have the same value at any time. It is minimal because every column is necessary in order to attain uniqueness. From our COMPANY database example, if the entity is Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:
Composite keyA composite key is composed of two or more attributes, but it must be minimal. Using the example from the candidate key section, possible composite keys are:
Primary keyThe primary key is a candidate key that is selected by the database designer to be used as an identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not be null. The primary key is indicated in the ER model by underlining the attribute.
In the following example, EID is the primary key: Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) Secondary keyA secondary key is an attribute used strictly for retrieval purposes (can be composite), for example: Phone and Last Name. Alternate keyAlternate keys are all candidate keys not chosen as the primary key. Foreign keyA foreign key (FK) is an attribute in a table that references the primary key in another table OR it can be null. Both foreign and primary keys must be of the same data type. In the COMPANY database example below, DepartmentID is the foreign key: Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) NullsA null is a special symbol, independent of data type, which means either unknown or inapplicable. It does not mean zero or blank. Features of null include:
NOTE: The result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is null (except functions that ignore nulls). Example of how null can be usedUse the Salary table (Salary_tbl) in Figure 8.6 to follow an example of how null can be used. Figure 8.6. Salary table for null example, by A. Watt.To begin, find all employees (emp#) in Sales (under the jobName column) whose salary plus commission are greater than 30,000.
This result does not include E13 because of the null value in the commission column. To ensure that the row with the null value is included, we need to look at the individual fields. By adding commission and salary for employee E13, the result will be a null value. The solution is shown below.
RelationshipsRelationships are the glue that holds the tables together. They are used to connect related information between tables. Relationship strength is based on how the primary key of a related entity is defined. A weak, or non-identifying, relationship exists if the primary key of the related entity does not contain a primary key component of the parent entity. Company database examples include:
A strong, or identifying, relationship exists when the primary key of the related entity contains the primary key component of the parent entity. Examples include:
Types of RelationshipsBelow are descriptions of the various types of relationships. One to many (1:M) relationshipA one to many (1:M) relationship should be the norm in any relational database design and is found in all relational database environments. For example, one department has many employees. Figure 8.7 shows the relationship of one of these employees to the department. Figure 8.7. Example of a one to many relationship.One to one (1:1) relationshipA one to one (1:1) relationship is the relationship of one entity to only one other entity, and vice versa. It should be rare in any relational database design. In fact, it could indicate that two entities actually belong in the same table. An example from the COMPANY database is one employee is associated with one spouse, and one spouse is associated with one employee. Many to many (M:N) relationshipsFor a many to many relationship, consider the following points:
Figure 8.8 shows another another aspect of the M:N relationship where an employee has different start dates for different projects. Therefore, we need a JOIN table that contains the EID, Code and StartDate. Figure 8.8. Example where employee has different start dates for different projects.Example of mapping an M:N binary relationship type
Unary relationship (recursive)A unary relationship, also called recursive, is one in which a relationship exists between occurrences of the same entity set. In this relationship, the primary and foreign keys are the same, but they represent two entities with different roles. See Figure 8.9 for an example. For some entities in a unary relationship, a separate column can be created that refers to the primary key of the same entity set. Figure 8.9. Example of a unary relationship.Ternary RelationshipsA ternary relationship is a relationship type that involves many to many relationships between three tables. Refer to Figure 8.10 for an example of mapping a ternary relationship type. Note n-ary means multiple tables in a relationship. (Remember, N = many.)
alternate key: all candidate keys not chosen as the primary keycandidate key: a simple or composite key that is unique (no two rows in a table may have the same value) and minimal (every column is necessary) characteristic entities: entities that provide more information about another table composite attributes: attributes that consist of a hierarchy of attributes composite key: composed of two or more attributes, but it must be minimal dependent entities: these entities depend on other tables for their meaning derived attributes: attributes that contain values calculated from other attributes derived entities: see dependent entities EID: employee identification (ID) entity: a thing or object in the real world with an independent existence that can be differentiated from other objects entity relationship (ER) data model: also called an ER schema, are represented by ER diagrams. These are well suited to data modelling for use with databases. entity relationship schema: see entity relationship data model entity set:a collection of entities of an entity type at a point of time entity type: a collection of similar entities foreign key (FK): an attribute in a table that references the primary key in another table OR it can be null independent entity: as the building blocks of a database, these entities are what other tables are based on kernel: see independent entity key: an attribute or group of attributes whose values can be used to uniquely identify an individual entity in an entity set multivalued attributes: attributes that have a set of values for each entity n-ary: multiple tables in a relationship null: a special symbol, independent of data type, which means either unknown or inapplicable; it does not mean zero or blank recursive relationship: see unary relationship relationships: the associations or interactions between entities; used to connect related information between tables relationship strength: based on how the primary key of a related entity is defined secondary key an attribute used strictly for retrieval purposes simple attributes: drawn from the atomic value domains SIN: social insurance number single-valued attributes: see simple attributes stored attribute: saved physically to the database ternary relationship: a relationship type that involves many to many relationships between three tables. unary relationship: one in which a relationship exists between occurrences of the same entity set. AttributionThis chapter of Database Design (including images, except as otherwisse noted) is a derivative copy of Data Modeling Using Entity-Relationship Model by Nguyen Kim Anh licensed under Creative Commons Attribution License 3.0 license The following material was written by Adrienne Watt:
How do you know if a relationship is oneA one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint. In the relationship window in Access, the primary key side of a one-to-many relationship is denoted by a number 1. The foreign key side of a relationship is denoted by an infinity symbol.
What is an example of a many to one relationship?In a database management system, a Many-to-One relationship is defined as a relationship between several instances of one entity and one instance of another entity. For example, it is possible for more than one student to work on a project.
Which goes in the table on the many side of a one many relationship?In a one-to-many relationship, the table on the one side of the relationship is the primary table and the table on the many side is the related table. A one-to-many relationship is the most common relationship found between tables in a relational database.
What is manyA many-to-many relationship exists when one or more items in one table can have a relationship to one or more items in another table. For example: Your Order table contains orders placed by multiple customers (who are listed in the Customers table), and a customer may place more than one order.
|