Must removed from the student table and placed table about faculty shown below

Client/Server Databases and the Oracle 10g Relational Database

2

File-based data processing

– Retrieving data

Student Registration System (using file-based data processing)

Problems with storing data in files:
– Redundancy
• Example: same student data or course data in
different files
– Inconsistency
• May have different info about students or courses in different files if not updated everywhere
– Proliferation of data management programs
(retrieving, updating, inserting, deleting data)
– Waste of disk space due to redundancy and
proliferation of data management programs.

4

All programs interface with the DBMS to access the database data.

– A record is all attributes about a specific instance of an
entity 6thcolumn/field
FACULTY table

Entity
– Object about which you want to store data (e.g.

students, faculty, courses, staff, supplies, etc.)
– Different tables store data about each different entityRelationships
– Used to connect information about different entities– Links that show how different records are related

8

Primary Key

Surrogate Keys

FACULTY table with surrogate key

Composite Key

Database Design
Main tasks involved with design of database: – Developing entity-relationship (ER) model – Normalizing database tables

Entity-Relationship Model (continued)One to one (1:1)

Step-by-step process used to determine which data elements should be stored in which tables
Purpose
– Eliminate data redundancy
Normalization process include:
– Beginning with unnormalized data/table
– Applying techniques to convert unnormalized data into 1stNormal Form (1NF) 2NF, 3NF
Having all tables in 3NF is the objective

17

First Normal Form

To convert an unnormalized table into 1NF: – Repeating groups must be removed
– Primary key field must be identified
Easiest way to remove repeating groups is
– To create a separate record for each value in the repeating group

What field(s) could be used as a primary key in the STUDENT table below?

Answer: ________________

Second Normal Form (continued)

To remove partial dependency from a table

Third Normal Form (cont.)

Consists of
– DBMS
• Manages physical storage and data retrieval
– Database applications
• Provide interface that allows users to interact with database
Server
– Computer that shares resources with other
computers

26

Personal Database Management Systems

DBMS and database applications run on same workstation
Appear to user as a single integrated applicationUsed primarily for creating single-user database applications
Can also be used for some multiuser applicationsShould be used only for applications that are not mission critical

Using a Personal Database for a Multiuser Application

– Grouping related database changes into units of work that must either all succeed or all fail

– DBMS can use the transaction log to reverse—or roll back—the changes

Server sends only requested data back to client rather than entire database

32

Client/Server Database Management Systems (continued)

Generate less network traffic than personal databases

Client/Server Database Management Systems (continued)

Preferred for
– Database applications that retrieve and manipulate small amounts of data from databases containing large numbers of records
– Mission-critical applications
– Web-based database applications that require increased security and fault tolerance

Client/Server Architecture for Oracle 10g DBMS

38

The Database CasesFictional organizations:
– Clearwater Traders
– Northwoods University

40

The Clearwater Traders Sales Order Database (continued)

42

Student registration system
Data items consist of information about:– Students
– Courses
– Instructors
– Student Enrollment

44

Visual Representation of the
Northwoods University Database