Entity-Relationship (ER)

1. Entity-Relationship (ER) • One-to-one • One-to-many • Many-to-many

2. Refining The Entity-Relationship Diagram • Entities cannot be modeled unrelated to any other entity. Otherwise, when the model was transformed to the relational model, there would be no way to navigate to that table.

3. Resolve Many-To-Many Relationships • Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model. • Therefore, many-to-many relationships must be resolved in the modeling process. • The strategy for resolving many-to-many relationship is to replace the relationship with an association entityand then relate the two original entities to the association entity.

4. Example 1: • A tourist can speak one or different languages. • A language can be spoken by different tourists.

5. Probable table:

6. Problems • More space is used • Empty records in the table • M-to-M relationship cannot be presented in a relational table

8. Resolution

10. What about this approach? There is repeating information!

11. Example 2: • Employees may be assigned to many projects. • Each project must have assigned to it more than one employee.

12. Notice that the schema changes the semantics of the original relation to: Employees may be given assignments to projects. Projects must be done by more than one employee assignment.

13. Primary and Foreign Keys • Primary and foreign keys are the most basic components on which relational theory is based. • Primary keys enforce entity integrity by uniquely identifying entity instances. • Foreign keys enforce referential integrity by completing an association between two entities.

14. The PRIMARY key • The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity. • To qualify as a primary key for an entity, an attribute must have the following properties: • it must have a non-null value for each instance of the entity • the value must be unique for each instance of an entity • the values must not change or become null during the life of each entity instance

15. PRIMARY KEY

16. FOREIGN Key • A foreign key is a relationship or link between two tables which ensures that the data stored in a database is consistent. • The foreign key link is set up by matching columns in one table (the child) to the primary key columns in another table (the parent). • Foreign keys are formed by migrating the entire primary key from the parent or generic entity.

17. Foreign Key In the example, there is a link between the Company and Contact tables.   The Company table is the parent table in the link.  The Contact table is the child: the Company_ID field in the Contact table indicates which Company a Contact belongs to

18. Basic Rules • Every entity in the data model shall have a primary key whose values uniquely identify entity instances. • The primary key attribute cannot be optional (i.e., have null values). • The primary key cannot have repeating values. That is, the attribute may not have more than one value at a time for a given entity instance is prohibited. This is known as the No Repeat Rule. • Entities with compound primary keys cannot be split into multiple entities with simpler primary keys. This is called the Smallest Key Rule. • The entire primary key must migrate from parent entities to child entities.

19. Data Integrity • Data integrity means, in part, that you can correctly and consistently navigate and manipulate the tables in the database. There are two basic rules to ensure data integrity; entity integrity and referential integrity. • The entity integrity rule states that the value of the primary key can never be a null value (a null value is one that has no value and is not the same as a blank). Because a primary key is used to identify a unique row in a relational table, its value must always be specified and should never be unknown. The integrity rule requires that insert, update, and delete operations maintain the uniqueness and existence of all primary keys. • The referential integrity rule states that if a relational table has a foreign key, then every value of the foreign key must either be null or match the values in the relational table in which that foreign key is a primary key.

20. Steps In Building the Data Model • While ER model lists and defines the constructs required to build a data model, there is no standard process for doing so. Typically, the sequence can be: • Identification of data objects and relationships • Drafting the initial ER diagram with entities and relationships • Refining the ER diagram • Add key attributes to the diagram • Adding non-key attributes • Validating the model through normalization • In practice, model building is not a strict linear process. The requirements analysis and the draft of the initial ER diagram often occur simultaneously. Refining and validating the diagram may uncover problems or missing information which require more information gathering and analysis.

21. Activity Study your topics. Formulate specific objectives. State the need to come up with database management. Come up with ENTITIES. Draw the E-R diagram. Think of the possible key ATTRIBUTES of every entity in your database. Link the entities with the proper keys (foreign and primary). Can the entities be linked and queried? Formulate some possible questions that your database system could provide answers to the clients. Give conclusions. For topics, research for institutions/organizations (1 per group) that need database management. Study their current database and come up with your own design.

22. Possible Organizations • DENR (select a department) • GIS Cebu • WRC • USC Registrar • USC CED