Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
81 Cards in this Set
- Front
- Back
5 limitations of file system |
- data redundancy - lack of security - lack of data integration: islands of information, hard to see the big picture - data anomalies - application dependent on data structure - no concurrency control - |
|
Data |
Raw facts that don't have meanings unless processed into information |
|
Field |
A character or a group of characters that is used to define and store data. Each attribute constitutes a fieldúhhj |
|
Record |
A logically connected set of fields that describe a person, place or thing. Each customer would constitute a record |
|
File |
A collection of related records |
|
Which characteristics of the file system can lead to data redundancy? |
The file system are unable to manage relationships between data |
|
What's data independence and why it's important? |
Data access programs not affected when data characteristics change. Improves efficiency and reduces maintenance costs |
|
Why database design is important |
- Improves data integrity by reducing redundancies and anomalies - good DBMS software doesn't overcome a bad design |
|
A database houses which 2 things? |
A collection of 1 or more tables and metadata |
|
What's metadata? |
Data about data. Including attribute characteristics and relationships between entity sets |
|
What's the most important advantage of relational databases? |
Provides structural and data independence and provide conceptual simplicity |
|
Relational databases are good for designers because |
They free designers from the complexities associated with physical data representation |
|
Logical independence means |
You can change the internal model without affecting the conceptual or external model (eg, changes to the DBMS) |
|
External model |
The end user's view of the database. Allows customizable views of the ERD based on the viewer |
|
Conceptual model |
The global view of the database through a ERD |
|
Internal model |
Representation of the database as seen by the DBMS. |
|
Physical independence means |
Can change physical model without affecting the internal model (eg, changes to the hardware) |
|
Physical model |
Lowest level of abstraction, describe the physical storage of data |
|
Secondary key is used for |
Data retrieval purposes, so don't necessarily yield a unique result but should also be restrictive enough that it produces manageable number of results |
|
Associative vs transactional entities |
Associative entities inherit their primary keys from their parent entities, while transactional have their own primary keys as well as foreign keys from patents. |
|
What's a weak entity? |
1. Existence-dependent on its parent entity 2. Inherits at least part of its primary key from the parent |
|
What's a strong (identifying) relationship? |
Between a weak entity and it's parent |
|
Recursive relationship |
Exists when an entity is related to itself. Eg a course is a prerequisite to another course |
|
What's a weak (non-identifying) relationship? |
When the participating entities have their own independent primary keys. Both identities are strong. |
|
What's a data dictionary? |
Stores data elements and their relationships |
|
Define DBMS |
A collection of programs that allows users to perform: 1. Data dictionary management 2. Data storage management 3. Data transformation and presentation 4. Security management 5. Multi user access control 6. Backup and recovery 7. Data integrity management |
|
DDL |
Data definition language. Used by DBMS to define database |
|
DML |
Data manipulation language. Used to update, retrieve, or delete data. Eg, SQL language |
|
DAP |
Database application program. Interacts with the DBMS via SQL. Allows users to perform data entry, queries, maintenance...etc |
|
Schema is |
The logical view of the entire database as seen by the database administrator |
|
Sub-schema |
The view as seen by the user or application programmer. Aka view |
|
What are 3 advantage of different views depending on users |
1. Security 2. Customizable appearance 3. Unchanging when DBMS, hardware or OS changed |
|
What's concurrency control |
Conflict when multiple users are making changes to same data. Controlled by row or cell locking |
|
What are 5 disadvantages of DBMS |
1. Size 2. Complexity 3. Costs: conversion, software, hardware 4. High impact of failure 5. Performance |
|
Kernel entity/base relation |
A table whose rows will be stored physically |
|
Instance/occurrence |
A tuple of real data in a table |
|
Compound attribute |
Composed of logically grouped attributes such as inv-line and inv-id |
|
Single valued attribute |
Holds a single value for each occurrence of entity |
|
Multi-valued attribute |
Holds multiple possible values for each occurrence. Eg, student contacts |
|
Derived attribute |
Calculated from other attributes |
|
Subsetting criteria |
An attribute whose finite values divide entity instances into criteria. Eg, student/instructor |
|
Constraints are implemented thru: |
1. Data type: characters or numbers 2. Attribute domains 3. Default values |
|
An intersection of row and column in a table contains a |
Data value |
|
Besides entities and relationships, what information do a conceptual schema contain? |
1. Data security and integrity information 2. Constraints 3. Semantic information |
|
Internal schema contains information about: |
- storage space allocation for data/indexes - data compression/encryption technique - record descriptions with sizes for data storage - record placement |
|
Alternate keys are |
Candidate keys that are not chosen as primary key |
|
Secondary keys are |
Any keys used for data retrieval |
|
Compound/composite keys |
Attributes that together can identify a single entity instance |
|
5 desirable characteristics of primary key |
1. Unique for each instance 2. Unchanging over time 3. Not be null 4. Auto-generated 5. Consistent |
|
What's the difference between inner vs left/right outer join? |
Inner join only joins fields with matching primary/foreign keys |
|
Multiplicity of entity in a relationship consists of: |
Participation..cardinality |
|
Ternary relationship |
Involves 3 or more entities. Appears in conceptual level only |
|
A M:N relationship is aka |
Non-specific relationship |
|
Transactional entities must have its own primary key as well as |
Foreign keys of related entities |
|
Generalization |
Bottom up process of identifying super type among several subtypes by finding common characteristics and relationships |
|
Specialization |
Top down process of identifying subtypes for a given super type by finding unique characteristics and relationships |
|
Redundant relationship |
Can be deleted without losing any information generation ability of the model |
|
Update anomalies |
The same change has to be done many times due to the presence of redundant data |
|
Insertion anomalies |
One or more attributes can't be added without the presence of other attributes |
|
Deletion anomalies |
Delete 1 attribute resulting in loss of data about other attributes that we wish to keep |
|
1NF |
All key attributes are defined (no repeating groups), and when all remaining attributes are dependent on the primary key |
|
A table in 1NF is automatically in 2NF when: |
It's primary key composes of a single attribute |
|
Advantages of denormalized table |
-use less processing power to perform join between tables. - maintain historical accuracy. Eg maintain a table for past salary based on old hourly rates |
|
Partial dependency |
B is partially dependent on A if it's also dependent on a subset of A. Eg EmpID + name determines Emp_address |
|
Full dependency |
Removal of any attributes from determinant result in not able to determine B. Eg Student _ID + course_ID determine grade |
|
Self determination |
A determine A |
|
Reflexive |
If B is a subset of A, then A determines B |
|
Augmentation |
If A determines B, then AC determine B |
|
Transitivity |
If A determines B and B determines C, then A determines C |
|
Decomposition |
If A determines BC, then A determines B and A determines C |
|
Union |
The opposite of decomposition. If A determines B and A determines C, then A determines BC |
|
Requirements statement |
A description of the business processes that can be used to derive business rules |
|
To find entities |
Look for nouns in the requirements statement and beware of synonyms. |
|
To find relationships |
Look for verbs in the requirements statement. Determine multiplicity constraints and document and update data dictionary |
|
To find attributes |
Use the requirements statement to find nouns that indicate a property, quality, identifier or characteristic of the entity |
|
Aggregation |
The child can exist independently even if the parent dies: engine (child) and car. Denoted by an open diamond in the ERD |
|
Composition |
A stronger form of aggregation relationship in which the child dies when the parent dies. Eg book (parent) contains index (child). Denoted by a closed diamond in the ERD |
|
Enhanced modelling concepts are |
-generalization/specialization - aggregation - composition |
|
Processes of forming a conceptual design |
Identify entities, relationships, and attributes Determine relationship types Determine attributes domain Select keys Consider enhanced modelling concepts Check model for redundancy Validate model against user transactions Review model with user |
|
Look for redundancy between relationship by |
Look for possible synonyms between 1:1 relationship |
|
Sometimes what can make relationship non-redundant |
Time dimension |