VA Session 2 ! Flashcards
1
Q
Interaction with database
A
- Database
- DBMS
- front End Application (= program with easy interface to use (e.g. HR system))
2
Q
Metadata
A
data that describes structure & properties of data -> proper understanding & use of data
3
Q
Database metadata
A
- 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
4
Q
Types of databases
A
- Transactional = Operational
- Analytical
5
Q
Transactional information
A
collected & used for day-to-day operational needs in organizations
6
Q
Analytical information
A
collected & used for analytical tasks (based on transactional info)
7
Q
Operational database
A
collects & presents transactional info in support of daily operational procedures & processes
8
Q
Analytical database
A
collects & presents analytical info in support of analytical tasks
9
Q
Use of types of databases
A
- Typically maintains & uses many operational data sources
- data warehouse created as separate data store because:
- performance of operational day-to-day tasks diminished when competing with analytical queries
- structure of database not efficient for operational & analytical purpose
10
Q
Types of databases: Differences
A
- 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
11
Q
ER modeling
A
- 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
12
Q
ER diagram (ERD)
A
= result of ER modeling; blueprint of database
13
Q
Relationship-Types
A
- 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
14
Q
Cardinality constraints
A
show how many instances of one entity can be associated with instances of another entity
15
Q
Maximum cardinality
A
- first
- One I
- Many <
16
Q
II
A
- One Mandatory
- 1
17
Q
I0
A
- One Optional
- 1 or 0
18
Q
> I
A
- Many Mandatory
- > = 1
19
Q
> O
A
- Many Optional
- 0, 1 or > 1
20
Q
Relationship attributes
A
- Sometimes: M:N relationship can have attributes on their own
- e.g. student (studentID, studentName) >I belongs to with role <I Organization (OrgID, OrgLocation)
21
Q
Relational database model
A
- represents a database as collection or related tables
22
Q
Column Synonyms
A
- Attribute
- Field
23
Q
Relation
A
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
24
Q
Primary Key
A
- (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
25
Composite primary key
primary key that is composed of multiple columns (only combination of attributes lets row be identify)
26
Entity integrity constraint
rule: in a relational table, no primary key column can have null (empty) values
27
Foreign Key
- 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
28
Relational schema
Visual depiction of relational database model
29
From ER to relational schemas
- 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
30
Relational Schema: Mapping entities with composite attributes
- make two columns out of both composite attributes
- e.g. customer - customerName - 1) customername1, 2) customername2 -> relation / mapped relation = both customernames,
31
Relational Schema: Mapping entities with optional attribute
- 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)
32
Relational Schema: Mapping 1:M relationships
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)
33
Relational Schema: Mapping M:N relations
- 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
34
Relational Schema: Mapping 1:1 relationship
- 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)
35
Relational Schema: Referential integrity constraints
- 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
36
Relational Schema: Mapping candidate keys (= multiple unique attributes): (U)
- Database designer chooses 1 of candidate as primary key
- Other candidate keys = mapped as non-primary key columns
37
Relational Schema: Mapping multivalued attributes
- Entity containing multivalued attributes: mapped without this attribute
- separate relation with column representing multivalued an attribute & foreign key column referring to primary key
38
Relational Schema: Mapping 1:M relationship
Foreign key must appear in the relation from the M side
39
Multivalued Attribute - definition
an attribute that can have >1 value associated with the key of the entity
40
Minimum cardinality
- second
- Optional O = no relationship necessary
- Mandatory I = must be at least one relationship
41
Entity
- 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
42
Differences Foreign & Primary Key
- multiple rows with one foreign key possible but with primary key not
- multiple foreign keys in one entity possible (not for primary key)
43
Attributes
- characteristics of an entity (column)
- within one entity: must have different name
44
Unique Attributes
- attributes whose values is different for each entity instance
- each entity must have at least one
45
Types to visually show database
- Er Model
- Relational Database Model
- relational Schema
