Section 5 - Entities

Complex databases are developed in three phases: 1. _____ develops an entity-relationship model, capturing data requirements while ignoring implementation details. 2. _____ design converts the entity-relationship model into tables, columns, and keys for a particular database system. 3. _____ design adds indexes and specifies how tables are organized on storage media.

1. Analysis 2. Logical 3. Physical

T or F? 1) "Students" is a correct entity name. 2) "License" is a correct attribute name. 3) "Employee-Manages-Employee" is a correct relationship name. 4) "People" is a correct entity name. 5) "PassengerMileagePlanCode" is a correct attribute name. 6) "Department-IsManagedBy-Employee" is a correct relationship name.

1. F Entity names should be singular, not plural. "Student" is a better entity name. 2. F. Attribute names must have a type suffix. The correct attribute name is "LicenseNumber", "LicenseCode", or similar. Optionally, the name can have an entity prefix, as in "EmployeeLicenseNumber". 3. T Sometimes relationships relate an entity to itself. Since "Manages" is the active form of "manage", "Employee-Manages-Employee" is a correct relationship name. 4. F Although "People" is grammatically singular, "People" suggests many individuals and thus is an incorrect entity name. "Person" is singular and a better entity name. 5. T "Passenger" is the name of the attribute's entity. "Code" is an attribute type, usually defined as a mix of letters and numbers. "MileagePlan" is a two-word qualifier. Thus, "PassengerMileagePlanCode" has the form EntityQualifierType and is a correct attribute name. 6. F "IsManagedBy" is the passive form of the verb "manage". Although relationship names are sometimes passive verbs, active verbs are more concise. "Employee-Manages-Department" uses an active verb and is a better relationship name.

Implementing many-many relationships.

A many-many relationship becomes a new table: The new table contains two foreign keys, referring to the primary keys of the related tables. The primary key of the new table is the composite of the two foreign keys.

Implementing one-one relationships

Airport-IsLocatedAt-Address is a one-one relationship. The Address entity becomes a table with primary key AddressID. Address contains addresses of people as well as airports, so Address has more rows than Airport. The foreign key LocationAddressID goes in the table with fewer rows.

Column A depends on column B when each B value is related to at most one A value. A and B may be simple or composite columns. In a _____ ____ normal form table, if column A depends on column B, then B must be unique.

Boyce-Codd

Partition Example

CreditCard, DebitCard, and GiftCard partition Card. CreditType is partition attribute. Values are "Credit", "Debit", "Gift". Visa, MasterCard, and Discover are another partition. Partition attribute is SponsorCode. Card has two partitions.

_______ means intentionally introducing redundancy by merging tables. Denormalization eliminates join queries and therefore improves query performance

Denormalization Redundancy can be desirable in reporting databases, as processing is faster and queries are simpler. Therefore, reporting databases may contain tables that, by design, are not in third normal form.

Discovery is a step in which phase?

Discovery is the first step of the analysis phase.

Relationship minimum example

Each booking must be included on exactly one flight and be held by at least one passenger. A new flight includes no bookings. The definition of passenger requires that all passengers hold a booking. Maxima and minima appear together on ER diagrams.

Relationship example

Each flight departs from at most one airport. Each airport has many departing flights. ArrivesAt and DepartsFrom have the same maxima

Implementing plural attributes

Each flight offers several kinds of in-flight meals. MealType is a plural attribute of Flight. The plural attribute MealType moves to the new table FlightMealType. The foreign key FlightNumber references the initial table. The primary key of the new table is the composite of plural attribute MealType and foreign key FlightNumber. At most three meal types are offered on each flight, so MealType has a small, fixed maximum. Plural attributes with a small, fixed maximum can be implemented as multiple columns in the same table. MealType becomes three different columns.

Attribute maximum example

Each number is assigned to one employee. Each employee has one number. PassportNumber is unique, since each passport belongs to one employee. PassportNumber is plural, since an employee can hold passports from different countries. A name can describe many employees. Each employee has one name. Each skill can describe many employees. Each employee can have many skills. ER diagrams sometimes omit the entity maximum.

Determine the maxima for the Entity-Has-Attribute relationship Entity Passenger, attribute PassengerName

Each passenger has one name, and each name can describe many passengers, so Passenger-Has-PassengerName is many-one.

Determine the maxima Student-Takes-Course

Each student can take many courses, and each course has many students, so Student-Takes-Course is many-many.

Attribute minimum example

EmployeeNumber is required, since each employee must have an employee number. PassportNumber is optional, since some employees have no passport. FullName is required and SkillCode is optional.

Discovery

Entities, relationships, and attributes are discovered in interviews with database users and managers. Users and managers are usually familiar with data requirements from an old database, or perhaps a manual process with paper records

Attribute names have the form EntityQualifierType, such as EmployeeFirstName:

Entity is the name of the entity that the attribute describes. When the entity is obvious, in ER diagrams or informal conversation, QualifierType is sufficient and the entity name can be omitted. Qualifier describes the meaning of the attribute. Ex: First, Last, and Alternate. Sometimes a qualifier is unnecessary and can be omitted. Ex: StudentNumber. Type is chosen from a list of standard attribute types such as Name, Number, and Count. Attribute types are not identical to SQL data types. Ex: "Amount" might be an attribute type representing monetary values, implemented as the MONEY data type in SQL. "Count" might be an attribute type representing quantity, implemented as NUMBER in SQL.

