Accounting 307 Chapter Database Design

Significance of Databases

1. Critical information 2. Volume 3. Distribution 4. Privacy 5. Irreplaceable data 6. Need for accuracy 7. Internet uses 8. Big data - refers to data that are of such great volume that cannot be captured, stored, and analyzed by traditional databases and existing hardware. Big data consist of structured data (Database) and unstructured data (CEO conference calls, press releases, blogs, social networking).

view control

A final database security feature that limits each user's access to information on a need to know basis.

Database Keys - Primary Keys

A primary key is used to uniquely identify each record. Example: Sales invoice number, SSN If a file has more than one unique identifiers each of the two unique identifiers is called a secondary record key. Only one of the two unique identifiers must be designated as the file's primary key When a combination of fields is needed to uniquely identify records in a file, the primary key is called a composite key. Examples: bank branch code and account number, area code and local phone number

Data Modeling

At a state department of social services, the director wants to know how many inquiries were made for a certain type of medical assistance last month At the headquarters of a department store chain, a vice president wants to know how many credit customers made partial payments on their accounts last week At a local university bookstore, a manager wants to know how many book orders went unfilled last year In each case above, a decision maker wants to design a database by employing a process called data modeling

The Data Hierarchy - File (Table)

At the fifth level, a set of all related records forms a file (e.g., the student file), or a table A file or table contains a set of related records If this university only had three students and five fields for each student, then the entire file would be depicted below

The Data Hierarchy - Database

At the highest level, a set of interrelated, centrally coordinated files forms a database

Table

At the third level of the data hierarchy, a set of common records forms a file or using database and Microsoft Access terminology. it contains a set of related records, a set of customer records, or inventory records.

An Overview - Additional Database Issues

Data dictionary - a data file about the data itself which contains metadata and describes the data fields in each database record

Database management systems (DBMSs)

Data in databases are manipulated by specialized software packages

Transaction controls

Database system that uses to accomplish a specific processing task that ensuring the database system performs each transaction accurately and completely

Concurrency

Database systems include locking mechanisms that do not allow multiple users to access the same record at the same time.

Data Integrity Controls

Designed by the database developers and are customized for different applications. The software used to create databases should include edit tests that protect databases from erroneous data entries.

Database Keys - Foreign Keys

Fields, or attributes, in a file that are not part of the primary keys, are referred to as non-key attributes Foreign keys are non-key attributes that can be used to link files, i.e. enable database records to reference one or more records in other files A foreign key is an attribute in one table that is a primary key in another table. Foreign keys are used to link tables together.

Relational Databases

Groups of related, two-dimensional tables

Concurrency controls

Protect records from errors that occur when multiple users access the same file record simultaneously

The Data Hierarchy - Bit, Character and Field

The lowest level of information is a binary digit or bit. A bit can have a value of either 1 or 0 At the second level, a computer combines eight bits to create a byte. Each byte can hold a single character. At the third level, several characters form a data field Information about the attributes of an entity (e.g., the student's ID number and birth date) are stored in fields

Database - Record Structures

The specific data fields in each record of a database table are part of what is called the record structure This structure can be fixed This structure may also vary. For example, the memo field in a file of customer complaints (the memo field in each record might vary in length)

The Data Hierarchy - File (Table) 2

Two basic types of files Transaction file - Contains records for the individual business transactions that occur during a specific fiscal period (e.g. sales transactions, payment transactions) Master file - Stores cumulative information about an organization's resources (e.g. inventory) and agents (customers, employees). It typically stores permanent information, part numbers, and part descriptions for the individual records in an inventory parts master file. Updated to reflect the effect of specific transactions

Database

a large collection of organized data that can be accessed by multiple users and used by many different computers application

Data Hierarchy

bit (1) -> Character (2) -> field (3) -> record (4) -> file (5) -> database (6)

Record (tuple)

stores all of the information (e.g., information about one inventory item in an inventory file, one employee in a payroll file, or one customer in a customer file) about one entity (i.e., a person, event, or thing) At the fourth level, a group of fields forms a record A database record stores all the information about one file entity row data about a certain student.

Database administrator (Administration)

supervises the design, development, and installation of a large database system and is also the person responsible for maintaining, securing, and changing the database. Therefore, it is essential that the administrator be both skilled and trustworthy.

Data field

the first level in the data hierarchy, which is information that describes a person, event, or event, or thing in the database. (ex) in a payroll file, data fields would include employees names, employee identification numbers, and pay rates for the employees. Other names for a data field are "attribute", "column," or simply "field"