Database Administration and Architecture

Study Notes

Database architecture refers to the database technology used to support data architecture.
It includes the database engine, database utilities, CASE tools, and database development tools.
The core of the DBMS is its database engine.
A data definition language (DDL) is used to physically define tables, fields, and structural relationships.
A data manipulation language (DML) is used to create, read, update, and delete records in the database, and navigate between different records.

A relational database implements stored data in a series of two-dimensional tables that are “related” to one another via foreign keys.
The physical data model is called a schema.
The DDL and DML for a relational database is called SQL (Structured Query Language).
Triggers are programs embedded within a database that are automatically invoked by updates.
Stored procedures are programs embedded within a database that can be called from an application program.

A database should provide for efficient storage, update, and retrieval of data.
A database should be reliable—the stored data should have high integrity and promote user trust in that data.
A database should be adaptable and scalable to new and unforeseen requirements and applications.

A database schema is a model or blueprint representing the technical implementation of the database.
It is also called a physical data model.

Review the logical data model.
Create a table for each entity.
Create fields for each attribute.
Create an index for each primary and secondary key.
Create an index for each subsetting criterion.
Designate foreign keys for relationships.
Define data types, sizes, null settings, domains, and defaults for each attribute.
Create or combine tables to implement supertype/subtype structures.
Evaluate and specify referential integrity constraints.

Key integrity: Every table should have a primary key.
Domain integrity: Controls must be designed to ensure that no field takes on an inappropriate value.
Referential integrity: The assurance that a foreign key value in one table has a matching primary key value in the related table.

Data distribution analysis establishes which business locations need access to which logical data entities and attributes.
Methods of data distribution include:
- Centralization: Entire database on a single server in one physical location.
- Horizontal distribution (also called partitioning): Tables or rows assigned to different database servers/locations.

Data architecture is a definition of how files and databases are to be developed and used to store data.
It includes the file and/or database technology to be used and the administrative structure set up to manage the data resource.

Data administrator: A database specialist responsible for data planning, definition, architecture, and management.
Database administrator: A specialist responsible for database technology, database design, construction, security, backup and recovery, and performance tuning.