Chapter 12 Study Aid Page 1 Of 3

Chapter 12 Study Aidpage 1 Of 3chapter 12 Up Through Section 1263 Ca

Chapter 12 STUDY AID Chapter 12 up through Section 12.6.3 can be a bit academic; the below summary can be used to help clarify some of the terms and key points that will be needed in assignments. The chapter is focused on Entity-Relationship models – in general it tends to add a layer of abstraction to represent more generic or complex situations then will be assigned in this course. The chapter uses the term entity type – we can think of that as an entity – which in turn will be a table in our databases. The chapter uses the term entity occurrence to be “a uniquely identifiable object of an entity type†– we can think of that as a row (or record) in a table. The chapter uses the term relationship type to be a “set of meaningful associations among entity types†– think of this as describing how the data in one table can be associated with the data in a second table, starting with a verb – for instance, the textbook refers to a relationship type called “POwns†which explains that each Business Owner can own 0, 1, or many properties, while each property can be owned by 0 or 1 Business Owner.

Notice that the textbook uses a verb to describe the association and then proceeds to give examples of how that verb can be applied to the rows in each entity (table). Don’t worry complex relationships – this course focus on relationships between two entities – or recursive relationships. Again, it is good to know about composite, and multivalued attributes – but they will not be used in the assignments. The same can be said for strong and weak entity types. A critical concept in this chapter is what they refer to as “multiplicity†– the chapter uses the term to be “the number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particulate relationshipâ€.

The textbook than explains that there can be one-to-one, one-to-many, and many-to-many types of relationships. The text goes on to discuss how specific rows in one table may be associated with rows in a second table. For instance, one member of staff may oversee zero properties, one property, or many properties. While is can be interesting and worthwhile to look at specific rows and see which staff has how many properties, we must design the database (tables) to provide the maximum flexibility. So, since one member of staff may oversee many properties we must design for that.

We also need to look backwards; that is, we look at the relationship between 2 entities from each side – for this course we will only be concerned with 2 entities in a relationship (the textbook refers to this as binary – because there are 2 entities involved). If we look at the staff and property relationship from the property side, the textbook tells us that each property can be managed by zero, or one member of staff. We are now ready to take the final step. But first, let’s provide more readable definitions for one-to-one, one-to-many, and many-to-many. One-to-One Relationship – a relationship between two entities (tables) in which each occurrence (row) in the first entity is related one occurrence (row) in the second; and each occurrence (row) of the second entity is related to one occurrence (row) of the first entity.

One-to-Many Relationship – a relationship between two entities (tables) in which each occurrence (row) in the first entity is related to zero, one, or many occurrences (rows) in the second; and each occurrence (row) of the second entity is related to at most one occurrence (row) of the first entity. Sometimes this is referred to as a parent/child relationship. Another way of saying this is, one row of the first table can be related to many rows in the second table; and each row in the second table is related to at most one row in the first table. Many-to-Many Relationship – a relationship between two entities (tables) in which each occurrence (row) in each entity (table) can be related to zero, one, or many occurrences (rows) of the other entity (table).

Another way of thinking about this is, each row in the first table can be related to zero, one, or many rows in the second table, and each row in the second table can be related to zero, one, or many rows in the first table. For this course – we will be using one-to-many and many-to-many relationship. Let’s look at some examples based on the DreamHome database (Connolly, pg. 112): Chapter 12 STUDY AID The Branch and Staff entities (tables) have a one-to-many relationship. Each Staff member can be assigned to zero or one Branch, and each Branch can have zero, one, or many assigned staff members.

The Private Owner and the Property for Rent entities (tables) have a one-to-many relationship. Each property can be owned by zero, or one owner, and each owner can own zero, one, or many properties. The Property table has several fields – focus on the ownerNo field for this relationship. You may notice that in a one-to-many relationship – a value in the primary key field in the first table will have the same value as that field in the associated rows in the many table. The Client and Viewing entities (tables) have a many-to-many relationship.

