CIS 3365: Chapter 4 - Entity Relationship (ER) Modeling

Domain

all attributes have this. It is the set of possible values for a given attribute. Ex. the domain for a GPA is written (0,4) Ex. the domain for traditional gender attributes is M or F

Binary relationship

an ER term for an association (relationship) between two entities. They are the most common type of relationship. Ex. PROFESSOR teaches CLASS

(IMG) An ER Representation of Recursive Relationships

(1:M unary relationship) An EMPLOYEE may manage many EMPLOYEES. Each EMPLOYEE is manage by one EMPLOYEE. (1:1 unary relationship) an EMPLOYEE may be married to one and only one other EMPLOYEE. (M:N unary relationship) a COURSE may be a prerequisite to many other COURSES. Each COURSE may have many other COURSEs as prerequisites.

(IMG) Implementation of the 1:M Recursive Relationship "EMPLOYEE manages EMPLOYEE"

(1:M unary relationship) An EMPLOYEE may manage many EMPLOYEES. Each EMPLOYEE is managed by one EMPLOYEE.

Connectivities and cardinalities are established by concise statements known as:

Business rules

(IMG) A Multivalued Attribute in an Entity

CAR_COLOR is the multivalued attribute of the CAR entity. In the Chen model, it is indicated by a double line connecting the attribute to the entity.

Building an ERD usually involves the following activities:

1. Create a detailed narrative of the organization's description of operations. 2. Identify the business rules based on the description of operations. 3. Identify the main entities and relationships from the business rules. 4. Develop the initial ERD. 5. Identify the attributes and primary keys that adequately describe the entities. 6. Revise and review the ERD. The revise and review process is repeated until the end users and designers agree that the ERD is a fair representation of the organization's activities and functions.

(IMG) Connectivity and Cardinality in an ERD

A CLASS can be taught by 1 PROFESSOR A PROFESSOR teaches many CLASSES, up to 4.

Relational schema

a frequently used shorthand notation for the ERD table structure. It uses the following format:

Composite identifier

a primary key composed of more than one attribute. Ex. a college database admin may decide to identify each CLASS entity instance by using a composite primary key of CRS_CODE and CLASS_SECTION instead of using CLASS_CODE. Either approach uniquely identifies each entity instance.

Recursive relationship

a relationship found within a single entity type. Naturally found within a unary relationship. Ex. an EMPLOYEE is married to an EMPLOYEE Ex. a PART is a component of another PART

Mandatory participation

a relationship in which one entity occurrence must have a corresponding occurrence in another entity. Ex. "an EMPLOYEE works in a DIVISION" - a person cannot be an employee without being assigned to a company's division. The existence of a mandatory relationship indicates that the minimum cardinality is at least 1 for the mandatory entity.

Weak relationship (i.e. non-identifying relationship)

a relationship in which the primary key of the related entity does not contain a primary key component of the parent entity. Ex. In image: a 1:M relationship between COURSE and CLASS. A weak relationship exists between COURSE and CLASS because CRS_CODE (the primary key of the parent entity) is only a foreign key in the CLASS ENTITY. In this example, the CLASS primary key did not inherit a primary key component from the COURSE entity.

Strong relationship i.e. identifying relationship

a relationship that exists when the primary key of the related entity contains a primary key component of the parent entity. A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationships exists whenever the primary key of the related entity contains the primary key of the parent entity. Ex. in image: a 1:M relationship between COURSE and CLASS The CLASS entity primary key is composed of CRS_CODE and CLASS_SECTION. Therefore, a strong relationship exists between COURSE and CLASS because CRS_CODE (the primary key of the parent entity) is a primary key component in the CLASS entity.

Ternary relationship

an ER term used to describe an association (relationship) between three entities. Ex. a DOCTOR prescribes a DRUG for a PATIENT

Unary relationship

an ER term used to describe an association within an entity. Ex. an EMPLOYEE might manage another EMPLOYEE

