WEEK 9 Databases & Database Management Systems

WEEK 8 involved creating a data model (Entities and relationships). What is the next step?
Create database design (tables with keys)
What is a Database Management System (DBMS)?
a program (software) used to create, process and administer a database. e.g access
Components of a Database Application System.
Applications make database data more accessible and useful.
A database application system consists of forms, formatted reports, queries and application programs.
Database management system (DBMS) processes database tables for applications.
What is the use for a DBSM?
A DBMS helps you specify the logical requirements for a database and access and use the information in a database.
The 5 components of a DBSM (diagram)
DBMS engine, Data definition subsystem, Data manipulation subsystem, Application generation subsystem, Data administration subsystem.
DBSM Engine
accepts logical requests from other DBMS subsystems, converts them into the physical equivalents, and access the database and data dictionary on a storage device.
Physical view
how information is physically arranged, stored, and accessed on a storage device
Logical view
how you need to arrange and access information to meet your needs
Data definition Subsystem
helps you create and maintain the data dictionary and structure of the files in a database. helps you define field names, data types (numeric, character, date etc), form (do you need an area code?),
default value, is an entry even required, etc
Data manipulation subsystem
helps you add, change, and delete information in a database and query it to find valuable information. Most often your primary interface, Includes (design) views, report generators.
What are Forms, Reports & Queries?
Reports show data in a structured context.
View
Different database views reveal different combinations of data. allows you to see the contents of a database file, make changes, and query the database to find information
Report generator
helps you quickly define formats of reports and what information you want to see in a report.
Query-by-Example (QBE)
helps you graphically design the answer to a question. (selected fields and relationships).
SQL
standardized fourth-generation query language found in most DBMSs. Visual representation of QBE. Mostly used by IT professionals.
Advantages of SQL
Users do not need to learn different DDLs and DMLs (programming languages). SQL can be embedded (mixed) with other programme languages, increasing efficiency and effectiveness.
Portable – programmers/users do not need to rewrite statements (not sure what that means)
Application generation subsystem
contains facilities to help you develop transaction-intensive applications. Mainly used by IT professionals
Data administration subsystem
helps you manage the overall database environment by providing facilities and tools for….
Setting up a security system involving user accounts, passwords, permissions and limits for processing the database,
Backup, Recovery, Security CRUD access, Query optimization, Reorganization, Concurrency control, Change management.
Backup
copy of information on another computer
Recovery
process of reinstalling the backup information in the event the information was lost.
Security CRUD access
who can create, read, update, delete information.
Query optimization
to minimize response times for large, complex queries by simplifying and minimising.
Reorganization
for physically rearranging the structure of the information according to how you most often access.
Concurrency control
what happens if two people attempt to make changes to the same record at the same time.
Change management
how will structural changes impact the overall database.
Why are Database Application Programs Needed?
Forms, reports and queries work well for standard functions, but most applications have unique requirements that a simple form, report or query cannot meet.
Application programs process logic that is specific to a given business need, and serve as an intermediary between the web server and database e.g.
respond to events, such as when a user presses a submit button.
Enterprise DBMS
Process large organisational and workgroup databases.
Support many, possibly thousands, of users and many different database applications.
Support 24/7 operations and can manage databases that span dozens of different magnetic disks with hundreds of gigabytes or more of data.
Personal DBMS
Designed for smaller, simpler database applications.
Used for personal or small workgroup applications that have fewer than 100 users (normally fewer than 15), often just a single user. e.g what you are doing right now (quizlet)
Microsoft access serves as a…
Data base management system AND application.
Factors to consider when selecting a DBMS
Database size (Megabytes, Gigabytes, Terabytes), Number of concurrent users (10s, 100s or 1000s), Performance, Integration, Features, The vendor (eg. support, reputation), Cost.