CP363

computerized record keeping system that consists of related data

Users, Hardware, Software

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

create the data base and incorporate technical controls to enforce decisions made by the DBA

stores definition of data and relationships, changes are automatically recorded

Shared Data between users, multiple users can access the database at the same time without compromising integrity

Inconsistent data happens when one redundant data has been updated and the other has not

Several updates in one transaction guarantees all or none are updated

ensures that the data in the database is accurate, reduce redundancy, increase consistency

only means of access to database is through proper channels, promotes data privacy

recovery after possible failures

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

• 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

Choice of model: User requirements and real world concepts should go into design

Normalization: Reduces complexity and avoids redundancy by adjusting diagrams

Optimization: Promotes good performance of the database

real world entity that has an independent existence, Represented by a table and attributes

exist on thier own and do not rely on others

existence depends on the existence of another strong entity

associates entities with one another

manager can manage only one department but each department can only have one manager

Department can have several employees but each employee can only work in one department

A supplier can supply parts to several jobs a jobs can receive parts for several suppliers

Relationship is total - (indicated by double lines):

EX: every department must be managed by a manager

Relationship is partial between two entities (indicated by a single line):

EX: not every employee manages a department

Not divisible (ex. Part #, weight)

Consists of several simple attributes (ex. Address)

unique value, not shared among data entries (ex. SIN#)

attributes that can be shared among data (ex. College majors, skills)

are stored in the database (ex. Date of birth)

can be derived from stored information (ex. Age)

unique attribute that is distinct for each individual entity instance and can be used to identify an entity

minimal subset attributes that uniquely identifies an entity (ex. Employee #)

• Can be specified using the unique clause

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

Represents not applicable, unknown missing information or not known information

created in the database design phase to describe the database and not expected to change

data in the database at a particular moment in time

shows how data is stored inside the system such as file organization and access paths

deals with the modeling of the whole database, conceptual schema is defined

models user oriented description of parts of the database, views for users

(external/conceptual): ability to modify conceptual schema without changing external views and application programs

(internal/conceptual): modify internal or physical schema without changing conceptual or view level schema or application programs

• For defining schemas at various levels

• Used by database designers and administrators

• Used to construct and use the database

• Used by end users for insertion, deletion, updates, retrievals

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

main type of the sub grouping

• 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

• 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

• 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

Result of taking the union of two of more lower level entity types to produce a higher level entity type

Abstraction through which relationships are treated as higher level entities

• Create new higher level entity called assignment to treat as the new entity

• 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

data type describing the values that can appear in each column

• Domain is a set of atomic values that are indivisible

key attributes and other attributes of a relation

• Allows you to be more specific to find a record

values that can be read but not updated by definition

variables that can be read and updated by definition

system component that determines how to implement user requests

set of system relvars that contain descriptors regarding various objects that are of interest to the system itself

logical unit of work involving several operations that begins by begin transaction and terminates normally or abnormally

Transactions are guaranteed either to execute or not execute at all

once a transaction successfully commits its updates are guaranteed to be applied to the database even if the system fails

database updates by given transactions are kept hidden from all distinct transactions unless the first transactions are successfully committed

interleaved execution of concurrent transaction is guaranteed to produce the same result as executing the same transactions in an unspecified order

operation that signals the end of a successful transaction

operation that signals the unsuccessful end of transaction

Structural Query Language

• Non procedural Language - no loops, conditionals or functions

• Combine with other programming languages for more functionality

• 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

• Combination of columns in one relationship that references primary key attributes of a second relationship

• Uses the keyword references

specifies a value to when no value is given

• Ensures that every row in the table satisfies a set condition

• Condition can be any valid expression that evaluates to true or false

• 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

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

• 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 - adds a new row to the selected table

  • Insert  by explicitly or implicitly specify  a query

• Insert results of a query into a table

Virtual Table opposed to a base table - specific view of a database group for users

• 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

• 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

• Use * to list all the attributes

  • If Using * the WHERE clause is optional

• Can pass strings and conditional statements through

• Order by Descending (DESC) or Ascending (ASC)

• Ascending order is the default

• Specify ordering with multiple different attributes

• Serves as an Alias to rename an attribute to be more specific

• Eliminates duplicate values

• AND, OR, NOT can be used to combine simple conditionals

• Precedence: NOT --> AND --> OR

• Same attribute names in different tables

• Use . To specify the attributes from the tables

• Used to test he existence of any record  subquery

• Returns true of conditions are met

• Combines the result set of two or more select statements

• Both tables must have the same number of columns and be similar types

• Not conditions for entries

• Minus does not work for MySQL, you must use NOT IN

• 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

• Simple shorthand for a range restriction instead of using relational operators

• Take a multiset of values and return a single value

• Average, Minimum, Maximum, Total/Sum and Count

• Group by a certain table column that have the same value

• List records that have a certain condition

• Can be combined with conditionals to create constraints

• Can be used with nested queries

• 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

• Split the relation into two or more relations

• Use join to get back to the original information

• 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

• FD between X -> Y is full if the removal of any attribute from X means that the dependency does not hold

FD between X -> Y is partial if there is some attribute AeX that can be removed from X and the dependency will still hold

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