Relationship

association between entities. Ex. a CUSTOMER may generate many INVOICES Ex. Each INVOICE is generated by one CUSTOMER Ex. a STUDENT takes a CLASS Ex. a PROFESSOR teaches a CLASS Ex. a DEPARTMENT employs a PROFESSOR

Multi-valued attributes

attributes that can have many values for a single entity occurrence. Ex. A person may have several college degrees. - EMP_DEGREE attribute might store the string, "BBA, MBA, PHD" to indicate three different degrees held. Ex. A household may have several different phones

Associative entities (i.e. composite entities)

exist in a M:N relationship between two or more entities. It "breaks up" the M:N relationship by establishing a 1:M relationship with the parent entities and is composed of the primary key attributes of each parent entity. Ex. in the M:N relationship, a STUDENT can take many CLASSes. A CLASS can be taken by many STUDENTS. An ENROLL entity can exist to break up the M:N Relationship. A STUDENT can have many ENROLLments. An ENROLLment can only be assigned to one student. An ENROLLment can only be assigned to one class. A CLASS can have many ENROLLments.

Optional participation

in ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship. Ex. in the "COURSE generates CLASS" relationship, you can note that at least some courses do not generate a class. In other words, an entity occurrence (row) in the COURSE table does not necessarily require the existence of a corresponding entity occurrence in the CLASS table. Therefore, the CLASS entity is considered to be optional to the COURSE entity. The existence of an optional entity indicates that its minimum cardinality is 0.

(IMG) Crow's Foot Symbols with Cardinality and Optional/Mandatory Participation

***Memorize these. They are the most important supplement to know when making ERD diagrams.***

Attribute

- characteristic of an entity. Ex. the STUDENT entity includes the attributes STU_LNAME, STU_FNAME, and STU_INITIAL.

If multivalued attributes exist, the designer must decide on one of two possible courses of action:

1. Within the original entity, create several new attributes, one for each component of the original multivalued attribute. Ex. the CAR entity's attribute CAR_COLOR can be split to create the new attributes CAR_TOPCOLOR, CAR_BODYCOLOR, and CAR_TRIMCOLOR which are then assigned to the CAR entity. 2. (Recommended) Create a new entity composed of the original multivalued attribute's components. This new entity allows the designer to define color for different sections of the car. Then this new CAR_COLOR entity is related to the original CAR entity in a 1:M relationship.

(IMG) The Implementation of a Ternary Relationship

A DOCTOR writes one or more PRESCRIPTIONS A PATIENT may receive one or more PRESCRIPTIONS A DRUG may appear in one or more PRESCRIPTIONS Note that it is possible to track all transactions. For instance, you can tell that the first prescription was written by doctor 32445 for patient 102, using the drug DRZ.

When implementing the ERM in a relational database, a M:N relationship must:

Be mapped to a set of 1:M relationships through a composite entity.

Connectivities and cardinalities are usually based on:

Business rules

(IMG) The CLASS table (Entity) components and contents

CLASS_CODE is the primary key, and the combination of CRS_CODE and CLASS_SECTION is a proper candidate key. If the CLASS_CODE attribute is deleted, the candidate key (CRS_CODE and CLASS_SECTION) becomes an acceptable composite primary key.

(IMG) The Fourth Tiny College ERD Segment

Created from the business rule: Each department should have one or more professors assigned to it. One and only one of those professors chairs the department, and no professor is required to accept the chair position. Therefore, DEPARTMENT is optional to PROFESSOR in the "chairs" relationship. How to read: - A PROFESSOR is employed by one and only one DEPARTMENT (1,1 = mandatory. A professor needs to be associated with a department). - A DEPARTMENT employs many PROFESSORS (1,M = mandatory. A department needs to have at least one professor associated with it.) - A PROFESSOR chairs one and only one DEPARTMENT (0,1 = optional. A professor can chair only one department, or chair no department) - A DEPARTMENT is chaired by one and only one PROFESSOR (1,1 = mandatory. A department needs to have one professor that chairs it) Also note how entities are connected with their primary keys and foreign keys.

