Database
A collection of organized data that allows access, retrieval, and use of data.
Database Management System (DBMS)
Software through which
... [Show More] users and application programs interact with a database
Character
Any written or printed letter, number, or symbol
Field
Group of related characters. Ex. Customer Name. Corresponds to Column in physical database
Record
a collection of fields about 1 customer (name, address, city, state, etc.) Corresponds to row in a physical database.
File
A group of records about the same type of entity (customers or inventory items)
DBMS Functionality
*Data Storage: Manage the physical structure of the database.
*Security: control user access and privileges.
*Multiuser access: Manage concurrent data access.
*Backup: enable recovery options for database failures.
*Data access language: provide a language that allows database access.
*Data integrity: Enable constraints or checks on data.
*Data dictionary: maintain information about database structure.
Systems Development Life Cycle (SDLC)
1. Systems investigation: Understanding the problem
2. Systems analysis: Understanding the solution to the previously identified problem
3. Systems design: Defining the logical and physical components.
4. Systems implementation: Creating the system
5. Systems integration and testing: Placing the system into production.
6. Systems deployment: Placing the system into production.
7. Systems maintenance and review: Evaluating the implemented system.
Entity
Any person, place, or thing with characteristics or attributes that will be included in the system.
Entity-Relationship Model (E-R Model)
Used to depict the relationship that exists among entities
E-R Model Relationships
One-to-one
One-to-many
Many-to-many
One-to-one Relationship
Each occurrence of data in one entity is represented by only one occurrence of data in the other entity. Ex. Each individual has just one SSN and each SSN is assigned to just one person.
One-to-many Relationship
Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity. Ex. A class has only one instructor, but each instructor can teach many classes.
Many-to-many Relationship
Data can have multiple occurrences in both entities. Ex. A student can take many classes, and each class is composed of many students. Cannot be included in the physical database.
Database Normalization
Determines required tables and columns for each table.
Multistep process.
Used to reduce or control data redundancy.
Data redundancy
When the same data exists in more than one place in a database.
Data anomalies
refers to data inconsistencies
Unnormalized Data
contains repeating groups in the Author column in the BOOKS table
First-Normal Form (1NF)
A relation that has a primary key and in which there are no repeating groups.
Composite Primary Key
More than one column is required to uniquely identify a row
Can lead to partial dependency--a column is only dependent on a portion of the primary key.
Second-Normal Form (2NF)
The second stage in the normalization process in which a relation is in 1NF and there are no partial dependencies (dependencies in only part of the primary key).
Third-Normal Form (3NF)
level of normalization where it is 2NF and contains no transitive dependencies
Relating tables within the database
Once tables are normalized, make certain tables are linked.
Tables are linked through a common field(existing in both tables).
A common field is usually a primary key in one table and a foreign key("many" side of one-to-many) in the other table.
Lookup Table
Common description for the table referenced in a foreign key relationship.
Structured Query Language (SQL)
Data sublanguage
Used to:
Create or modify tables
Add data to tables
Edit data in tables
Retrieve data from tables
ANSI and ISO standards
Primary Key
A field (or group of fields) that uniquely identifies a given entity in a table
Foreign Key
A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables
Common Field
used to join data contained in two or more tables [Show Less]