copyright © 2003 by prentice hall module 4 database management systems 1.what is a database? data...

Copyright © 2003 by Prentice HallCopyright © 2003 by Prentice Hall

Module 4Module 4Database Management SystemsDatabase Management Systems

1. What is a database?Data hierarchy and data organizationField, record, file, databases, data warehouse

2. Database modelsRelational databases, Hierarchical, Network, Object-orientedData integrity

3. Database management systems and database featuresCreating a database, Data dictionary, Data maintenance, Data retrieval: Queries, Reports, Concurrency control, Security, Backup and recovery

4. Using databaseCreating the database, Determining the table structure, Setting up the file structure, Entering the data, Using the database

22Module 4: databaseModule 4: database

The Hierarchy of DataThe Hierarchy of DataField – The smallest meaningful unit of dataField – The smallest meaningful unit of data

E.g. LAST NAME, FIRST NAME, STREET, CITY, STATE, ZIPCODEE.g. LAST NAME, FIRST NAME, STREET, CITY, STATE, ZIPCODE

Record – the set of fields containing dataRecord – the set of fields containing data E.g. AKERS, TED, 4302 LEMON AVE. OAKLAND, CA, 94709E.g. AKERS, TED, 4302 LEMON AVE. OAKLAND, CA, 94709

File – a collection of related recordsFile – a collection of related records


Files and DatabasesFiles and Databases

Traditionally, information systems were Traditionally, information systems were developed with a file processing approachdeveloped with a file processing approach

A database is an organized collection of A database is an organized collection of information or data.information or data.

Database approach creates separate files Database approach creates separate files for each entityfor each entity


File System ApproachFile System Approach

Each application had its own fileEach application had its own file Data was not shared among applicationsData was not shared among applications

Resulted in a great deal of data redundancy, Resulted in a great deal of data redundancy, the repetition of the same data valuesthe repetition of the same data values Increased the risk of inaccurate dataIncreased the risk of inaccurate data Increased the amount of storage space neededIncreased the amount of storage space needed


The Database ApproachThe Database Approach

A collection of related A collection of related tablestables In database technology, a In database technology, a

file is called a tablefile is called a table Each entity is stored in a Each entity is stored in a

separate tableseparate table Tables are linked by a Tables are linked by a

relationship between relationship between primary and foreign keysprimary and foreign keys


Primary KeyPrimary Key

A field that uniquely identifies a recordA field that uniquely identifies a record SalesID can be a primary key for the SalesID can be a primary key for the

Salesperson tableSalesperson table Once a SalesID appears in the table, no other Once a SalesID appears in the table, no other

salesperson can have that IDsalesperson can have that ID


Foreign KeyForeign Key

A field in one table that is a primary key in A field in one table that is a primary key in another tableanother table SalesID can be used in the Customer table to SalesID can be used in the Customer table to

identify the salesperson who serves that identify the salesperson who serves that customercustomer

The same SalesID can appear in many The same SalesID can appear in many customer records (a salesperson can serve customer records (a salesperson can serve many customers)many customers)


Database ModelsDatabase Models

Define the way a database organizes dataDefine the way a database organizes data

Four main modelsFour main models RelationalRelational HierarchicalHierarchical NetworkNetwork Object-orientedObject-oriented


Relational ModelRelational Model

Data organized in table Data organized in table formatformat Columns represent fieldsColumns represent fields Rows represent recordsRows represent records

Tables related by Tables related by primary/foreign key primary/foreign key relationshiprelationshipMost current database Most current database development uses this development uses this modelmodel


Hierarchical ModelHierarchical Model

Arranges data in hierarchical “parent-child” Arranges data in hierarchical “parent-child” relationship, i.e. in a tree structure:relationship, i.e. in a tree structure: Each parent record can have many child Each parent record can have many child

recordsrecords Each child record has only one parent recordEach child record has only one parent record

Complex and inflexibleComplex and inflexible


Network ModelNetwork Model

Arranges data in complex network of Arranges data in complex network of “parent-child” relationships“parent-child” relationships Each parent record can have many child Each parent record can have many child

recordsrecords Each child record can have many parent Each child record can have many parent

recordsrecords

Complex and inflexibleComplex and inflexible


Object-Oriented ModelObject-Oriented Model

