MIS 3320 - Exam 2 Review - Ch 5

What is a Surrogate Key?

An auto-generated integer defined as the Primary Key (Building ID).

What is an Entity?

An entity is a noun (person, place, event, object) that is within our scope and that needs to be remembered and stored. Entities are basically tables in the database.

Why did we talk about "Things" in our system?

"Things" are entities in our system that need to be recorded and remembered.

Know the ERD Rules & Tips

- An entity with only one instance should not be modeled. An entity should represent one instance/record in the table - A good PK name should match the entity name, should be unqiue, must have a value, and doesn't change over time - In Analysis, only resolve a many-to-many when there is a non-key attribute - Be careful with mandatory one-to-one relationships (may be same) - Order Report/Form is usually 3-4 entities

Know how to model an Order

1. Remove multi-valued attribute 2. Make a separate entity 3. Move primary key to entity 4. Name is a combined name of original name and multi-valued name 5. Make a concatenated primary key with both original PK and multi-valued key ???

What are the characteristics of a good Primary Key?

1. Unique - no duplicate values 2. Required - cannot be null 3. Static - does not change ("build name" is poor, it changes)

What are attributes?

A characteristic about an entity that is within our scope and that we need to remember.

What is an ERD? Know the various ERD symbols/notation.

An Entity Relationship Diagram (ERD) is a data modeling technique. It shows nouns and "things" within our scope that need to be remembered and stored.

What does a relationship represent in an ERD?

An association between entities. It represents a "NEED TO KNOW" the relationship between the instances of each entity. Relationships must have a name. Occurs in 2 directions.

What is a Primary Key?

An attribute (or group of attributes) that uniquely identifies an instance. Every entity must have one.

What is an Atomic Attribute?

An attribute that holds a single value (good)

What is a Derived Attribute?

An attribute that holds info that results from a calculation (bad)

What is a Composite Attribute?

An attribute that holds multiple values/info, more than one piece of information (bad)

What is an Instance?

An instance is an occurrrence of our entity

What is meant by Cardinality?

Cardinalities represent the maximum and minimum of entities.

Know the Degrees of Relationships

Degrees of Relationships refer to how many entities participate in the relationship - Unary: one entity (self-referencing) - Binary: relationship is two entities - Ternary: relationship is three entities

What are issues concerning composite and derived attributes?

Don't want attributes to hold too much information. You should break down information.

What will an entity eventually be in implementation?

In implementation, ERDs are used by programmers to know where to read, update, and delete the data from the database.

Are instances represented via a symbol on an ERD diagram? What will an instance eventually be in implementation?

Instances are not represented on an ERD diagram (no symbol), but we must understand instances as they'll play a role in our design.

What is a Candidate Key?

It is multiple potential Primary Keys. Multiple unique attributes may exist, so pick the attribute that is used most often and define it as the primary key.

Know how to resolve a Multi-Valued Attribute

Multi-Valued Attributes are bad. For example, majors is a multi-valued attribute. Move the MV attribute to become a new entity, so there will be a new entity called StudentMajor. Concatenated PK will be StudentID and Major: uniqueness is across both.

What is the significance of nouns & verbs in data modeling & process modeling?

Nouns represent entities or "things" (person, place, event, object) within our scope and that we need to remember. This can consist of roles, locations, organizational units, events, tangible things. Verbs are used to describe relationships or associations.

What determines the relationship type?

Relationship Type is determined by the maximum cardinalities (one-to-many)

What is an Associative Entity?

Represents an association between two fundamental entities that has a potential many-to-many cardinality or that holds some attributes. Need one when you have a non-keya ttribute that doesn't belond in either of the 2 entities that connected.

What is the PK of an associative and what type of key is this?

The Associative Entity's Primary Key is a combination of the entitites that it's connected to (concatenated PK). Yes, the uniqueness is across the entire group of attributes in the PK.

Who initially designs the ERD in the analysis phase? How will this be used in the design phase?

The Systems Analyst and Business Analyst initially design the ERD in the analysis phase to model database requirements. In the Design Phase, the Database Business Analyst (DBA) will use the ERD to build a database.

How do we deal with an associative entity that may need to hold a historical account?

Try adding a date field to a concatenated PK for the associative entity (maybe just entity). Date-related fields include: year, month, semester, quarter, date, date timestamp.