Database Modeling (DM)

Database

Organized collection of logically related data

Data

Stored representations of meaningful objects and events

Structured Data

Includes numbers, text, and dates

Unstructured Data

Includes images, video, and documents

Information

Data processed to increase knowledge in the person using the data

Metadata

Data that describes the properties and context of user data

Program-data dependence

Disadvantage of file-based systems where all programs maintain metadata for each file they use

Duplication of data

Disadvantage of file-based systems where different systems/programs have separate copies of the same data

Limited data sharing

Disadvantage of file-based systems where there is no centralized control of data

Lengthy development times

Disadvantage of file-based systems where programmers must design their own file formats

Database Management System (DBMS)

Software used to define, create, maintain, and provide controlled access to the database

Define (in DBMS)

Specify data types, structures, and constraints for the data to be stored

Populate (in DBMS)

Process of entering/storing the data

Manipulate (in DBMS)

Includes functions such as querying, updating the database, and generating reports

Control (in DBMS)

Granting/restricting permissions and ensuring security

Enterprise data modeling

First step in database development, specifying scope, content, entity types, relationships, and business rules

Entity-Relationship (ER) Diagram

Most popular conceptual model used in database design, representing entities and relationships among them

Entity

Object in the user environment about which the organization wishes to maintain data

Strong entity type

Exists independently of other entity types

Weak entity type

Existence depends on some other entity types

Attribute

Property or characteristic of an entity or relationship type

Required attribute

Attribute that must have a value for every entity or relationship instance

Simple attribute

Attribute that cannot be broken down into smaller components

Composite attribute

Attribute with meaningful component parts

Single-valued attribute

Attribute with one meaningful value for a given entity or relationship instance

Multivalued attribute

Attribute that may take on more than one value for a given entity or relationship instance

Stored attribute

Attribute with a static value

Derived attribute

Attribute whose values can be calculated from related attribute values

Identifier attribute

Attribute that distinguishes instances of an entity type

Relationship

Association representing an interaction among instances of one or more entity types

Unary relationship

Relationship where an entity is related to itself

Binary relationship

Relationship between two different entity types

Ternary relationship

Relationship between three different entity types

Cardinality

Number of relationship instances that a given entity instance can participate in

Associative entity

Entity type that associates instances of one or more entity types and contains relationship-specific attributes

Ternary relationships

Should be converted to associative entities

Many-to-many relationships

Should be converted to associative entities

Entity-Relationship Diagram (ERD)

A visual representation of data using entities and their relationships

Associative entities

Entities created to resolve many-to-many or ternary relationships

What are some common categories of entities?

A. Person, Place, Object, Event, Concept

B. Color, Shape, Size, Texture, Weight.

C. Animal, Vegetable, Mineral, Bacteria, Virus.

D. Verb, Noun, Adjective, Adverb, Pronoun

A. Person, Place, Object, Event, Concept

Strong entity types

Entity types that have a primary key and are not dependent on another entity

Weak entity types

Entity types that do not have a primary key and depend on another entity

Attributes

Characteristics or properties of entities

Degree (entity types)

The number of entity types that participate in a relationship

Cardinality (entity instances)

The number of instances of one entity that are associated with the number of instances of another entity

Supertypes and Subtypes

A generic entity type with one or more specialized entity types

Attribute Inheritance

Subtype entities inherit values of all attributes of the supertype

Generalization

Defining a more general entity type from specialized entity types (BOTTOM-UP)

Specialization

Defining subtypes of a supertype and forming supertype/subtype relationships (TOP-DOWN)

What are Completeness Constraints?

A. Rules determining if an instance of a supertype must belong to at least one subtype

B. Rules determining if an instance of a supertype can belong to multiple subtypes

C. An attribute of the supertype determining the target subtype(s)

D. Describes data in terms of a specific data management technology for database implementation

Rules determining if an instance of a supertype must belong to at least one subtype

What are Disjointness Constraints?

A. Rules determining if an instance of a supertype must belong to at least one subtype

B. Rules determining if an instance of a supertype can belong to multiple subtypes

C. An attribute of the supertype determining the target subtype(s)

D. Describes data in terms of a specific data management technology for database implementation

Rules determining if an instance of a supertype can belong to multiple subtypes

What is a Subtype Discriminator?

A. Rules determining if an instance of a supertype must belong to at least one subtype

B. Rules determining if an instance of a supertype can belong to multiple subtypes

C. An attribute of the supertype determining the target subtype(s)

D. Describes data in terms of a specific data management technology for database implementation

An attribute of the supertype determining the target subtype(s)

What is a Logical Data Model/Schema?

A. Rules determining if an instance of a supertype must belong to at least one subtype

B. Rules determining if an instance of a supertype can belong to multiple subtypes

C. An attribute of the supertype determining the target subtype(s)

D. Describes data in terms of a specific data management technology for database implementation

Describes data in terms of a specific data management technology for database implementation

What is a Relation (in databases)?

A. A named, two-dimensional table of data

B. Special fields serving as unique identifiers in relations

C. An attribute of the supertype determining the target subtype(s)

