Data Modeling & Table Mapping (ISYS)

Question 1

Data modeling

Answer 1

process used to define data requirements needed to support the business processes within organizations

Question 2

Conceptual data model

Answer 2

technology-independent specification about the data
(during data modeling, data requirements are initially recorded as conceptual data model)
-constructed through collab with business stakeholders

Question 3

physical data model

Answer 3

model that is used by the organization

Question 4

entity relationship diagram (ERD) method

Answer 4

• most common conceptual database modeling approaph

- includes three logical constructs: entities, attributes, and relationships

Question 5

entities

Answer 5

-person, place, or thing about which an organization wishes to save information
EX: employee, state, order, and time sheet
-logical concept in the mind of the analyst that represents a category of objects that have common characteristics or attributes we want to store

Question 6

attributes

Answer 6

-properties of entities that an organization cares about
EX: color, employment date, name, and SS number
-represents a fact that an organization wants to store for eacj instance of an entity
-EX: ID number first name, last name, birth date, employment date

Question 7

relationships

Answer 7

• verbs that describe how entities are related to each other
• entity relationship entity phrase is called a “relationship entity pair” (bidirectional)

Question 8

instance

Answer 8

• single occurrence of the object that correspond to a specific entity
• when a conceptual database is mapped to a physical database, a separate table is typically created to represent each entity, each record within the table represents once instance

Question 9

cardinality

Answer 9

• how many instances in one table can be related to one or more instances of related entity
• -specification of the maximum number of instances of one entity can be related to the number of instances of another entity

Question 10

one-to-one (1:1)

Answer 10

an instance of entity A can relate to one and only one instance of B and vice versa
EX: one wife has one husband

2 types: mandatory-participation relationships and optional-participation relationship

Question 11

one-to-many (1:M)

Answer 11

one instance of entity A can relate to one or many instances of entity B, but an instance of entity B can only relate to instance A
EX: mother has many children, children have only 1 mother

-put the primary key from “one” side as a foreign key in the table on the “many” side of the relationship

Question 12

many-to-many (M:M)

Answer 12

an instance of entity A can relate to one or more instances of entity B while instances of B can relate to one or more instances of entity A
EX: uncle has many nephews, nephews have many uncles

• to implement the relationship link for many-to-many cardinality, we need to create a new Relationship (intersection) table
• the primary keys in the relationship table are the primary keys of all the entities participating in the relationship

Question 13

serialized instances

Answer 13

-each individual instance is assigned a unique identifier

EX: Cars have VIN (Vehicle Identification Number)

Question 14

virtual instances

Answer 14

-same identifier is assigned to all the instances of that entity
EX: cereal, broom

Question 15

table mapping

Answer 15

• ERD’s mapped to database table with primary keys and foreign keys
• foreign keys are not shown on ERD’s but implied by the cardinality of the relationships between the entities
• during table mapping, foreign keys are made explicit

Question 16

key migration

Answer 16

rules for which primary key from one side of the relationship becomes a foreign key on the other side of the relationship based on the cardinality of the relationship

Question 17

mandatory-participation 1:1 relationship

Answer 17

there must be one record on each side of the relationship

Question 18

optional-participation 1:1 relationship

Answer 18

participation on one side of the relationship is optional

Question 19

primary key

Answer 19

• uniquely identifies an instance
• for every row, the value of the key must uniquely identify that row
• the value must belong to one and only one row

Question 20

simple key

Answer 20

key contains a single attribute

Question 21

composite key

Answer 21

key that contains more than one attribute

Question 22

semantic/natural key

Answer 22

key for which the possible values have an obvious meaning to the user or the data

Question 23

artificial key

Answer 23

key for which the possible values have no obvious meaning to the user of the data

• artificial keys are used instead of semantic keys if the value of a semantic key may change or if the use of existing semantic attributes can be used to guarantee uniqueness
• to ensure a unique value for each record, an artificial key may be added as an attribute whose value is generated by the system