Database Management Midterm

Database

its purpose is to help people keep track of things of interest to them

Tables

Data is stored here, they have rows and columns like a spreadsheet

instance

Each row in a table stores data about an occurrence or of the thing of interest

Data

facts and objects, for example, student names, email addresses, grade, images, sound etc.

Information

data processed in such a way it can increase the knowledge of the person who is using it

Transactional Data

• Data change

• Relational Database

Historical Data

• Read only data

• Periodically updated in a batch

• Data warehouse

relational database

stores data in tables. A table holds data about one and only one theme. stores data and relationships.

Redundancy and multiple themes create modification problems

• Update problem

• Deletion problem

• Insertion problem

Structured Query Language

is an international standard for creating, processing and querying databases and their tables

The four components of a database system are

• Users

• Database application

• Database Management system (DBMS)

• Database

The four components of a database system are:

Users, database application, database management system (DBMS), Database

Desktop DBMS

User to Database application, to database management system (DBMS), to database

A user of a database system will track

• Use a database application to track things

• Use forms to enter, read, delete and query data

• Produce reports

The database

a self describing collection of related records

Self-describing

the database itself contains the descriptions of its structure

Metadata

is data describing the structure of the database, e.g., the names of the columns and the tables to which they belong to, properties of tables and columns, etc

Database contents

• user data

• metadata

• indexes and other overhead data

• application metadata

Database management system (DBMS)

• it serves as an intermediary between database applications and the database

• The DBMS manages and controls database activities

• The DBMS creates, processes and administers the databases

Functions of a DBMS

• Create databases

• Create tables

• Create supporting structures like indexes

• Read database data

• Modify database data (insert, update, delete)

• Maintain database structures

• Enforce constraints (or rules)

• Control currency

• Provide security

• Perform backup and recovery

Referential integrity constraints

• the DBMS will enforce many constraints or rules

• it ensures that the values of a column in one table is valid based on the values in another table

Database application

is a set of computer programs that serves as an intermediary between the user and the DBMS

Functions of Database Applications

• Create and process forms

• Process user queries

• Create and process reports

• Execute application logic

• Control database applications

What do database Management systems contain?

they typically have:

• a few tables

• are simple in design

• involve only one computer

• support one user at a time

Enterprise-Class (Organizational) DBMS

they typically:

• Support several user simultaneously

• include more than one application

• involve multiple computers

• Are complex in design

• Have many tables

• Have many databases

What is a NoSQL Database?

• NoSQL database= non-relational databasse

• Web 2.0 Applications: Facebook, Twitter, Pinterest

• Apache Software Foundation Cassandra: Facebook, Twitter

Commercial DBMS products

• EX Desktop DBMS products: Microsoft Access

• EX Organizational DBMS products: Microsoft’s SQL server, Oracle’s oracle, Oracle’s MySQL

• IBM’s DB2

Entity

is something of importance to a user that needs to be represented in a database. It represents one theme or topic. It is restricted to a thing that can be represented by a single table.

Relation

is a two dimensional table consisting of rows and columns that has specific characteristics

Characteristics of a relation

1. Rows contain data about an entity

2. Columns contain data about attributes of the entity

3. Cells of the table hold a single value

4. All entries in a column are of the same kind

5. Each column has a unique name

6. The order of the columns is unimportant

7. The order of the rows is unimportant

8. No two rows may be identical

Synonyms for Table

File, Relation

Synonyms for Row

Record, Tuple

Synonyms for Column

Field, Attribute

Key

a key is one or more columns of a relation that is used to identify a row

Uniqueness of Keys

Data value is unique for each row. Consequently, the key will uniquely identify a row. For example, EmployeeNumber

A composite key

is a key that contains two or more attributes. For a key to be unique is must become a

Candidate key

the key that uniquely identifies each row in a relation. It is a to become the primary key.

Primary key

is the candidate key chosen to be the key for a relation. E.g.: EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Email, Phone)

Surrogate Key

is a unique numeric value added to a relation to serve as a primary key

• has no meaning to users and is usually hidden on forms, queries and reports

• is often used in place of a composite primary key

• EX: CLASS_PROFFESOR (ClassName, Section, Term, Proffesor), CLASS_PROFFESOR (AssignmentID, ClassName, Section, Term, Proffesor)

Relationships between tables

A table may be related to other tables

• For example: An employee works in a department, a manager controls a project

A foreign key

To preserve relationships, you may need to create a

• A is a primary key from one table placed into another table

Referential Integrity

states that every value of the foreign key must match a value of the primary key in another table.

• For example: if EmpID = 4 in EMPLOYEE has a DeptID = 7 (a foreign key), a Department with DeptID = 7 must exist in DEPARTMENT

• The primary key value must be exist before the foreign key value is entered

Null value

is a missing value in a cell in a relation

Problems of Null values

A null is often ambiguous. It could mean…

• The column value is not appropriate for the specific row

• The column value is not decided

• The column value is unknown

• Each may have entirely different implications

Functional Dependency

A relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table

• Illustration… (UnitPrice,Quantity) —> ExtendedPrice

Determinants

