Professor Adams' Lecture Materials

Learning Objective 4-02: Explain basic relational database principles.

Relational Data Models

Relational data models offer many advantages over other data models, including:

Flexibility and Scalability: handle changes quickly and easily. (e.g., SAP, Oracle, Microsoft, etc.)
Simplicity: easy to communicate to both database users and database developers
Reduced Information Redundancy: requires each piece of data to be recorded only in one place

Definitions for Databases

Database Management System (DBMS)

A computer program that creates, modifies and queries the database.
Designed to manage a database’s storage and retrieval of information.

Data Dictionary

Describes the data fields in each database record such as field description, field length, field type (e.g., alphanumeric, numeric), etc.

Database Administrator

The person responsible for the design, implementation, repair and security of a firm’s database.

Fundamentals of Relational Databases: Entities and Attributes

Primary and Foreign Keys

Primary Key: Uniquely identifies a specific row in a table.
Foreign Key: attribute in one table that acts as primary key in another table; provides a logical relationship, or a link, between two tables.

Basic Requirements of Tables

The Entity Integrity Rule: the primary key must have data values (cannot be null).
The Referential Integrity Rule: the data value for a foreign key must either be null or match one of the data values that already exist in the corresponding table.
Each attribute: unique name
Value of each attribute: same type
Each attribute (column) of a record (row): single-valued
All other nonkey attribute: describe a characteristic of the class (table) identified by the primary key.

Introduction to Microsoft Access

Access is a simple database management system that can be used to run databases for individuals and small firms.

Microsoft Access Objects

Tables: store data which consists of a series of rows (records) and columns (attributes) connected by relationships (links between tables by foreign keys).
Queries: retrieve and display data derived from records stored within the database.
Forms: enter data into tables and view existing records, powered by queries.
Reports: integrate data from one or more queries and tables, cannot edit database info
Pages: web-based forms, data can be entered in real time from outside of the database system.
Macros: automate processes like opening a specific form.
Modules: alter Access’s code.

REA Data Model for Steve’s Stylin’ Sunglasses’ (SSS) Sales Process

A. SSS may sell minimum one and maximum many pairs of glasses in a sales transaction.
B. Every pair glasses created could be sold once only. Zero means a pair designed already but not sold yet.
C. A salesperson (new) may not handle any sale transaction yet, and at most each salesperson could handle many sale transactions.
D. Only one salesperson greets and assists a customer of the sale transaction.
E. Each customer in the database has made at least one transaction and most many transactions with SSS. Customer data are recorded after the first purchase.
F. Each sale involves one and only one customer.

A. Each cash receipt is deposited in one and only one bank account.

B. Each bank account could have no deposit or many deposits from the cash receipt event.

C. SSS will have many cash receipts from one customer. The “1” means a down payment is required.

D. Each cash receipt is from only one customer.

E. A cashier (new) may not handle any cash receipt yet, or a cashier could take charge of many cash receipts.

F. Each cash receipt (e.g., a check from a customer) is handled by only one cashier.

A. Each cash receipt is for only one sale transaction.

B. Each sale transaction may have at least one cash receipt (i.e., a down payment is required), and at most many cash receipts from customers (i.e., installment sales).

Page updated

Google Sites

Report abuse