“i p12 B.Tech. Il Year Il Semester Examinations ai DATABASE MANAGEMENT SYSTEMS | (Common to CSE and 7) Foes | Answer any five questions, one question froin Each Unit All questions carry Equal marks (6x45 Marks = 75 Marks), Solutions 4 @ What is a database? Explain the different types of databases. (Unit 0) . +p)! Discuss the activities of different database users: (unita 012) oR 2 (@) Discuss the basic building blocks of data models, | (Unit 017) (0) What do you mean by environment in database systems? Explain with the help ‘ of database system structures. (unit! /023) 4. (@) Write short notes on the following. (Domain (ii) Attribute , (ii) Tuple . : - + (w) Relation. . (Unit / 03) (t) Explain the importance of avording NULL values in a database. (units /05) OR 4. (@)” By considering suitable examples, describe the usage of SQL CREATE and ALTER statements. _ Waitat 044) (t) Define function. Briefly explain about classification of SAL function. : (Units /023) & @) Define the teims, entity, entity set, attributes. (Unit / 02) (0) ‘What is relation schema and state? 2 a *(unieat 08) 4 Oe . \ (@) Discuss about creating tables with relationship. {unita 013) () Distinguish between independent and correlated nested queries. provide appropriate. : : . ‘ (Unit Ait | 020). Sy ‘examples to. support your explanation. || [SPECTRUM ALL-IN-ONE JOURNAL FOR ENGINEERING STUDENTS Scanned with CamScanner= MANAGEMENT SYSTEMS [JNTU-KAKINADA} ( BAS! pirate ae (union | \ 7a)’ What is schema refinement? {6) ‘State the Armsirorg inferénce fules. Provide suitable exainpies to describe each. (WnitV 107) " wagavam ZATAG 8. (a) Explain about lossless join decoriposition. omnis (Unit f01g) | : (b). What is minimal coverlirreducible,set of functional dependencies? Write and.explain.. i the steps of the algorithm used for finding minimal cover. Consider an example set |. of FDs and trace the algorithm. : (Unit1v +05) ‘9. (a) Drawtransaction state diagram and describe each state that a transaction goes 4° through during its execution.” | (Unit V 102) >». (@) Write about transaction isolation levels. How is the implementation of isolation done (Unity 044) in'the transaction? eo oR 40, (a) State and explain various file organisation methods. Give suitable examples to each them. (Unit | 028) (b)_ Compare heap file organization with hashed file organization. (Unit 1033) Scanned with CamScannerModel Question Papers with Solutions z MP. #19 OPE ml Becki. Il Year Il Semester Examinations... APER 2 | DATABASE MANAGEMENT SYSTEMS (common to CSE and IT) Time: 3 Hours 7 - TS “Answer any five questions, one question fom Bech Unit. a . Allquestions carry Equal marks = / (5 x 15 Marks = 75 Marks) sofikion’ 1. (a) What is DBMS? Explain the components of database system. {Unit | 02) _.(@), What are the advantages of Dems? (units )014) oR ta models. (Unit / 018) : < What is a data model? Describe various dat: (0) Discuss in brief about centralized database structure. (Unit | 024) \ relation schema, relation instance, domain constraints, 3. (a) Define the terms database schema. cardinality of a relation, degree of a relation, relational (Unit tt / a1) Explain about database schema with the help of example. (nit / 012) (b) - OR . 4. © (@)_ Discuss about select and project operations. (Witt /020) (0). Define character function. What are the different character functions? Discuss. (Unit / 025) 5. (a). Define rélationship and relationship sets. Also discuss the degrees of relationship {Unit-tit | Q5) with the types. (b) Explain about superclass and subclass of an entity types with an example. (Unit4N/03) + OR 6. (a) Write short notes on different types of joins. ins. (Unie tn | 031) (b) What is a subquery? What are its basi ic chi ist ierene nbs iaracteristics? What are the possible ota yak ¥ (Unittt | 022) Scanned with CamScannerEe OE a 4 DATABASE MANAGEMENT SYSTEMS [JNTU-KAKINADa) a - (a). Explain the role of functional dependency in normalization with suitable example. . (Unie oT : : (b) Discuss in detail about various normal forris. f . (Unitav | ap) OR * 8 (a) Whats the importance of dependency preservation during decompo: ¢ How to achieve it? 3 : “ (Uunieav 019 (b) How does BCNF differ from 3NF? Explain with an example: (Unitav | 012) 9. (a) Explain shadow-copy technique for atomicity and dura (Unit v 05) (b) » Explain failure-classification in detail (Unit-V | 016) OR 10. (a) _ Explain about variable-length file organization with an example. © (UnitV | 028) (b) Give a comparison of various file organizations, (Unit 034) Scanned with CamScannerB.Tech. Il Year ll Semester Examinations DATABASE MANAGEMENT SYSTEMS (Common to CSE and IT) “Timer 3 Hours ‘Max. Marks: 75 “Answer any fie questions, ane question from Each Unit All questions carry Equal marks . (6 * 15 Marks = 75 Marks) Solutions 1. -(a)’ What are the responsibilities of DBA?“ (Unit | 05) (6)" Explain database system applications, {Unit | 015) ae ; OR 2. (a). Discuss briefly about, (Instances and schemas (i), Data independence. . (Unit4 | 020) (b) Describe in detail abouit two-tier and three-tier client-server architectures. (Units 025) 3 @) Explain the form of basic SQL query in detail with examples. (Unit 011) (6). List and explain the common data types available in SQL. (Unit). 013) oR . 4, (a) - Discuss various arithmetic operations in SQL with examples. (Unies / 021) (b) _Explain about Date and Time functions of SQL. (a) What is ER model? Explain its concepts. (Unit-tm 101) (0) With the aid of appropriate examples, describe how to model the following in ER model: (i). Entity type: (ii), Relationship type (ii) Super class (iv) ‘Sub class. (Unittm | 010) . oR : 6 (a). Explain briefly integrity constrair ints i i : over relations. 2 (Unie 018) (b) Explain the purpose of Group by and Having clauses in SQL. : ; 7 {Uniean | 026) SAAT coreronm any se men - pase Scanned with CamScannereat DATABASE MANAGEMENT SYSTEMS [JNTU-KAKINAt MP6 oy “What is normalization? Explain its need. “(o) Wat's surrogate key? What {fe its advantages and disadvantages? it = (@). What is multivalued dependency? Illustrate 4NF with’ an ‘xamples. (©) “Explain how to decompose a relation into BCNF with example. (a) Define serializability. Explain conflict serializability and view serializability in detail (©). Explain the concept of transaction atomicity. t OR” (2) Define an index. What are the different kinds of indices? Explain the factors based'” (on which the technique of indexing is evaluated (©) Explain heap file with unclustered tree index, Scanned with CamScannerpeer —— | 19 ; | RI? « -B.Toch. Il Year 1 Semester Examinations DATABASE MANAGEMENT SYSTEMS (Common to CSE and IT) Finer) Hours Max, Maris 73 , Answer any five questions, ‘one question from Each Unit “Altquestions carry Equial marks (6 x 15 Marks = 75 Marks) Solutions 4. (a) Describe the characteristics of a database system. : (Unit | G6) (b)_ Explain about data modeling and data tmadets along with the Importance, (Unit 016) - "OR 2 (a) ’ Draw and explain three-tier schema architecture of database system. (Unit |. 021) (0) Explain the process of developing client application. (Unit 027) (Unit | 010) 3. (a) Whatis SQL? List the features of SQL. 5 (Unittl | 015) (b) Write and explain the structure of SQL SELECT statement with suitable example. OR 4. What is SQL single row function? By means of suitable examples illustrate the usage of SQL date, character and number functions. (Unit-tl | 028) & (a) Discuss the importance of entiy integrity and referential integrity constraints. (Unit / 07) (b) Whats a view? How views are implemented? (Unitil | 033) OR a — a (a) Whatis a nested query? Explain with an example. {Unit-lll | 018) (0) List and explain aggregate functions used in SQL with examples. (UnittN / 027) 2 @ in all the functic i it . ) Explain all the functional dependenciés in entity sets. (UnitAV | 05) (b) State BCNF. How does it diffe 1 it differ from 3NF? (Unit-tv / 011) OR & " (a) Explain about Fith Normal Form (51 i , (SNF). (UnitAV / 018) Prove that : /e that the 3NF synthesis algorithm produces a lossless-join decomposition Cf the relation containing all the Original attributes. (Unittv 1.015) (Milne coco on Scanned with CamScannerMP.8 10. (a) *(b) (a) (b) DATABASE MANAGEMENT SYSTEMS [JNTU-KAKINAD,) ‘What is transaction? Mention the desirable properties of transaction. (Upiev 1 Q5, Explain about the B*-tree and the structure of B*-tree in detail with an example. Whitv 025 OR ae Explain in detail about the two basic approsiches for organizing data entries. * (Unity | 03) Explain f xes and performance tuning in detail. (Unit v (037) Scanned with CamScanner“Answer any five questions, one question from Each Unit | RID i iach Voorn emesir aint ) i DATABASE MANAGEMENT SYSTEMS Yq (Common to CSE and IT) : ' ‘Max. Marks: 75 | Fime: 3 Hours: \ \ i i | | ©) \ | 12 @ oe) | \ ;3 @ | it toe sy \4 (@) 1 an) i5 @ ) All questions carry Equal marks (5 * 15 Marks = 75 Marks) What is meant by data independence? RefenUimie Od List out the characteristics of database system. Refer Ualeh 8 OR + List diferent types of database users. REREFURIEG OS Describe in detail about two-tier and three-tier client-server architectures. REfEEUAIET O28 Define the terms relation schema, relation instance, domain constraints, cardinality of a relation, degree of a relation, rélational database schema. Refer Uai@liyO1 Listand explain the common data nee available in SQL. Refer UaieinQrs oR What is DML? Explain DML operations with examples. Reer Uniti, Qis Discuss about select and project operations, Ree Unie G0 Describe the concept of referential integrity. Refer Unie 7 & e Dis 'Scuss in detall about the concepts of E-R model with Suitable examples. Ring OR SPECTROM ALLIN-ONE tonne: on =. Scanned with CamScanner10. ! © (a) (b), (a) . 0) (a) (b) , State ANF, 2NF and 3NF and explain with examples. ReterUi ‘Why normalization is needed? Explain the processes of normalizatior State BCNF. How does it differ from 3NF? ‘What is schema refinement? Refer Ui Draw transaction state'diagram and describe each state that a transaction goes through during its execution. . s ele ae Explain about the B’-tree and the structure of B-trée in detai Scanned with CamScannera © B.Tech: Il'Year Il Semester Examination _ DATABASE MANAGEMENT SYSTEMS... , (Common to'CSE and IT) ‘Time: 3 Hours ; Max Manisa “Answer any five questions, one question from Bach Unit. ; All questions carry Equal marks (8 18 Marks = 75 Marks) 1. (@) ‘What is DBA? Mention the fanctionalties of DBA? Refer Unies (b) Differentiate between file system and database system. Ket Beer Pune TARO OR 2. (a) Draw and explain three-tier schema architecture of database system. Ref (b) Explain the client-server architecture of DBMS. Refer Unit-ly ¢ 3 (a) Explain the importance of Null values in relational model. Refe rUr (b) With the aid of relevant examples ilustrate different DDL. statements supported by SQL. ¥ OR 4,” "(a)~ Explain the form of basic SQL query in detail with examples. f (b) - What is SQL singlé row function? By means of suitable examples illustrate the usage of ‘SQL date, character and number functions. Refér | (@) Explain in detail about inheritance, specialization and generalization using ER diagrams. Refer Unite Qi (b) Differentiate between independent and correlated nested queries. OR 6 ; f (@) Explain in detail about various key constraints used in database system: (b) Explain relational set opefators with examples. | : {{ SPECTROM @LLIN-ONE JOURNAL FOR ENGINEERING stapents Ji Scanned with canisean nerDATABASE MANAGEMENT SYSTEMS {[JNTI GP.4 7. List and explain the inferencé rules of functional dependencies. Refer Unit) {b) Explain all the operations on B*-trees by taking'a sample example. B OR 10. (a) Explain indexes anid performance’ tuning in detail. Ref - (b)_ Explain failure-classification in detail, Refer Baie Scanned with CamScannerDATABASE SYSTEM Q1. What is a database? Explain the different types of databases. Answer : Database A database is defined as an integrated, shared collection of data. It consists of both ‘end-user dats’, which includes ray facts of interest and ‘metadata’. This database includes data about data using which end-user data are ee and. integrated, re is usually so large that it has to be stored ‘on the secondary storage devices like disks or tapes. Such data.can es maintained a, collection of operating system files and stored in a database structure called DataBase Management System (DI MS), ‘Types of Databases The classification of databases is done based on the following, 1. Number ofusers ” : { 2. — Location of databases 3. Type of usage of databases, . ‘ ny "Qty 1, Number of Users - The following databases aré classified based on “number of users” criterion, © Single-User Database: It refers toa database that ca be used by only one user ata time. GD Desktop Database: It refers to a single-user database running on a personal computer. (CH) Multi-user Database: It refers toa database that can be used by several users at the same time. () Workgroup Database: It refers to a multi-user database that can be used by a small number of users ie, by a particular department in an organization ‘ () Enterprise Database: Itrefers to a multi-user database that can be used by ‘multiple users ie., by many departinens in an organization (or by an entire organization), 2. Location of Databases ‘The following database are classified based on “location of databases" eriterion, © Centralized Database: It refers toa database that supports the data residing on a particular site,” Distributed Database: It refers toa database that support the data, which is distibuted among multiple sites. 3. Type of Usage of Databases The following databases are classified based on the “type of usage of databases” criterion, (© Operational Database: It refers to a database that supports operational data (day ‘mostly used in making decision, which are based on the historical data }) Data Warehouse: It refers to a database thet contains data derived Such data must be extracted so as to make certain trategicor plan What is DBMS? Explain the components of datal : Answer : ’ Database Management System (DBMS) 'y-to-day activities). This database is from multiple sources. The Scanned with CamScannerComponent ‘The following are the major ‘components of a database system ‘components of Database SP - 1 Hardware . Hardware is collection of the following devices like, 0 physical Devices . . ‘these devices include Computers, workstations, mainframes, printers, etc E o Netiwork Devices ye Be ‘est devies include routers, switebes hubs, ete. a Storage and Other Devices . ‘these deviees includes ID card readers automated teller machines etc. . Basically, these devices together are used to support comiputer-related activities such as data’and ‘communication processing. 2, Software Software is a set of jnstructions/programs used by the hardware in order to process. information. It inchides the following, (Operating System Software It is responsible for managing all the components . related to the hardware ‘and also for making the: software tfficient, so that it can run on the COmpUIGT. ‘Such type of software includes | ‘Microsoft Windows OS, MACOS, ‘UNIX 0S, LINUX OS, ete. (i) DBMS Software Itis responsible for managing the database structure ina particular database environment. Such type of software include oracle corporation's oracle, IBM’s DB2 and inicrosoft access and SQL server. (i Application Software and Utility Software Application software refers to a set of programs that ate responsible for accessing arid manipulating the data (chistingin the DBMS) in order to perform some specific business activities like generating reports, mal decisions and preparing tabulations. ntti nity tines io ds frat tat ar responsble for manayig several componeits ata system environment by retrieving the data data storage, converting programs into machine Understandable form, sorting data and soon. The se util n hardware uses utility system software to run the UNIT Intreduction cam 8S : ae os of Database Systems 3 People : People refers.to all the users who are workir working ini a particule database environment. They code, @ System Administrators : ‘They are the people who manage the general operations le Q6. Describe ‘the characteristics of ‘a database ‘system. (Model Papersv, a1(b)| OctJNov.-16, Set-1, a2(a)) OR. Ust out the characteristics of database system, Answer : Nov.-5, Set-3, 21a) The following are the various characteristics of a database, i) Self -Descriptive nature ‘of a database system (i) Provides insulation between program, data and data abstraction. — « ‘ Gil) ‘Supports multiple views of data’ 7 (iv) Shares data and multiuser transaction Processing (¥) - Controls data ‘redundancy, (vi). Supports implementation of integrity constraints (vii) Regulates ‘unauthorised access (viii) Supports data independence (b) Provides backp and recovery facil Manages information Provides 8 tures that 2 database to its users, Database System For answer refer ‘ment System (DBMS), Features of Database System 3 ‘The database system: its users. Unit-I, Q2, Topic: Database Manage- Provides the following features to _Forteinaining answer refer Unit Q6, : LEZ, warene: *EtoxPhelocopying of ts bookie & CRIMINAL 2. Data dependence. Structural Dependence - 1 Structural dependence refers associated with structure of the file. For Address" field of Employee file structure is changed, then arty’ Program, which performs the following steps istequired boca ‘he previous programs donot work with the new Employee fig 8 to the dependency r instance, when “emp. () | Read field record from the actual “Enniployee” file (6) Modify the actual data to meet the new requirements of the file stricture. Git) Update the changed data in the new file structure, (iv) Repeat steps (ii) to (ii) for every field record presenti the actual Employee file. ‘Thus, all the file a pplication programs must be changed in accordance to the ne\ 'w requirements of file structure, In contrast to strictural dependence, structural independence also exists inthe file systems wherein the changes made in one file structure doesn’t have any impact on the application programs that access the data 2.. Data Dependence Data dependence refers to the dependency associated With the data characteristics on its file structure. For insance, When the data ofa particular field record is changed from integer {e'decimal then this change may require all the programs te be changed that access the data inthe file structure, Thus hes changes are made in the characteristics of data, they largely affect the application programs that access the data, In contrast to data dependence, Data Redundancy “data contt# Redundancy refers to the situation when the same data exists in more than one entity. It may also refer to the eet that unneceséary ‘or duplicated data is stored at different locations in the database, For ‘instance, if the “mgt name” and ‘ttt id is found in both Employee file and Macager fie then this situation leads to data redundancy. The characteristics of Sle system that lead to data redindaney are Scanned with CamScannerUNIT-1 Iniroduction Data Inconsistency pata inconsisteny 06 + gstsin Manager may. 2 pata anomaly refers 0 her lated fields occuring iifferent locations: For instance, other ticular city, then ‘mgr_address” field.along with the “mgr_phoin smaly also occurs Wl be changed. Here, data anor ‘can be of the following forms, must .n abnormal situations when a ¢! 7 sini at ie data ext different entries of database. Fi i file and ifthe field i changed in managers feb or instance, if the cn fica nin acm eg eerie a ing id” in two diferent files, Moreover dota sr of deta cy er, Tess eras si when ire net sibaetakat ars then beeause ofthe incorest ‘um lead to data inconsistency.” * Jhange made to a single field forces the changes to be made in the if the manager decides t6 move from one area to another ares 1e" must be changed. Hence, all the fields related to tis nen all the changes in the redundant data are not successfully done seal ‘adata anomaly (insertion Anomalies ‘ : ‘This anomaly refers to the sitwatid that ists when a ew entry added in one file must also be added in other file. For Trample, anew manager added in anager file must also be added in the Employee file fo the relevant Employees. (g) Deletion Anomalies ‘his anomaly refers tothe situation that exists when a Held deleted from a file frees the deletion of the same data in the other ile: For instance, if manaBer'S nagted from Manager file then the data corresponding {0 the employee in the Employee fle must also be deleted (Update Anomalies ‘his anomaly refers to the situation tht exists when a change made to #"particulay field in one file forces the change f0 sense in other file. For instance if he re of “Emp_ performance” is changed in Manager file then the same data of “Emp performance” in the Employes file must also be cha : Gi, Differentiate between file ‘system and database system. zi Scanian 54,020) oR ‘compare thie database system with conventional file system. iot8, sot ate) OR List the significant differencs File Processing System ‘es between a file processin: .g system and a DBMS. abase agement System olion of related files that are arranged Database is a coll File is a memory block device like disk, magnetic tape that logically ‘sores related records permanently. Data is accessed via single or multiple files. File manager is used to store all relationships in directories. Data in file system is insecure, sin restrict unauthorized access. Daia recovery and backup cannot be performed. Data redundancy issue exist in filesystem. Itis not possible to represent complex relationships among data. Ttdoes not providevany y sort of storage structure for efficient query processing. Itdoes not provide effici ent utili so pi lization of File mana, ar 3ger fails to resolve the conflicts : jicts amon: Yatious users, while accessing the same a ce it fails 10° ofa secondary storage into different groups. ata is accessed using relations i, table. Database’ administrator is used to store the in the form of structural table, is highly secure since, it restrict relationship Data in database is unauthorised access. Data recovery and backup can be performed. Data redundancy issue is removed in DBMS. resent complex relationships amops Itis possible to rep data. - It provides storage structure for efficient aueny processing. It provides efficient utilization of resources: : DBA resolves the conflict among multiple users while 1) PY SPECTRUM ALLAN-ONE JOURNAL FOR ENGINEERING STUDENTS. "| accessing the same data. Scanned with CamScanner18 Answer : l a DATABASE MANAGEMENT SYSTEMS [JNTU-KAKINADA) Q11, Discuss the main characteristics of database approach and specify how it differs from tra¢ tional file systems. Now-18, Set2, a2(a) “The characteristics that distinguishes the database ‘approach from traditional file system are as follows, 2 1. Describes itself (database) 2. Insulates programs and data and data abstraction 3. Supports multiple views of data 4, Shares data and transaction processing among multiusers, 7 : Describes Itself ‘The database system contains a DBMS catalog which describes the entire database structure like file, * type, storage format and constraints on data: This information which is stored in data is called meta, aia. DBMS software and database users use catalog to ‘know about the database structure. Basieally a genteel propose software package refers tothe catalog ts know the structure of the files of specific databases, DEMS softivare must be applied for any number of databases as longas the catalog stores the database definition Incas of waditional file processing, application programs ae Festrieted w access only one specific database, which contain structure declaration. Ths is due the fet hat efntion of data isa part ofthe application programs Whereas DBMS software can work with different databases by extracting the database definitions from the catalog, 7 its Insulites Programs and Data and Data Abstraction tn traditional fle processing, the modifiations done to the structire ofthe file requires modifestiog: ne be done f3.ll programs that access that fle “rn because application program are embedded inside te: datafile structure, Whereas DBMS access program does not require such type of modifications bectnee Disha catalog store the dataile structures separately fon as access programs. Ths property iscaled as prosrene Ge independence. Users can define operations inom nee of database ystemsa8.a part of the database debate An operation is also called as fincton or meting, specified in two pats @ ‘ Operation mime and datatype of its parsimeters ae included in the interface or (sigmparsy ean, implementation is specified separately. Hence wits affecting the interface, implementation can teehee ‘An operation canbe performed onthe date hese application programs just by invoking ihe gee and parameters of the operation, This prepenensy known as program-operation independeeee tat Absractionalows these wo properties ie Presents independence and program-operation independenve, Interface and: Implementation, 1.1.2 Database User: Data model is a type of data abstraction which su ‘conceptual representation. It hides the sto implementation detail y making use of logical concap such as objects, ther properties etc. Database users oq application programs refer conceptual repre sentation of files which retrieving file storage information from the catalog. rage and Supports Multiple Views of the Data A view is defined as database subset which contains he virtual data that is derived from the files ofthe database but not stored explicitly. Any number of users can access database cach user may require a different view ‘ofthe databases. A multi user DBMS must facilitate the ‘multiple views definition, Shares Data and Transaction Processing Among Multiusers A multiuser DBMS must support the concurrent access to the database to multiple users. This characteristic is essential if a single database integrates the data for ‘multiple applications. Concurrency control software must be included in DBMS. So that concurtent users ‘can update thé same data correctly. f Ensuring the efficient and correct transaction is the main role of multiuser DBMS software. A program under execution is kaiown as transaction or process, which can ‘access more than one database. This transaction concept hhas become fundamental to lot of database applications, Several transaction properties must be enforced by the DBMS to ensure correct execution of the transactions The atomicity property ensure that either all or none of the database transactions are executed. The isolation Property, ensures that execution of each transaction appears to be isolated from other transaction. Even though many number of transacti ions may be executed concurrentl s (Actors on Scene, Workers Behind the Scene) 12: List different types of database users. Answer : Now-18, Set2, ate) oR Discilss the activities of different database users, (Model Papers, Q1(b) | Nov.-15, Sets, 024) Database Users and Administrators {222 WARNING: xoroxsPntosépyng o ts book s'a CRIMINAL act Anyei ‘There are four ‘types of database user. They are, (®)_Diitabase administrators ‘ (©) Designers ©," Endusers @ System analysts/Application developers. ; « nd Sly te LIABLEto face LEGAL proceedings. ELS Scanned with CamScanneraes LUNIT-1 Introduction Tn a single-user DBM manipulates queries: But for person cannot manage all asks ja) Database Administrator ‘Asthé database is being used ame should some control over the data a2 aooess to data. i the one who provides DataBase Administrator (DBA) ist 7 the control over the data and program. designed to make data accessible. (0) _ Designers of Database imself defines and perso bi Pe DBMS, @ single ‘multiple-user ‘among multiple users, there ‘programs that provide Logical database Fipseal dita _ secs designers igure: Classi gner ‘The responsibility of database designer is to identify, (> Data to be stored Gi). The structure ofthe data Database designer interact with all the users of database so as to identify the requirements of database. Each designer interacts witha group of users and developa view. All the views are then analyzed in order to develop a final view of the database that satisfies the requirements of al the users. Database designers are classified into two types. They (Logical database designers (i). Physical database designers. Logical database designers are the oine who identify the data and the relationship among the data, Physical database designer decides how to,represent the logical database design, physically (© End Users End users are the clients of database who access the database for various reasons. Based on the level of awareness of the database, end-users can be classified into three types. Sanat Figure: Classification of End User = © Naive Users Naive users do not have much io © wwledge about the database system. They simply navigate through all the Provided user interface tdetrieve the data, They invoke stray writen and tested application programs through Is without beiag aware of how it Gut are of LOW they are written and 1.9 For example, if a student want to know the list of the ‘courses offered by the University of Australia; the student + browse the information via the links provided. Here, the ‘student acts as a naive user as he/she does not need to know the way the reqiiest is being processed, (i). “Sophisticated Users Sophisticated users are the users whounderstand the capabilities of the DBMS and use certain queries to retrieve the data. : “These uesies are submitted to separate component called “query processor”, where the query is evaluated ahd procéssed. They interact with database system without ‘writing application programs. (iit) " Specialized Users Specialized users are sophisticated users who write specialized application program for queries, which cannot be answered directly by the conventional data processing environment. (@ Application Developers (Programmers) ‘Application developers writes certain software codes lity to the end users. They so as to provide required functior use certain tools to design users interfaces, one such tool is, Rapid Application Development (RAD) that enable designers to develop interfaces with less programming effort. Application programmers embed DML:statements into high-level programming language to extend capabilities of DML statement. Such high-level languages are called host languages or fourth-generation languages (4GL). Q13. Mention various groups of database users. Explain about their roles in detail. OctiNow-16, Sets, a2(a) oR 3 Describe in detail about the database users, (i)” Actors on the scene (il) Workers behind the sce: Answer : Groups of Database Users Database users can be grouped i grouped into actors on the scene ‘and workers behind the scene. ee @ Actors on the Scene Scanned with CamScanner0 Workers Behind the Scene... ree pe nora differ on the scene” who are associé fi @ (a) System designers and implementors (©) Tool developers (©) Operators and maintenance personnel () ‘System Designers and Implementors DBMS software is a complex system consisting of various components (or modules) and interface, Therefore, the primary focus of the workers of this ‘category isto design and implement the DBMS modules and interfaces. DBMS software comprises various modules for implementing the catalog, processing the interfaces and query language, controlling concurrency, buffering data, data recovery and sccurity. It also interfaces with system software like operating system 8s well as compiler in order to compile with different programming languages. ©) Tool Developers ‘The workers of this category focus on designing and implementation of tools for software packages such as database design, graphical interface, performance monitoring, prototyping and simulation. These tools are optional and are often developed by software vendors and sold separately. z ©. Operators and Maintenance Personnel The workers of this categor hardware and software as a database system. ry focus on the operating of well as their maintenance for Q14. Whatis the role of | DBMS? Write its advantages, or. ‘What are the advantages of DBMS? (Refer Only Topic: Answer : Role of DBMS ~ A Database Manag, that defines a database, language, produces repor Typically, it is a collect manages the access made interm Advantages of DBMS) (Mosel Papert G10) | Nov.5, Set, cai) fement System (D} BMS) is Stores the data, eee supports a query ; ata entry screens ion of programs ag that controls and ‘othe data in the database. It acts as at plication programs, to the DBMS, which DATABASE MANAGEMI ENT SYSTEMS: (INTU-KAKINAD) requests nto complex Operations ang pnd Te ag ; he DBMS, The DBMS utility pro 7 is done basically ud i iogranci which are written in lan, tig Gre COBOL (Common Business-Oriented Languyy ike CH, oss Tica [ }-—— te ” Locum Consequently, the primary goal of database management system, is to provide an environment that is both convenient” and efficient to use in retrieving and storing information. Advantages of DBMS, 1. Improves Data Sharing {tallows the same data to be shared among different Users, thereby facilitating the end users to access and ‘manage thé data in an efficient manner. 2. Maintains Data tritegrity {tmaintains the data integrity Le, the data inthe. database eatcurate. This can be achieved by defining the integrity Fe aints by the administrator who conforms the dala in the database, 3 Reduces Data Inconsistency It reduces the different ent sites, data inconsistency that exists among ies of the same data located at differest Quick Retrieval Jarovides several sophisticated techniques so ta “data can be stored and retrieved quickly and effici Improves Decision Making ae ization IC improves the decision making in an oranizate because of the efficient and improved data and management, Supports Flexibility lt provides flexibility to use any of the know: like C, C+, Java, SQL, etc. Scanned with CamScanner7, Increases the Users Itinereases the number fend users thereby ml the human effort of searching the dat. 3, Reduces Redundancy It reduces the redundancy data), Here, redundancy meat in two or more files. Provides Concurrent Data A Ie provides a concurrent data access (0 nof ns the same data existing of data (i.e., duplicatior 9. the users 10, Provides Security It provides security of daia so a unauthorized users from accessing those data. ‘Q15. Explain database system applications. ~~ Model Paper s to restrict any Answer “Applications of Database System Database management systems are used b; individuals either directly or indirectly, Some af the applications ‘of DBMS are listed below, () Transactions in Bank ‘The user accesses tlie bank database for crediting or debiting the account. The bank database stores the details of individual customers, their account, loans etc yy many (i) Ticket Reservation For schedule information and for reserving the ticket, wwe access the database of airlines. The database should be reliable. , Suppose if two travelling agents compete fora last seat at the same time, the database should accept only one request and inform the other that his request cannot be accepted. (ii) Students at Universities ‘Universities have database of various courses they offer ‘and a database forthe faculty and students. Each student record contains the name of the student, marks scored ete. Each faculty record contains the name of faculty, his employee id, salary, subjects dealing with etc. (i) Internet Interactions Internet applications are mainly database driven. Suppose if a user want to send an e-card to their friend, then ‘user should initjally interact with the database based on purpose of the e-card. If user want to send a birthday card, then baser need to select from categories and retrieve various eards from the database, Online shopping is an another example for this alae storing 2 one ee fo ae the database and then send their credit card number with \\ Some additional information to buy the product, ‘DLA srecrRan aLLAn-one jouRNAL FOR ENGINEERING STUDENTS 1.12 ee () Computer Horoscope Computer Horoscope is uéed fora erecta on ae the kes and dies ina form posta to am these details are compared against a dntabase to search fora perfect match and predict the future waa! (vi). Database ina Library This database stores details of the available books. The user accesses this database to find a book quickly, The database allows easy management by allowing a user to eserve a book and intimate them through a mail when | the book is available, The system also sends a reminder to customers who did not return the book by diue-date, This system uses a bar code reader top database. |‘ (vil) Shopping at Supermarket ‘The cashier places the bar code of a product against bar dode reader. The application program uses the bar code to identify the price and reduces the number of jtems on the database + ean also call the supermarket to know the ict. The telecaller accesses the ble oF not, The user availability of the produc database to know if the product is av (vill) Tetecommunications Database is used to store the details of number of calls made to generate the bill. For prepaid customers, it stores the credit available. 1.2 BRIEF INTRODUCTION OF DIFFERENT DATA MODELS Q16. Explain about data modelling and data models along with the importance. Modo! Paper-V, Q1(b) Answer = Data Modeling, Data modeling is the initial step in designing a database. Itis basically an iterative, progressive process wherein specific data model is created for a particular problem domain. Dat modeling is done so as to, creeen (Help the users in understanding the meaning (semantics) of data. (ii) Enable communication regarding information requit q require- ‘ments between end-users, application programmers. ‘Data Model Data model isthe underlying structure used in DBMS Its defined as, “a collection of conceptual tots that aie used for describing data, relationship, data structures, consti transformations so as 1o suppor particular problem domai Daa me! is basal farm of communication fol si enables interaction between designers, application programmes and end users. This model is considered as an abstcaction of Scanned with CamScanner wrovide access to.”1.12 ‘complex real-world entities. In simple words, data model can be defined as, “a collection of high-level data description that hides . inany low-level storage details”. It is generally a blueprint that ifies the necessary instructions for developing a database according tothe endater's rele Then barns ee descriptive and graphicalin nature, s they contain both textu description and also useful diagrams for illustrating the primary data‘clements ofthe systems. Based on this blue print itis very «easy to develop a good database design irrespective of different views of data. However, if the blueprint isnot designed properly, then tis very difficult to create a good database. The following are certain crteria's that need to be satisfied in order to design ‘an optimal data model, @ Structural Validity The data models neéd to be corisistent in defining and- organizing the organizations information. Gi) Simplicity ‘The data model must be very easily understood by pro- fessional as well as non-technical users. Expressability ‘The data model must differentiate between data, their relationships and constraints @) Shareabitity ‘The data model must not be specified to a particular application i.., it must be shared by snultiple users Importance of Data Models, Ithelps the users in understanding the complexities of real-world environment. Teenables a communication between designer, applica: tion programmers and end users, I provides a clear and improved understanding about the information requirement of the organization to the designers. Te helps in organizing data in accordance to end-user’s view. Tt describes data, their relationships, seinahtids and cb Sins in orderto easly understand a particular problem domai . sage acon ifr plon tion areas, a er 7. tps in nsating concep scsi ints pi datastuctes, 8 It helps the users in understand without requiting any physical implementation details, ing the nature of data Answer “ Building Blocks of Data Models * @ wo Gi) Attributes "Example \TABASE MANAGEMENT SYSTEMS [INTU-KAKINADA} : DAT @i7. Discuss the basic building blocks of data, Modis, Model Papers, cajy ‘The basic building blocks of every’ data mode! are ay follows, 7 @ En ies (iii) ‘Relationships (iv) Constraints. Entities zi An entity is defined as a particular type of object that exists in real world. These objects are distinguishable since, each occurrence of the entity is unique and distinct. Itis possible to collect and store data pertaining to these objects. Entities can either be physical objects (student ‘or department) or abstractions (flight routes or musical concerts) Example } Student Student is an entity and it has many distinguishable o¢- Currence of student like Alice, Bob, Racheal Attributes Anaitribute defines a characteristic property pertaining to aparticular entity. The attributes generally resembles the fields defined in the file systems. Example ‘The Attributes associdted with STUDENT entity include Roll-No, Name, Address, Age, Phone Number; Gendet. Relationships s Relationship defines an entities. These associat entities in a meaningful the relationships is rep quantifiers, logical poi Generally, relationshi 'S used in determining the way of usin a: ig the data wit the database. The following three types of relationships ae used in data models, ; @: ‘One-to-many relationship (©) One-to-one relationship” association among, varius ions interrelate data items of the Way. In many databuse models, resented either using relationship inter structures or inference rules. ©), Many-to-Many. Telationship. 4 One-to-Many Relationship (1_.M) In this relations hip, the ity is associated with many instancnshiP, the first entity is associat of second entityUNIT-1 Introduction eto-one Relationship (1) on ee o . "nie ‘Je of relationship, the fist entity i associated with only one inst of ond entiy Example 3 ‘Amanager man: te a Many-to-Many Relationship EM, 7) ay 5 dsgp of many instances of fist entity are associated with many instances of second entity, In this type of relations, ° Example “Astudent registers for many fv) "Constraints 7 ” Constraints are defined as restriction on data and are straints is to ensure data integrity. aaa = is jodel? Describe various models. ‘O48, What is a data m : {Model Paper-t, Q2(a) | Oct Mov.-16, Set-3, Q2(b)) ‘Answer = : Data Model 7 - For answer refer Unit, Q16, Topic: Data Model, : ‘Typés of Data Models < ‘The various data models are as follows, Hierarchical model 2, Network model . 3. Relational data model 4, Entity relationship model , 5. Object oriented data model. R » 1. Hierarchical Model Hierarchical database model is one of the traditional database models developed in 1960's. In this model, the records are represented in the form of upside-down tree (i.e., hierarchical structure). This tree-structured model consists of many levels (seg- + ments), which are similar to-record types in file system. The top most level of the tree is consideréd as the root (parent) of the levels ‘below it i.e., root is the parent of'level 1 segment, which in turn is the parent of level 2 segment and so on. In other words, itcan be said that the segment below is the. child segment of its immediate higher-level segment. These data models define one-' ‘to-many relationship between a parent and its child segment wherein every parent can have ‘many children but a child can have nly on parent. In hierarchical models, it isnecessary to ensure that hierarchy of segment conforms to the following conventions, @ Thetop-most level must contain only one segment (.¢., root segment i , children relationship. ¢ cmt) i) Every segment apart from the root segment mist partcip (Gi) Every segment must participate as a parent segment in (9) Ate mut onuina easement which doesnt ample ages oe department ad every department is managed by only one manager. © courses arid each course can be registered by many studerits.’ 2 {represented in the form of rules: The main purpose of definifig con- which cannot participate as child segment in parent ate as child segment in only one parent-children relationship.” atleast one parent-childret relationship. -ontain any children. <— Root segment i 1 Scanned with CamScannerDATABASE MANAGEMENT SYSTEMS [JNTU-KAKINADA} 114 1 - ‘ S Neoernieen loped inorder| the problem encountered in using the hierarchical ' to overcome the pro! : mmodels Then nels wee sar ed bye anda organ, Conference on Data System Language (CODASYL), which 3 Te “ny 9 This gop ras responsible for defining standard specifications. These specifications were used for created Database Task Group. we d crating database and for manipulating data within the database. networ preset i ecords and relationship between these Tecords ** e database is represented as a collection of recor : sfaedin te fr tke, nlceierceat model, network models are capable of handling thay oman eltinship emer sired on Sis avai og arent, Basil, a relationship is termed asa set in network database wherein, in which a chil every set consists of the following two types of records: (Owner record (si to parent segment in hierarchical model) FX (i) Member record (similar to child segment in hierarchical model)... Project-detais 7 Figure: Network Data Model {athe above model for an organization, employee, manager, Project, department, project-managed, project-detals are the record types, Department is owned by employee a8 well ae Monager and project-details has two owner records ie. project and ~ project-managed, 5 3. Relational Data Model ! elatioal database model is one ofthe most popular database jnodel developed in 1970 by E.ECodd. In this model, data ‘and relationship oro aia is tepresented inthe form of table consisting of rows see columns. The edlumn represents the list of atsibutes and row represents an instance ofthe entity. The wie ashi links rows of two different tables by inserting a row identifier fone tableas an attribute value in other table (ve. two tablec ne ‘elated using a comnion atuibute) present in bore tables, Basically, relational database table maintains a set of related siuls. These tables provide complete dati and structural leependence as they are purely logical stuctue, Relation! dae ‘model 7 are pur ‘sre managed by relational database management system whose major responsibility isto: Fi @ ‘very easy to understand the model, 4 Gi end users ii) iB Teasons, 3 * the way data is stored in database) ° "This language enable the user to specify only the operations . application’ £8 to retrieve the requesied dat, The following 4 relationa application, @) » pation a is ued for enabling meaningfi iniracion between the end user and {their own customized interface using spolionres enerators b) i oF independent tables which allows the end users to easily understand the data. © oi a * “SQL engine is a part of DBMS software, nd-users. This engine is responsible for executing all "queries or processing data requests, 2 (GEE WARNING: xerczePhotocopying ot i Boxk ind iyi i a Scanned with CamScanner