CIS275 - Chapter 3: Database Design Flashcards
An _____ is a high-level representation of data requirements, ignoring implementation details.
entity-relationship model
An entity-relationship model guides implementation in a particular database system, such as MySQL.
Example:
- In an airline reservation system, Passenger and Booking are entities.
- Holds is a relationship between Passenger and Booking.
- PassengerNumber, PassengerName, BookingCode, BookingCost are attributes.
An ___ is a person, place, product, concept, or activity.
entity
A relationship is a statement about two entities.
relationship
An ______ is a descriptive property of an entity.
attribute
Attribute is used in both entity-relationship and relational models. In the relational model, attribute is a formal term for column. Since entity-relationship attributes typically become relational columns, the meaning of attribute is similar in both models.
An _____ diagram is a schematic picture of entities, relationships, and attributes.
entity-relationship diagram
ER diagramEntities are drawn as rectangles. Relationships are drawn as lines connecting rectangles. Attributes appear as additional text within an entity rectangle, under the entity name.
Entities and relationships always appear in ER diagrams. Attributes are optional and only appear when additional detail is needed.
A ____, also known as a _____ or _____, documents additional detail in text format.
glossary
also known as a data dictionary or repository
A glossary includes names, synonyms, and descriptions of entities, relationships, and attributes.
The ER diagram and glossary are complementary and, together, completely describe an entity-relationship model.
An _____ is a set of things. Ex: All employees in a company.
entity type
Entity, relationship, and attribute types usually become tables, foreign keys, and columns, respectively.
A _____ is a statement about entity types. Ex: Employee-Manages-Department.
relationship type
Entity, relationship, and attribute types usually become tables, foreign keys, and columns, respectively.
An _____ is a set of values. Ex: All employee salaries.
attribute type
Entity, relationship, and attribute types usually become tables, foreign keys, and columns, respectively.
An _____ is an individual thing. Ex: The employee Sam Snead.
entity instance
Entity, relationship, and attribute instances usually become rows, foreign key values, and column values, respectively
A _____ is a statement about entity instances. Ex: “Maria Rodriguez manages Sales.”
relationship instance
Entity, relationship, and attribute instances usually become rows, foreign key values, and column values, respectively
An _____ is an individual value. Ex: The salary $35,000.
attribute instance
Entity, relationship, and attribute instances usually become rows, foreign key values, and column values, respectively
Complex databases are developed in three phases:
- Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details.
Analysis is particularly important for complex databases with many users when documenting requirements is challenging. For small databases with just a few tables and users, analysis is less important and often omitted.
- Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system.
Analysis and logical design steps are summarized in the table below. Although these steps are presented in sequence, in practice execution is not always sequential. Often an early step is revisited after a later step is completed.
- Physical design adds indexes and specifies how tables are organized on storage media.
Physical design is dependent on specific index and table structures, which vary greatly across relational databases. Physical design is discussed elsewhere in this material.
Discover relationships
- Flight-ArrivesAt-Airport, Flight-DepartsFrom-Airport, and Passenger-Makes-Booking are relationships.
- Passenger can be determined from booking. Replace Flight-Has-Passenger with Flight-Includes-Booking.
- Further interviews determine the database does not track parts. Part-ShippedTo-Airport is not a relationship.
Discover entities.
- In an interview with a database user, flight, airport, aircraft, passenger, and booking are entities.
- Airplane is another word for aircraft. Traveler is another word for passenger.
- Usually, address and credit card have many details and are entities.
- Further interviews determine the database does not track parts. Part is not an entity.
Discover attributes
- Airport code, total cost, and name are attributes of Airport, Booking, and Passenger.
- Mileage plan number is an attribute of Passenger or, if additional mileage plan information is tracked, a separate entity.
Attribute names have the form _____, such as EmployeeFirstName:
EntityQualifierType
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.
In entity-relationship modeling, _____ refers to maxima and minima of relationships and attributes.
cardinality
The greatest number of instances of one entity that can relate to a single instance of another entity.
Relationship maximum
_____ is the least number of instances of one entity that can relate to a single instance of another entity.
Relationship minimum
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).
Entities have an implicit relationship with their attributes, called _____.
Entity-Has-Attribute
This relationship, like any other, has maxima and minima.
The following terms describe Entity-Has-Attribute maxima:
_____— each entity instance has at most one attribute instance.
Singular attribute
The following terms describe Entity-Has-Attribute maxima:
_____ — each entity instance can have many attribute instances.
Plural attribute
The following terms describe Entity-Has-Attribute maxima:
_____ — each attribute instance describes at most one entity instance.
Unique attribute
The following terms describe Entity-Has-Attribute minima:
_____ — each entity instance has at least one attribute instance.
Required attribute
The following terms describe Entity-Has-Attribute minima:
_____— each entity instance can have zero attribute instances.
Optional attribute
A _____ depends on another entity, called the _____.
dependent entity
master entity
The dependent and master entities are related by a _____.
dependency relationship
Ex: Task-BelongsTo-Project is a dependency relationship. On ER diagrams, the dependency relationship is drawn as an arrow pointing to the master entity.
An _____ does not depend on any other entity.
independent entity
Ex: In a university database, students and departments can be created without reference to any other entity, so Student and Department are independent entities.
In the entity-relationship model, dependence means each instance of a dependent entity always relates to an instance of another entity.
Entity-relationship dependence is called _____.
existence dependence
In the relational model, dependence means each value of a column relates to at most one value of another column.
Relational dependence is called _____.
functional dependence
All exams belong to a course. A course must exist before course exams are created. When a course is deleted, course exams are also deleted. Courses are always part of an academic department. We set up academic departments in the database before we add courses.
UML
Unified Modeling Language
Expected:
Orchard is independent
Wholesaler is independent
Order depends on Orchard and Wholesaler
Deleting an Order, or deleting a Wholesaler, does not cause an Orchard to be deleted, so Orchard is independent.
Deleting an Order, or deleting an Orchard, does not cause a Wholesaler to be deleted, so Wholesaler is independent.
An Order is cancelled when either the Orchard or the Wholesaler goes out of business, so Order is dependent on both Orchard and Wholesaler.
Band is independent
Arena is independent
Concert depends on Band and Arena
Deleting an Arena, or deleting a Concert, does not delete the Band, so Band is independent.
Deleting a Band, or deleting a Concert, does not delete the Arena, so Arena is independent.
If a band breaks up, the rest of the concerts on its tour are cancelled. If an arena is no longer available, the concerts scheduled for that Arena are cancelled. So, concert is dependent on both Arena and Band, because if the Arena or Band of a concert is deleted, the Concert is deleted too.
OfficeBuilding is independent
OfficeSuite depends on OfficeBuilding
MeetingRoom depends on OfficeSuite
Deleting a MeetingRoom or OfficeSuite does not cause the building to be deleted, so OfficeBuilding is independent.
Office suites of a building are deleted when the building is destroyed, so OfficeSuite is dependent on OfficeBuilding.
Meeting rooms of an office suite are deleted when the office suite is eliminated, so MeetingRoom is dependent on OfficeSuite.
SchoolDistrict is independent
SchoolBoard depends on SchoolDistrict
Person is independent
Deleting a SchoolBoard, or deleting a Person does not cause the SchoolDistrict to be deleted, so SchoolDistrict is independent.
A SchoolBoard is deleted when the SchoolDistrict is deleted, so SchoolBoard depends on SchoolDistrict.
Deleting a SchoolDistrict, or deleting a SchoolBoard, does not cause the Person to be deleted, so Person is independent. (A Person may move to another SchoolBoard.)
TrackMeet is independent
Event depends on TrackMeet
Heat depends on Event
Deleting an Event, or deleting a Heat, does not cause the TrackMeet to be deleted, so TrackMeet is independent.
Events of a TrackMeet are deleted when the TrackMeet is deleted, so Event is dependent on TrackMeet.
Heats of an Event are deleted when the Event is deleted, so Heat is dependent on Event.
Player is independent
Team is independent
Contract depends on Player and Team
Deleting a Contract, or deleting a Team, does not delete the Player, so Player is independent. (The Player may move to another Team.)
Deleting a Contract, or deleting a Player, does not delete the Team, so Team is independent. A Contract is deleted when a Player leaves a Team, or when the Team is disbanded, so Contract is dependent on both Player and Team.
- 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.
The dependency relationship from subtype to supertype is called an _____.
IsA relationship
Ex: Manager-IsAn-Employee.
- Subtype entities have an IsA dependency relationship to the supertype.
- IsA relationships are assumed and usually not shown in an ER diagram.
_____ are entities that have many common attributes and relationships.
Similar entities
- 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.
An entity with many optional attributes often becomes a supertype entity. Optional attributes move to new subtype entities. Required attributes remain with the supertype entity.
- Vehicle has required attribute ManufacturerCode and optional attributes BatteryCapacity and ChargingType.
- Optional attributes become the new subtype entity ElectricVehicle.
A _____ of a supertype entity is a group of mutually exclusive subtype entities.
partition
Subtype entities in a partition cannot share common instances.
Ex: Undergraduate and graduate students are mutually exclusive subsets of students. UndergraduateStudent and GraduateStudent partition the supertype entity Student.
- 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.
Creating supertype and subtype entities is the last of four analysis steps:
- Discover entities, relationships, and attributes
- Determine cardinality
- Distinguish independent and dependent entities
- Create supertype and subtype entities
PublisherName is an attribute of a supertype entity
Format is an attribute of a subtype entity
Each resource has a publisher name, so PublisherName is an attribute of the supertype entity Resource.
Only eBooks have a format, so Format is an attribute of the subtype entity EBook.
- PartName is unique and required, but also complex and meaningful. PartName is not a good primary key.
- PartCode and PartSize are not individually unique; however, the composite (PartCode, PartSize) is unique, as well as required and simple.
- PartSize is meaningful and might change due to data entry errors or part redesign. (PartCode, PartSize) is not a good primary key.
- The database designer creates a new PartNumber column for internal database use. PartNumber is unique, required, stable, simple, and meaningless, and therefore a good primary key.
- 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.
- 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.
- Task depends on Project. ProjectCode is the primary key of Project.
- Task’s primary key includes Project’s primary key and an additional column, necessary for uniqueness.
- ProjectCode in Task table is also a foreign key.
- Booking depends on Flight and Passenger.
- Booking’s primary key includes Flight and Passenger’s primary keys. In this case, no additional columns are necessary for uniqueness.
- FlightCode and PassengerNumber in Booking primary key are also foreign keys.
Expected: Required
CollegeName could not be a good primary key.
CollegeName is:
Required. Every college must have a name. However, CollegeName is: Not unique. More than one college can have the same name. Not stable. A college may be renamed. Not simple. A college can have a name that is too long to type easily in a WHERE clause. Not meaningless. A college name could reflect which statewide system it is part of or what its specialty is. Therefore, CollegeName could not be a good primary key.
1
2
Expected: (DivisionNumber, DepartmentID)
Department is dependent on Division, so the primary key of Department is a composite that includes the primary key of Division.
DepartmentName can’t be used in primary key because DepartmentName is not stable. (A Department may be renamed.)
The composite (DivisionNumber, DepartmentID) uniquely identifies a department and is a good primary key. No additional information is needed in the primary key.
Expected: EmployeeID
Manager is a subtype of Employee. EmployeeID is a sufficient primary key for the Manager table, because it uniquely identifies a row in the Manager table: for every row in the Manager table there is a corresponding row in the Employee table. There is no need for additional information in the primary key of the Employee table.
Expected: (ShipperID, CustomerID, ShipmentNumber)
Shipment is dependent on both Shipper and Customer. Therefore, the primary key of Shipment must be a composite that includes ShipperID and CustomerID.
Because a shipper accepts many shipments from a customer, the primary key of Shipment must include some additional distinguishing information.
ShipmentDate is not sufficient because a Customer might deliver multiple Shipments to a Shipper on the same date.
So, (ShipperID, CustomerID, ShipmentNumber) would be the primary key for Shipment.
- 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.
- 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.
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.
The new table is dependent on the related tables, so primary key cascade and foreign key restrict rules are specified.
The new table name consists of the related table names with an optional qualifier in between. The qualifier is derived from the relationship name and clarifies the meaning of the table.
In the figure below, the many-many relationship Airline-Schedules-Flight becomes the new table AirlineFlight. The primary keys from Airline and Flight become foreign keys in AirlineFlight, and the composite key (AirlineCode, FlightNumber) becomes AirlineFlight’s primary key.
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.
The new table is dependent on the related tables, so primary key cascade and foreign key restrict rules are specified.
The new table name consists of the related table names with an optional qualifier in between. The qualifier is derived from the relationship name and clarifies the meaning of the table.
In the figure below, the many-many relationship Airline-Schedules-Flight becomes the new table AirlineFlight. The primary keys from Airline and Flight become foreign keys in AirlineFlight, and the composite key (AirlineCode, FlightNumber) becomes AirlineFlight’s primary key.
Expected: Campus, UniversityID, No
The foreign key goes in the Campus table, the ‘many’ side of the relationship.
The foreign key refers to UniversityID, the primary key on the ‘one’ side. NULLs are not allowed in the UniversityID column of the Campus table, because the minimum on the University side is 1.
Expected: Citizen, StateCode, Yes
The foreign key goes in the Citizen table, the ‘many’ side of the relationship. The foreign key refers to StateCode, the primary key on the ‘one’ side. NULLs are allowed in the StateCode column of the Citizen table, because the minimum on the State side is 0.
Expected: Book, CardNumber, Yes
The foreign key goes in the Book table, the ‘many’ side of the relationship. The foreign key refers to CardNumber, the primary key on the ‘one’ side. NULLs are allowed in the CardNumber column of the Book table, because the minimum on the User side is 0.
Expected: CongressionalDistrict SocialSecurityNumber
No: the minimum on the Representative side is 1.
The foreign key usually goes in the table with fewer rows. The CongressionalDistrict table will usually have fewer rows than the Representative table, so the foreign key goes in the CongressionalDistrict table.
The foreign key in the CongressionalDistrict table refers to SocialSecurityNumber, the primary key of the other table.
NULLs are not allowed in the SocialSecurityNumber column of the CongressionalDistrict table, because the minimum on the Representative side is 1.
Expected:
State
SocialSecurityNumber
Yes: the minimum on the Person side is 0.
The foreign key usually goes in the table with fewer rows. The State table will usually have fewer rows than the Person table, so the foreign key goes in the State table.
The foreign key in the State table refers to SocialSecurityNumber, the primary key of the other table.
NULLs are allowed in the SocialSecurityNumber column of the State table, because the minimum on the Person side is 0.
Expected: DockSlip Registration Yes: the minimum on the Sailboat side is 0. The foreign key usually goes in the table with fewer rows. The DockSlip table will usually have fewer rows than the Sailboat table, so the foreign key goes in the DockSlip table.
The foreign key in the DockSlip table refers to Registration, the primary key of the other table.
NULLs are allowed in the Registration column of the DockSlip table, because the minimum on the Sailboat side is 0.
Expected: Country FlagID
No: the minimum on the Flag side is 1.
The foreign key usually goes in the table with fewer rows. The Country table will usually have fewer rows than the Flag table, so the foreign key goes in the Country table.
The foreign key in the Country table refers to FlagID, the primary key of the other table.
NULLs are not allowed in the FlagID column of the Country table, because the minimum on the Flag side is 1.
Expected: Mailbox ApartmentNumber
Yes: the minimum on the Apartment side is 0.
The foreign key usually goes in the table with fewer rows. The Mailbox table will usually have fewer rows than the Apartment table, so the foreign key goes in the Mailbox table.
The foreign key in the Mailbox table refers to ApartmentNumber, the primary key of the other table.
NULLs are allowed in the ApartmentNumber column of the Mailbox table, because the minimum on the Apartment side is 0.
Expected: both EmployeeID and ProjectID (ProjectID, EmployeeID) and (EmployeeID, ProjectID)
The new table contains foreign keys referring to the primary keys of the related tables, so both EmployeeID and ProjectID.
The primary key of the new table is a composite of the two foreign keys. The order within the composite does not matter, so (EmployeeID, ProjectID) or (ProjectID, EmployeeID).
both PartID and ModelNumber
(ModelNumber, PartID) and (PartID, ModelNumber)
The new table contains foreign keys referring to the primary keys of the related tables, so both PartID and ModelNumber.
The primary key of the new table is a composite of the two foreign keys. The order within the composite does not matter, so (PartID, ModelNumber) or (ModelNumber, PartID).
- 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.
- The glossary specifies data types CHAR(4) and VARCHAR(30) for attributed types Code and Name, respectively.
- In the Airport entity, two attributes have attribute type Code and two have attribute type Name.
- In an SQL CREATE TABLE statement, Attribute type Code is implemented as CHAR(4). Attribute type Name is implemented as VARCHAR(30).
- 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.
_____ means intentionally introducing redundancy by merging tables.
Denormalization
Denormalization eliminates join queries and therefore improves query performance. Denormalization results in first and second normal form tables and should be applied selectively and cautiously.
In the figure below, the Booking, Passenger, and Fare tables are denormalized into a single Booking table. The red highlight indicates redundancy in the denormalized table.
