Database/ Relational Databases

What Is A Relational Database?
A database is a collection of tables  and other “objects” that are related and collectively describe an entity. Other objects are forms, queries,  reports, macros, modules, & pages.

Advantages Of Database Accounting Systems
Store data only once - lower cost, Reduces errors, Eliminate data redundancy,Avoid duplicate processing, Simplify maintenance, Centralize data management & security

Advantages Of Database Accounting Systems, Offer greater flexibility, Simplify report modifications and updates, Provide ad-hoc query capabilities, Cross functional data analysis, Permits multiple use and simultaneous data analysis

Disadvantages Of Database Accounting Systems
Increased costs (usually offset by  savings), Hardware requirements, The software itself, Database administrator

Disadvantages Of Database Accounting Systems
Centralized management and security control, System operation becomes critical
Errors in data entry effects all users, Potential disputes over data ownership

Tables
Fundamental storage structures for data
Tables are sometimes called files
Looks like a spreadsheet
Consist of rows (TUPLES) which are the equivalent of a record and columns  (ATTRIBUTES) which are the equivalent of a field
Table Terms
Primary key - An attribute (column) that uniquely identifies a given row so the system can distinguish each record of a table   PRIMARY KEYS CAN’T  BE  NULL!
Foreign key - An attribute (column) in one table that must match the primary key in another table.  Used to join tables together.
Composite (concatenated) key - A primary key that consists of more than a single column.
Table Rules
No duplicate tables
No duplicate rows or columns in table
Sequence of rows/columns doesn’t matter
Each table must have a primary key
The primary key CANNOT be null
Each table is about ONE concept
Joining Tables
Queries
Allows you to ask questions about your data examples:
How many employees earn more than $40,000?
Which customer invoices are more than 60 days  old ?
 SQL (structured query language pronounced seequel) is the underlying “how” of  making queries of databases
Queries
Access does SQL by using QBE or query By example.  QBE makes it easy to pick up simple queries, but becoming accomplished enough to write more complex queries takes time and effort.
Queries
Queries are our friend
Queries are where the real POWER of a database lies
Relationships between tables must be properly established  in order for queries to work correctly
Forms
Shows data from a table in a format that is more attractive and easier to understand
Access forms can be made to resemble paper forms that users are already familiar with
Forms are commonly used to input or change data
Reports
Hard copy of output of information contained in the database
We will often create reports that are the result of queries we made
Access allows you to create board room quality reports
Macros
A macro is a set of one or more actions that you write to automate common  tasks, such as moving to the next record, checking inventory on hand when placing an order, or printing a report with one click of a button.
Modules
Visual Basic  for Application code that can executed at the click of a button or when a form is opened.  They are useful in developing more sophisticated internal controls into an Access based accounting information system.  Modules are beyond the scope of this course.
Pages
Date access pages are new in Access 2000 and are  a special type of Web page designed for viewing and working with data from the Internet or an intranet that is stored in a Microsoft Access database or a Microsoft SQL Server database.
DOCUMENTATION METHODS
Data flow diagrams (DFD) and Entity Relationship (ER) diagrams are primary documentation methods associated with relational databases (akin to flowcharts).
If you were developing a relational database accounting system from scratch you would spend significant time on DFD’s and ER diagrams (REAL model).
Entity Relationship Diagram Sales/Collection Cycle
INTIMIDATING TOPICS
There are certain topics that tend to “freak” students out when they first see them, but they aren’t going to be that bad when we actually DO them so HANG ON!
They are CARDINALITY AND  NORMAL FORMS
REVIEW TABLE RULES
No duplicate tables
No duplicate rows or columns in table
Sequence of rows/columns doesn’t matter
Each table must have a primary key
The primary key CANNOT be null
Each table is about ONE concept

DATABASE NORMALIZATION
Database normalization is the process of ensuring that each table contains data about only ONE concept.

REPEATING GROUPS AND NORMALIZATION TO FIRST NORMAL FORM (1NF)
WHAT IS THE PROBLEM WITH DESCRIPTION/PRICE?
Insert anomalies
Delete anomalies
Update anomalies
DECOMPOSITION OF A FIRST-NORMAL-FORM (1NF) TABLE
DATABASE NORMALIZATION
Functional dependency is key in understanding the process of normalization. Functional dependency means that if there is only one possible  value of Y for every value of X, then Y is functionally dependent on X.

DATABASE NORMALIZATION
Think of an invoice table.  Two fields would be invoice # and date.  Which field is functionally dependent on the other?

Functional Dependency is “good”.  With functional dependency the primary key (Attribute A) determines the value of all the other non-key attributes (Attributes B,C,D,etc.)
Transitive dependency is “bad”.  Transitive dependency exists if the primary key (Attribute A) determines non-key Attribute B, and Attribute B determines non-key Attribute C.
DECOMPOSITION OF A SECOND-NORMAL-FORM (2NF) TABLE
SUMMARY OF 3NF RELATIONS FOR SALES DATABASE