(IMG) The Fifth Tiny College ERD Segment

Created from the business rule: Each professor may teach up to four classes; each class is a section of a course. A professor may also be on a research contract and teach no classes at all. How to read: - A PROFESSOR teaches many CLASSES (0,N = optional. A professor can teach 0 classes or multiple - in this case, up to 4 classes). - A CLASS is taught by one and only one PROFESSOR (1,1 = mandatory. A class needs to be taught by one professor). Also note how entities are connected with their primary keys and foreign keys.

(IMG) The Third Tiny College ERD Segment

Created from the business rules: 1. A CLASS is a section of a COURSE. That is, a department may offer several sections (classes) of the same database course. In short, a 1:M relationship exists between COURSE and CLASS. 2. Additionally, each class is offered during a given semester. SEMESTER defines the year and the term that the class will be offered. It is possible that the Tiny College calendar is set with semester beginning and ending dates prior to the creation of the semester class schedule so CLASS is optional to SEMESTER. 3. Because a course may exist in Tiny College's course catalog even when it is not offered as a class in a given semester, CLASS is optional to COURSE. How to read: - A SEMESTER includes many CLASSES (0,N = optional. It can include 0 classes or many) - A CLASS is included in one and only one SEMESTER (1,1 = mandatory. It needs to be associated with a semester) - A CLASS is generated by one and only one COURSE (1,1 = mandatory. A class needs to be associated with one course) - A COURSE generates many CLASSES (0,N = optional. A course can generate 0 classes or many classes) Also note how entities are connected with their primary keys and foreign keys.

(IMG) The Implementation of the M:N Recursive Relationship "PART contains PART"

If a part can be used to assemble several different kinds of other parts and is itself composed of many parts, two tables are required to implement the "PART contains PART" relationship.

(IMG) Implementation of the M:N Recursive Relationship "COURSE requires COURSE"

In this example, MATH-243 is a prerequisite to QM-261 and QM-362, while both MATH-243 and QM-261 are prerequisites to QM-362. (M:N unary relationship) a COURSE may be a prerequisite to many other COURSES. Each COURSE may have many other COURSEs as prerequisites.

(IMG) CLASS is optional to COURSE

In this scenario, it is possible for the department to create the COURSE entity first and then create the CLASS entity after making the teaching assignments. In the real world, there may be courses for which sections (classes) have not yet been defined. Some courses do not generate classes each semester. Note the Crow's foot notation on the left. It is a (1,1) cardinality, the "1" side is mandatory. Note the Crow's foot notation on the right. It is a (0,N) cardinality, the "many" side is optional. (0,N) means a COURSE can generate no classes or many classes (1,1) means a single CLASS only corresponds to a single COURSE.

(IMG) Another Unary Relationship: "PART contains PART"

Indicates that a rotor assembly is composed of four 2.5-cm washers, two cotter pins, one 2.5-cm steel shank, four 10.25-cm rotor blades, and two 2.5-cm hex nuts. The relationship implemented in Figure 4.19 thus enables you to track each part within each rotor assembly

(IMG) Alleviating a Multivalued Attribute using a New Entity Set

Instead of having a multivalued attribute - CAR_COLOR - in CAR entity, you create a new entity called CAR_COLOR. You fill the new entity with the attribute's components, then you relate the new entity to the original CAR entity using a Foreign Key.

(IMG) An Optional Class Entity in the Relationship "PROFESSOR teaches CLASS"

It is quite possible for a professor not to teach a class. Therefore, CLASS is optional to PROFESSOR. On the other hand, a class must be taught by a professor. Therefore, PROFESSOR is mandatory to CLASS. Note that the cardinality next to CLASS is (0,3) indicating that a professor may teach no classes or as many as three classes. The cardinality next to the PROFESSOR table (1,1) indicates that each CLASS table row references one and only one PROFESSOR row.

