Database Management Exam Study Guide

Problem with lists – Modification problems when deleting/adding due to multiple themes in one list.

• Null Values – inserted rows with missing values
• Mod problems occur due to having two or more data sets
• Causes redundancy.

Relational DataBase tables – relational model emerged due to list problems.  It is a collection of separate tables. Table holds data for only one theme, not multiple. When evaluating a database design, consider mod actions: insert/delete/update.

Structured Query Language SQL – leading tech for data definition and manipulation.  Can reconstruct lists from their underlying tables, query for specific data conditions, perform computations,  can update/delete/insert data.

Database System  four components:

• Database – collection of related tables and other structures
• Database Management System DBMS – computer program used to create, process, and administer the database.  Receives requests encoded in SQL and translates them into action on the database.  Large complicated program licensed by software vendors.
• Database Application – Set of one or more computer programs that serves as an intermediary between the user and the DBMS,  These programs read or modify data by sending SQL statements to the DBMS.
• Users – use the DB Application to keep track of things. Use forms to read, enter, and query data.

Database – defined as self-describing (a description of the structure if the database is contained within the database itself) relational collection of tables.

Metadata – data about the structure of the a database. (IE names of tables, names of columns, and the tables in which they belong, properties of tables and columns)

Database contents – user data, metadata, indexes, and other overhead data (these improve performance). Application data (describes application elements, such as forms and reports)

DBMS examples – MS Access, MS SQL Server, Oracle Corp MySQL, Oracle DB, IBM’s DB.

Steps DBMS can do:

• Create Database, create tables, other supporting structures.
• Create indexes for faster queries
• Modify and read database data, done by SQL requests.
• Maintains all database structures, IE change format of a table
• Enforces declared rules, IE restricting value entries (called Referential Integrity Restraints)
• Controls concurrency – ensures one users word does not interfere with anothers
• Provides security
• Perform backup and recovery

Applications can do:

• Create and process forms and reports
• Process user queries
• Execute application logic, basically IF functions if this do that
• Control Application, done in two ways(app needs to present logical options for the users, and app needs to control data activities with the DBMS.

Personal Database System – used by only one person, few tables, several hundred rows.

USER <-> DB APP <-> DBMS <->Database

Enterprise Class Database System – thousands of users, millions of rows, hundreds of tables, 24/7 op.

NoSQL – nonrelational database. Doesn’t use SQL

Entity – thing being tracked, something of importance to the user that needs to be represented in the database

DBMS – products store data in the form of relations, which is a special type of table