Designed to deal with complex data typesDesigned to deal with complex data types

Focuses on the objectFocuses on the object An object represents an entityAn object represents an entity Represents data about that entity and the Represents data about that entity and the

types of operations that change that entitytypes of operations that change that entity


Data IntegrityData Integrity

Refers to the degree to which data is Refers to the degree to which data is accurate and reliableaccurate and reliable

Integrity constraints – rules all data must Integrity constraints – rules all data must followfollow If integrity constraints are not followed, the If integrity constraints are not followed, the

data is unreliabledata is unreliable


Integrity ConstraintsIntegrity Constraints

Define acceptable values for a fieldDefine acceptable values for a field For example, the value of a month cannot be For example, the value of a month cannot be

greater than 12greater than 12

Primary keys cannot be duplicatedPrimary keys cannot be duplicatedForeign keys cannot be used unless they Foreign keys cannot be used unless they exist as a primary keyexist as a primary key A SalesID that is used in the customer table A SalesID that is used in the customer table

must exist as a primary key in the must exist as a primary key in the salesperson tablesalesperson table


Database ManagementDatabase ManagementDatabase Management System (DBMS) – A Database Management System (DBMS) – A software allows you to create a database and software allows you to create a database and enter, modify, and retrieve data as neededenter, modify, and retrieve data as needed

Available at all levelsAvailable at all levels Sophisticated systems for a mainframe environment. Sophisticated systems for a mainframe environment.

E.g. Oracle, IBM DB2E.g. Oracle, IBM DB2 Inexpensive, easy-to-use personal computer systems. Inexpensive, easy-to-use personal computer systems.

MySql, MS Access, MS SQL Server DatabaseMySql, MS Access, MS SQL Server Database

Access through WebAccess through Web


Database FeaturesDatabase Features

Data dictionaryData dictionary

Data maintenanceData maintenance

Data retrievalData retrieval

Concurrency controlConcurrency control

SecuritySecurity

Backup and recoveryBackup and recovery


Data DictionaryData Dictionary

Stores data about the tables and fields Stores data about the tables and fields within the databasewithin the database For each table, it stores the table name and For each table, it stores the table name and

relationships to other tablesrelationships to other tables For each field, it records information about For each field, it records information about

that fieldthat field

Also called a catalogAlso called a catalog


Data MaintenanceData Maintenance

Three basic operationsThree basic operations Adding new dataAdding new data Modifying existing dataModifying existing data Deleting dataDeleting data

Two methods for performing operationsTwo methods for performing operations Interacting directly with the DBMSInteracting directly with the DBMS

working on tableworking on table Using programs to access the data using special Using programs to access the data using special

commandscommandse.g., working on database forms, web formse.g., working on database forms, web forms


Data RetrievalData Retrieval

Involves extracting the desired dataInvolves extracting the desired data

Two primary forms of data retrievalTwo primary forms of data retrieval QueriesQueries ReportsReports


QueriesQueries

You present a set of criteria; the DBMS You present a set of criteria; the DBMS selects matching data from the databaseselects matching data from the database

Use a query languageUse a query language Structured Query Language (SQL) is Structured Query Language (SQL) is

supported by most relational databasessupported by most relational databases Query-by-example (QBE) uses a graphical Query-by-example (QBE) uses a graphical

interface to generate the SQLinterface to generate the SQL Display results in a table-like gridDisplay results in a table-like grid


SQLSQL

Uses English-like statementsUses English-like statements SELECT specifies the field(s) to be selectedSELECT specifies the field(s) to be selected FROM specifies the table(s) containing the fieldsFROM specifies the table(s) containing the fields JOIN specifies the relationship between tablesJOIN specifies the relationship between tables WHERE specifies the criteria that must be metWHERE specifies the criteria that must be met


Query by ExampleQuery by Example

User adds tables to queryUser adds tables to query User drags fields down into User drags fields down into

grid at bottom of windowgrid at bottom of window Criteria can be added by Criteria can be added by

typing them in the Criteria typing them in the Criteria row of the gridrow of the grid

Use can execute the Use can execute the query by clicking an icon query by clicking an icon on a toolbaron a toolbar


Query ResultsQuery Results

Retrieved records are displayed as rowsRetrieved records are displayed as rows

