Studied by 12 people
Database System
computerized record keeping system that consists of related data
Database System Components
Users, Hardware, Software
User Support
Supports single or many users
Data is Integrated - unify distinct files while eliminating redundancy
Data is shared - different users will have different access and views to the same data
Database Administrators
create the data base and incorporate technical controls to enforce decisions made by the DBA
Why Database - Data Dictionary
stores definition of data and relationships, changes are automatically recorded
Why Database - multiaccess user support
Shared Data between users, multiple users can access the database at the same time without compromising integrity
Why Database - Reduced redundancy and inconsistency
Inconsistent data happens when one redundant data has been updated and the other has not
Why database - Transaction Support
Several updates in one transaction guarantees all or none are updated
Why Database - Data integrity support
ensures that the data in the database is accurate, reduce redundancy, increase consistency
Why Database - security
only means of access to database is through proper channels, promotes data privacy
Why database - backup/recovery
recovery after possible failures
Data Independence
changing info in one place without changing it in another
Changes in application programs without changing the structure of the underlying data
Database can grow without impairing existing applications
Disadvantages of the DBMS
Increased Costs: hardware and software costs, hiring experts
Management Complexity: different types of technology is harder to manage
Vendor Dependence: reluctantly to change the system once it has been established
Frequent Update/Replacement Styles: costs incurred to update and train people
Conceptual Schema Design - Stage 1
Choice of model: User requirements and real world concepts should go into design
Conceptual Schema Design - Stage 2
Normalization: Reduces complexity and avoids redundancy by adjusting diagrams
Conceptual Schema Design - Stage 3
Optimization: Promotes good performance of the database
Entities
real world entity that has an independent existence, Represented by a table and attributes
Strong Entity
exist on thier own and do not rely on others
Weak Entity
existence depends on the existence of another strong entity
Relationships
associates entities with one another
Mapping Constraints - One-to-One
manager can manage only one department but each department can only have one manager
Mapping Constraints - One-to-Many
Department can have several employees but each employee can only work in one department
Mapping Constraints - Many-to-Many
A supplier can supply parts to several jobs a jobs can receive parts for several suppliers
Total Participation
Relationship is total - (indicated by double lines):
EX: every department must be managed by a manager
Partial Participation
Relationship is partial between two entities (indicated by a single line):
EX: not every employee manages a department
Simple/Atomic Attributes
Not divisible (ex. Part #, weight)
Composite Attributes
Consists of several simple attributes (ex. Address)
Single valued attributes
unique value, not shared among data entries (ex. SIN#)
Multi valued attributes
attributes that can be shared among data (ex. College majors, skills)
Stored Attribute
are stored in the database (ex. Date of birth)
Derived Attribute
can be derived from stored information (ex. Age)
Key Attribute
unique attribute that is distinct for each individual entity instance and can be used to identify an entity
Candidate Key
minimal subset attributes that uniquely identifies an entity (ex. Employee #)
Can be specified using the unique clause
Primary key
candidate key chosen by designer to access each entity
Only one primary key per table
Can be specified after the variable type or before with the values that make up the key
Null Values
Represents not applicable, unknown missing information or not known information
Database Schema
created in the database design phase to describe the database and not expected to change
Database Instance
data in the database at a particular moment in time
Levels of Database Architecture (ANSI/SPRAC) - Internal Level
shows how data is stored inside the system such as file organization and access paths
Levels of Database Architecture (ANSI/SPRAC) - Conceptual Level
deals with the modeling of the whole database, conceptual schema is defined
Levels of Database Architecture (ANSI/SPRAC) - External Level
models user oriented description of parts of the database, views for users
Logical Data Independence
(external/conceptual): ability to modify conceptual schema without changing external views and application programs
Physical Data Independence
(internal/conceptual): modify internal or physical schema without changing conceptual or view level schema or application programs
Data Definition Language (DDL)
For defining schemas at various levels
Used by database designers and administrators
Data Manipulation Language (DML)
Used to construct and use the database
Used by end users for insertion, deletion, updates, retrievals
Subclass
allows sub groupings of entities
Members of a subclass inherit all the attributes of the superclass
Each sub class has its own attributes along with inherited attributes
Superclass
main type of the sub grouping
Specialization
Process of defining a et of subclasses of an entity type
Based on some distinguishing characteristic of the entity type
Multiple specializations can be defined on a single entity type
Specialization - Disjointness Constraint
Disjoint: an entity can be a member of at most one of the subclasses of the specialization
Overlapping: the same entity can be a member of more than one subclass of the specialization
Specialization - Completeness Constraint
Total: every entity in superclass must be a member of some subclass
Partial: an entity might not belong to any of the subclasses in the specialization
Generalization
Result of taking the union of two of more lower level entity types to produce a higher level entity type
Aggregation
Abstraction through which relationships are treated as higher level entities
Create new higher level entity called assignment to treat as the new entity
Relational Model
Uses tables called relations to represent a collection of related data values
Rows are called tuples
Columns are called attributes
Number of attributes (# of columns) is called the degree/arity
Relational Model - Domain
data type describing the values that can appear in each column
Domain is a set of atomic values that are indivisible
Super Key
key attributes and other attributes of a relation
Allows you to be more specific to find a record
Relations
values that can be read but not updated by definition
Relvars
variables that can be read and updated by definition
Optimization
system component that determines how to implement user requests
Catalog
set of system relvars that contain descriptors regarding various objects that are of interest to the system itself
Transaction
logical unit of work involving several operations that begins by begin transaction and terminates normally or abnormally
Atomicity
Transactions are guaranteed either to execute or not execute at all
Durability
once a transaction successfully commits its updates are guaranteed to be applied to the database even if the system fails
Isolation
database updates by given transactions are kept hidden from all distinct transactions unless the first transactions are successfully committed
Serializability
interleaved execution of concurrent transaction is guaranteed to produce the same result as executing the same transactions in an unspecified order
Commit (normal Termination)
operation that signals the end of a successful transaction
Rollback (abnormal termination)
operation that signals the unsuccessful end of transaction
SQL
Structural Query Language
Non procedural Language - no loops, conditionals or functions
Combine with other programming languages for more functionality
SQL Data Types
Numeric
Integer and floating point numbers
Integer - size to 40 digits, 4 bytes in DB2
Small int - smaller storage space, 2 bytes in DB2
Fractional Numbers - specify scale(default) and size or none
Character String
Fixed: CHAR(size) specified, reserved fixed amount in memory
VARCHAR(size), does not reserve max space
Date / Time
YYYY-MM-DD for dates
HH.MM.SS for times
Foreign Key
Combination of columns in one relationship that references primary key attributes of a second relationship
Uses the keyword references
Default Value
specifies a value to when no value is given
Check Conditions
Ensures that every row in the table satisfies a set condition
Condition can be any valid expression that evaluates to true or false
Referential Integrity Actions
Set Default - the attribute value is set to default value
Set Null - attribute value is set to null
Cascade - updates are propagated, each attribute value is updated
No Action
Alter Table
to update and modify an existing table by adding/update/delete an existing column
Add/Delete column by using ADD command
Add column at any time if not null is not specified
Modify column definition using MODIFY command
Can increase character column width, number of decimal place and number of digits at any time
Drop Table
Use DROP TABLE command at the bottom of the dependency chain to properly delete
Use CASCADE CONSTRAINTS to drop all constraints that refer to professor table
INSERT
Insert - adds a new row to the selected table
Insert by explicitly or implicitly specify a query
Insert results of a query into a table
DELETE
UPDATE
Views
Virtual Table opposed to a base table - specific view of a database group for users
Characteristics of Views
View is not created at definition, instead materializes on the first use
Do not need to create a physical table --> derive table from implementation
View tables are updated automatically if the base table info is updated
Purpose of Views
Performance optimization
Used when subqueries are repeated through existing queries
Security
Users only can be provided with data that is needed and not all of it
Provide read only access
Creating Views
SELECT
Use * to list all the attributes
If Using * the WHERE clause is optional
Can pass strings and conditional statements through
ORDER BY
Order by Descending (DESC) or Ascending (ASC)
Ascending order is the default
Specify ordering with multiple different attributes
AS
Serves as an Alias to rename an attribute to be more specific
DISTINCT
Eliminates duplicate values
LOGICAL OPERATORS
AND, OR, NOT can be used to combine simple conditionals
Precedence: NOT --> AND --> OR
JOINING TABLES
Same attribute names in different tables
Use . To specify the attributes from the tables
EXISTS
Used to test he existence of any record subquery
Returns true of conditions are met
UNION
Combines the result set of two or more select statements
Both tables must have the same number of columns and be similar types
MINUS/NOT IN
Not conditions for entries
Minus does not work for MySQL, you must use NOT IN
LIKE
Allows you to pull entries that are similar to a given condition
Can be used in place of an equals sign to a string
Case sensitive matching
% will match 0 or more characters
_ will match a single character
BETWEEN
Simple shorthand for a range restriction instead of using relational operators
Aggregate Functions
Take a multiset of values and return a single value
Average, Minimum, Maximum, Total/Sum and Count
GROUP BY
Group by a certain table column that have the same value
HAVING
List records that have a certain condition
Can be combined with conditionals to create constraints
Can be used with nested queries
Problems with Poor database design
Redundancy - duplicate data
Update anomalies - Direct consequence of redundancy
Insertion Anomalies - Cannot insert new data unless there is a record that applies to it
Deletion Anomalies - Lose important information from deletion
Decomposition
Split the relation into two or more relations
Use join to get back to the original information
1NF Normalization
A relation is said to be in 1NF if it contains only atomic, simple and scalar data
This data is usually comma delimited and contains multiple values associated with one record
Full Functional Dependency
FD between X -> Y is full if the removal of any attribute from X means that the dependency does not hold
Partial Dependency
FD between X -> Y is partial if there is some attribute AeX that can be removed from X and the dependency will still hold
2NF Normalization
A relational schema is in 2NF if it is at first in 1NF and every non key attribute is fully functionally dependent on the primary key / candidate key of the relation
Note 
Flashcard (104)