3818166
Flashcards by Jan Seewald, updated more than 1 year ago
|
Created by Jan Seewald
over 5 years ago
|
|
Description
| 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? |