One client may view several properties, and each property can be viewed by several clients. Look at the tables in above figure, the fields in those tables do not suggest that there is a many-to-many relationship between them! We need to look at the Viewing table to “see†the many-to-many relationship. The Viewing table has a composite key – made up of the ClientNo and PropertyNo fields. That is not an accident – that composite key supports the many-to-many relationship between the Client and Property for Rent entities.

If you look at the above spreadsheet we can see that the Viewing table’s composite key fields can be used to link us back to each of those tables – and that the client number and property number fields by themselves have duplicate entries in the Viewing table. That will always be the case for a many-to-many relationship – we will always need a third table to support this type of relationship between to other tables (entities). Bibliography Connolly, T. M., & Begg, C. E. (2015). Database Systems A Practice Approach to Design, Implementation, and Management. (6th). Boston: Pearson.

Paper For Above instruction

The focus of Chapter 12 of the textbook by Connolly and Begg (2015) is on Entity-Relationship (ER) models, which serve as an abstraction layer to represent complex or generalized relationships among data entities within a database system. This chapter introduces fundamental concepts such as entity types, entity occurrences, relationship types, and the notion of multiplicity, which collectively enable database designers to visualize and structure data more effectively. An entity type refers to a generalized category of objects—represented as tables—while an entity occurrence pertains to individual objects within those categories, akin to rows or records in a table. Relationship types illustrate how entities are associated, often described with verbs to specify the nature of the association, such as "owns" in a property ownership scenario.

Understanding the nature of relationships is crucial for database design, particularly distinguishing among one-to-one, one-to-many, and many-to-many relationships. A one-to-one (1:1) relationship occurs when each record in one entity relates to only one record in another, and vice versa. Example scenarios may involve personal data where each individual has a unique account, or vice versa. In contrast, a one-to-many (1:N) relationship involves one entity's record potentially linking to multiple records in another, but each of those in the second can only link back to one in the first—such as a 'branch' and 'staff' relationship, where each branch can have many staff members, but each staff member works for only one branch.

Many-to-many (M:N) relationships are more complex, with each record in either entity potentially linked to multiple records in the other. For example, in the DreamHome database, clients and properties exhibit a many-to-many relationship, as a client can view multiple properties, and each property can be viewed by multiple clients. This complex relationship necessitates an associative or join table—such as the Viewing table with a composite key comprising ClientNo and PropertyNo—to manage the relationships efficiently. Such join tables are essential in relational database design to normalize data and reduce redundancy.

The chapter emphasizes the importance of designing databases to accommodate maximum flexibility and future growth. For instance, designing for one-to-many relationships involves ensuring that foreign key fields in the child tables correctly reference primary keys in parent tables. The use of primary and foreign keys maintains referential integrity across relationships. The possible cardinalities and their implications are vital considerations for accurate data modeling and retrieval.

Furthermore, the chapter briefly touches on the constraints and characteristics of composite keys, particularly in many-to-many relationships, and highlights their role in linking relational tables. It also clarifies that while relationships can get increasingly complex, this course primarily focuses on binary relationships between two entities, simplifying the modeling process by excluding multivalued attributes, composite attributes, or complex weak and strong entities unless explicitly specified.

In summary, understanding ER models and the types of relationships—one-to-one, one-to-many, and many-to-many—is fundamental for effective database design. These relationships govern how data is stored, linked, and queried, influencing the overall efficiency and scalability of the database. The application of these concepts, as exemplified by the DreamHome database, illustrates how careful analysis and planning lead to robust relational structures, facilitating accurate data management and retrieval.

References

• Connolly, T. M., & Begg, C. E. (2015). Database Systems: A Practice Approach to Design, Implementation, and Management (6th ed.). Boston: Pearson.
• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
• Date, C. J. (2012). An Introduction to Database Systems. Addison-Wesley.
• Modern Database Management. Pearson.
• Rob, P., & Coronel, C. (2012). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill.
• Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
• Korth, H. F., & Silberschatz, A. (2010). Database System Concepts. McGraw-Hill Education.
• Connolly, T. M., & Begg, C. E. (2015). Database Systems: A Practice Approach to Design, Implementation, and Management. Pearson.