Test 2 - Database Management

SDLC

Systems Development Life Cycle

Five stages of the SDLC

System definition, requirements analysis, component design, implementation, and system maintenance.

Purpose of a data model

To document data requirements and show how data are organized and related in a database.

Original E-R model creator

Peter Chen in 1976.

Main components of the E-R model

Entities, attributes, identifiers, and relationships.

Entity

A thing or object in the real world that users want to track.

Entity class

A collection of entities of the same type (e.g., all CUSTOMER records).

Entity instance

A single occurrence of an entity (e.g., Customer #12345).

Attribute

A characteristic that describes an entity, such as Name or HireDate.

Identifier

An attribute (or set of attributes) that uniquely identifies each entity instance.

Types of identifiers

Unique and non-unique identifiers.

Relationship in an E-R diagram

An association among two or more entities.

Types of binary relationships

1:1 (one-to-one), 1:N (one-to-many), and N:M (many-to-many).

Cardinality

The number of entity instances that may participate in a relationship instance.

Minimum cardinality

Indicates whether participation in a relationship is optional (0) or mandatory (1).

Notation using 'crow's feet'

Information Engineering (IE) notation.

1:N relationship representation in Crow's Foot

A single line from the "1" side to a crow's foot symbol on the "many" side.

Weak entity

An entity that cannot exist without being associated with another entity (a strong entity).

Strong entity

An entity that can exist independently of other entities.

ID-dependent weak entity

A weak entity whose identifier includes the identifier of its parent entity.

Identifying relationship

A solid line linking a strong and weak entity when the weak entity is ID-dependent.

Non-identifying relationship

A dashed line used when the relationship is not ID-dependent.

Associative (association) entity

An entity created to represent a many-to-many relationship and store attributes about that relationship.

Subtype entity

A special case of a supertype entity; shares the same identifier and represents a subset of instances.

Connection between subtypes and supertypes

An IS-A relationship.

Discriminator

An attribute in a supertype that indicates which subtype applies.

Recursive (unary) relationship

An entity related to itself (e.g., EMPLOYEE supervises EMPLOYEE).

Deliverable of data modeling

A validated E-R diagram showing entities, attributes, and relationships.

Validation of a data model

Show it to users and compare against use cases and requirements.

Database design

The process of transforming a data model into a relational database structure.

Three levels of database design

Conceptual design, logical design, and physical design.

Design level covered in Chapter 5

Logical design.

Normalization

Organizing data to reduce redundancy and avoid update anomalies.

Three most common normal forms

1NF (eliminate repeating groups), 2NF (eliminate partial dependencies), 3NF (eliminate transitive dependencies).

Denormalization

Combining tables to improve performance at the expense of redundancy.

When is denormalization used?

When faster query performance is needed and redundancy is acceptable.

Weak entity representation in relational design

Include the primary key of the parent entity as part of its own primary key.

1:1 relationship representation

By placing the primary key of one table as a foreign key in the other; either side may hold the foreign key.

1:N relationship representation

By placing the primary key of the '1' side as a foreign key in the 'many' side.

N:M relationship representation

By creating a new intersection table that contains the primary keys of each related table as a composite primary key.

Intersection table

A table created to represent an N:M relationship and link two tables together.

Referential integrity

A rule that ensures foreign key values match existing primary key values in the related table.

Recursive relationship in relational design

A relationship where a table references itself through a foreign key (e.g., Employee → ManagerID).

Determining foreign key side

The maximum cardinality of the relationship.

Composite key

A primary key made up of two or more attributes.

Importance of normalization

It reduces data redundancy and ensures data integrity and consistency.

Functional dependency

A relationship where one attribute depends on another for its value.

Purpose of specifying column properties

To define data type, size, null status, default values, and constraints for each column.

Foreign key

An attribute in one table that references a primary key in another table.

Surrogate key

A unique identifier added to a table when no natural key is suitable.

Data anomaly

An error that occurs when data are not properly normalized, causing update, insert, or delete problems.

1NF requirement

Each cell in a table contains a single atomic value and each record is unique.

2NF requirement

All non-key attributes must depend on the whole primary key, not just part of it.

3NF requirement

No non-key attribute depends on another non-key attribute (no transitive dependencies).

Trade-off in denormalization

Reduced complexity and faster queries versus increased redundancy and update anomalies.

Steps for transforming a data model into a design

Create tables for each entity, add primary keys, represent relationships with foreign keys or intersection tables, and define column properties.

Crow’s Foot Notation Chart

(Ch.4) What is a computer-based information system?

An information system that uses computer technology to collect, store, process, and distribute information; its five components are hardware, software, data, procedures, and people.

(Ch.4) What is a business process?

A sequence of activities that convert inputs into outputs to accomplish a specific organizational goal.

(Ch.4) How do information systems support business processes?

They automate manual tasks, coordinate data flows, provide timely information, and improve decision making across process steps.

(Ch.4) Describe information systems and include processes.

An information system combines technology, data, and procedures to capture, process, and produce information that supports organizational processes.

(Ch.4) What is systems analysis and design?

The process of creating and maintaining information systems through studying business needs, modeling data and processes, and designing technical solutions.

(Ch.4) What is the Systems Development Life Cycle (SDLC) model?

A framework of steps for building information systems: system definition, requirements analysis, component design, implementation, and system maintenance.

(Ch.4) What is a user requirements document (URD)?

An approved list of user needs that defines what the system must do; produced during the requirements-analysis stage of the SDLC.

(Ch.4) What are the three stages in the process of developing database systems?

Requirements analysis, component (database) design, and implementation.

(Ch.4) What is a data model and what is its purpose?

A logical description of data structures and relationships used to capture business data requirements.

(Ch.4) What is a prototype and what is its purpose?

A working sample of part of a system used to verify requirements and get user feedback before final implementation.

(Ch.4) What is a use case and what is its purpose?

A description of how a user interacts with the system to accomplish a specific goal; used to identify needed data and processes.

(Ch.4) What is a data constraint?

A rule that limits acceptable values in a database field to ensure data accuracy and integrity.

(Ch.4) Give an example of a business rule that would need to be documented in a database project.

Example: “Each customer must have exactly one assigned sales representative.”

(Ch.4) Define an entity and give an example.

An object or concept about which data are stored, such as EMPLOYEE, CUSTOMER, or PRODUCT.

(Ch.4) Explain the difference between an entity class and an entity instance.

An entity class is a set of entities of the same type (e.g., all CUSTOMERS); an instance is one specific occurrence (Customer #105).

(Ch.4) Define the term attribute and give examples.

An attribute is a property describing an entity, such as Name, Address, or HireDate.

(Ch.4) Define the term identifier and indicate which attribute is defined in your answer to 4.18.

An identifier uniquely distinguishes each entity instance; e.g., CustomerID uniquely identifies each CUSTOMER.

(Ch.4) Define the term composite identifier and give an example.

A primary key consisting of two or more attributes, such as (StudentID, CourseID) in an ENROLLMENT table.

(Ch.4) Define the term relationship and give an example.

An association between two entities; e.g., STUDENT enrolls in COURSE.

(Ch.4) Explain the difference between a relationship class and a relationship instance.

A relationship class is the general association type (e.g., Enrolls In), while a relationship instance is one specific occurrence (Student 101 → Course MISY3330).

(Ch.4) Define the degree of relationship and give an example.

The number of entity classes involved: binary (two), ternary (three), etc. Example: EMPLOYEE works on PROJECT is binary.

(Ch.4) List and give an example of the three types of binary relationships.

1:1 – Manager → Office; 1:N – Department → Employees; N:M – Student ↔ Course.

(Ch.4) Define maximum cardinality and minimum cardinality.

Maximum cardinality = the most instances that can participate; Minimum cardinality = whether participation is optional (0) or mandatory (1).

(Ch.4) Define the term ID-dependent entity and give an example.

An entity whose primary key includes the primary key of another entity; e.g., DEPENDENT with key (EmployeeID, DependentName).

(Ch.4) Define the term weak entity and give an example of a non-ID-dependent weak entity.

An entity that cannot exist without its parent; a non-ID-dependent example is ORDER_ITEM linked to ORDER by a foreign key.

(Ch.4) Explain the ambiguity in the definition of a weak entity.

The term can refer to any entity that depends on another for existence, not only those whose identifiers include the parent’s key.

(Ch.4) Define the term associative entity and give an example.

An entity created from a many-to-many relationship to hold attributes about that relationship; e.g., ENROLLMENT between STUDENT and COURSE.

(Ch.4) Define the terms supertype, subtype, and discriminator.

A supertype is a general entity; subtypes are specialized forms; a discriminator attribute in the supertype determines which subtype applies.

(Ch.4) What is an exclusive subtype relationship?

Each supertype instance can belong to only one subtype (e.g., VEHICLE is either CAR or TRUCK but not both).

(Ch.4) What is an inclusive subtype relationship?

A supertype instance may belong to multiple subtypes (e.g., EMPLOYEE can be both INSTRUCTOR and ADVISOR).

(Ch.4) Give an example of a recursive relationship.

EMPLOYEE supervises EMPLOYEE or PART is made-of PART.

(Ch.4) Give an example of a business rule.

Example: “Each order must have at least one line item.”

(Ch.4) Why is evaluating a data model important?

To verify that it accurately represents business rules and eliminates redundancy before implementation.

(Ch.4) Summarize one technique for evaluating a data model.

Show the model to end users for feedback or prototype forms/reports to confirm the data requirements are met.

(Ch.5) Explain how entities are transformed into tables.

Each entity becomes a separate table in the database; each attribute becomes a column, and the entity’s identifier becomes the primary key.

(Ch.5) Explain how attributes are transformed into columns.

Each attribute of an entity is represented as a column in its table. Column properties include data type, length, NULL status, default value, and constraints.

(Ch.5) Why is it necessary to apply the normalization process to the tables created?

Normalization removes redundancy and update anomalies and ensures data integrity by organizing data into well-structured tables.

(Ch.5) What is denormalization?

The process of combining tables to improve performance at the expense of redundancy.

(Ch.5) When is denormalization justified?

When faster query performance or simpler joins outweighs the cost of redundant data.

(Ch.5) Explain how the representation of weak entities differs from strong entities.

Weak entities include the primary key of their parent as part of their own primary key; strong entities have independent primary keys.

(Ch.5) Explain how supertype and subtype entities are transformed into tables.

The supertype and each subtype become separate tables; the subtype’s primary key is also the supertype’s primary key and includes a discriminator field.

(Ch.5) Explain how binary relationships are transformed into tables.

For 1:1 and 1:N, add a foreign key; for N:M, create an intersection table that holds the primary keys of both entities.

(Ch.5) List the three types of binary relationships and give an example of each.

1:1 – Employee ↔ ParkingSpace, 1:N – Department → Employees, N:M – Student ↔ Course.