Transcript Project: DatabasePP

What is a Database
By: Cristian Dubon
What is a Database?
• The definition of a database is
an ordered compilation of
records or information that is
accumulated in a computer
system.
• Databases are intended to
offer a structured system for
filing, sorting, managing, and
retrieving information
electronically.
Advancements In Databases
• The term “data base” started being used around 1964 by worker in
military information systems.
• The idea of shared applications and data by multiple users at once
was great for efficiency.
• Early database data structure classes worked off of punch cards.
• When magnetic tape was introduced it advanced the technology but
it wasn’t till the RAM-based data structures such as arrays, tables
and lists that database truly showed the effect it would have on
information storage and distribution.
• Most databases today are large hard drives, or servers, run by a
program called a database management system, that dictates how
information is filed, managed, sorted, and retrieved.
Types of Databases
• The hierarchical model database is a tree like structure that
branches into other folders, each child data has only one parent
data, the way most computer operating system display folders to
users. Each link is nested to keep data organized on the same level.
• The defining feature of a network model is that a record is stored
with a link to other records allowing the branches to overlap, here a
child data can have more than one parent data, working like a
shortcut on you desktop.
• These two models are not as flexible or fast in the retrieval of data
because the search follows a trickle down pattern.
Types of Databases
• The relational model is the most commonly used database model
and an exceptionally potent tool.
• The relational database model is based on a table that is organized
by rows, or records, and columns, or fields, with a special field
reserved for the key.
• The key field serves as the identifying marker for data, with the
possibility that a unique field can act as a primary key.
• A table can have many records and each record can have many
fields.
• This model is designed to allow data to be processed with eight
mathematical operations, that fall into two categories, set operations
and relational operations, as well as combining the operations.
• The set operations are union, difference, intersection and Cartesian
product, leaving the operations join, projection, selection and
division, as the relational operations.
Relational Databases
• The set operations act on sets of rows to produce a new set of rows,
determining which rows from the input appear in the output.
• The union and intersection operations do just as they would in math
when you say A union B or A intersection B. Union takes to two or
more tables with related fields and combines the tables together to
get a new table that consist of all rows with no duplicates entries.
• Intersection takes two or more related tables and extracts only the
rows that the tables have in common and outputs a new table.
• The different operation can extract all of the rows from the first table
that are not included in the second table producing a new table of
either A-B or B-A, depending on which rows are to be excluded from
the new table, remember the tables have to consist of the same
fields.
• The Cartesian product operation is a process that merges all rows in
the two tables, for example two tables of three rows resulted in a
table of nine rows. However unlike the other operation this operation
requires the fields are not related.
Relational Databases
• The relational operations work differently from the set operations.
• The projection operation can extract columns from one or more
tables.
• The selection operation can extract rows from one or more tables.
So, one operation act vertical on tables while the other acts
horizontally on tables.
• The join operation literally takes two tables that have at least one
field in common and merges the tables around that one common
field, outputting a well organized table that gives more data than
either table alone.
• The division operation extracts the rows from a table that match
values in the columns of the second table, but returns only the
columns that do not exist in the second table. Remember that the
new tables themselves can have operations used on them.1
Before Building A Database
•
•
•
•
•
Databases are built based on an
entity-relationship model in order to
understand the conditions that govern
the data you want to place in the
database and how it should be
arranged.
An entity is a real-world object or thing.
The relation must be found between
them its either
one-to-one, one-to-many, or many-tomany.
In a many-to-many relation, M objects
have a relation with N objects; the
number of associations between
entities is called cardinality.
From this an unnormalized form is
obtained, a table in which items that
appear more than once have not been
removed.
Before Building A Database
• This table is then divided into smaller, simpler tables and primary
and foreign key are decided on to allow interaction between tables.
• In a relational database, a value is called functionally dependent if
that value determines values in other columns.
• In the second normal form, the table is divided so that values in
other columns are functionally dependent on the primary key.
• In the third normal form, a table is divided so that a value is not
determined by any non-primary key.
• In a relational database, a value is called transitively dependent if
that value determines values in other columns indirectly, which is
part of functionally dependent operation.
• In the third normal form, the table is divided so that transitively
dependent values are removed.
Structured Query Language (SQL)
• This is done using a Structured Query Language (SQL)
also called sequel
• SQL’s has three distinct types of commands, data
definition language (DDL), data manipulation language
(DML), and data control Language (DCL).
• The DDL commands that SQL creates the framework of
a database, creates a table within a database, as well as
changing and deleting tables.
• The DML commands allow for manipulation of data, like
inserting, deleting, and updating data in tables. It also
has commands that allow you to search for data.
• The DCL commands offers the ability to control a
database so that no data conflicts do not happen as
multiply user enter data.
The SQL statement Syntax
•
•
•
•
•
•
•
•
•
CREATE TABLE. The syntax inside a table depends on the kind of database being used.
When a table is created the column names must be given, a primary key or foreign key is given to
each column.
Constraints such as amount or types of character to be in a certain row may have to be given to
prevent data conflict later on.
The INSERT, UPDATE, and DELETE statement do as they describe to data from tables made by
the CREATE TABLE statement, considering that any of the database’s constraints are not
violated.
Next is the view table that is viewed by a user of the database. It was created by extracting data
from the selected tables. This table is derived from these selected tables that are called base
tables.
Structured Query Language use basic data search functions in the form of query-type statements
as input.
To search for a certain set of tables, commands like select, from, where, group by, having, and so
on are used. In these statement comparison operators like less than (<), greater than (>), equal to
(=), not equal to (<>), less than or equal to (<=), greater than or equal to (>=), and between x and
y.
Logical operators such as like, and, or and not are used to create more complex comparison
statements.
There are also conditions that let you search for pattern matching using wildcard characters. Such
as * to search all, _ plus a letter before or after to retrieve matches with two characters that start or
end with a specific letter, % plus a letter before or after to retrieve matches with any number of
characters that start or end with a specific letter, and is null if you are looking for empty records.
Aggregate functions or set functions like maximum and minimum values, number of items, and
sum, are used to collect and output new tables.