MIS Chapter 6

Capabilities of DMS

-Data definition capability: specifies structure of database content, used to create tables and define characteristics of fields (field properties) -Data dictionary: automated or manual file storing definitions of data elements and their characteristics -Querying and reporting

Primary Key

-Field in table used for key fields -One field in each table, cannot be duplicated, provides unique identifier for al information in any row

Problems with traditional file environment

-Files maintained Separately by different departments -Data Redundancy (Presence of duplicate data in multiple files) -Data Inconsistency (same attribute has different values (record updates)) -Program-data dependance (When changes in program requires changes to data accessed by program) -Lack of flexibility -Poor security -Lack of Data sharing and availability

Table

-Grid of columns and rows -Rows ( tuples): records for different entities -Fields (columns): represents attribute for entity -key field: field used to uniquely identify each record

Designing Databases

-Must understand the relationships among the data, the type of data being maintained, how the data will be used, impacts to organization in order to manage data company wide -Conceptual (logical) design: describe how the data elements in the database are to be grouped. Abstract model from business perspective. (what data should be contained within the supplier table? -Physical design: how database is arranged on direct-access storage devices.

Design process identifies

-Relationships among data elements, redundant database elements -Most efficient was to group data elements to meet business requirements, needs of application programs

Database Management System (DMS)

-Software allowing organizations to centralize data, manage them efficiently, and provide access -Interfaces between applications and physical data files -Separates logical and physical views of data (relieves the programmer or end user from the task of understanding where and how the data are actually stored) -Solves problems of traditional file environment (controls redundancy, eliminates inconsistency, uncouples programs and data, enables organization to centrally manage data and data security) Identify and diagram data relationships

Normalization

-Streamlining complex groupings of data to minimize redundant elements and awkward many-to-many relationships -the process of creating small, stable, yet flexible and adaptive data structures from complex groups of data

Entity-relationship diagram

-Used by database designers to document the data model -illustrates relationships between entities

Referential Integrity Rules

-Used by relational databases to ensure that relationships between coupled tables remains consistent -E.g., when one table has a foreign key that points to another table, you may not add a record to the table with a foreign key unless there is a corresponding record in the linked table

Relational DMS

-most prolific database model -organizes data into two-dimensional tables called relations or files -each table contains data or entity and attributes -one table for each entity (customer, supplier, etc. )

Querying and reporting

Data manipulation language: used to add, change, delete, and retrieve data from the database -structured query language (SGL) -microsoft access user tools for generation SQL Many DMS have report generation capabilities for creating

Field

Group of Characters as words or numbers

File

Group of records of the same type

Database

Group of related files Collection of data organized to serve many applications by centralizing data and controlling redundant data

Logical vs Physical view

Logical: presents data as they would be perceived by end users or business specialist Physical view: shows how data are actually organized and structures on physical storage media

Foreign key

Primary key used in a second table as look-up field to identify records from original table

Operations of a relational DMS

Select: creates subset of data of all records that meet stated criteria (subset of rows) Join: combines relational tables to provide user with more information than available in individual tables Project: creates subsets of columns in table, permits the user to create new tables containing only the desired information

Attribute

Specific characteristics or qualities describing each entity -Supplier: Name, Address -Part: Description, unit, price, supplier

Record

describes an entity: a person, place, thing, or event on which we store and maintain information on