(IMG) Various Implementations of the 1:1 Recursive Relationship

Note that first implementation stores everything in one table. The EMPLOYEE_V1 table is likely to yield data anomalies. Ex. if Anne Jones divorces Anton Shapiro, two records must be updated - setting the respective EMP_SPOUSE values to null. If only one record is updated inconsistent data occurs. In the 2nd implementation, the MARRIED_V1 table is in a 1:M relationship with EMPLOYEE. It does eliminate the nulls for employees who are not married to other employees. However, this approach yields possible duplicate values. Ex. the marriage between employees 345 and 347 may still appear twice, once as (345,347) and once as (347,345). In the 3rd approach, two entities, MARRIAGE and MARPART is in a 1:M relationship. MARPART contains the EMP_NUM foreign key to EMPLOYEE. However, even this approach has issues. It requires the collection of additional data regarding the employees' marriage—the marriage date. If the business users do not need this data, then requiring them to collect it would be inappropriate. As you can see, a recursive 1:1 relationship yields many different solutions with varying degrees of effectiveness.

(IMG) Converting the M:N Relationship into Two 1:M Relationships

Note that the composite ENROLL entity is existence-dependent on the other two entities. The composition of the ENROLL entity is based on the primary keys of the entities that connected by the composite entity. The composite entity may also contain additional attributes that play no role in the connective process (in this case, the ENROLL_GRADE attribute). The ENROLL table's key (CLASS_CODE and STU_NUM) is composed entirely of the primary keys of the CLASS and STUDENT tables. Therefore, no null entries are possible in the ENROLL table's key attributes.

(IMG) The 1:1 Recursive Relationship "EMPLOYEE is married to EMPLOYEE"

Note that you can determine James Ramirez is married to Louise Ramirez, who is married to James Ramirez. Anne Jones is married to Anton Shapiro, who is married to Anne Jones. (1:1 unary relationship) an EMPLOYEE may be married to one and only one other EMPLOYEE.

(IMG) The M:N Relationship between STUDENT and CLASS

Note the Crow's notation: the symbol has cardinality of (0,N) "zero or many" with the "many" side being optional. Therefore, to read this, you say the Student can enroll in 0 or many classes. Furthermore, a CLASS can have 0 or more students.

(IMG) A Composite Entity in an ERD

