MIS 311 3/4

What is a database?

- A collection of data that you organize and access according to the logical structure of the that data.

Why is OLAP important?

- OLAP is how knowledge workers and decision makers read and interpret patterns in large, evolving bodies of data.

What are the steps for database design?

-(1) Define entities, primary keys and volumes. -(2) Define relationships among entity classes. -(3) Define all remaining attributes (fields) for each relation (table). After this, you can use a data definition language within a database tool (eg Microsoft Access) to create the database

What is a Foreign Key?

-A Foreign Key is a field installed in an entity that refers to a primary key field (usually in some other entity).

What is a Relationship?

-A RELATIONSHIP exists between two entities if any occurrence of one entity is somehow associated or paired with one or more occurrences of the other entity, and this association is of interest.

What is Normalization?

-A process that improves relational database design: reduces redundancy, increases maintainability, places attributes in correct entities

What is an attribute?

-An attribute is a fact that can be collected about each occurrence of an entity (eg. the salary of an employee, the last name of a customer).

What is the first step of Normalization?

-Eliminating many-to-many relationships is the first step of normalization. -Also called "achieving 1st normal form" or "removing multi-valued attributes". -Each many-to-many relationship is resolved (eliminated) by replacing it with a newly created intersection entity.

What is an Integrity Constraint?

-a limiting rule that helps ensure the quality of the data and the information that can be produced from it. Integrity constraints are normally registered in the database catalog so the database will enforce the constraint.

What is an Entity?

-a person, place or thing about which data is being collected. -of interest to the business, like "customers" and "orders", not computer objects like reports or forms -represents an entire collection (called a set) of specific occurrences -When designing a Relational Database, each Entity will ultimately become a "Relation" (ie Table)

What is a composite primary key?

-a primary key that consists of more than one field, (often the foreign key fields from two intersecting entities).

What are the characteristics of a Relational Database?

Each relation (table) is made up of records, also called rows. - Each relation (table) must have a unique field which describes each record. This unique field is called a primary key. It cannot be empty. - Relations (tables) are connected using a common field. The primary key is one relation is called the foreign key in the relation it is joined to. - A user needn't be concerned about where the data is stored on the storage device. - A data dictionary can be used to specify each field in the database along with the rules for how it must be entered.

What are Database Management Systems (DMBS) and what do they do?

- Database Management Systems (DBMS) are programs that protect the database and become a single, controlled access point. No one can access database data except through the DBMS program. - Today, most DBMS programs follow the Relational approach to database organization, and are called Relational DBMS or RDBMS.

What is the Relational Database approach to building and using databases?

- Establishing a logical view of data that is independent of physical platform -eg. use table name "Customer", key field name "Customer_number" and value "1056732" to retrieve customer data -this will not change, even if data is physically re-configured on new technologies / computers / storage media - Minimizing data duplication (aka "redundancy") -redundant copies of data are not just wasteful of space and labour-intensive to maintain, they are error prone (which is fatal).

What is the logical structure of a database?

- It is the way the data is organized based upon the properties and requirements of your business (ie, the business meaning). - This is different from the physical structure of the database, which is the way the data is organized inside the computer (ie, which disk drive, which track)

Why is OLTP important?

- OLTP is how all database data gets changed (inserting, updating and deleting records (rows)) - OLTP is the backbone of all major computer systems: each system may have millions of transactions per day.

What is the difference between On-line Transaction Processing (OLTP) and On-line Analytic Processing (OLAP)?

- OLTP keeps databases up-to-date. OLAP uses the data from single databases or multi-database data warehouses to show meaningful information (patterns), but it does not change or update the data.

What is a Primary Key?

-a field or group of fields that uniquely identifies each occurrence in an entity. -Primary keys must be -UNIQUE (no duplicate values) -MANDATORY (no empty values) for all occurrences within an entity.

What is Cardinality?

-a symbol that indicates whether ONE occurrence or MANY occurrences of an entity participate in a relationship

Why are data storage and retrieval necessary and important?

- Consolidation of multiple copies of data into one integrated database creates the one official version of "organizational memory" -Until this is done, no one knows how much data is missing / duplicated -Until this is done, there is disagreement about correct versions of data -Until this is done, there is disagreement about ownership / control - Capturing and maintaining data independently of its intended use is simpler and more maintainable. - Centralizing storage and retrieval supports many uses (even "unplanned" uses), and is therefore more efficient in the long run.

What are the major subsystems of a DBMS?

- Data Definition (Create and change the logical structure of the database) - Data Manipulation (Query (ie, read) and make changes to the information content of the database) - Application Generation (Create re-usable menus, screens, reports and other application software for operating the database) - Data Administration (Establish security profiles for access and use of data, make backups and recover data, see effects of proposed changes to data structures)

What is Object-Orientation and what will it do to databases?

The Object orientation approach says that data and the programs that manipulate that data should be catalogued and stored together in units called "objects". -This approach allows users and designers of databases to more quickly see or diagnose how data can or should be used. -This approach provides "encapsulation", where data is not accessible except through the programs stored with it: more controlled. -This approach allows new objects to be built out of many other smaller, re-useable objects: this is called "re-usable components". -Object Orientation will move to database technologies more slowly than other technologies, to ensure that capabilities are not lost.