Hi there,
I'm currently revising for a databases exam and looking over some past papers, but there's one question that I'm slightly unsure about and was wondering if someone could offer some assistance.
"Describe EACH of the THREE levels of the ANSI SPARC 3 level architecture. Your answer should include the purpose of EACH of the schemas, the level of abstraction they provide and the software tools that would be used to access and support them."
As I understand it (although please correct me if I'm wrong): the internal schema specifies the physical storage of the data; the conceptual schema specifies the structure of the database and the domains; and the external schemas are how the database is viewed by "users" (applications, etc.). As for the abstraction, I understand that the conceptual layer means that the physical data storage can be altered without the end user being affected, likewise the
The bit that I'm not sure about is what tools are used to access and support each layer. Would the internal schema be handled by the DBMS, the conceptual schema handled by some sort of DDL interpreter and the external schema handled by a DML interpreter (or have I misunderstood what each level does)?
Any assistance would be greatly appreciated.
Thanks, Moonshield
EDIT - Answer
Here's what I came up with for the software:
Physical Level - Concerns the physical storage of data - where and how it's stored on disk, indexes, etc. This is primarily handled by the DBMS, the storage settings can be tweaked by changing the configuration settings of the DBMS. DDL SQL is also used to support this level, for example setting specific datatypes and sizes.
Conceptual Level - This is essentially the logical structure of the database, basically your 3NF relational model. This would be primarily be supported by SQL (both DDL and DML I guess), although I suppose you could argue that CASE tools, etc. that are useful in mapping /documenting or implementing this would also be used to support this level. Also, you could probably argue that the DBMS supports this level too.
External Level - This is the view of the database that the end user sees, it's the highest level of abstraction because the user doesn't even necessarily see the underlying structure of the database (tables, entities, etc.), maybe only the data itself presented in the application interface. The software used at this level would be things like your stored procedures, JDBC code, etc. ranging up to full applications. SQL used here would mostly be DML seeing as most of the time you won't want end-users having access to the database structure.