AIS Chapter 4

What are the five major advantages of database systems?

Data Integration

Data Sharing

Minimal Data Redundancy

Data Independence

Cross-Functional Analysis

What is data integration?

Combining previously separate data files into a single database that can be used throughout the organization.

Example: Payroll, HR, and employee skills data can be stored in one employee database.

What is data sharing?

Authorized users can access the same centralized data.

Example: Sales, Accounting, and Shipping can all access customer information.

What is data redundancy?

Storing the same data multiple times.

Example: A customer's address appears in both billing and shipping files.

Why is minimizing data redundancy important?

It reduces inconsistencies and saves storage space.

What is cross-functional analysis?

Analyzing relationships between different business functions.

Example: Comparing advertising costs with sales revenue.

What are the basic elements of the data hierarchy from largest to smallest?

Database → Table (Entity) → Record (Row) → Field (Column)

What is a database?

A well-organized collection of data stored with minimal redundancy.

What is a table (entity)?

A collection of data about a specific object.

Examples:

• Customer Table

• Sales Table

• Inventory Table

What is a record?

One row in a table representing a specific occurrence of an entity.

Example:
Customer #151

What is a field?

A column that stores a specific attribute.

Example:
Customer Name
Address
Credit Limit

What is data independence?

The separation of data from application programs.

Why is data independence important?

Changes to data storage do not require changes to application programs.

Example of data independence?

The DBA changes how customer data are physically stored, but reports still work normally.

What is a DBMS?

Software that manages the database and controls communication between users and the database.

What are the major functions of a DBMS?

• Store data

• Retrieve data

• Update data

• Control access

• Enforce security

• Maintain integrity

What three components make up a database system?

Database

DBMS

Application Programs

Who is responsible for managing a database?

Database Administrator (DBA)

What is the logical view of data?

How users see and understand data.

What is the physical view of data?

How data are actually stored on computer hardware.

Example of a logical view?

A sales manager sees a customer table with customer names and balances.

Example of a physical view?

Data stored in disk sectors and indexed storage structures.

Why are logical and physical views separated?

To allow changes in storage without affecting users.

What is a schema?

A description of the database structure and relationships.

What are the three schema levels?

External Schema

Conceptual Schema

Internal Schema

What is the external schema?

A user's view (subschema) of the database.

What is the conceptual schema?

Organization-wide view of the entire database.

What is the internal schema?

Physical storage details such as addresses and indexes.

Which schema is most associated with users?

External Schema

Which schema is most associated with the DBA?

Conceptual Schema

Which schema contains record layouts and storage details?

Internal Schema

What is a data dictionary?

A file that contains metadata about the database.

What information is stored in a data dictionary?

• Field names

• Descriptions

• Data types

• Field lengths

• Authorized users

• Programs using the data

Why is a data dictionary important?

• Documentation

• Database design

• Auditing

• Security management

How does a relational database organize data?

Into related two-dimensional tables.

What is a row called in database terminology?

Tuple

What does each row represent?

One occurrence of an entity.

What does each column represent?

An attribute of an entity.

What is a primary key?

An attribute that uniquely identifies a row.

Example of a primary key?

CustomerID in the Customer table.

What is a foreign key?

An attribute that is a primary key in another table and links the tables together.

Example of a foreign key?

CustomerID in the Sales table.

Which table contains the primary key CustomerID?

Customer Table

Which table contains CustomerID as a foreign key?

Sales Table

Customer Table

CustomerID	Name
151	Vivian

Sales Table

InvoiceID	CustomerID
11101	151

 Identify the primary key and foreign key.

• Primary Key = Customer.CustomerID

• Foreign Key = Sales.CustomerID

What are the four basic requirements of a relational database?

Every cell contains one value.

Primary keys cannot be null.

Foreign keys must match a primary key in another table.

Non-key attributes describe the object identified by the primary key.

What is the Entity Integrity Rule?

Primary keys cannot be null.

What is the Referential Integrity Rule?

Foreign keys must match an existing primary key value.

Customer Table:

151
152
153

Sales Table:

11101 → CustomerID 151

11102 → CustomerID 999

Which rule is violated?

Referential Integrity Rule

(CustomerID 999 doesn't exist)

Sales Table:

InvoiceID = NULL

 Which rule is violated?

Entity Integrity Rule

What is an update anomaly?

Same data must be changed in multiple places.

Example of an update anomaly?

Customer address changed in Billing but not Shipping.

What is an insert anomaly?

Cannot add information without adding unrelated information.

What is a delete anomaly?

Deleting one record accidentally removes needed information.

What are CRUD rights?

• Create

• Read

• Update

• Delete

Which CRUD right allows viewing data?

Read (R)

Which CRUD right allows entering new data?

Create (C)

Which CRUD right allows changing existing data?

Update (U)

Which CRUD right allows removing data?

Delete (D)

Why are delete rights rarely granted in accounting systems?

Because deleting transaction records threatens data integrity and audit trails.

Why is a relational database better than storing all data in one large table?

• Reduces redundancy

• Eliminates update anomalies

• Eliminates insert anomalies

• Eliminates delete anomalies

• Improves data integrity