VA Session 2 ! Flashcards
Interaction with database
- Database
- DBMS
- front End Application (= program with easy interface to use (e.g. HR system))
Metadata
data that describes structure & properties of data -> proper understanding & use of data
Database metadata
- 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
Types of databases
- Transactional = Operational
- Analytical
Transactional information
collected & used for day-to-day operational needs in organizations
Analytical information
collected & used for analytical tasks (based on transactional info)
Operational database
collects & presents transactional info in support of daily operational procedures & processes
Analytical database
collects & presents analytical info in support of analytical tasks
Use of types of databases
- 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
Types of databases: Differences
- 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
ER modeling
- 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
ER diagram (ERD)
= result of ER modeling; blueprint of database
Relationship-Types
- 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
Cardinality constraints
show how many instances of one entity can be associated with instances of another entity
Maximum cardinality
- first
- One I
- Many <
II
- One Mandatory
- 1
I0
- One Optional
- 1 or 0
> I
- Many Mandatory
- > = 1
> O
- Many Optional
- 0, 1 or > 1
Relationship attributes
- Sometimes: M:N relationship can have attributes on their own
- e.g. student (studentID, studentName) >I belongs to with role <I Organization (OrgID, OrgLocation)
Relational database model
- represents a database as collection or related tables
Column Synonyms
- Attribute
- Field
Relation
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
Primary Key
- (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
Composite primary key
primary key that is composed of multiple columns (only combination of attributes lets row be identify)
Entity integrity constraint
rule: in a relational table, no primary key column can have null (empty) values
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
Relational schema
Visual depiction of relational database model
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
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,
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)
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)
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
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)
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
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
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
Relational Schema: Mapping 1:M relationship
Foreign key must appear in the relation from the M side
Multivalued Attribute - definition
an attribute that can have >1 value associated with the key of the entity
Minimum cardinality
- second
- Optional O = no relationship necessary
- Mandatory I = must be at least one relationship
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
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)
Attributes
- characteristics of an entity (column)
- within one entity: must have different name
Unique Attributes
- attributes whose values is different for each entity instance
- each entity must have at least one
Types to visually show database
- Er Model
- Relational Database Model
- relational Schema
