DATABASE DEVELOPMENT

Overview

Definitions

A Database is a structured collection of related data (not necessarily electronic)

A Relational database is a database structure based on tables linked by common (key) fields.

A Database Management System (DBMS) is software for managing one or more databases and providing database functions such as backup and restore

SQL is the language relational databases use for all their functions including creating database objects, retrieving, manipulating, inserting data, and for administrative tasks

Definitions Continued

A client application is software that uses the database management system for its data manipulation needs

XML is a markup language that has become central to the transfer and manipulation of data between applications especially on the web.

Types of Databases

Flat files—comma delimited text files or spreadsheet programs like Excel Advantages: easy to understand and use Disadvantages: data redundancy, data integrity issues,

difficult to manage as it becomes larger

Hierarchical databases—structured like the file system in windows Advantages: fast and easy to navigate Disadvantages: data redundancy , difficulty comparing

different sets of data

Relational Databases

Relational databases were designed to solve problems in other types of databases Data Redundancy Data integrity Comparison of distinct sets of data

The relational database design principles are based on the mathematics of set theory.

In a well designed database any piece of data can be related to any other piece of data

Structure of Relational Databases

Relational databases consist of tables. Each table represents one distinct aspect of the data.

Tables consist of columns and rows.Columns contain the name of the data fieldRows contain the actual dataTables are related by means of a common

field usually the “key” field of one table repeated in a second table. (much more on this later)

Example

Department

Location Phone

ACC B2304 206.344.2001

HR B2305 206.344.2020EmployeeID Lastname Firstname Departmen

t

20155 Larson Sara HR

90221 Town Jason ACC

30301 Manning Patricia ACC

32002 Adams Tom HR

Disadvantages of Relational Databases

Relational database structure is complex. It is easy to make a bad database

Relational database require a lot of processing and can be slow (Not as much of a problem with modern machines)

DBMSs

Access MicrosoftMySQL MySQLDB2 IBMSQL Server MicrosoftOracle Oracle

SQL

Everything that can be done in a Relational database can be done in SQL

SQL is a 4th generation language. That means you code what you want to do not how.

SELECT LastName, FirstName, Phone, City

FROM Customers

WHERE City = ‘Seattle’

Client Applications

Customers, most managers and business users don’t want to work directly with the DBMS.

Client applications are designed to allow users to interact with the data in a more natural and convenient way.

Client applications can be written in a variety of languages such as PHP, Java, C++, C#, Visual Basic.Net and many others.

XML

Xml is a markup language which has rapidly become important for transferring data between database applications. (as well as for many other purposes)

XML is Operating System and Application Neutral.

As Text, it is safe and humanly readableMost DBMSs can export data as xml and import

xml files into the databasesSome DBMSs such as SQL Server can store xml

as a native data type

XML Example

<dataroot> <customer> <CustID>1</CustID> <CustLastName>Jordan</CustLastName> <CustFirstName>Mary</CustFirstName> <CustAddress>2002 South Mercer Street</CustAddress> <CustCity>Seattle</CustCity> <CustState>WA</CustState> <CustZipcode>98190 </CustZipcode> <CustPhone>2065558828</CustPhone> </customer> <customer> <CustID>2</CustID> <CustLastName>Danner</CustLastName> <CustFirstName>Thomas</CustFirstName> <CustAddress>100 Boardwalk South</CustAddress> <CustCity>Seattle</CustCity> <CustState>WA</CustState> <CustZipcode>98190 </CustZipcode> <CustPhone>2065551001</CustPhone> </customer> <customer> <CustID>3</CustID> <CustLastName>Terrance</CustLastName> <CustFirstName>Sarah</CustFirstName> <CustAddress>202 Rt 3</CustAddress> <CustCity>Bellevue</CustCity> <CustState>WA</CustState> <CustZipcode>98120 </CustZipcode> <CustPhone>3605550128</CustPhone> </customer> </dataroot>