Chapter 9:

43  Download (0)

Full text

(1)

Copyright © 2015 Pearson Education, Inc.

Computer Science: An Overview Twelfth Edition

by

J. Glenn Brookshear Dennis Brylow

Chapter 9:

Database Systems

(2)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-4

Figure 9.1 A file versus a database

organization

(5)

Copyright © 2015 Pearson Education, Inc. 9-5

Figure 9.2 The conceptual layers of a

database implementation

(6)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-8

Database Models

Database model: A conceptual view of a database

– Relational database model

– Object-oriented database model

(9)

Copyright © 2015 Pearson Education, Inc. 9-9

Relational Database Model

Relation: A rectangular table

Attribute: A column in the table

Tuple: A row in the table

(10)

Copyright © 2015 Pearson Education, Inc. 9-10

Figure 9.3 A relation containing

employee information

(11)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-13

Figure 9.4 A relation containing

redundancy

(14)

Copyright © 2015 Pearson Education, Inc. 9-14

Figure 9.5 An employee database

consisting of three relations

(15)

Copyright © 2015 Pearson Education, Inc. 9-15

Figure 9.6 Finding the departments in

which employee 23Y34 has worked

(16)

Copyright © 2015 Pearson Education, Inc. 9-16

Figure 9.7 A relation and a proposed

decomposition

(17)

Copyright © 2015 Pearson Education, Inc. 9-17

Relational Operations

Select: Choose rows

Project: Choose columns

Join: Assemble information from two or

more relations

(18)

Copyright © 2015 Pearson Education, Inc. 9-18

Figure 9.8 The SELECT operation

(19)

Copyright © 2015 Pearson Education, Inc. 9-19

Figure 9.9 The PROJECT operation

(20)

Copyright © 2015 Pearson Education, Inc. 9-20

Figure 9.10 The JOIN operation

(21)

Copyright © 2015 Pearson Education, Inc. 9-21

Figure 9.11 Another example of the

JOIN operation

(22)

Copyright © 2015 Pearson Education, Inc. 9-22

Figure 9.12 An application of the

JOIN operation

(23)

Copyright © 2015 Pearson Education, Inc. 9-23

Structured Query Language (SQL)

• Operations to manipulate tuples

– insert

– update

– delete

– select

(24)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-27

Figure 9.13 The associations

between objects in an object-

oriented database

(28)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-32

Figure 9.14 The structure of a simple

employee file implemented as a text file

(33)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-35

Indexed Files

Index: A list of key values and the location

of their associated records

(36)

Copyright © 2015 Pearson Education, Inc. 9-36

Figure 9.17 Opening an

indexed file

(37)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-38

Figure 9.18 Hashing the key field

value 25X3Z to one of 41 buckets

(39)

Copyright © 2015 Pearson Education, Inc. 9-39

Figure 9.19 The rudiments of a

hashing system

(40)

Copyright © 2015 Pearson Education, Inc. 9-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)

Copyright © 2015 Pearson Education, Inc. 9-41

Data Mining

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

Data warehouse: A static data collection to be mined

Data cube: Data presented from many

perspectives to enable mining

(42)

Copyright © 2015 Pearson Education, Inc. 9-42

Data Mining Strategies

• Class description

• Class discrimination

• Cluster analysis

• Association analysis

• Outlier analysis

• Sequential pattern analysis

(43)

Copyright © 2015 Pearson Education, Inc. 9-43

Social Impact of Database Technology

• Problems

– Massive amounts of personal data are being collected

• Often without knowledge or meaningful consent of affected people

– Data merging produces new, more invasive information

– Errors are widely disseminated and hard to correct

• Remedies

– Existing legal remedies often difficult to apply

– Negative publicity may be more effective

Figure

Updating...

References

Related subjects :