The attribute (or attributes) that we use as the starting point (the variable on the left side of the equation) is called a determinant

• Ex: (UnitPrice,Quantity) = Determinant

A candidate/ primary key

of a relation will functionally determine all other attributes in a relation

Normalization

A process of analyzing a relation to ensure that it is well-formed (well-structured)

• More specifically, if a relation is normalized (well-formed), rows can be inserted, deleted or modified without creating modification problems (anomalies)

Normalization principles

• for a relation to be considered well formed, every determinant must be a candidate key

• Any relation that is not well-formed should be broken into two or more relations that are well formed

Normalization to BCNF

• a relation is considered normalized when: Every determinant is a candidate key

• This is Boyce-Codd Normal Form (BCNF)

Normalization Process (putting a relation into BCNF)

1. Identify all candidate keys of the relation

2. Identify all the functional dependencies in the relation

3. If there is a functional dependency that has a determinant that is not a candidate key: A. Place the columns of that functional dependency into a new relation. B. Make the determinant of that functional dependency the primary key of the new relation. C. Leave a copy of the determinant as a foreign key in the original relation. D. Create a referential integrity constraint between the original relation and the new relation

4. Repeat step 3 until every determinant of every relation is a candidate key

First Normal Form (1NF)

Each cell has only one value, and all entries in a column are of the same kind

Second Normal Form (2NF)

• Each table is in 1NF and all non-key attributes are determined by only the single attribute primary key of a single attribute

• No partial functional dependencies exist (some of non-key attributes are determined by a part of the composite primary key)

Third Normal Form (3NF)

• Each table is in 2NF and no non-key attributes are determined by another non-key attribute (no transitive dependencies)

Boyce Codd Normal Form (BCNF)

Each table is in 3NF and all determinants are candidate keys

Fourth Normal Form (4NF)

Each table is in BCNF and all multivalued dependencies have been moved to their own table

Other Normal Forms

• Fifth Normal Form (5NF)

• Domain/Key Normal Form (DK/NF)

Structured Query Language

• Acronym: SQL

• Originally developed by IBM as the SEQUEL language in the 1970s

• SQL-92 is an ANSI national standard adopted in 1992

• SQL 2011 is current standard

SQL defined

• SQL is not a programming language, but rather a data sublanguage

• SQL is compromised of:

• Data definition language (DDL): which is used to define database structures

• Data manipulation language (DML): Data definition and updating, data retrieval (Queries)

• SQL/Persistent Stored Modules (SQL/PSM): Procedural programming capabilities

• Transaction Control Language (TCL): control transaction behavior

• Data Control Language (DLC): Grant and revoke database permissions

SELECT

• is the best known SQL statement

• will retrieve information from the database that matches the specified criteria using the SELECT/FROM/WHERE framework

Query

• pulls information from one or more relations and creates (temporarily) a new relation

• This allows it to:

• Create a new creation

• Feed information to another one (as a sub query)

Asterisk

• To show all of the column values for the rows that match the specified criteria, use an _____ (*)

DISTINCT

This keyword may be added to the SELECT statement to inhibit duplicate rows from displaying

WHERE

This clause stipulates the matching criteria for the record that is to be displayed

AND

Representing an intersection of the data sets

OR

Representing a union of the data sets

IN

the column may equal to any of the values in the list

NOT IN

the column must not be equal to all the values in the list

BETWEEN

• SQL provides a keyword that allows a user to specify a minimum and maximum value on one line

The WHERE clause operators may include

• Equals to “=”

• Not Equal to “<>”

• Greater than “>”

• Less than “<“

• Greater than or Equal to “>=”

• Less than or Equal to “<=”

The SQL LIKE keyword

allows searches on partial data values

LIKE

can be paired with wildcards to find rows matching a string value

Multiple character wildcard character

is a percent sign (%)

Single character wildcard character

is an underscore (_)

the ORDER BY clause

Query results may be sorted using the

COUNT

counts the number of rows that match the specified criteria

MIN

Finds the minimum value for a specific column for those rows matching the criteria

MAX

Finds the maximum value for a specific column for those rows matching the criteria

SUM

calculates the sum for a specific column for those rows matching the criteria

AVG

Calculates the numerical average of a specific column for those rows matching the criteria

GROUP BY

Subtotals can be calculated using this clause

HAVING

This clause can be used to restrict which data is displayed

Subqueries

The result of a query is a relation. As a result, a query may feed another query.

Joins

Another way of combining data is by using a join

• Join (also called an Inner Join)

• Left Outer Join

• Right Outer Join

Outer join

this syntax can be used to obtain data that exists in one table without matching data in the other table

CREATE

to create database objects

ALTER

To modify the structure and/or characteristics of database objects

DROP

to delete database objects

Insert

will add a new row in a table

Update

will update the data in a table that matches the specified criteria

Delete

Will delete the data in a table that matches the specified criteria

CHECK

this constraint can be used to create sets of values that restrict the values that can be used in a column

• MSAccess doesn’t support this constraint

SQL View

its a virtual table created by a DBMS-stored SELECT statement which can combine access to data in multiple tables and even in other views

• MSAccess doesn’t support Views