sap4help@gmail.com +91 8956877466
This website is always under updation with new contents
Unit - II Data Models
Unit I
Unit II
Unit III
Unit IV
Unit V
Draw E-R Diagram for library management system.
Explain relationship among entities with example.
Ans: Relationship among two entities known as binary relationship. It may be one-to-one, one- to-many, many-to-many. These are explained as follows:
One –to-One Relationship: In this entity is related to only one entity. For ex consider the relationship from Manager to Department and from Department to Manager is both 1:1. In this case of an organization each department has only one manager.

Many-to-Many Relationship: When entities are related to each others on the basis of many objects of them it is called as many-to-many relationship. The relationship between the employee and the entity project can be derived as follows: Each employee could be involved in a number of different projects and a number of employees could be involved in a number of different projects and a number of employees could be working on a given project. This relationship between Employee and Project is many-to-many.

What is generalization and aggregation E-R models ? Explain with examples. Ans:

Specialization or generalization: An entity set may include sub groupings of entities that are distinct in some way from other entities in the set. For instance a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. These sub groupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set. Consider the entity set account with attributes account number and balance. An account is further classified as being one of the following:-
i) Savings account and
ii) Checking account
Each of this account type is further described by a set of attributes that includes all the attributes of entity set account plus additional attributes. For example saving account entities are described further by the attribute interest-rate, where as checking account entities are further described by the attribute overdraft-amount. The process of designating sub groupings within an entity set is specialization. The specialization of account allows us to distinguish among accounts based on the type of account.
Generalization defines a is-a-kind of relationship in which one class shares its structure and/or behavior with one or more other classes. Consider the example of checking account entity from the above figure with attributes account_number, balance and overdraft_amount and the saving account entity set with the attributes account_number, balance and the interest_rate.
There are similarities between the cheking_account entity set and the saving_account entity set in the sense that they have several attributes in common. This commonality can be expressed by generalization, which is a containment relationship that exists between a higher level entity set and one or more lower level entity set. Here the account entity set is the higher level entity set and the checking account is the lower level entity set.
Aggregation - a feature of the entity relationship model that allows a relationship set to participate in another relationship set. This is indicated on an ER diagram by drawing a dashed box around the aggregation. The E-R model cannot express relationships among relationships.
Consider a DB with information about employees who work on a particular project and use a number of machines doing that work. We get the E-R diagram shown in Figure

Figure A : E-R diagram with redundant relationships
Relationship sets work and uses could be combined into a single set. However, they shouldn't be, as this would obscure the logical structure of this scheme.
The solution is to use aggregation.
• An abstraction through which relationships are treated as higher-level entities.
• For our example, we treat the relationship set work and the entity sets employee and project as a higher-level entity set called work.
• Figure B shows the E-R diagram with aggregation.

Transforming an E-R diagram with aggregation into tabular form is easy. We create a table for each entity and relationship set as before. The table for relationship set uses contains a column for each attribute in the primary key of machinery and work.
Explain direct file organization.
Ans: A Direct file is one whose records are stored in such a way that access to any one of them is direct – that is without going through a lot of records to get to the one(s) you want.
Records in a Direct File are not placed one after the other – rather a location for the file is set aside on disk and then the records are written to any location that is free within this area. A technique known as “hashing” uses a Key to produce the location on the disk for each of the Records – and it is through this that individual records can be accessed directly.
A Direct file provides the fastest possible access to records. ISAM (indexed-sequential-access method) also provides users with direct access to individual records. Direct file is typically the best when access time is critical and when batch processing is not necessary.
Access time is the interval between the moment at which an instruction control unit initiates a call for data and the moment at which delivery of the data is completed. For example, direct access memory is faster than sequential access memory.
A Direct file uses a formula to transfer the primary key to the location of each record. This formula is called a Hashing algorithm. Therefore, no index is needed to locate individual records. Many hashing algorithms have been developed. In general, the primary key value is divided by a prime number, which corresponds to the maximum number of storage locations allocated for the records of this file. The reminder obtained in this division is then used as the relative address of a record, but relative address can be translated into physical locations on the storage medium.
For example you input a Student ID Number, a mathematical formula is applied to it, and the resulting value is the value that points to the storage location on disk where the record can be found.
Explain entities, attributes and associations example.
Entity is a thing in the real world with an independent existence and entity set is collection or set all entities of a particular entity type at any point of time. It is used to define object such as student, customer, employee. Take an example: a company have many employees ,and these employees are defined as entities(e1,e2,e3....) and all these entities having same attributes are defined under ENTITY TYPE employee and set{e1,e2,.....} is called entity set.
Attributes: An entity can be defined by its property. These properties describe the characteristics of an entity and it is used to store data about an entity.
In a database management system (DBMS), an attribute may describe a component of the database, such as a table or a field, or may be used itself as another term for a field.
Types of attributes:
Single valued and multivalued attributes: The attributes all have a single value for a particular entity are said to be single valued. For ex. Loan entity refers to only one loan number. An entity which has multiple value such as an employee entity set with the attribute phone_number. An employee may have zero, one or several phone numbers and different employees may have different numbers of phone. This type of attribute is said to be mutivalued.
Derived attribute: The value for this type of attribute can be derived from the values of others related attributes or entities. Suppose that the customer entity set has an attribute age , which indicates the customer’s age. If the customer entity set also has an attribute date of birth, we can calculate age from date of birth and the current date. Thus age is derived attribute.
Association:
What are different models use in DBMS? Explain any one of them.
Ans: Data Model can be defined as an integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization.
A data model comprises of three components:
• A structural part, consisting of a set of rules according to which databases can be constructed.
• A manipulative part. Defining the types of operation that are allowed on the data (this includes the operations that are used or updating or retrieving data from the database and for changing the structure of the database).
• Possibly a set of integrity rules, which ensures that the data is accurate.
The purpose of a data model is to represent data and to make the data understandable.
There have been many data models proposed in the literature. They fall into three broad categories:

