2049570
Description
Flashcards by Yorria Raine, updated more than 1 year ago
|
Created by Yorria Raine
about 9 years ago
|
|
| Question | Answer |
| File Based Systems | Tend to be tasked based. Files were created based on needs of a particular application driven by a business process. Files evolved as applications changed or new applications were required to meet demands of business. |
| Database System | Structured set of persistent Data. |
| DataBase Management System (DBMS) | Controls access to data Performs all routine data-handling operations Stores information related to data management (constraint rules). |
| Client Server Database System Architecture Includes what two things? | Server Process & Client |
| Database System Arch. Server Process | Program that listens for request for resources from clients and responds to request. |
| Database System Arch. Client | Program that request server resources |
| Personal Database Management Systems (include example) | DBMS and database applications run on same workstation. Used primarily for creating single-user database applications. Should be used only for applications that are not mission critical. Example is Access |
| Client/Server Database Management System Advantages | Generates less network traffic than personal databases. Inherent fault tolerant features to minimize chance of failure. Powerful data recovery mechanisms. Standardized interface. Enhanced Security. |
| Relational Database Model includes what two things? | Set Theory and Predicate Logic |
| Set Theory | Focuses on how things relate to different categories. Often explained using visual overlapping circles. |
| Predicate Logic | Used to form predicate expressions containing variables as "queries" with the purpose of filtering data. Example: find all students from a certain hometown and of a certain agae |
| Name the aspects of a Relational Database Model | Structural Aspect, Integrity Aspect, Manipulative Aspect |
| Structural Aspect of Relational Model | Data is perceived by users as tables, a logical view not the physical structure. |
| Integrity Aspect of Relational Model | Those tables (and stored data) satisfy certain integrity constrains as dictated by the DBMS |
| Manipulative Aspect of Relational Model | The operators available for retrieving data are operators that derive tables from tables. (Restrict (rows), Project (columns), Join) |
| Table (relation) | Matrix with columns (attributes) and rows (tuples). Columns rep different data categories (char or att. of entities). Rows contain individual records (properties of a specific entity). |
| Integrity Aspect Key Fields and Types | Ensure that each record in a table is precisely identified, and serve to establish table relationships and enforce integrity. Types: Primary (candidate, surrogate), Foreign, Composite |
| Primary Keys | Column in relational database table whose value must be unique for each row. Serves to identify individual occurrence of record. Every record must have a primary key. Cannot be NULL. |
| Candidate Keys | Any column data in a table that may be used as the primary key. Must be a column that is unique for each record and does not change. |
| Surrogate Keys | Column created to be record's primary key identifier. Has no real relationship to row to which it is assigned other than to identify it uniquely. Surrogate key values automatically generated using a sequence. |
| Foreign Keys | Column in table that is a primary key in another table. Creates relationship between two tables. Values must exist in table where it is the primary key and match exactly. |
| Composite Key | Unique key that is created by combining two or more columns. Usually comprises fields that are primary keys in other tables. |
| Properties of Relations Structural Aspect | Data stored in cells must be atomic. Each tuple (row) is unique). Tuples (rows) have no order, top to bottom. Attributes (columns) are unordered, left to right. |
| E.F.Codd's Twelve Rules for Fully Relational Database | 1. Information is represented as atomic data that is stored in cells 2.Each data item must be accessible by combining the table name and its primary key of the row/column name 3.Nulls must be treated consistently to represent missing data 4. An active, online dictionary(i.e. Catalog) should be stored as relational tables and access through the data access language 5. One Data sub-language i.e. ______ 6.,7. Must Support Updatable Views and High-level Updates 8. Physical data independence 9. Logical data independence 10. Must support data integrity constraints -Integrity rules stored in data dictionary 11. Must support data distribution 12. Nonsubversion |
| Codd's Summary: | Rule Zero: “For a system to qualify as an RDBMS, that system must use relational facilities exclusively to manage the database.” |
| Entity-Relationship Model Goal | Goal is to help identify which entities to include in database AND to identify the relationship between the entities (not a formal concept). Use E/R Diagrams to represent the logical structure of a database in a pictorial manner |
| Normalization | Step by step process used to determine which data elements should be stored in which tables Purpose: - Eliminate data redundancy, - Ensure that table data is related |
| User Views | User view(s): set of requirements necessary to support operations of a particular database user or group. May have several user views depending on complexity of problem |
| Information Level Design Steps | Step 1: Represent the User Views as a collection of normalized tables Step 2: Identify all keys in these tables Step 3: Determine the properties for each table(i.e. additional attributes) Step 4: Merge the results of steps 1 – 3 into the cumulative design |
| Relationship Types | One-to-One Many-to-Many One-to-Many |
| One to One (1:1) Rarely implemented as such | Each occurrence of a specific entity is found only once in each set of data, i.e,: one record in a primary table links to only one record in a secondary table Example: 1 husband has 1 wife How do handle this relationship? From the logical standpoint, a 1:1 relationship should be merged into a single table OR converted to a 1:M relationship DEPENDING ON THE DATA |
| Many-to-Many(N:M) | Many-to-many relationships occur when each record in Table A may have many linked records in Table B and vice-versa. The many-to-many relationship is usually a mirror of the real-life relationship between the objects the two tables represent. Example: There are many clubs(entity) at WSU, there are many students(entity) enrolled at WSU. Many students may belong to many clubs OR a student may belong to zero clubs. |
| One-to-Many(1:M) | Instance can occur once in 1 entity but 1 or more(many) times in second entity This is the most used and most desirable relationship in a relational database Examples: 1 vendor publishes many books |
| General Design Principles | 1. After gathering data on all user views, review them to get a general feel for the organization. Do not focus on relations, yet. 2. Determine the basic entities. Do not worry that you might not see all entities. If you omit some, they will show up later when you are looking for relationships. 3. For each entity, start a table. Ex. Students( 4. Determine and fill in the PK for each table. 5. Add additional non-key attributes 6. Create linking tables and add FKs |
| Functional Dependence | An attribute (i.e. Column),B, is functionally dependent on another attribute, A, if a value for A determines a single value for B |
| First Normal Form | A table is in First Normal Form if it does not contain repeating groups and identifies a primary key Assumptions: Will store one position per employee. Will store highest degree earned per employee. Best Practice: If an attribute contains a repeating group, break it out and create another table |
| Second Normal Form | A table is in Second Normal Form if it is in 1 NF and it includes no partial dependencies: No attribute is dependent on only part of the primary key. If the PK was a composite, this could be a problem |
| Third Normal Form | A table is in 3NF if: it is in 2NF * contains no transitive dependencies: One or more attributes is dependent on a non-key attribute |
| 4NF | Table (relation) in fourth normal form (4NF) It is in third normal form No multivalued dependencies - To converting table to fourth normal form. Split third normal form table into separate tables, each containing the column that multi-determines the others |
| Design Methodology | 1. Determine entities needed and relationship between entity (1 or Many) Draw E-R Diagram 2. List tables. Determine the Primary Key for each table 3. Determine the properties for each entity - define additional columns (attributes) 4. Create intersection tables and foreign keys to relate tables. State Assumptions 5. Double check that all tables are normalized to 3NF |
| IDEF1X | Integrated DEFinition for Data modeling – derived from a family of data modeling techniques developed by Air Force * F1X –logical data modeling Notation is more detailed and meaningful * Does not require table list |
| IDEF1X Shapes (Rectangle / Line Type) | SHAPE OF RECTANGLE is Key * Rectangle with curved corners – denotes DEPENDENT relationship, relationship is dependent on relationship with another entity to be identified. Look for composite PK * Rectangle with squared corners – denotes INDEPENDENT relationship, relationship stands on its own LINE TYPE is also Key * Solid line denotes IDENTIFYING relationship, requires relationship with another entity to be identified ____________ Look for composite PK Cannot stand on it’s own, needs strong support * Dashed line denotes NONIDENTIFYING relationship, relationship can stand on it’s own, example: each Rep can be identified without the Customer ---------------- |
| The Entity-Relationship Model | Peter Chen is the Originator of the ER Model * Entities are drawn as rectangles * Relationships are drawn as diamonds with lines connecting the entities involved in relationships * Both Entities and Relationships are Named * The lines are named to indicate the type of relationship: 1 to n, m to n |
| Refinement: Existence Dependence | An entity is existence dependent if its sole reason for existence is to be associated with another entity. It has a mandatory foreign key attribute Example: Employee dependents can't exist without employee |
| Weak Entity | * If an entity is existent dependent and has a strong relationship with its parent, it is a weak entity * Can represent a weak entity by placing an E in the relationship diamond * A double rectangle encloses a weak entity. |
| Cardinality | * Cardinality – indicates the number of items that must be included in a relationship * Mathematically, the number of elements in a set. If 2 sets have the same number of elements, then they have the same cardinality. * An entity in a relationship with minimum cardinality of zero plays an optional role in the relationship * An entity with a minimum cardinality of one plays a mandatory role in the relationship |
| Structured Query Language | * Standard data access language for relational databases * ANSI (American National Standards Institute) and ISO (International Standards Organization) compliant * Consists of about 30 commands |
| Basic Categories for SQL Commands | DDL (Data Definition Language): Define structure of database DML (Data Manipulation Language): Interact with database Transaction Control: Commit and Rollback |
| User Accounts and Schema | * An account is created for each user. User Roles, Profile, Privileges are configured by DBA * Each user logs into his/her schema. Schema – dedicated user space where all objects are owned by user and accessible only by that user or the DBA: May grant access to other users if desired if you have that privilege. |
| SELECT Statement | SELECT statements are used to retrieve data from the database, i.e. query the database |
| Basic Queries | View All Data in a Table: Substitute an * for the column names in a SELECT clause View Multiple, Specific Columns in a Table: Separate column names with a comma |
| Entity Subtypes | * Subtype called a category in IDEF1X terminology * Incomplete category: records that do not fall into the subtype Example: Not all students are represented as some students do not live in dorms Incomplete category * Complete categories: all records fall into the categories |
Want to create your own Flashcards for free with GoConqr? Learn more.