Instances and Schemas

Databases change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is called an instance of the database. The overall design of the database is called the database schema.

Consider the following database table:

Employee Table:
 

In the Employee Table, schema and instance are clearly shown.

Database systems have several schemas, partitioned according to the levels of abstraction. The physical schema describes the database design at the physical level, while the logical schema describes the database design at the logical level. A database may also have several schemas at the view level, sometimes called subschemas, that describe different views of the database.

The logical schema is the most important among all the schemas since programmers construct application programs or front end interfaces by using logical schemas. The physical schema is hidden beneath the logical schema, and can usually be changed easily without affecting logical level. Hence application programs do not need to be rewritten if physical schema changes and are said to exhibit physical data independence.

I am writing a book on Databases. In the current blog, I will give you every Friday morning BDT time, small excerpts on Databases from my book in order to brush up on the subject. My eBook on Databases is a copyright in 2015 by me, Rosina S Khan. No part(s) or whole of these excerpts may be used or reproduced in any form whatsoever, without written permission by the author.

Data Abstraction

Since many database-system users are not handy with computers, developers hide certain complexity details through several levels of abstraction in order to make it easier for users’ interaction with the system.

·     Physical level: The lowest level of abstraction describes how data are actually stored. It describes complex low-level data structures in detail.

·        Logical level: The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data. It describes the entire database in terms of a small number of relatively simple structures. Database administrators use the logical level of abstraction who must decide what information to store in the database.

·     View Level: The highest level of abstraction describes only part of the entire database. In this level users need to access only part of the entire database. The level simplifies the interaction of users with the system. The system  may provide many views for the entire database.

I am writing a book on Databases. In the current blog, I will give you every Friday morning BDT time, small excerpts on Databases from my book in order to brush up on the subject. My eBook on Databases is a copyright in 2015 by me, Rosina S Khan. No part(s) or whole of these excerpts may be used or reproduced in any form whatsoever, without written permission by the author.

Why the Advent of Databases-2

File-processing systems have other disadvantages over Databases as well. Hence the advent of Databases. I give two other of these disadvantages below:

·        Concurrent-access anomalies: Multiple users may update a system simultaneously. This facilitates faster response and overall performance of the system. However, interaction of concurrent updates may result in inconsistent data. Consider bank account A, containing Tk500. If two customers withdraw funds say Tk50 and Tk100, respectively from Account A at about the same time, the result of concurrent transactions may leave the account in an inconsistent state. If the two transactions occur concurrently, they may both read the value Tk500, and write back Tk450 and Tk400, respectively. Depending on which one writes the value last, the account may contain either Tk450 or Tk400, rather than the correct value of Tk350. To prevent this from happening, the system must maintain some form of supervision. But supervision is difficult to provide because data may be accessed by various different application programs that have not been coordinated previously.

·        Security problems: Every user of the database system should not be able to access all the data. For example, in a banking system, payroll personnel (tax officer) needs to see only that part of the database that has information about the various bank employees. They do not need to access information about customer accounts. As another example, bank tellers see only that part of the database that has information on customer accounts. They cannot access information about salaries of bank employees. Enforcing such security constraints on a file-processing system is difficult because application programs are added to the system in an ad hoc manner.

P.S  I am writing a book on Databases. In the current blog, I will give you every Friday morning BDT time, small excerpts on Databases from my book in order to brush up on the subject. My eBook on Databases is a copyright in 2015 by me, Rosina S Khan. No part(s) or whole of these excerpts may be used or reproduced in any form whatsoever, without written permission by the author.

Why the Advent of Databases-1

I am writing a book on Databases. In the current blog, I will give you every Friday morning BDT time, small excerpts on Databases from my book in order to brush up on the subject. My eBook on Databases is a copyright in 2015 by me, Rosina S Khan. No part(s) or whole of these excerpts may be used or reproduced in any form whatsoever, without written permission by the author. Here goes the very first post:

Before the advent of databases, organizations stored information using a typical file- processing system. In this system, permanent records were stored in various files and different application programs were written to extract records from, and to add records to, the appropriate files.

File-processing systems have a number of disadvantages. Two of these are outlined as follows:

 

• Data redundancy and inconsistency: Different programmers may write the files and application programs over a passage of time. As a result files may have different structures and the programs may be written in different several programming languages. Also the same information may appear in different files. Data inconsistency results when the same information is updated in one place but not in another place in addition to higher storage and access cost.

• Difficulty in accessing data: Data retrieval may be problematic. Suppose a bank officer needs to find out the list of customers who live in a certain postal code. Such an application program does not exist. There is, however, an application program to generate the list of customers. The data processing department based on the demand of the bank officer has either to generate the list of customers and extract the needed info manually. The other alternative is to write a new application program to meet the demand. Both the alternatives are unsatisfactory. After a week or so, the bank officer needs to trim down the list of customers with bank balances more than Tk50,000. Again the data processing department is left with two alternatives both of which are unsatisfactory. The point here is that file processing systems do not allow data to be retrieved in a convenient and efficient manner.