• Object Based Data Models
• Physical Data Models
• Record Based Data Models

The object based and record based data models are used to describe data at the conceptual and external levels, the physical data model is used to describe data at the internal level.
Relational model:
1. It is an example of record-based model. In record based models the database is structured in fixed –format. Relational model contains a collection of tables.
2. These tables are used to represent both data and the relationship among those data. Each table has multiple columns with unique names.
3. The relational model has three basic components :
a. 1) Data structures: It is the set of attributes and relation.
b. Data manipulation: These are the operations on relation.
c. Data integrity rules.
4. Fig shows a sample relational database. It consists of two tables. One is customer table and another is account table.

• The customer table stores the customer information for example the customer Yash has id 002-1140 lives on Satara Road and has an account number 512.
• In relational data model the data structure is used to store data and relation among the data. It is used to represent both entities and the relationship between them.
• Relations are stored in table. Rows of relation called as tuples of the relation and columns are called attributes of the relation.
• Each attribute of a relation has distinct name. The values for an attribute or a column are taken from a set of values. These set of values are known as Domain.
• The basic building blocks of the relational model are listed and are shown in fig.
o Relation: It is a table of data e.g. Cust table.
o Tuple: It is row of table.
o Attribute: It is a column of table.
o Cardinality: The number of tuples in a relation is called as cardinality of the relation.
o Degree: The number of attributes in a relation is called as degree of a relation.
o Primary key: It is used to identify each row uniquely. It is also called as integrity constraint.
What are the advantages and disadvantages of index sequential file?
Ans: Advantges:
An Indexed Sequential file can be accessed in two ways
1. Sequentially reading through each of the records from the beginning in the Main data file – ie exactly the same way as a sequential file OR
2. Using the Index file (which is a MUCH smaller file) to determine the location on disk of the Record – and then accessing that Record using the Direct manner.
3. They can be quite efficiently used for sequential processing of high activity ratio applications.
Disadvantages:
An Indexed file has problems with lots of records that are added and deleted i.e. deleted records are not really deleted – they are “flagged” as deleted and additions to the file are not placed in sequential order in the main data file – they are placed in any available space – and this is catered for in the Index file.
The Indexed Sequential File is not really suitable for Volatile files i. e. the files in which a lot of records are being written and deleted.
Differentiate between network and hierarchical data model in DBMS.
Ans: Network data model:
• It is based on Record based data model.
• Data in Network is represented by the collection of records and the relationships among data are represented by links.
• Links are nothing but the pointers.
• The records in the database are organized as collection of arbitrary graph.
• A network database is a collection of records.
• In graphical representation these records are connected to one another through links.
• A link is an association between two records in the database.
• A data structure diagram is used to represent the design of a network database. In this diagram boxes are used to represent record types and lines are used to represent links.
• Following figure shows sample network databse suing the same information as represented in fig.

• In network model any data item can be linked to any other item.
• This structure allows One- To – One, One- To- Many , Many – To – One, Many-To- Many relationship. Network model has a complex structure.
Hierarchical Data Model:
• It is one of the types of record based data model. It is similar to the network data model.
• In both models the records and the links are used to represent data and relationship among data respectively.
• In this model records are organized as a collection of trees rather than arbitrary graphs.
• To represent hierarchical database tree structure diagram is used. In this diagram boxes are used to represent record types and lines are used to represent links.
• For example consider a customer and account database. They are represented as two record types. A sample database is shown in fig.

