Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.



Intro to SQL Databases

Dave Hagan

on 9 May 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of DatabaseIntroduction

Database Overview Database Definition A Database contains:

Known facts that can be recorded
That are related to each other
And have meaning A Database represents:

Some aspect of the “real world”
The universe of discourse
Changes to the “real world”
These changes are reflected in the database A Database:

Is designed for a specific purpose
Has an intended group of users
Has some target application Types of Database Flat files
Indexed/Hashed Files
Hierarchic – IMS DB
Codasyl – IDMS
Relational (RDBMS) – Oracle
Object (ODBMS) – Db4o
No-Sql RDF Triplestore Database Management System A DBMS (Database Management System)
Is a collection of programs that manage a Database
Is a general purpose software system
Facilitates Defining, Constructing and Manipulating
Handles access to data and exceptions Metadata Defines the Database(s) in the system catalogue

Records database structure, constraints and objects

Allows DBMS software to know database structure

Allows the DBMS to be a non specific system Schema The schema is a collection of database resources defined by the metadata - tables,indexes procedures triggers etc. There can be many schemas in a database and many databases managed by an rdbms SQL Structured Query Language Data Definition Language DDL is SQL used to create the database objects such as tables and views Data Manipulation Language DML
is the language used to query, update or delete the database objects - examples are:

select * from table
update table set variable = value
delete from table where condition
insert into table values Stored Procedures & Triggers Stored procedures are executed in the database reducing network traffic– triggers are executed when certain actions happen such as column values changing Transactions Transactions consist of groups of related updates – for instance deleting rows of data. If this deletion violates a foreign key constraint then the a rollback can be issued which will undo the changes until the beginning of the transaction otherwise if all is well a commit can be issued which will save the data and end the transaction. Row Locking Row locking can be used to prevent concurrent updating
Can cause problems such as "Deadly Embrace" Data Security and Integrity Backup Recovery Data Integrity or Consistency Access If there is a system crash the database rolls back to the last good synchronisation point and then rolls forward using the journal untill the latest good transaction. The database can also be recovered from a backup. Data can be dumped out of the database in the form of ddl. A journal system keeps track of all good transactions to be used in the event of a failure Data integrity is maintained by the use of constraints. There are several types of constraint:
Primary Key
Foreign Key
Not Null Data access is controlled by means of Roles defined within the database. These can have different levels of security or “Grants” so a user can have readonly access up to database creation rights. Database Users Database Administrators
In enterprise databases the database administrator manages the data and dbms – authorises access – coordinating and monitoring its use – acquires hardware & software resource. Handles security breach & poor performance (by tuning db)

Database Designers
Are responsible for how data is stored in the database - communicates with users – and produces design to meet requirements. Implements and tests the data structures. Would normally interact with users to produce views of dta that meets their needs.
Software Developers
Produce applications that use databases to store (persist) data

End Users
require access to database for querying, updating and reporting.The database exists for their use
System Administrators
Manage the infrastructure – hardware storage and operating systems that allow the dbms to run What is a Database A database consists of Data stored in an organised way.

A book could be considered to be a database. We can find information by opening the book and turning the pages one after another. If the book has an index then we can find information more quickly by going directly to a page

Generally in computer terms, a database is an organised collection of data that is maintained by a database management system (DBMS)

The dbms can maintain the data, can ensure that it is consistant, that we can create , destroy and amend data. The dbms can alter the structure of the data. Relational databases are made up of TABLES. Each table has a set of ROWS. Each row is made up of COLUMNS which contain data. The data type is determined by the column ATTRIBUTE, numeric character date etc.

The data in each table is logically grouped together. A table can represent an object such as a company. The columns would contain data relating to the company such as company number, company name, company address. Every table should have a PRIMARY KEY Column. This should be UNIQUE and should contain a value (NOT NULL). We can use the company number for this Relational Databases Relating Tables Together We may want to store data about employees in our database. If we knew haow many employees we had we could store them in the company table and create columns for the employee data.

Generally we do not know how many employees we have. The solution is to create an employee table where we can store employee data. We can use the company number to refer to a row in the company table. The column woud be called a FOREIGN KEY because it refers to a row on a different table by that tables PRIMARY KEY. Data can be stored in a database and retrieved by issuing commands to the Relational Database Management System. These commands are in the form of Structured Query Langauge (SQL) An introduction to database systems
Date, C. J

Fundamentals of database systems
By Elmasri, Ramez

Database systems : design, implementation, and management
By Rob, Peter

Relational database principles
By Ritchie, C Reading
Full transcript