VA Session 2 !

Question 1

Interaction with database

Answer 1

• Database
• DBMS
• front End Application (= program with easy interface to use (e.g. HR system))

Question 2

Metadata

Answer 2

data that describes structure & properties of data -> proper understanding & use of data

Question 3

Database metadata

Answer 3

• represents the structure of database
• Database content that is not the data itself (data about the data)
• e.g. Names of data structures, data types, data descriptions

Question 4

Types of databases

Answer 4

1. Transactional = Operational
2. Analytical

Question 5

Transactional information

Answer 5

collected & used for day-to-day operational needs in organizations

Question 6

Analytical information

Answer 6

collected & used for analytical tasks (based on transactional info)

Question 7

Operational database

Answer 7

collects & presents transactional info in support of daily operational procedures & processes

Question 8

Analytical database

Answer 8

collects & presents analytical info in support of analytical tasks

Question 9

Use of types of databases

Answer 9

• Typically maintains & uses many operational data sources
• data warehouse created as separate data store because:
• 1. performance of operational day-to-day tasks diminished when competing with analytical queries
• 2. structure of database not efficient for operational & analytical purpose

Question 10

Types of databases: Differences

Answer 10

• Operational Data:
• time-horizon days / months, detailed, current
• used by all types of employees, subject oriented
• small amounts used, high frequency of access, can be updated, non-redundant
• Analytical Data:
• time years, summarized (&/or detailed), values over time (snapshots)
• Used by narrower set of users, application oriented
• large amounts used, low/modest frequency of access, read & append only, no redundancy

Question 11

ER modeling

Answer 11

• Entity-relationship
• conceptual database modeling technique that
• Organizer & graphically represents
• the requirements of the collection process
• good to do before database to know where to put what & not move everything around often

Question 12

ER diagram (ERD)

Answer 12

= result of ER modeling; blueprint of database

Question 13

Relationship-Types

Answer 13

• One-to-one = one row on one table is connected to one row in other table
• One-to-many = one row in one table is connected to many rows in other table
• Many-to-many = many rows in one table are connected to many rows in other table

Question 14

Cardinality constraints

Answer 14

show how many instances of one entity can be associated with instances of another entity

Question 15

Maximum cardinality

Answer 15

• first
• One I
• Many <

Question 16

II

Answer 16

• One Mandatory
• 1

Question 17

I0

Answer 17

• One Optional
• 1 or 0

Question 18

> I

Answer 18

• Many Mandatory
• > = 1

Question 19

> O

Answer 19

• Many Optional
• 0, 1 or > 1

Question 20

Relationship attributes

Answer 20

• Sometimes: M:N relationship can have attributes on their own
• e.g. student (studentID, studentName) >I belongs to with role <I Organization (OrgID, OrgLocation)

Question 21

Relational database model

Answer 21

• represents a database as collection or related tables

Question 22

Column Synonyms

Answer 22

• Attribute
• Field

Question 23

Relation

Answer 23

Table in a relational database:
-1. & 2. each column & row = unique name
- 3. All values in each column must be from the same (predefined) domain & single valued
- 4. order of columns & rows is irrelevant
- 5. + 6. must have (non NAN values) primary key

Question 24

Primary Key

Answer 24

• (set) column whose value is unique for each row
• Each relation must have one
• Cannot have 0 values
• Never changes (e.g. not phone number no primary key)
• Primary key name = underlined to distinguish from other columns

Question 25

Composite primary key

Answer 25

primary key that is composed of multiple columns (only combination of attributes lets row be identify)

Question 26

Entity integrity constraint

Answer 26

rule: in a relational table, no primary key column can have null (empty) values

Question 27

Foreign Key

Answer 27

- Column in a relation that refers to primary key column in another (referred) relation - Mechanism used to depict relationships in RDMs -relational schema: line from foreign key to corresponding primary key

Question 28

Relational schema

Answer 28

Visual depiction of relational database model

Question 29

From ER to relational schemas

Answer 29

- Once ERD constructed -> mapped into relational schema (= collection of relations) - Each regular entity -> relation - Each regular attribute of regular entity -> column of newly created relation - If entity has single unique attribute: attribute -> primary key

Question 30

Relational Schema: Mapping entities with composite attributes

Answer 30

- make two columns out of both composite attributes - e.g. customer - customerName - 1) customername1, 2) customername2 -> relation / mapped relation = both customernames,

Question 31

Relational Schema: Mapping entities with optional attribute

Answer 31

- make one column of optional & just include existing values - e.g. Employee - Bonus (O) -> relation = bonus (0); mapped relation = Bonus, not all rows have values; bonus (O)

Question 32

Relational Schema: Mapping 1:M relationships

Answer 32

Relation mapped from entity on M side has a foreign key that corresponds to primary key of relation mapped from 1 side -> connect by error; attributename (FK)

Question 33

Relational Schema: Mapping M:N relations

Answer 33

- Additional relation created to represent M:N relationship itself - 2 foreign keys (corresponding to primary keys of 2 relations representing the 2 entities) -> form the composite primary key of new relation

Question 34

Relational Schema: Mapping 1:1 relationship

Answer 34

- Same as 1:M relations - One of mapped relations has foreign key referring to primary key of the other mapped relation (direction not fixed, what fits better)

Question 35

Relational Schema: Referential integrity constraints

Answer 35

- In each row of relation containing a foreign key: value of foreign key either a) matches primary key value or b) is 0 - Referential integrity constraint lines = lines pointing from foreign to primary key

Question 36

Relational Schema: Mapping candidate keys (= multiple unique attributes): (U)

Answer 36

- Database designer chooses 1 of candidate as primary key - Other candidate keys = mapped as non-primary key columns

Question 37

Relational Schema: Mapping multivalued attributes

Answer 37

- Entity containing multivalued attributes: mapped without this attribute - separate relation with column representing multivalued an attribute & foreign key column referring to primary key

Question 38

Relational Schema: Mapping 1:M relationship

Answer 38

Foreign key must appear in the relation from the M side

Question 39

Multivalued Attribute - definition

Answer 39

an attribute that can have >1 value associated with the key of the entity

Question 40

Minimum cardinality

Answer 40

- second - Optional O = no relationship necessary - Mandatory I = must be at least one relationship

Question 41

Entity

Answer 41

- e.g. table - Constructs that represent what database keeps track of (e.g. people, places, items) - Basic building block of an ERD - Within one ERD each entity must have a different name

Question 42

Differences Foreign & Primary Key

Answer 42

- multiple rows with one foreign key possible but with primary key not - multiple foreign keys in one entity possible (not for primary key)

Question 43

Attributes

Answer 43

- characteristics of an entity (column) - within one entity: must have different name

Question 44

Unique Attributes

Answer 44

- attributes whose values is different for each entity instance - each entity must have at least one

Question 45

Types to visually show database

Answer 45

- Er Model - Relational Database Model - relational Schema