ANSI-SPARC practical explanation

Question

I keep on hearing and seeing people mention ANSI-SPARC, we even have such a question in the exam list. External, conceptual, internal levels and stuff. I have crammed it, but when I actually try to understand it, it just feels like "blah blah blah" that was written to make books thicker. Could somebody clarify ANSI-SPARC concepts by a real life example. Lets say we have a MySQL server. What exactly is going on on each level?

Answer 1

The DBMS is the one that keep fisic and logic independence of the database. To maintain the logic independence we use views. Views are the representation of information from rational table. Ex: In an airport we have a table. There we have the id of the pasager, name, number of fly and seat number.

create table passengers ( nif varchar(200), name varchar(200), 
                         num_flight varchar(200), num_seat varchar(200) );
create view passengers_external_view as select * from passengers;

If we divided the table into 2 parts (id-name and fly-seat number) we only will need to modify the view inside the database, and there's no need to modify the program.

create table passengers ( nif varchar(200), name varchar(200) )
create table flight_passengers ( nif varchar(200), num_flight int, 
                                num_flight varchar(200), num_seat varchar(200) );

create view passengers_external_view as 
   select p.*, fp.num_flight, fp.num_seat
     from passengers p
       left outer join flight_passengers fp 
          on fp.nif = p.nif ;

Answer 2

These are the description of the three levels of ANSI-SPARC: External Data Level

In the relational model, the external schema also presents data as a set of relations. An external schema specifies a view of the data in terms of the conceptual level. It is tailored to the needs of a particular category of users. Portions of stored data should not be seen by some users and begins to implement a level of security and simplifies the view for these users

Examples:

• Students should not see faculty salaries.
• Faculty should not see billing or payment data.

Information that can be derived from stored data might be viewed as if it were stored.

• GPA not stored, calculated when needed.

Applications are written in terms of an external schema. The external view is computed when accessed. It is not stored. Different external schemas can be provided to different categories of users. Translation from external level to conceptual level is done automatically by DBMS at run time. The conceptual schema can be changed without changing application:

• Mapping from external to conceptual must be changed.
• Referred to as conceptual data independence.

Conceptual Data Level

• Also referred to as the Logical level
• Hides details of the physical level.

In the relational model, the conceptual schema presents data as a set of tables. The DBMS maps data access between the conceptual to physical schemas automatically.

• Physical schema can be changed without changing application:
• DBMS must change mapping from conceptual to physical.

• Referred to as physical data independence. Physical Data Level The physical schema describes details of how data is stored: files, indices, etc. on the random access disk system. It also typically describes the record layout of files and type of files (hash, b-tree, flat). Early applications worked at this level - explicitly dealt with details. E.g., minimizing physical distances between related data and organizing the data structures within the file (blocked records, linked lists of blocks, etc.) Problem:

• Routines are hardcoded to deal with physical representation.

• Changes to data structures are difficult to make.
• Application code becomes complex since it must deal with details.
• Rapid implementation of new features very difficult.

Here is an example:

    Sample of keep logical independence through database dessign changes:
    Before the change a single table of customers with email and fax:
    create table customers ( id int, name varchar(200), 
                             email varchar(200), fax varchar(200) );

create view customers_external_view as select * from customers;

After the change the customer table is splitted on 2 tables, but external view shows the same fields:

create table customers ( id int, name varchar(200) );
create table customers_adresses ( id int, id_client int, 
                                  adress_type varchar(10), adress varchar(200) );

create view customers_external_view as 
   select c.*,  ca2.adress as email, ca1.adress as fax
     from customers c
       left outer join customers_adresses ca1 
         on ca.adress_type = 'fax' and ca.id_client = c.id
       left outer join customers_adresses ca2 
         on ca.adress_type = 'email' and ca.id_client = c.id ;

Answer 3

You could see it like you are some kind of Bank manager, so, you need an App wich connects to your DBMS.

First of all, your employers wich manage the client database (debts, income, transactions...) and some people who want to watch their bank account, so, based on the architecture of the three levels of ANSI-SPARC, the external level, the employers must have access to the view (Management software), like your customers (ATM), for redirect them to the next level, named coneptual level, where they control and manage their personal data.

Example:

You have this customers’ table with the data(money, name, balance):

create table customers( nif varchar(200), name varchar(5000), 
                        balance numeric(15,2) );
create view customers_external_view as select * from customers;

Now, the bank must write every single transaccion the customer do, but the customer still want to see their currency:

create table customers( nif varchar(200), name varchar(5000) );
create table movements( nif varchar(200), moment datetime, import numeric(15,2) );
create view customers_external_view as 
  select c.nif, c.name, sum( m.import ) as balance
    from customers c
    left outer join movements m 
      on c.nif = m.nif
group by c.nif, c.name ;

Now, before that, the data is re-writed in the database, wich is located in the last level from ANSI-SPARC, the internal level.

Answer 4

It's a proposed standard from long ago. It had many good ideas in it and codified some existing best practices. It was never actually adopted as a standard, but it influenced how modern database systems are designed. If you had to distill it down to one sentence, the most important idea is that users of the database should be abstracted away from the actual details of the data storage: no user should know where or how the physical bits are stored, of course, but also custom views are available which provide clients with only the data that they should be allowed to see.

The Wikipedia article is extremely good.