Entities have an implicit relationship with their attributes, called ____ ___ _____ This relationship, like any other, has maxima and minima.

Entity-Has-Attribute.

T or F? Entities, relationships, and attributes always map directly to tables, foreign keys, and columns, respectively.

F. In the logical design phase, entities, relationships, and attributes usually become tables, foreign keys, and columns. Sometimes, however, an entity splits into several tables, several entities merge into one table, and relationships and attributes become tables.

T or F? An entity instance can be in two subtypes of the same partition.

F. Subtypes of a partition are mutually exclusive. Ex: A card cannot be both Visa and MasterCard, or Visa and Discover, at the same time.

Implementing many-one relationships

Flight-ArrivesAt-Airport is a many-one relationship. The Airport entity becomes a table with primary key AirportCode. ArrivesAt becomes the foreign key ArrivalAirportCode on the 'many' side, referencing the primary key AirportCode on the 'one' side.

The dependency relationship from subtype to supertype is called an _____ relationship.

IsA Ex: Manager-IsAn-Employee.

Normalizing a table to Boyce-Codd normal form involves three steps:

List all unique columns. Unique columns may be simple or composite. In composite columns, remove any columns that are not necessary for uniqueness. The primary key is unique and therefore always on this list. Identify dependencies on non-unique columns. Non-unique columns are either external to all unique columns or contained within a composite unique column. Eliminate dependencies on non-unique columns. If column A depends on a non-unique column B, A is removed from the original table. A new table is created containing A and B. B is a primary key in the new table and a foreign key in the original table.

Primary key of subtype table

MileagePlanMember is a subtype of Passenger. The subtype's primary key PassengerNumber is identical to the supertype's primary key. The primary key of a subtype is also the foreign key referencing the supertype.

________ eliminates redundancy by decomposing a table into two or more tables in higher normal form. Ex: A table in first normal form might be replaced by two tables in third normal form

Normalization In principle, normalization decomposes tables to any higher normal form. Fourth and fifth normal form are complex, however, and have limited practical value. As a practical matter, database designers usually normalize tables to Boyce-Codd normal form.

Artificial Key example

Passenger is an independent table. Each attribute becomes a column in the initial design. FullName is not unique and cannot be the primary key. Not all passengers have a mileage plan number. MileagePlanNumber is not required and cannot be the primary key. Every passenger must provide identification, such as a driver's license or passport. The composite (IdentificationNumber, IdentificationType) is unique. Passengers may provide different identification in the future, so (IdentificationNumber, IdentificationType) is unstable and a poor primary key. Since no suitable primary key exists, a database designer creates an artificial key PassengerNumber.

______ _______ is the greatest number of instances of one entity that can relate to a single instance of another entity. A relationship has two maxima, one for each of the related entities. Maxima are usually specified as one or many, and denoted as 1 or M in ER diagrams.

Relationship maximum

_____ _______ is the least number of instances of one entity that can relate to a single instance of another entity. A relationship has two minima, one for each of the related entities. Minima are usually specified as zero or one. On ER diagrams, minima are shown after maxima in parentheses. Ex: M(1) or M(0).

Relationship minimum

The following terms describe Entity-Has-Attribute minima: ______ attribute — each entity instance has at least one attribute instance. ______ attribute — each entity instance can have zero attribute instances.

Required Optional

_____ entities are entities that have many common attributes and relationships. They become subtypes of a new supertype entity

Similar

Determine the minima Person-Marries-Person

Since a person can be unmarried, Person-Marries-Person is zero-zero.

The following terms describe Entity-Has-Attribute maxima: _____ attribute — each entity instance has at most one attribute instance. _____ attribute — each entity instance can have many attribute instances. ______ attribute — each attribute instance describes at most one entity instance.

Singular Plural Unique Unique attributes are not the same as singular attributes. Ex: FullName is singular, because each employee has one name. FullName is not unique, because each name can describe several employees.

Can exist without any dependency on any other relationship. Typically, these entities are ones that do not have any foreign keys. These tables are the ones that you would typically create first since they don't depend on other entities. Other entities will typically depend on them. There are times where the foreign key is also the primary key component in the related entity which creates a ____ relationship.

Strong entity

Similar Entity Example

Student, Faculty, and Administrator are similar entities with common attributes FullName, SocialSecurityNumber, and EmailAddress. Common attributes move to a new supertype entity called Person. Subtype-specific attributes remain attributes of each subtype.

______ are common in informal communications. To avoid confusion, one official name is selected for each entity, relationship, and attribute. Other names are documented in the glossary as this.

Synonyms

T OR F? Relationships names have the form Entity-Verb-Entity, such as Division-Contains-Department. When the related entities are obvious, in ER diagrams or informal conversation, Verb is sufficient and entity names can be omitted

T

T or F? Entity names are a singular noun. Ex: Employee rather than Employees

T