Fields specified in the select clause are Fields specified in the select clause are displayed as columnsdisplayed as columns


ReportsReports

Provide a formatted Provide a formatted presentation of data from the presentation of data from the databasedatabase Allow you to group reports and Allow you to group reports and

total by group, if desiredtotal by group, if desired Normally contain more data than Normally contain more data than

queriesqueries

Reports are designed with a Reports are designed with a report generatorreport generator


Concurrency ControlConcurrency Control

Manages simultaneous database usersManages simultaneous database users If several users tried to update the same If several users tried to update the same

record at the same time, updates might not be record at the same time, updates might not be processed correctlyprocessed correctly

Employs a record-locking schemeEmploys a record-locking scheme Once a user accesses a record, it is locked Once a user accesses a record, it is locked

until the first update is completeuntil the first update is complete


SecuritySecurity

Many security features are built into most DBMS Many security features are built into most DBMS softwaresoftware Users can be required to enter a user ID and Users can be required to enter a user ID and

passwordpassword Each user ID may have different access to the dataEach user ID may have different access to the data

Read-only – permits the user to look at the data but not Read-only – permits the user to look at the data but not change itchange it

Update – permits the user to make changes to the dataUpdate – permits the user to make changes to the data

No privileges – user can not read or update the dataNo privileges – user can not read or update the data


Backup and RecoveryBackup and Recovery

Backup – a copy of the databaseBackup – a copy of the database Backups should be made periodicallyBackups should be made periodically

Recovery – replaces a damaged database Recovery – replaces a damaged database with the good backupwith the good backup


Creating and Using a DatabaseCreating and Using a Database

Creating the databaseCreating the database

Determining the table structureDetermining the table structure

Setting up the file structureSetting up the file structure

Entering the dataEntering the data

Using the databaseUsing the database


Creating the DatabaseCreating the Database

Consider your needsConsider your needs Reports you will needReports you will need Inquiries you will want to makeInquiries you will want to make


Determining the Table StructureDetermining the Table Structure

Sketch the table structure – Sketch the table structure – what kind of data is needed in what kind of data is needed in each columneach columnDetermine characteristics of Determine characteristics of fieldfield

Field nameField nameEach field must have a unique field Each field must have a unique field namename

Field typeField type Field widthField width

The maximum number of The maximum number of characters, including decimal characters, including decimal placesplaces


Field TypesField Types

Character fields – contain descriptive dataCharacter fields – contain descriptive data

Numeric fields – contain numbers used for Numeric fields – contain numbers used for calculationcalculation Specify the number of decimal placesSpecify the number of decimal places

Date fieldsDate fields

Logical fields – keep track of true/false or Logical fields – keep track of true/false or yes/no conditionsyes/no conditions


Setting Up the File StructureSetting Up the File Structure

Design the structure for Design the structure for each tableeach table Define the tableDefine the table Define each field in the Define each field in the

tabletable Define primary keyDefine primary key

Set up the table in design Set up the table in design viewview


Entering the DataEntering the Data

Enter data into the tables in Enter data into the tables in datasheet viewdatasheet view

Enter data into the tables by Enter data into the tables by using a graphical formusing a graphical form


Using the DatabaseUsing the Database

Many operations can be performed to view Many operations can be performed to view and modify the dataand modify the data List the recordsList the records List specific fieldsList specific fields Make a queryMake a query Modify the dataModify the data


List the RecordsList the Records

Displays the table in Displays the table in datasheet viewdatasheet view Displays all rows and all Displays all rows and all

columnscolumns

Can be displayed on Can be displayed on monitor or printedmonitor or printed


List Specific FieldsList Specific Fields

Displays only the fields you Displays only the fields you want to viewwant to view

Use SQL to select the desired Use SQL to select the desired fieldsfields


QueryQuery

Use SQL or query-by-Use SQL or query-by-example to produce a example to produce a queryquery Use relational operator to Use relational operator to

define criteriadefine criteria Can be displayed on monitor Can be displayed on monitor

or printedor printed Can be formatted into a Can be formatted into a

reportreport


Modify the DataModify the Data

Add new recordsAdd new records

Update (make changes to) an existing Update (make changes to) an existing recordrecord

Delete recordsDelete records

copyright © 2003 by prentice hall module 4 database management systems 1.what is a database? data...

Documents