Database - Midterm Cards

Jan Seewald
Flashcards by Jan Seewald, updated more than 1 year ago
Jan Seewald
Created by Jan Seewald over 5 years ago
7
1

Description

Flashcards on Database - Midterm Cards, created by Jan Seewald on 10/19/2015.

Resource summary

Question Answer
What is structural independence? Why is it important? Structural independence means that you dont need to change the application when you are changing the characteristics of the data like e.g. adding a column. Important: decrease programming effort!
What is data? Data are RAW FACTS and the building block of information.
What is information? Processed data data with meaning for knowledge creating if its accurate, relevant and timely it supports decisions (good for global competition)
Character or group of characters with a specific meaning fields / attributes / column
One or more fields that describes a person, place, thing records / tuples / rows
Collection of related records files / entities / tables
Database Types (Number of Participants) Single User (on PC) Multi User
Database Types (Localization) Centralized Distributed Cloud
Database Types (Strategy) Operational (day-to-day operations) Analytical/Data Warehouse (tactical/strategic decision)
Database Types (Structure) Unstructured data Structured data Semi structured data Extensible Markup Language
What is data redundancy? doubled data (unnecessary)
What is data inconsistency conflicting data in different places who used to be the same data
What is data anomalies? changes in redundant data not made correctly
What are the three redundancy/anomalies problems? data redundancy data inconsistency data anomalies
The 5 roles of people System Administrator Database Administrator Database Designer Application Developers End-Users
The 9 funtions of DBMS Data dictionary management Data storage management Data transformation, and presentation Security management Multi-user access control Backup and recovery management Data integrity management Database access languages and application programming interfaces Database communication interfaces
What does the data dictionary management? (DBMS) It does exactly this: Saving and changing the data dictionary.
What does the data storage management? (DBMS) It manages the access and storage for e.g. speed purpose.
What does the data transformation, and presentation? (DBMS) It makes the data logically and then visible for us to understand! (make it logically and visible to us)
What does the security management? (DBMS) Ensures data privacy and access control.
What does the multi-user access control? (DBMS) It avoids conflicts with others or resolves them.
What does the backup and recovery management? (DBMS) It makes backups and in case of failures it can recover them.
What does the data integrity management? (DBMS) It ensures that the integrity and rules are followed.
What does the database access languages and application programming interfaces? (DBMS) It allows us to communicate with the database e.g. with SQL
What does the database communication interfaces? (DBMS) Exactly.
What is an Relational Database Model? i dont know
What is an entity? Object used to collect and store data Must be distinct
What is a compound attribute? Student_Address
What is a single valued attribute? Cant be subdivided
What is a multi valued attribute? Student_Contacts
What is a derived attribute? Calculated from other attributes (mostly not stored)
What is an attribute? It is the characteristic of the entity and also distinct Compound + Single valued + Multi-valued + Derived
What relationships do you know? 1:1 1:N N:M
What is a constraints and what constraints do you know? A constraint is a rule Data Type + Attribute Domain + Default Value + NOT_NULL + UNIQUE
Business Rules! Description of policies, procedures, or principles within a specific organization Source: company managers + policy makers + department managers + written docus + interview end users Nouns => Entity Verbs => relationship (bidirectional, how many of B related to A and vice versa)
Level Architecture. What is that? The level of abstraction.
What is schema and subschema? Schema is the conceptual organization of the whole db (seen from admin) Subschema is only the schema as seen from an enduser (only a part of it
External model (Level Achitecture) end user view (of the data environment) how each people group sees it e.g. students vs professors vs school admin etc
Conceptual model(Level Achitecture) what data + relationships (entire db) (WHAT WE ARE DOING) high abstraction O.O. software and hardware independent + global view of database
Internal model(Level Achitecture) db seen by dbms medium abstraction software and hardware DEPENDENT! cuz yeah
Physical model(Level Achitecture) how stored physically low abstraction and how much space etc! do not affect internal model
What does a key and what keys do you know? Keys identify uniquely a tuple in an entity + important for relationships + integrity! Candidate Key + PK + Composite Key + Secondary Key + FK
Integrity Rules? Domain constraints: &Entity Integrity: unique pks+notnull &referential integrity: fk matches or null &relationship cardinality: &general constraints: rules from enterprise
DBMS Language SQL supports what languages? DDL, DML, DCL
What is DDL? Data Definition Language: It is used to define ethe DB and the DB objects
What is DML? Data Manipulation Language: Used to retrieve, insert, update and delete data
What is DCL? Data Control Language: Used to define the control access
What relational operators do you know? SELECT PROJECT UNION INTERSECT DIFFERENCE PRODUCT JOIN
What does SELECT? (relational operators) It returns a filtered table. (horizontal)
What does PROJECT? (relational operators) Returns a table with only some columns. (vertical)
What does UNION? (relational operators) Takes union compatible given tables and returns a table with all information. duplicates are only once inside.
What does INTERSECT? (relational operators) See UNION but returns only duplicates.
What does DIFFERENCE? (relational operators) See UNION but returns first table without duplicates with second table.
What does PRODUCT? (relational operators) everyone with everyone! TOO BIG!
What JOINS do you know and what are they doing? (relational operators) INNER JOIN: returns only matching rows of two tables on given attribute LEFT/RIGHT OUTER JOIN: LEFT/RIGHT side with additional columns with matching rows
For what is an assosiative entity mostly used? It is usd to correctly display a many to many relationship! 2 OPTIONS: compound PK or own PK (for more possibilities)
Where do you implement multiplicity and where not? You implement them not in the design because you cant -> you use the sofatware or triggers
What does cardinality mean? The min and max in a relationship! Participation: if its optional or mandatory!
What it the difference of Non-Identifying and Identifying Relationship? NIR: weak R but string Entity! because it lives on his own! (indep. PK) IR: strong R and weak Entity! It needs its dad like a room needs the building around! (FK is part of PK)
UNARY BINARY TERNARY - Relationships Count of entities
What is a HOMONYM and a SYNONYM? same name for different meanings e.g. C_Name in Customer AND Consultant different name for same meanings e.g. car and auto
What are the iterative steps to design a database? 1. Narrative description of organizations operations! 2. Find business rules based on 1 3. Find main entities and relationships 4. MAKE ERD! 5. Find attributes and PKs 6. REVIEW!
What is important to know about database design? complex and logically desired relationships < speed
NORMALIZATION!!! Whats that?
Show full summary Hide full summary

Similar

Architektur von Datenbanksystemen - Dienste
Marcel Fehring
Verständnisfragen DBS - Ebenenmodell und Sprachkomponenten
Marcel Fehring
A-Level Physics: Course Overview
cian.buckley+1
2PR101 1.test - 10. část
Nikola Truong
REGLAMENTO DE USO LEGAL, ADECUADO Y PROPORCIONAL DE LA FUERZA PARA LA POLICÍA NACIONAL DEL ECUADOR
edwin patrico
Estrategias en el Punto de Venta y Mercadeo
Yazmin Toral
VIAJES DE EXPLORACIÓN
Maverick MVP
Certificación por Competencias
Maria Jose Lopez Sibaja
Key Biology Definitions/Terms
Chelsie Cottingham
Estado e Relaçao de Poder
Adeildoethatiane Silva
Vectores en R2 y R3: Noción de distancia, definición algebraica de vector.
duwan parra carrillo