• In the above sample database all customers and accounts records are organized in the form of a rooted tree, where the root of the tree is a dummy node.
• Hierarchical model is sometimes called as tree structure. It is composed of the hierarchy of elements called nodes.
• The upper most level of hierarchy has only one node called the root. No element can have more than one parent.
• The structure allows one to- one, one-to –many relationships.
• The disadvantage of this structure is wastage of storage space.
Discuss sequential file.
Ans: Sequential file:
• A sequential file is one whose records are arranged in an order – that is by a key of some kind. To put the transactions in order the file has to be sorted according to the order of the Key Field. Examples of Key fields would be Tax File Number (TFN), University Student Number or Medicare Number.
• To gain access to a record in a sorted sequential file involves going though all the records until the desired record is encountered. This is why it is called sequential and Tape is a common way of storing sequential files. Sequential files can also be stored on Disk.
• Updating the Sequential must occur by a Batch process which first involves sorting the new data. The next step is to read the sequential file (which itself is in order) and insert the new items in their correct locations – and then rewriting the new file in the process
• Systems that are suitable for sequential batch processing are systems that are able to collect data and accept output at a leisurely rate. You cannot get instant results with a Batch processed Sequential File
Explain entiy relationship model .
Ans: The ER model is based on the perception of a real world, which consists of a collection of basic objects called entities and the relationship among these objects. An entity is object that is distinguishable from other objects by a specific set of attributes.
Eg. The accounts number and balance describe one particular account in a bank. A relationship is an assocoaition among several entities.
Eg. The customer- account relationship associates a customer with each account that he has.
The set of all entities at the same time and relationships of the same type are termed as entity set and relationship set respectively.
The Er model represents certain constraints. One important constraint is mapping cardinalities which express the number of entities to which another entity can be associated via a relationship set.
The overall logical structure of a database can be expressed graphically by an ER diagram which cocnsists of the following components.
i) Rectangles which represents entity sets.
ii) Ellipses which represents attributes.
iii) Diamonds which represents relationship among sets
iv) Links which link attribute to entity sets and entity sets to relationship.
v) The enity relationship model is the best – known example of a class of data models usually known as extended or sementics models.
Different symbols used in ER diagram:

Explain the index sequential file organization.
Ans: A compromise between sequential and direct file organization is Indexed File organization. The data file is written in an order sorted by a Key Field (as with sequential) but also an Index File is created.
• Each record of a file has a key field which uniquely identifies that record.
• An index consists of keys and addresses (physical disc locations).
• An indexed sequential file is a sequential file (i.e. sorted into order of a key field) which has an index.
• A full index to a file is one in which there is an entry for every record.
• Indexed sequential files are important for applications where data needs to be accessed sequentially as well as randomly using the index.
• An indexed sequential file allows fast access to a specific record. Example: A company may store details about its employees as an indexed sequential file.
• Sometimes the file is accessed sequentially. For example when the whole of the file is processed to produce pay slips at the end of the month randomly. Maybe an employee changes address, or a female employee gets married and changes her surname.
• An indexed sequential file can only be stored on a random access device eg magnetic disc, CD.
Draw an E-R diagram for bank organization. Various entities to be considered are listed below
i)Branch (attributes: branch name, branch-city, assets)
ii) Customer(attributes: Customer-no, customer-name, city)
iii)Loan (attributes: Loan_no, amount, org_branch)
iv) Account( attributes: account_no,balance)

Mapping cardianlities of the system are
i)	Borrower is many-to-may relationship between customer and loan.
ii)	Loan-branch is one-to –many relationship from loan to branch
iii)	Depositor is many-to-many relationship between  customer and account.
iv)	Account- branch is one-to-many relationship from account to branch.
Ans:
Construct an ER diagram for car insurance company that has a set of customer, each of whom own some or more cars. Each car has associated with its zero to any number. Ans:
Draw an e-r diagram for the following problem.
Customer place orders for universal products. 
Orders are filled in the order processing dept by order processing clerks. 
In the order processing dept an order number is assigned to each other for identification 
and an invoice with the cost of the products for the order is produced. When the invoice 
is sent to the customer  a shipment is also made to the customer by the shiping department.
Ans: Entities : customer(attribute: custid, custname, address)
Product(attribute: pid, pname, price)
Customer has one- to- many relationship with the product.
what is hashed addressed direct file organization? Give steps to find the hashed address for each and every key of the file.
Hashed addressed direct file organization :
The keys of records in the hashed addressed direct file organization are scattered randomly throughout an area called hashing table which enables the users to access each and every record in a rapid manner directly without using indexes. Hence it is considered to be the most efficient methods for accessing files in most of the online system.
The hashed file consists of primary storage area and overflow storage area. Each of this area consists of several buckets and slots. Each bucket has a set of slots. A slot is the smallest storage location in the hashed file organization.
Each and every key value of a record will be stored in a slot of bucket using a given hashing algorithm.
There are many algorithms to find the hashed address for each and every key of the file of our interest. An efficient key conversion algorithm is remainder algorithm.
Steps to find the hashed address for each and every key of the file are as follows:
step 1. Identify the total number of records in the file.
Step 2. Decide the number of slots per bucket.
Step 3. Determine the approximate number of buckets required to store the keys of all the records of the file. This should be a prime number.
Step 4. Consider the first key.
Step 5. Divide that key by the prime number and find the remainder. Let it be R.
Step 6. Check whether ther is at least one unfilled slot in the Rth bucket of the primary storage area. If the answer is yes, go to step 7, otherwise go to step 8.
Step 7: Store the current key in the first unfiled slot of the Rth Bucket. Then go to step 9.
Step 8.Store the current key in the overflow storage area.
Step 9. Consider the next key.
Step 10. Repeat step 5 through step 9 until all the keys are placed either n the primary storage area or in the overflow area of the hashed file structure.
For accessing computer programs go to TECHNOLOGY