Chapter 4: Relational Databases Flashcards
Entity
Something about which data is stored: resources, activities (events), people (agents). Entity = students
Attributes/ Fields
Characteristics of the entity. The data for each attribute stored in fields.
Records
Individual Items, people or activities. ex: entity = students, record = one specific students information.
Primary Key
Attribute that uniquely ids each record. Usually a single attribute and can be combined with another primary key attribute. (concatenated key for many to many relationships)
database
a set of inter-related, centrally coordinated files
Database management system (DBMS)
the interface between the database and the various application programs.
Database System
the database, the DBMS, and the application programs that access the database through the DBMS .It separates the storage and use of data elements.
Master Files
stores data about Resources and people (agents), spans accounting periods, changes infrequently, similar to ledgers (AR subsidiary ledger customer information)
Transaction Files
Stores data about activities (events), each transaction is a record, or multiple records. Similar to Journals (Sales Journal Captures all Sales transactions)
Database Administrator (DBA)
responsible for the database
Data warehouse
separate databases that contain both detailed and summarized data for a number of years and is used for analysis rather than transaction processing. They are updated periodically not in real time like transaction processing databases.
Business Intelligence
the use of data warehouses for strategic decision making.
two main techniques used in business intelligence:
OLAP and Data Mining
Online Analytical Processing (OLAP)
using queries to guide the investigation of hypothesized relationships in data
Advantages of database systems
- Data Integration
- data sharing
- Minimal Data redundancy and data inconsistencies
- Data independence
- Cross-functional analysis
Data Integration
Master files are combined into large “pools” of data that many application programs access.
Data Sharing
Integrating data are more easily shared with authorized uses. Databases are easily browsed to a problem or obtain detailed information underlying a report.
Data Independence
because data and the programs that use them are independent of each other, each can be changed without changing the other, This facilitates programming and simplifies data management.
Cross-functional Analysis
in a database system, relationships, such as the association between selling costs and promotional campaigns, can be explicitly defined and used in the preparation of management reports.
Logical View
how people conceptually organize and understand the data.
Physical view
refers to how and where data are physically arranged and stored in the computer system.
Schema
describes the logical structure of a database.
3 levels of schema
Conceptual
External
Internal
Conceptual level of schema
organization-wide view of the entire database. Includes all data elements and relationships.
External level of schema
user view of portions of the database.Consists of individual user views of portions of the data base, each of which is referred to as a subschema
Internal level of schema
a low-level view of the database, describes how the data are stored and accessed, including record layouts, definition, addresses, and indexes.
Data dictionary
contains information about the structure of teh database. It is maintained by the DBMS
Reports of data dictionary
- programs or reports using a data item
- synonyms for the data elements in a file
- data elements used by a user.
Data Query Languages (DQL)
interrogate and manipulate the data. Contains powerful, easy-to-use commands that enables users to retrieve, sort, order, and display data.
Report Writer
simplifies queries and report by giving users limited choices.
Data definition language (DDL) - not on pp
builds the data dictionary, creates the database, describes logical views for each user, and specifies records or field security constraints.
Data Manipulation Language (DML) - not on pp
changes database content, including data element updates, insertions, and deletions.
Relational Data model
represents conceptual and external level schemas as if data are stored in tables. Their actually not stored in tables but in the manner described in the internal level schema.
Requirements of Relational Database
- every column in a row must be single valued
- Primary keys cannot be null
- foreign keys, if not null must have values that correspond to the value of the primary key in another table
- All non-key attributes in a table must describe a characteristic of the object identified by the primary key.
Entity integrity rule
every row in a table represents something and that it can be identified. the primary key have a non-null value. This ensures that a specific object exists in the world and can be identified.
referential integrity key
any non-null value of a foreign key must correspond to a primary key in the referenced table
Normalization
assumes everything is in one large file/table. Separates attributes into multiple files/table - eliminate anomalies: delete, update, insert
Semantic Data Modeling
Use business processes to create diagrams representing the database structure. Proper modeling eliminates anomalies. Preferred method because it captures the end user requirements
update anomaly
data values updates are not correctly recorded
delete anomaly
occurs when deleting a row has unintended consequences.no way to store information about prospective customers until they ma
data modeling
the process of creating a logical representation of the structure of the database based on a company’s business processes.
