Topics 6-10

Topics 6-10

Topics 1-5                        Topics 6-10  

Topics 11-15                    Topics 16-20 

   Topics 21-25

 

 

Topic 6-DBMS ARCHITECTURE

DBMS Architecture is divided into three parts

 EXTERNAL LEVEL
How data is viewed by an individual user

CONCEPTUAL LEVEL
How data is viewed by a community of users

INTERNAL LEVEL
How data is physically stored 

 

 

 

 

 

 

 

 

 

Topic 7-Data Independence

A major objective for three-level architecture is to provide data independence, which means that upper levels are unaffected by changes in lower levels.

 

 

 

Topic 8-Data Models

Conceptually, there are three broad options with regard to data­base models. These are:

 

a. Hierarchical model

b. Network model

c. Relational model

 

(a) Hierarchical model:

This model presents data to users in a hierarchy of data elements that can be represented in a sort of inverted tree. In a sales order processing system, a customer may have many invoices raised to him and each invoice may have dif­ferent data elements. Thus, the root level of data is customer, the second level is invoice and the last level is line items such as in­voice number, date, product, quantity, etc. This structure is quite natural when seen from the event point of view. However, the lower levels are owned by higher level data elements, and elements at the same level have no linkage at all. As a result, the query such as what products are purchased by which customer, in the above example, shall be difficult to carry out in the hierarchical struc­ture.

The query as to which customer purchased which product would be convenient. Thus, where there are many-to-many rela­tionships between two entities, this model would not be appropri­ate. Figure 9.4 shows the hierarchical model of data for a sales order processing application.

 

(b) Network model:

In the network model of database, there are no levels and a record can have any number of owners and also can have ownership of several records. Thus, the problem raised above in the sales order processing will not arise in the network model.

As there is no definite path defined for retrieval of data, the number of links is very large and thus network databases are complex, slow and difficult to implement. In view of the difficulty in implementation, network model is used only when all other options are closed.

The typical ex­ample of a network database may be the employee and the depart­ment he/she has worked or can work with in future. Figure 9.5 shows the network model of data for an employee information system.

 

 

(c) Relational model:

The most recent and popular model of data­base design is the relational database model. This model was devel­oped to overcome the problems of complexity and inflexibility of the earlier two models in handling databases with many-to-many rela­tionships between entities.

These models are not only simple but also powerful. In the relational database, each file is perceived as a flat file (a two dimensional table) consisting of many lines (records), each record having key and non-key data item(s). The key item(s) is the data element(s) that identifies the record. Figure 9.6 shows the files, and the fields that each record shall have in a customer invoicing system.

 

In these files, the key data items are customer id, invoice no, and product code. Each of the files can be used separately to gener­ate reports. However, data can also be obtained from any combina­tion of files as all these files are related to each other with the help of key data items specified above.

This is the fundamental advan­tage of the relational model of database along with its simplicity and the robustness.

Topic 9-Keys

·        Primary key - a primary key is a value that can be used to identify a unique row in a table. Attributes are associated with it. Examples of primary keys are Social Security numbers (associated to a specific person) or ISBNs (associated to a specific book). In the relational model of data, a primary key is a candidate key chosen as the main method of uniquely identifying a tuple in a relation.

·        Candidate key - A candidate key is a field or combination of fields that can act as a primary key field for that table to uniquely identify each record in that table.

·        Alternate key - An alternate key is any candidate key which is not selected to be the primary key

·        Compound key - compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes.

·        Superkey - A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent.

·        Foreign key - a foreign key (FK) is a field or group of fields in a database record that points to a key field or group of fields forming a key of another database record in some (usually different) table. Usually a foreign key in one table refers to the primary key (PK) of another table. This way references can be made to link information together and it is an essential part of database normalization.

 

Topic 10-Introduction to SQL

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.