Database Systems: ANSI-SPARC Architecture

• Study notes on Database Systems, covering the ANSI-SPARC architecture, data abstraction levels, conceptual models, and the database design and life cycle

3 Level ANSI-SPARC Database Architecture

• ANSI/SPARC classified data models in the 1970s by abstraction degree: conceptual, external, and internal.
• Key system requirements include all users accessing the same data and user views being immune to changes in other views.
• Users should not need to know database storage details.
• A Database Administrator (DBA) should be able to alter storage structures without affecting user views.
• The internal database structure should withstand changes to physical storage aspects.
• DBAs should also be able to modify the database’s conceptual structure without affecting all users.

External Level

• This level is the user's perspective of the database and describes the part relevant to a particular user.

Conceptual Level

• It is a global, community view of the database serving as the basis for identifying and describing the main data objects and the relationships among the data.

Internal Level

• An internal level has a physical representation of the database on the computer, detailing how data is stored on storage media.
• The internal level is sometimes referred to as the Physical Level.

Data Abstraction Levels

• The conceptual model and logical model are the same concept.
• The conceptual model consists of the context model(M:N relationships), keys model(identify the PK and FKs) and fully attributed model (all attributes identified with keys).
• Context Model: Generalized representation without specific data, model, or DBMS details (no PKs or FKs).
• Fully Attributed Model: Specific to a data model (relational), including PK's and FK's, but not DBMS-specific regarding data types.

Physical ERD Model

• Representation is specific to a particular relational DBMS, such as Oracle, with specific data types and sizes.
• A schema file can be run against the database to create table structures.

The Systems Development Life Cycle (SDLC)

• Planning: Initial assessment and feasibility study.
• Analysis: Addresses user requirements, evaluation of existing systems, and logical system design.
• Detailed Systems Design: Detailed system specification.
• Implementation: Coding, testing, debugging, and fine-tuning the installation.
• Maintenance: Involves evaluation, maintenance, and enhancements.

Phase 1 :Initial Database study

• The analysis of the company's situation, identifying operational components and their functions, helping define problems, constraints, objectives, scope, and boundaries.
• This includes defining the extent of the design based on operational needs and determining data structures, entity types, and database physical size.
• The first phase of the DBLC involves interactive and iterative processes for successful completion.

Phase 2: Database design

• A focus on the identification of characteristics required to build a database model.
• Involves both the business view of data as an information source and the designer’s view of data structure with emphasis on access and information transformation.
• This phase doesn't follow a fixed order but uses continuous feedback to refine the previous steps with these components: data analysis, ER modeling, model verification, and possibly distributed database design.
• Logical Design: Involves advantages, disadvantages, and software independence.
• Conceptual Design: Embodies four stages: Data Analysis /Requirements, ER Modeling, Model Verification and Distributed Database Design.
• Physical Design: Selecting data storage/access characteristics; it is hardware-, system-, and DBMS-dependent.

Phase 3 implementation and loading

• Implementation requires creating storage constructs for end-user tables.

Phase 4 Testing and Evaluation

• Testing and tuning are performed by the DBA for performance, integrity, concurrent access, and security.
• Testing and evaluation Runs parallel with application programming, using database tools for prototyping, and refinement of system as well as DBMS parameters.

Phase 5 and 6 Operation and Maintenance/Evaluation

• The database is considered fully functional post-evaluation and begins system evolution.
• Periodic maintenance includes preventive measures (backups), corrective actions (recovery), and adaptive enhancements (performance tuning, adding entities/attributes).
• Access rights are assigned and managed for new and existing users, generation of database access statistics, with regular security audits, and system-usage reports.

Database design Strategies

• Top-Down: Begins with identifying data sets, then defining data elements, and then identifying entity types/attributes.
• Bottom-Up: it starts with data elements, then grouping them into data sets, and later defining attributes to group them into entities.