D. Describes data in terms of a specific data management technology for database implementation

A named, two-dimensional table of data

What are Relational Keys?

A. Rules determining if an instance of a supertype must belong to at least one subtype.

B. Special fields serving as unique identifiers in relations

C. An attribute of the supertype determining the target subtype(s). D. Describes data in terms of a specific data management technology for database implementation

Special fields serving as unique identifiers in relations

Primary keys

Unique identifiers of relations guaranteeing row uniqueness

Foreign keys

Identifiers enabling a dependent relation to refer to its parent relation

What are Integrity Constraints?

A. Rules maintaining data accuracy and integrity in a database

B. One attribute's value determining another attribute's value

C. A unique identifier that may become the primary key

D. Ensures no multivalued attributes and atomic attribute values

Rules maintaining data accuracy and integrity in a database

What is Functional Dependency?

A. Rules maintaining data accuracy and integrity in a database

B. One attribute's value determining another attribute's value

C. A unique identifier that may become the primary key

D. Ensures no multivalued attributes and atomic attribute values

One attribute's value determining another attribute's value

What is a Candidate Key?

A. Rules maintaining data accuracy and integrity in a database

B. One attribute's value determining another attribute's value

C. A unique identifier that may become the primary key

D. Ensures no multivalued attributes and atomic attribute values

A unique identifier that may become the primary key

What is First Normal Form (1NF)?

A. Rules maintaining data accuracy and integrity in a database

B. One attribute's value determining another attribute's value

C. A unique identifier that may become the primary key

D. Ensures no multivalued attributes and atomic attribute values

Ensures no multivalued attributes and atomic attribute values

What is Second Normal Form (2NF)?

A. 1NF plus every non-key attribute is fully functionally dependent on the entire primary key

B. Rules maintaining data accuracy and integrity in a database

C. Ensures no multivalued attributes and atomic attribute values

D. 2NF plus no transitive dependencies on non-primary-key attributes

1NF plus every non-key attribute is fully functionally dependent on the entire primary key

What is Third Normal Form (3NF)?

A. 1NF plus every non-key attribute is fully functionally dependent on the entire primary key

B. Rules maintaining data accuracy and integrity in a database

C. Ensures no multivalued attributes and atomic attribute values

D. 2NF plus no transitive dependencies on non-primary-key attributes

2NF plus no transitive dependencies on non-primary-key attributes

Information is processed data (e.g., average age); information must be stored in a database management system (DBMS) for real-time reporting.
True
False

False

Data redundancy/duplication is a major shortcoming of a centralized database management system (DBMS).
True
False

False

Which of the following is a major phase in an agile software development approach?
1. Logical design
2. Planning
3. None of the answers
4. An Entity-Relationship Diagram (ERD)
5. Analysis

3. None of the answers

An entity should NOT be....
1. An object that we are trying to model
2. A user of the database system
3. An object that will have many instances in the database
4. An object that will be composed of multiple attributes

A user of the database system

If the existence of an entity depends on some other entity types, we call it an identifier attribute.
True
False

False

If an attribute cannot be broken down into smaller components, we call it a single-valued attribute.
True
False

False

A customer can place one or more orders--this is a one-to-many relationship; an associative entity is needed to convert a one-to-many relationship (between two entities) into two one-to-one relationships in an ERD.
True
False

False

1. What is a database?
A) A collection of unrelated data
B) A collection of logically related data
C) A random assortment of files
D) An unorganized set of records

B) A collection of logically related data (Correct Answer)

2. What is metadata?
A) Data about other data
B) The same as the data
C) A type of unstructured data
D) A program used for data analysis

A) Data about other data (Correct Answer)

3. Which of the following is a disadvantage of file-based systems?
A) Centralized data management
B) Minimal data redundancy
C) Limited data sharing
D) Automated file format design

C) Limited data sharing (Correct Answer)

4. What is the primary purpose of a Database Management System (DBMS)?
A) To maintain file-based systems
B) To define, create, maintain, and provide controlled access to databases
C) To store data in unstructured formats
D) To handle program-data dependence

B) To define, create, maintain, and provide controlled access to databases (Correct Answer)

5. Which of the following is a function of a DBMS?
A) Data deletion
B) Data manipulation
C) Data encoding
D) Data duplication

B) Data manipulation (Correct Answer)

6. What is the Systems Development Life Cycle (SDLC)?
A) A rapid prototyping approach
B) A detailed and well-planned development process
C) A method for managing file-based systems
D) A database query language

B) A detailed and well-planned development process (Correct Answer)

7. What does the internal schema represent in a database design?
A) User-specific views of the database
B) Overall organizational data structure
C) The underlying design and implementation
D) The data relationships and constraints

C) The underlying design and implementation

8. What is an ER Diagram (ERD) used for?
A) To model data as a collection of entities and relationships
B) To design file-based systems
C) To control data access
D) To encode data into binary format

A) To model data as a collection of entities and relationships (Correct Answer)

9. What type of entity is "Student" in a database?
A) An object
B) A person
C) A concept
D) An event

B) A person (Correct Answer)

