¢ more Feiati pe ans that 60 O ; dant relations menust be merged a i ee in ju jations 2 ‘oO ati ‘ mvgundant TR. suppose there 2 osteo mas view integr™ str” pa . pa i 5,PHONE) cont p_DOB) Eg. They ca em a gee ee, pat EM ions is a8 i. | mrt of merging the BON” ee DOB) wont NE, EMP_JOB-COPE, EN" ting attribut ed | ADDRESS, PHONE, thout any repean& es. assig™ The new rel malize the Relations ted in step 1 and step 2 mY may arise while upd me unnecessary redundancy have so! ations. The process o: 4, Not ating these Te The relations creal lies or errors sme certain arom ‘ ; sor sation nfnes these relations to avoid these problems: £ £115 Physical Databas® Design the major objective o . Physical design is the last stage of database design Peas dis, files, indexes and snysical dataase design is to implement the databse as a set of rec other data structures. 2.15.1 Major Inputs to Database Design Three major inputs to physical database des 1. Logical Database Structure These are developed during logical database design such as normalized relations. ign are as follows: 2. User Processing Requirements . includes the size and frequency of database usage, response time, st backup 0 : ge, response time, security, . 3. Characteristics of DBMS It includes the characteris ving teristics of DBMS and other components of computer operating 2.15.2 Components of Physical Database Design Different components of physical database desi ing are as follows: ical dat; i se desir 1. Data Volume and Usage Analysis a Ibis used to es timate the si: of database siz le size or volume and Ze is used to select ume and usage patterns of dal le Be select the physical storage devices. It is also an cieovmine te 5 costs of stora; ge. The estim, metho, ate of usag nethods. It is also use, {usage patterns are used to select file organization and acces d to pl 2. Data Dice: Plan for the use of indexe: ata Distribution Strategy ndexes and a strategy for data distribution. a. Se Many or; ore Banization organization face sare using, di es Meee a Feo signi 1% distributed computer networks now a days. Thes* whe vhie ant problei em in physical database design. The problem is that the and select nod odes or sites ol es N- Network at which data will be located physicallyrte, Environment chapter 2=> Database latj, ‘The basic data distribution strategies are as follows: i as Ye ah; centralized / tion,” yn this strategy, all data is located at a single site. It is simple and easy to conduct. This ategy as three disadvantages: : pata stored at remote sites is not accessible readily. pata communication costs may be very high. AN be The database system fails totally when the central system fails. ii. Partitioned In this strategy, the database is divided into partitions or fragments. Each partition is tes, ignd toa particular site. The major advantage ofthis strategy is that data is moved closer so }eal user. Data becomes more easily accessible. incy, iti, Replicated 5S of In this strategy, the full copy of the database is assigned to more than one site in the network. This strategy maximizes local access. But it creates update probelms because each database change must be reliably processed and synchronized at all sites. iv, Hybrid e of : ae and In this strategy, the database is divided into critical and non-critical fragments. The critical fragments are stored at multiple sites. The non-critical fragments are stored at one site only. 3. File Organization File organization is a technique for physically arranging the records of a file on secondary devices. The system designer must recognize several constraints for selecting a file organization. These constrains inlcude the following: * Physical characteristics of secondary storage devices * Available operating systems and file management software up © User requirements for storing and accessing data Criteria to Select File Organization The criteria for selecting a file organization are as follows: NE * Fast access for data retrieval * High throughput for processing transactions * Efficient use of storage space * Protection from failure or data loss * Minimizing need for data re-organization aT * Security from unauthorized use ce ile Organization Methods 5 ss The files are organized on storage media in the following methods: 4. Sequential Files rimsequence, A 500760 the race records in sequential file organization are stored In Se hese se Fecords are stored one after the other. The F ase Wh a ; Nich they were stored, The principal stor:pe 50 IT Series => A Fundamental Study of Database Management yy SY, The major disadvantage of sequential access is that it is very slow. If the the lag ni is to be retrieved, all preceding record are read before reaching the last record. b. Direct or Random Files The records in direct file organization are not stored in a particular sequence Ay value of a record is used to determine the location to store the record. Each record is acc, directly without going through the preceding records. F : . rie This file organization is suitable for storing data on disk. Direct file organizatic, ree much faster than sequential file organization for finding a speci se A problem may occur in this type of files known as synonym. The problem occu: N the same address is calculated to store two or more records. c. Indexed Sequential Files ba‘ In indexed sequential file organization, records are stored in ascending or descenc:ri order. The order is based on a value called key. Additionally, indexed file organizaY maintains an index ina file. ‘An index consists of key values and the corresponding disk address for each recor, ad the file. Index refers to the place on a disk whete a record is stored. The index file is upd? whenever a record is added or deleted from the file. 7 ‘The records in indexed file organization can be accessed in sequential access as wel’gr random access or direct access. The records in this file f " 1 ‘ require ms a4 media. This method is slower than direct file orpitization se it requir or space on stoner search. quires to perform an int, 4. Indexes ‘An index is a table that is used to determine the lo used to speed up the sorting and searchin; improved with these indexes. The index may foreign key etc. be created on primary 5. Integrity Constraints i : a fe tocar of rows in a table. Indexes" ti » The performance of databas 'y, key, secondary key #* Database integrity means the correctness database protection. Integrity is related to the out help of integrity constraints. These constrains an a ind consisten, c - ia ity of data. int of data. It is another for™ consistent and correct. They act like a check on tre, the ruk the rules thee is maintained with * database maintains the quality of the incomii . enforce integrity of the data. ¥ data stored in it. Daye oe