DBMS FINAL OTHER
Home > Preview
The flashcards below were created by user
on FreezingBlue Flashcards.
Relation Properties (6)
- 1. Unique Name
- 2. No Multivalued attributes
- 3. Each row is unique
- 4. Each Attribute (column) has a unique name
- 5. Sequence of columns is insignificant
- 6. Sequence of rows is insignificant.
Importance of creating sample data (4)
- 1. Test your assumptions of the design
- 2. Check the accuracy of the design
- 3. Improve communications w/ users discussing the design
- 4. Develop prototype applications & test queries.
Referential Integrity Constraint - What happens to order day if we delete a customer who has submitted orders (3)
- 1. Cascade Delete - delete associated orders
- 2. Safety Check - prohibit deleting customer until we delete orders first.
- 3. Null - Place a null value in the foreign key (i.e. customerID is null in order table).
We need to add a new employee.
PK is the combination of EmpID and CourseTitle.
- Therefore, to insert a new row, the user must supply values for both
- EmpID and CourseTitle (because primary key values cannot be null or nonexistent).
- This is an anomaly because the user should be able to enter employee data
- without supplying course data.
Delete employee 140
This will result in losing the information that this employee completed a course (Tax Acc) on 12/8/201X.
Results in losing the information that this course had an offering that completed on that date.
Employee 100 gets a salary increase.
We must record the increase in two rows for that employee; otherwise, the data will be inconsistent.
Issues with CASE Tools (4)
- 1. Cannot model ternary relationships or super type/subtype.
- 2. Sometimes there are legitimate alternatives that require you to pick a different solution.
- 3. Must perform quality check on results
- 4. Understanding the transformation process is important.
Steps in Transforming EER Diagrams into Relations (7)
- 1. Map Regular entities
- 2. Map weak entities
- 3. Map binary relationships (including many to many)
- 4. Map associative entities
- 5. Map unary relationships
- 6. Map Ternary relationships.
- 7. Map supertype/subtype relationships
Mapping Weak Entity (3)
- 1. Include simple attributes
- 2. Include the primary key of identifying relation as foreign key in weak entity.
- 3. Primary key of weak entity is this foreign key and an identifier (dependent first/last name) + employee ID
Surrogate Primary Key + when to create (3)
Serial number or other system-assigned primary key for a relation.
- 1. There is a composite primary key
- 2. Natural primary key is inefficient (may be too long)
- 3. Natural primary key is recycled - may not be unique over time.
Benefit from Normalization when: (2)
- 1. Logical DB design phase - quality check for relations obtained from ER diagram
- 2. Reverse-engineering older systems - redundancies and anomalies exist in many cases.
Goals of Normalization (4)
- 1. Minimize data redundancy, avoid anomalies, conserve storage space
- 2. Simplify enforcement of referential integrity constraints
- 3. Easier to maintain data (insert, update, delete)
- 4. Better design, better representation of real world, easier for future growth.
First Normal Form
Remove multivalued attributes (repeating groups) and only one value (possibly null) at intersection of row/column.
Second Normal Form
remove partial dependencies (PCOMP --> NK)
Third Normal Form
Remove transitive dependences (NK --> NK).
Byce-Codd normal form
Remove remaining anomalies resulting from multiple candidate keys.
Fourth Normal Form
No multivalued dependencies (think insurance plan, provider, type issue in class)
Candidate Key must satisfy these (2):
1. Unique - Value must uniquely identify the row. All nonkey attributes are functionally dependent on the key
2. Nonredudancy - No attribute in the key can be deleted without destroying the property of unique identification.
Merging Integration Problems (4)
- 1. Synonyms
- 2. Homonyms
- 3. Transitive Dependencies
- 4. Supertype/Subtype
Merging Issue - transitive Dependency
Merging Issue - Supertype/subtype
One patient is resident patient, one is outpatient.
Normalization Solves for these Anomalies (3)
- Deletion - Delete one row, end up deleting more than intended
- Insertion - Adding data and have to make up attributes
- Update - Make an update that changes more than intended
Steps in Normalization (4)
- 1. Move columns in dependency to a new table
- 2. Identify PK in new table
- 3. Make the PK a FK in original table
- 4. Remove non-key fields in the new table from original table
Primary key Rules (5)
- 1. Unique
- 2. Numeric
- 3. Required
- 4. Should not change
- 5. Minimalistic
Why normalize Pros (5)
- 1. Flexibility in queries
- 2. Eliminate anomalies
- 3. Reduce data redundancy
- 4. Maximize storage space
- 5. Improve data integrity
Normalize cons (2)
- 1. Speed + performance (more tables)
- 2. Difficult queries/process (more tables to link together).
Disadvantages of file processing systems (5)
- 1. Program-data dependence
- 2. Duplication of data
- 3. Limited data sharing
- 4. Lengthy development times
- 5. Excessive program Maintenance
Costs and Risks of the Database Approach (5)
- 1. New, specialized personnel
- 2. Installation cost and complexity
- 3. Conversion cost
- 4. Need for explicit backup
- 5. Organizational conflict
Drawbacks of two-tier DB technology (2)
Amount of functionality that needs to be programmed into the application on the users’ computer can be pretty significant because it needs to contain both the user interface logic as well as the business logic.
Each time there is a change, each client computer needs to be updated.
What would you like to do?
Home > Flashcards > Print Preview