FINAL EXAM Flashcards
Purpose of a Database
- Keep track of things
- Store information that is more complicated than a simple list
Problems with Lists
- Redundancy
- Multiple Themes
List Modification Issues
- Deletion
- Update
- Insertion
What causes modification issues?
- Redundancy
- Multiple Themes
Relational Database
- Stores each topic in its own table
- Breaks up a list
- More complicated than a list
What does a relational database do?
- Minimizes redundancy, preserves relationships, and allows partial data.
- Gives foundation for user forms and reports
Four Components of a Database System?
- Users
- Database Application
- Database Management System (DBMS)
- Database
DBMS
- Database Management System
- Serves as an intermediary between database applications and the database
- Manages and controls database activities
- Creates, processes, and administers the databases it controls
Functions of a DBMS
- Create databases, tables, supporting structures
- Read data
- Modify data
- Maintain structures
- Enforce rules
- Control concurrency
- Provide security
- Perform backup and recovery
Referential Integrity Constraints
- Ensure that the values of a column in one table are valid based on the values in another table
Table
- Same as file and relation
File
- Same as table and relation
Relation
- A two-dimensional table that has specific characteristics
- The table dimensions consist of rows and columns
- Same as table and file
Row
- Same as record and tuple
Record
- Same as row and tuple
Tuple
- Same as record and row
Column
- Same as field and attribute
Field
-Same as column and attribute
Attribute
- Same as column and field
Unique Key
- Data value is unique for each row
- Consequently, the key will uniquely identify a row
Nonunique Key
- Data value may be shared among several rows
Composite Key
- A key that contains two or more attributes
- For a key to be unique, it must often become a composite key
Candidate Key
- A “candidate” to become the primary key, value is used to find the value of every attribute in the table
- Contains only one attribute
- A unique key
Primary Key
- Main key for the relation
- If you know the value of the primary key, you will be able to uniquely identify a single row
Foreign Key
- A primary key from one table placed into another table
- The key is called a foreign key in the table that received the key
Surrogate Key
- A unique, numeric value that is added to a relation to serve as the primary key
- Values have no meaning to users and are usually hidden on forms, queries, and reports
- Often used in place of a composite key
Functional Dependency
- A relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table
Normalization Concept
- A process of analyzing a relation to ensure that it is well formed
- If a relation is normalized (well formed), rows can be inserted, deleted, or modified without creating update anomalies
Normalization Principles
- Every determinant must be a candidate key
- Any relation that is not well formed should be broken into two or more well-formed relations
Normal Forms
- First Normal Form
- Second Normal Form
- Third Normal Form
- Boyce-Codd Normal Form
SQL
- Structured Query Language
- Data sublanguage
- Compromised of DDL, DML, DLC
DDL
- Create Table
- Alter Table
- Drop Table
DML
- Select
- Update
- Insert
- Delete
SQL Data Retrieval : Match Criteria
( = < > <= )
SQL Data Retrieval : Match Operators
- AND : Representing an intersection of the data sets
- OR : Representing a union of the data sets
- Example:
select * from quote
where quoteid=3 and projectid= 2;
SQL Data Retrieval : List of Values
- The WHERE may include the IN keyword to specify a particular column value must be included in a list of values
- Example:
SELECT EmpName
FROM EMPLOYEE
WHERE DeptID IN (4,8,9);
SQL Data Retrieval : The Logical NOT Operator
- Any criteria statement may be preceded by a NOT operator, which is to say that all information will be shown except that information matching the specified criteria
- Example:
SELECT EmpName
FROM EMPLOYEE
WHERE DeptID NOT IN (4,8,9);
SQL Data Retrieval : Finding Data in a Range of Values
- BETWEEN keyword allows a user to specify a minimum and maximum value on one line
- Example:
SELECT EmpName
FROM EMPLOYEE
WHERE SalaryCode BETWEEN 10 AND 15;
SQL Data Retrieval : Allowing for Wildcard Searches
- LIKE keyword allows searches on a partial data value
- LIKE is paired with wildcard values (% for multiple character wildcards, _ for single character wildcard)
- Example:
SELECT EmpID
FROM EMPLOYEE
WHERE EmpName LIKE ‘Kr%’;
SQL Data Retrieval : Sorting Results
- ORDER BY clause sorts results
- Example:
SELECT *
FROM EMPLOYEE
ORDER BY EmpName
COUNT
- Counts the number of rows that match the specified criteria
SELECT COUNT(DeptID)
FROM EMPLOYEE;
MIN
- Finds the minimum value for a specific column for those rows matching the criteria
- Example:
SELECT MIN(Hours) AS MinimumHours
FROM PROJECT
WHERE ProjID > 7;
MAX
- Finds the maximum value for a specific column for those rows matching the criteria
- Example:
SELECT MAX(Hours) AS MaximumHours
FROM PROJECT
WHERE ProjID > 7;
SUM
- Calculates the sum for a specific column for those rows matching the criteria
- Example:
AVG
- Calculates the numerical average of a specific column for those rows matching the criteria
- Example:
SELECTAVG(Hours) AS AverageHours
FROM PROJECT
WHERE ProjID > 7;
GROUP BY
- Subtotals may be calculated by using the GROUP BY clause.
- Example:
SELECT DeptID,
COUNT(*) AS NumOfEmployees
FROM EMPLOYEE
GROUP BY DeptID
HAVING
- The HAVING clause may be used to restrict which data is displayed
- Example:
SELECT DeptID,
COUNT() AS NumOfEmployees
FROM EMPLOYEE
GROUP BY DeptID
HAVING COUNT() > 3;
Elements of E-R Diagram
- Entities
- Attributes
- Identifiers
- Relationships
Entity
- Something of importance to a user that needs to be represented in a database.
- An entity represents one theme or topic.
- Entities are restricted to things that can be represented by a single table.
Weak Entity
- An entity that cannot exist in the database without the existence of another entity.
Strong Entity
- Any entity that is not a weak entity
ID-Dependent
- An ID-Dependent weak entity is a weak entity that cannot exist without its parent entity.
Non ID-Dependent
- A non-ID-dependent weak entity may have a single or composite identifier, but the identifier of the parent entity will be a foreign key.
Associative entity
- Used when there are attributes that are associated with the relationship between two entities rather than with either of the two entities themselves.
Subtype entity
- Subtypes can be exclusive or inclusive
- If exclusive, the supertype relates to at most one subtype.
- If inclusive, the supertype can relate to one or more subtypes.
Relationships
- Entities can be associated with one another.
Relationship Degree
- Defines the number of entity classes participating in the relationship:
–Degree 2 is a binary relationship.
–Degree 3 is a ternary relationship.
1:1
–A single entity instance in one entity class is related to a single entity instance in another entity class.
1:N
–A single entity instance in one entity class is related to many entity instances in another entity class.
N:M
–Many entity instances in one entity class is related to many entity instances in another entity class
Maximum Cardinality
- The maximum number of entity instances that may participate in a relationship instance—one, many, or some other fixed number.
Minimum Cardinality
- The minimum number of entity instances that must participate in a relationship instance.
- These values typically assume a value of zero (optional) or one (mandatory).
Recursive
- It is possible for an entity to have a relationship to itself
Transforming a Data Model into a Relational Design
- Create a table for each entity:
- Specify primary key
- Specify properties for each column (data type, null status, default value, specify data constraints)
- Verify normalization - Create relationships by placing foreign keys:
- Strong entity relationships (1:1, 1:N, N:M)
- ID-Dependent and non-ID Dependent weak entity relationships
- Subtypes
- Recursive
Intersection Table
- To create a N:M relationship, a new table is created.
- An intersection table has a composite key consisting of the keys from each of the tables that it connects
Association
- When an intersection table has columns beyond those in the primary key
Supertype/Subtype Relationships
- The identifier of the supertype becomes the primary key and the foreign key of each subtype
Database Administration Functions
- Concurrency Control
- Security
- Backup
- Recovery
Concurrency Control
- Ensures that one user’s actions do not impact another user’s actions.
Implicit Locks
- Issued automatically by the DBMS based on an activity.
Explicit Locks
- Issued by users requesting exclusive rights to the data.
Two-Phased Locking
- Lets locks be obtained and released as they are needed.
- A growing phase, when the transaction continues to request additional locks
- A shrinking phase, when the transaction begins to release the locks
Deadlock
- When two transactions may indefinitely wait on each another to release resources
Consistent Transactions (ACID)
–Atomic
–Consistent
–Isolated
–Durable
Database Security Guidelines
- Run the DBMS behind a firewall
- Apply the latest operating system and DBMS service packs and patches
- Limit DBMS functionality to needed features
- Protect the computer that runs the DBMS
- Manage accounts and passwords
Rollback
–Log files save activities in sequence order.
–It is possible to undo activities in reverse order that they were originally executed.
–This is performed to correct/undo erroneous or malicious transaction(s) after a database is recovered from a full backup.
Rollforward
–Activities recorded in the log files may be replayed.
–In doing so, all activities are re-applied to the database.
–This procedure is used to resynchronize restored database data by adding transactions to the last full backup.
Atomic
- One in which all of the database actions occur or none of them do.
Consistent
- No other transactions are permitted on the records until the current transaction finishes
Isolation
- Within multiuser environments, different transactions may be operating on the same data
Durable
- One in which all committed changes are permanent.
Database Security
- Strives to ensure that only authenticated users perform authorized activities
Internet Application Processing Environment
-Internet Application Processing is more complicated than traditional application processing.
-Specifically, with Internet Application Processing
–The network becomes an integral part of the application.
Application Programming Interface (API)
–An API is a collection of objects, methods and properties for executing DBMS functions from program code.
–Each DBMS has its own API, and APIs vary from one DBMS product to another.
N-Tier Architecture
-Tiers refer to the number of computers involved in the Web database application.
–The workstation with Web browser is the first tier.
–Two-tier architecturemeans that the Web server and the DBMS are on the same server.
–Three-tier architecturemeans that the Web server and the DBMS are on separate servers.