Note the optionalities (open circle Crow's notation) must be transferred to ENROLL. A STUDENT is written in (0,N) "zero or many" ENROLLments An ENROLL contains (1,1) "one and only one" STUDENT (mandatory) An ENROLL is found in (1,1) "one and only one" CLASS (mandatory) A CLASS can contain (0,N) "zero or many" ENROLLments Also note that the keys of the ENROLL composite entity is entirely composed of the primary keys of the parent entities. They are also the foreign keys of the ENROLL entity.

ERD connectivity describes:

Relationship classification (1:1, 1:M, M:N)

How to implement a relationship between entities in a database?

The primary key (PK) of one entity (the parent entity, normally on the "one" side of a one-to-many relationship) appears as a foreign key (FK) in the related entity (the child entity, mostly the entity on the "many" side of the one-to-many relationship). Sometimes, the foreign key also is a primary key component in the related entity.

ERD cardinality expresses:

The specific number of entity occurrences associated with an occurrence of a related entity.

Unified Modeling Language (UML) class diagrams are used to represent:

The static data structures in a data model. The symbols used in the UML class and ER diagrams are very similar. The UML class diagrams can be used to depict data models at the conceptual or implementation abstraction levels.

(IMG) COURSE and CLASS in a Mandatory Relationship

This condition is created by the constraint imposed by the semantics of the statement, "Each COURSE generates one or more CLASSes" In ER terms, each COURSE in the "generates" relationship must have at least one CLASS. Therefore, a CLASS must be created as the COURSE is created to comply with the semantics of the problem. Crow's foot notation on left: one and only one relationship, the "1" side is mandatory. Crow's foot notation on the right: one or many relationship, the "many" side is mandatory (1,1) means that a single CLASS corresponds to a single COURSE (1,N) means that a COURSE can have at least one and upwards of many CLASSes.

(IMG) The Second Tiny College ERD Segment

This is created from the business rule: Each department may offer courses. For example, the management/marketing department offers courses such as Introduction to Management, Principles of Marketing, and Production Management. - Note that this relationship is based on the way Tiny College operates. For example, if Tiny College had some departments that were classified as "research only," they would not offer courses; therefore, the COURSE entity would be optional to the DEPARTMENT entity. How to read: - A DEPARTMENT offers many COURSES (0,N = optional. It does not need to offer any course.) - A COURSE is offered by one and only one DEPARTMENT (1,1 = mandatory. A Course needs to have an associated department). Also note how entities are connected with their primary keys and foreign keys.

Iterative process

a process based on repetition of steps and procedures. Developing an ERD uses this type of process.

Existence-independent (i.e. strong entity or regular entity)

a property of an entity that can exist apart from one or more related entities. Such a table must be created first when referencing an existence-dependent table. Ex. suppose that the XYZ Corporation uses parts to produce its products. Furthermore, suppose that some of those parts are produced in-house and other parts are bought from vendors. In that scenario, it is quite possible for a PART to exist independently from a VENDOR in the relationship "PART is supplied by VENDOR" because at least some of the parts are not supplied by a vendor. Therefore, PART is existence-independent from VENDOR.

Existence-dependent

a property of an entity whose existence depends on one or more other entities. In other words, an entity is existence-dependent if it has a mandatory foreign key (a foreign key attribute that cannot be null). Ex. if an employee wants to claim one or more dependents for tax-withholding purposes, the relationship "EMPLOYEE claims DEPENDENT" would be appropriate. In that case, the DEPENDENT entity is clearly existence-dependent on the EMPLOYEE entity because it is impossible for the dependent to exist apart from the EMPLOYEE in the database. In such an environment, the existence-independent table must be created and loaded first because the existence-dependent key cannot reference a table that does not yet exist.

Composite attribute

an attribute that can be further subdivided to yield additional attributes. Ex. the attribute ADDRESS can be subdivided, into street, city, state, and zip code. Ex. the attribute PHONE_NUMBER can be subdivided into area code and exchange number

Single-valued attribute

an attribute that can have only a single value. Ex. a person can only have one Social Security number Ex. A manufactured part can have only one serial number

Simple attribute

an attribute that cannot be subdivided. Ex. Age, sex, marital status

Optional attribute

an attribute that does not require a value; therefore, it can be left empty.

Required attribute

an attribute that must have a value; in other words, it cannot be left empty.

Derived attribute

an attribute whose value is calculated (derived) from other attributes. The derived attribute need not be physically stored within the database; instead, it can be derived by using an algorithm. Ex. an employee's EMP_AGE may be found by computing the integer value of the difference between the current date and the EMP_DOB. Ex. AVG_SALARY can be computed by summing up all the stored salaries in the database then dividing by the total count.

Weak entity

an entity that displays existence-dependence and inherits the primary key of its parent entity. - The entity is existence-dependent; it cannot exist without the entity with which it has a relationship - The entity has a primary key that is partially or totally derived from the parent entity in the relationship - A strong (identifying) relationship indicates that the related entity is weak. Ex. a DEPENDENT requires the existence of an EMPLOYEE (for tax withholding purposes). A primary key component of DEPENDENT is also the primary key/foreign key of EMPLOYEE.

Cardinality

expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. A property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity. In the ERD, cardinality is indicated by placing the appropriate numbers beside the entities, using the format (x,y). The first value represents the minimum number of associated entities, the second value represents the maximum number of associated entities.

Identifiers

one or more attributes that uniquely identify each entity instance. Identifiers are underlined in the ERD In the ERD, the entity identifier is mapped as the table's primary key (PK)

Participants

the entities that participate in a relationship. Ex. in the relationship, "PROFESSOR teaches CLASS." The participants are PROFESSOR and CLASS.

Relationship degree

the number of entities or participants associated with a relationship. A relationship degree can be unary, binary, ternary, or higher.

Connectivity

used to describe the relationship classification. Classifications include 1:1, 1:M, M:N

ERDs depict the database's main components:

Entities, attributes, and relationships

The ERM's main components are:

Entities, relationships, attributes

True or False: The conceptual model can handle M:N relationships and multivalued attributes, and you should implement them in the RDBMS.

False. You should not implement multivalued attributes in the RDBMS.

Implementing the ___ relationship requires the use of an additional (associative) entity.

M:N

Participation in an entity relationship is either:

Optional or mandatory

Entities represent

Real-world objects. In ER modeling, an entity corresponds to a table. The ERM refers to a table row as an entity instance or entity occurrence.

The ERM uses ___ to represent the conceptual database as viewed by the end user.

ERDs (Entity Relationship Diagrams)

True or False: To facilitate detailed queries, it is wise to change composite attributes into a series of simple attributes.

True

(IMG) The Sixth Tiny College ERD Segment

Created from the business rules: 1. A student may enroll in several classes but take each class only once during any given enrollment period. 2. Each student may enroll in up to six classes, and each class may have up to 35 students, thus creating an M:N relationship between STUDENT and CLASS. 3. Because a CLASS can initially exist at the start of the enrollment period even though no students have enrolled in it, STUDENT is optional to CLASS in the M:N relationship. This M:N relationship must be divided into two 1:M relationships through the use of the ENROLL entity. Note that the optional symbol is shown next to ENROLL. If a class exists but has no students enrolled in it, that class does not occur in the ENROLL table. Note also that the ENROLL entity is weak: it is existence-dependent, and its (composite) PK is composed of the PKs of the STUDENT and CLASS entities. You can add the cardinalities (0,6) and (0,35) next to the ENROLL entity to reflect the business rule constraints. How to read: - A STUDENT is written in many ENROLLS (0,N = optional. A student can have 0 enrollments or multiple) - An ENROLL writes one and only one STUDENT (1,1 = mandatory. An enroll needs to have one student.) - An ENROLL is found in one and only one CLASS (1,1 = mandatory. An enroll needs to be associated with one class.) - A CLASS can have many ENROLLS (0,N = optional. A class can have 0 enrolls or multiple). Also note how entities are connected with their primary keys and foreign keys.

(IMG) The Seventh Tiny College ERD Segment

Created from the business rules: 1. Each department has several (or many) students whose major is offered by that department. However, each student has only a single major and is therefore associated with a single department. 2. However, in the Tiny College environment, it is possible—at least for a while—for a student not to declare a major field of study. Such a student would not be associated with a department; therefore, DEPARTMENT is optional to STUDENT. How to read: - A DEPARTMENT has many STUDENTS (0,N = optional. It can have 0 students or many students) - A STUDENT is within only one DEPARTMENT (0,1 = optional. A student can be without a department or with only one department). Also note how entities are connected with their primary keys and foreign keys.

(IMG) The Eighth Tiny College ERD Segment

Created from the business rules: 1. Each student has an advisor in his or her department; each advisor counsels several students. 2. An advisor is also a professor, but not all professors advise students. Therefore, STUDENT is optional to PROFESSOR in the "PROFESSOR advises STUDENT" relationship. How to read: - A STUDENT is advised by one and only one PROFESSOR (1,1 = mandatory. A student needs to have an advisor). - A PROFESSOR can advise many STUDENTS (0,N = optional. A professor can advise 0 students or multiple students). Also note how entities are connected with their primary keys and foreign keys.

(IMG) The Ninth Tiny College ERD Segment

Created from the business rules: 1. The CLASS entity contains a ROOM_CODE attribute. Given the naming conventions, it is clear that ROOM_CODE is an FK to another entity. Clearly, because a class is taught in a room, it is reasonable to assume that the ROOM_CODE in CLASS is the FK to an entity named ROOM. 2. In turn, each room is located in a building. So, the last Tiny College ERD is created by observing that a BUILDING can contain many ROOMs, but each ROOM is found in a single BUILDING. 3. In this ERD segment, it is clear that some buildings do not contain (class) rooms. For example, a storage building might not contain any named rooms at all. How to read: - A BUILDING may contain many ROOMS (0,N = optional. A building can contain 0 named rooms or many rooms) - A ROOM is within one and only one BUILDING (1,1 = mandatory. A room needs to have an associated building) - A ROOM may contain many CLASSES (0,N = optional. A room can contain 0 classes or many classes) - A CLASS is within one and only one ROOM (1,1 = mandatory. A class needs to have an associated room). Also note how entities are connected with their primary keys and foreign keys.

(IMG) Depiction of a Derived Attribute

Denoted by the dotted line

Database designers must often make compromises that are triggered by conflicting goals, such as:

Design standards - The database design must conform to design standards that minimize data redundancies. Design standards allow you to work with well-defined components and to evaluate the interaction of those components with some precision. Processing speed - In many organizations, particularly those that generate large numbers of transactions, high processing speeds are often a top priority in database design. High processing speed means minimal access time, which may be achieved by minimizing the number and complexity of logically desirable relationships. Information requirements - Complex information requirements may dictate data transformations, and they may expand the number of entities and attributes within the design. Therefore, the database may have to sacrifice some of its "clean" design structures and high transaction speed to ensure maximum information generation.

(IMG) The First Tiny College ERD Segment

This is created from two business rules: 1. Tiny College (TC) is divided into several schools: business, arts and sciences, education, and applied sciences. Each school is administered by a dean who is a professor. Each professor can be the dean of only one school, and a professor is not required to be the dean of any school. Therefore, a 1:1 relationship exists between PROFESSOR and SCHOOL. Note that the cardinality can be expressed by writing (1,1) next to the entity PROFESSOR and (0,1) next to the entity SCHOOL. 2. Each school comprises several departments. For example, the school of business has an accounting department, a management/marketing department, an economics/finance department, and a computer information systems department. Note again the cardinality rules: The smallest number of departments operated by a school is one, and the largest number of departments is indeterminate (N), therefore it is expressed (1,N). On the other hand, each department belongs to only a single school; thus, the cardinality is expressed by (1,1). That is, the minimum number of schools to which a department belongs is one, as is the maximum number. How to read: - PROFESSOR is the dean of one SCHOOL (0,N = optional. A professor can be dean of one school or the dean of 0 schools.) - A SCHOOL has one and only one DEAN (1,1 = mandatory. A school needs to have a dean.) - A SCHOOL operates many DEPARTMENTS (1,M = at least 1 is mandatory) - A DEPARTMENT belongs to only one and only one SCHOOL (1,1 = mandatory. A department needs to be associated with a school.) Also note how entities are connected with their primary keys and foreign keys.

(IMG) A Weak Entity in a Strong Relationship

Two conditions for a weak entity: - Must be existence-dependent - Must have a strong relationship (PK inherited from parent's PK) In this example, a DEPENDENT in existence-dependent on an EMPLOYEE (you cannot have a dependent without an employee). And a PK component of DEPENDENT (EMP_NUM) is inherited from the PK of EMPLOYEE (EMP_NUM). Therefore, DEPENDENT is a weak entity to EMPLOYEE