Chapter 9:

(1)

Computer Science: An Overview Twelfth Edition

by

J. Glenn Brookshear Dennis Brylow

Chapter 9:

Database Systems

(2)

Chapter 9: Database Systems

• 9.1 Database Fundamentals

• 9.2 The Relational Model

• 9.3 Object-Oriented Databases

• 9.4 Maintaining Database Integrity

• 9.5 Traditional File Structures

• 9.6 Data Mining

• 9.7 Social Impact of Database Technology

(3)

Database

A collection of data that is multidimensional

in the sense that internal links between its

entries make the information accessible

from a variety of perspectives

(4)

Figure 9.1 A file versus a database

organization

(5)

Figure 9.2 The conceptual layers of a

database implementation

(6)

Schemas

• Schema: A description of the structure of an entire database, used by database

software to maintain the database

• Subschema: A description of only that portion of the database pertinent to a

particular user’s needs, used to prevent

sensitive data from being accessed by

unauthorized personnel

(7)

Database Management Systems

• Database Management System (DBMS): A software layer that manipulates a database in response to requests from applications

• Distributed Database: A database stored on multiple machines

– DBMS will mask this organizational detail from its users

• Data independence: The ability to change the

organization of a database without changing the

application software that uses it

(8)

Database Models

• Database model: A conceptual view of a database

– Relational database model

– Object-oriented database model

(9)

Relational Database Model

• Relation: A rectangular table

– Attribute: A column in the table

– Tuple: A row in the table

(10)

Figure 9.3 A relation containing

employee information

(11)

Relational Design

• Avoid multiple concepts within one relation

– Can lead to redundant data

– Deleting a tuple could also delete necessary

but unrelated information

(12)

Improving a Relational Design

• Decomposition: Dividing the columns of a relation into two or more relations,

duplicating those columns necessary to maintain relationships

– Lossless or nonloss decomposition: A

“correct” decomposition that does not lose any

information

(13)

Figure 9.4 A relation containing

redundancy

(14)

Figure 9.5 An employee database

consisting of three relations

(15)

Figure 9.6 Finding the departments in

which employee 23Y34 has worked

(16)

Figure 9.7 A relation and a proposed

decomposition

(17)

Relational Operations

• Select: Choose rows

• Project: Choose columns

• Join: Assemble information from two or

more relations

(18)

Figure 9.8 The SELECT operation

(19)

Figure 9.9 The PROJECT operation

(20)

Figure 9.10 The JOIN operation

(21)

Figure 9.11 Another example of the

JOIN operation

(22)

Figure 9.12 An application of the

JOIN operation

(23)

Structured Query Language (SQL)

• Operations to manipulate tuples

– insert

– update

– delete

– select

(24)

SQL Examples

• SELECT EmplId, Dept FROM Assignment, Job

WHERE Assignment.JobId = Job.JobId AND Assignment.TermData = '*';

• INSERT INTO Employee

VALUES ('43212', 'Sue A. Burt',

'33 Fair St.', '444661111');

(25)

SQL Examples (continued)

• DELETE FROM Employee

WHERE Name = 'G. Jerry Smith';

• UPDATE Employee

SET Address = '1812 Napoleon Ave.'

WHERE Name = 'Joe E. Baker';

(26)

Object-oriented Databases

• Object-oriented Database: A database

constructed by applying the object-oriented paradigm

– Each entity stored as a persistent object – Relationships indicated by links between

objects

– DBMS maintains inter-object links

(27)

Figure 9.13 The associations

between objects in an object-

oriented database

(28)

Advantages of Object-oriented Databases

• Matches design paradigm of object- oriented applications

• Intelligence can be built into attribute handlers

• Can handle exotic data types

– Example: multimedia

(29)

Maintaining Database Integrity

• Transaction: A sequence of operations that must all happen together

– Example: transferring money between bank accounts

• Transaction log: A non-volatile record of each transaction’s activities, built before the

transaction is allowed to execute

– Commit point: The point at which a transaction has been recorded in the log

– Roll-back: The process of undoing a transaction

(30)

Maintaining database integrity (continued)

• Simultaneous access problems

– Incorrect summary problem – Lost update problem

• Locking = preventing others from accessing data being used by a transaction

– Shared lock: used when reading data

– Exclusive lock: used when altering data

(31)

Sequential Files

• Sequential file: A file whose contents can only be read in order

– Reader must be able to detect end-of-file (EOF)

– Data can be stored in logical records, sorted by a key field

• Greatly increases the speed of batch updates

(32)

Figure 9.14 The structure of a simple

employee file implemented as a text file

(33)

Figure 9.15 A function for merging two sequential files

def MergeFiles (InputFileA, InputFileB, OutputFile):

if (both input files at EOF):

Stop, with OutputFile empty if (InputFileA not at EOF):

Declare its first record to be its current record if (InputFileB not at EOF):

Declare its first record to be its current record while (neither input file at EOF):

Put the current record with the “smaller” key field value in OutputFile if (that current record is the last record in its corresponding input file) :

Declare that input file to be at EOF else:

Declare the next record in that input file to be the file’s current record Starting with the current record in the input file that is not at EOF,

copy the remaining records to OutputFile

(34)

Figure 9.16

Applying the merge algorithm (Letters are used to

represent entire records.

The particular letter indicates the value of the record’s

key field.)

(35)

Indexed Files

• Index: A list of key values and the location

of their associated records

(36)

Figure 9.17 Opening an

indexed file

(37)

Hashing

• Each record has a key field

• The storage space is divided into buckets

• A hash function computes a bucket number for each key value

• Each record is stored in the bucket

corresponding to the hash of its key

(38)

Figure 9.18 Hashing the key field

value 25X3Z to one of 41 buckets

(39)

Figure 9.19 The rudiments of a

hashing system

(40)

Collisions in Hashing

• Collision: The case of two keys hashing to the same bucket

– Major problem when table is over 75% full – Solution: increase number of buckets and

rehash all data

(41)

Data Mining

• Data Mining: The area of computer science that deals with discovering patterns in collections of data