Dependent entities example

Task depends on Project. The dependency relationship is an arrow from the dependent entity to the master entity. Hierarchies of dependent entities are common. A dependent entity can depend on multiple master entities.

Boyce Codd NF example

The Registration table lists student registration for courses by term. RegistrationCode is a unique column. (StudentID, CourseNumber, Term) is a composite unique column. CourseName and Credit depend on CourseNumber, which is not unique. Registration is not in Boyce-Codd normal form. Redundancy is eliminated by removing CourseName and Credit. RegistrationNew is in Boyce-Codd normal form. CourseNumber, CourseName, and Credit are tracked in a new Course table. All dependencies in Course are on a unique column. Course is in Boyce-Codd normal form.

Synonym Glossary Example

The description states the meaning of each entity, relationship, or attribute in complete sentences. The description begins with the name and includes examples and counterexamples to illustrate usage. Glossary Entity Name: Passenger Synonyms: Traveler, Customer Description: A passenger is any person who occupies a seat on a flight. Passengers include both paid and unpaid seat occupants, but excludes flight officers, flight attendants, and in-cabin pets.

______ is a modeling standard intended for software development

Unified Modeling Language, or UML, is commonly used for depicting software requirements. Since software data structures are similar to database structures, UML can be used for ER models and diagrams.

Identification of entities, relationships, and attributes precedes documentation. Always Usually Never

Usually, database designers identify entities, relationships, and attributes before documentation. However, the steps are iterative. Additional identification often takes place after names, synonyms, and descriptions are documented.

Supertype and subtype entities example

Vehicle supertype has ElectricVehicle and GasVehicle subtypes. Number of passengers and number of wheels apply to both electric and gas vehicles. Battery capacity applies to electric vehicles only. Number of cylinders applies to gas vehicles only.

There are instances where the primary key of one entity only appears as the foreign key in the related entity, and in those cases, we have a ____ relationship.Ex. The Rating entity will have dependencies on both the user and the movie. A rating cannot exist without having the user that submitted the rating. A rating cannot exist without being connected to a movie either

Weak Entity

An _______ key is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.

artificial

An ______ is a descriptive property of an entity.

attribute

______ refers to maxima and minima of relationships and attributes

cardinality

Entity A ______ ____ entity B if instances of A exist only in relation to instances of B.

depends on Ex: Task depends on Project if all tasks must be part of a project. A project must exist before project tasks are created. When a project is deleted, all project tasks must also be deleted.

An ____ is a person, place, product, concept, or activity.

entity

In entity-relationship modeling, a type is a set: An ____ type is a set of things. Ex: All employees in a company. A _____ type is a statement about entity types. Ex: Employee-Manages-Department. An ____ type is a set of values. Ex: All employee salaries.

entity relationship attribute

An ____ _____ ______ commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. Entities are drawn as rectangles. Relationships are drawn as lines connecting rectangles. Attributes appear as additional text within an entity rectangle, under the entity name.

entity-relationship diagram

An _____ ______ model is a high-level representation of data requirements, ignoring implementation details. It guides implementation in a particular database system, such as MySQL

entity-relationship model

Dependence and depend on have different meanings in entity-relationship and relational models. In the entity-relationship model, dependence means each instance of a dependent entity always relates to an instance of another entity. In the relational model, dependence means each value of a column relates to at most one value of another column. Formally, entity-relationship dependence is called _____ dependence and relational dependence is called _______ dependence

existence, functional

A ______, also known as a data dictionary or repository, documents additional detail in text format. It includes names, synonyms, and descriptions of entities, relationships, and attributes.

glossary

An ______ entity does not depend on any other entity.

independent Ex: In a university database, students and departments can be created without reference to any other entity, so Student and Department are independent entities.

A dependent entity depends on another entity, called the ______ entity

master The dependent and master entities are related by a dependency relationship

Each employee has at most three telephone numbers in the database, and each telephone number belongs to at most one person. The Employee-Has-Telephone maxima are ____ to _____

one-three

A _____of a supertype entity is a group of mutually exclusive subtype entities. Each partition corresponds to a _____ ______ of the supertype entity.

partition, partition attribute

A _______ is a statement about two entities.

relationship

primary keys should be: _____ Primary key values should not change. Unstable primary keys cause database management problems. When a primary key value changes, statements that specify the old value must also change, and the new primary key value must cascade to foreign keys. _____ Primary key values should be easy to type and store. Small values are easy to specify in an SQL WHERE clause and speed up query processing. Ex: A 2-byte integer is easier to type and faster to process than a 15-byte character string. ______. Primary keys should not contain descriptive information. Descriptive information occasionally changes, so primary keys containing descriptive information are unstable.

stable, simple, meaningless and also unique

A _____ entity is a subset of another entity type, called the ______ entity.

subtype, supertype Ex: Managers are a subset of employees, so Manager is a subtype entity of the Employee supertype entity. On ER diagrams, subtype entities are drawn within the supertype.

Relationships are read in the direction of the _____

verb "Schedules" starts at "airline" on the ER diagram, so "airline" is read first.

A diamond can also mean ___ ____ ____in ER Diagrams

zero or one