10. What distinguishes a strong entity type from a weak entity type?
A) Strong entities are part of relationships, while weak entities are not
B) Strong entities exist independently, while weak entities depend on other entities
C) Weak entities have more attributes than strong entities
D) Strong entities have no attributes, while weak entities do

B) Strong entities exist independently, while weak entities depend on other entities (Correct Answer)

11. Which attribute type cannot be broken down into smaller components?
A) Composite attribute
B) Simple attribute
C) Multivalued attribute
D) Derived attribute

B) Simple attribute (Correct Answer)

12. What is a composite attribute?
A) An attribute with a single meaningful value
B) An attribute with multiple values
C) An attribute with meaningful component parts
D) An attribute that can be calculated from other attributes

C) An attribute with meaningful component parts (Correct Answer)

13. Which of the following is an example of a multivalued attribute?
A) Employee ID
B) Date of Birth
C) Skills
D) Address

C) Skills (Correct Answer)

14. What is the purpose of an identifier attribute?
A) To store additional information about an entity
B) To uniquely distinguish instances of an entity type
C) To categorize entities into different types
D) To describe relationships between entities

B) To uniquely distinguish instances of an entity type (Correct Answer)

15. What does the cardinality of a relationship describe?
A) The degree of the relationship
B) The number of relationships an entity can participate in
C) The attributes of the entities involved
D) The data types of the relationship fields

B) The number of relationships an entity can participate in (Correct Answer)

16. Which relationship type involves only one entity type?
A) Unary (recursive)
B) Binary
C) Ternary
D) N-nary

A) Unary (recursive) (Correct Answer)

17. What is an associative entity used for?
A) To model many-to-many relationships with additional attributes
B) To represent simple relationships between two entities
C) To define attributes of an entity type
D) To describe relationships between subtypes

A) To model many-to-many relationships with additional attributes (Correct Answer)

18. What is the process of generalization in database design?
A) Defining specific subtypes from a general supertype
B) Creating a more general entity type from specialized entity types
C) Establishing unique identifiers for entities
D) Identifying relationships between different entity types

B) Creating a more general entity type from specialized entity types (Correct Answer)

19. What does the completeness constraint in a supertype/subtype relationship ensure?
A) An instance of the supertype can belong to multiple subtypes
B) Each instance of the supertype must belong to at least one subtype
C) No instance of the supertype can belong to any subtype
D) Subtypes can have multiple attributes

B) Each instance of the supertype must belong to at least one subtype (Correct Answer)

20. Which of the following is a requirement for a primary key?
A) It can be null
B) It must be unique for each row
C) It can be composite or simple
D) It can be derived from other attributes

B) It must be unique for each row (Correct Answer)

21. What is referential integrity?
A) Ensuring that foreign key values match primary key values in related tables
B) Maintaining unique values in a primary key
C) Preventing null values in any attribute
D) Ensuring that all attributes have data

A) Ensuring that foreign key values match primary key values in related tables (Correct Answer)

22. Which normalization form addresses partial dependencies on a primary key?
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
D) Fourth Normal Form (4NF)

B) Second Normal Form (2NF) (Correct Answer)

23. What is a transitive dependency in the context of normalization?
A) A functional dependency where the primary key is a determinant for another attribute, which in turn is a determinant for a third attribute
B) A dependency between non-key attributes
C) A dependency involving only composite attributes
D) A dependency that affects only multivalued attributes

A) A functional dependency where the primary key is a determinant for another attribute, which in turn is a determinant for a third attribute (Correct Answer)

24. What type of attribute is "Age" in a database?
A) Composite attribute
B) Multivalued attribute
C) Single-valued attribute
D) Derived attribute

C) Single-valued attribute (Correct Answer)

25. What does the term "relation" refer to in a relational database?
A) A relationship between two entities
B) A named, two-dimensional table of data
C) A type of attribute
D) An operation used to query data

B) A named, two-dimensional table of data (Correct Answer)

26. Which of the following is true about foreign keys?
A) They uniquely identify each row in a table.
B) They are used to refer to primary keys in related tables.
C) They cannot be null.
D) They must be composite attributes.

B) They are used to refer to primary keys in related tables. (Correct Answer)

27. What is the primary goal of normalization?
A) To enhance data redundancy
B) To create complex relationships
C) To avoid data anomalies and reduce redundancy
D) To simplify data types

C) To avoid data anomalies and reduce redundancy (Correct Answer)

28. What does a simple attribute refer to?
A) An attribute with multiple component parts
B) An attribute with a single, indivisible value
C) An attribute that can be decomposed into smaller attributes
D) An attribute that holds derived data

B) An attribute with a single, indivisible value (Correct Answer)

29. What type of key is used to enforce uniqueness within a table?
A) Foreign key
B) Composite key
C) Primary key
D) Secondary key

C) Primary key (Correct Answer)

30. What does the term "domain constraint" refer to?
A) Restrictions on the range of values for an attribute
B) Constraints related to the physical storage of data
C) Constraints ensuring foreign key relationships
D) Constraints on data retrieval performance

A) Restrictions on the range of values for an attribute (Correct Answer)