| Ge 1 5 Database Concepts -» 1 wrRODUCTION ie speak about an organization, a lary ane we? n org , a large amount of data is required saled: This data handling is performed by arranging data in the eee erie eee ipn ageregated and analyzed to make dectsions for the organization ae tabase is defined as an organized collection of data (informati : ; : ; tion) about an enti i or things. Itis - shared collection of related data/information ST ere activities and decision-making of a particular organization. It also allows the cee shes access and analyze their data quickly and easily. It serves as a container which may contain various database objects. Database is integrated as well as shared. @ User <— >| Application |<—>] Dams —s ada spat exists) Fig. 7.1: Database and Database Management System peo all files belonging to an organization will be treated as the database of that = ema A database, therefore, is considered as the repository for the stored data. We will Rit iscuss some components such as files or tables, records, fields, etc. which are important of a database, ted data that serves many applications. ase shall constitute tables related to Cons Mer the example of a “School” database. This databs as per the file organization Stud hath teacher, result, etc. The data is arranged inside a database Y as shown in Fig, 7.2.tion which is > Data/Character is the smallest unit of file eo ee represented in the form of a bit that may é! in a computer. bits make a byte that represents a character I ent ed together to represen > A Field is a set of characters which Is us‘ int A specific data ite specific data elements, It {s also termed ae aaah nel or an individual data item within a recor the fields in a Goll For example, roll number, name, age and marks are Oia student's record, i Fig. 7.2: File Otay > Collection of fields is termed as a Record. Each field contains one tay data item. For example, a student record consists of the fields Roll No., Name, Age ang Matis as shown in Fig. 7.3. Fleld/Data Item Roll No, Name Age. = Marks ok | Record zo_| soa | 1g | 90 (———> Fig. 7.3: Student Table » Collection of logically related records is called a File. A file is also termed as a table or a relation. A table has rows and columns, where rows represent records or tuples and columns represent attributes or fields. For example, the entire information about all the students (in the form of records) in a class is kept in a file or.table named “Student’, » Database is, therefore, a place where related Pieces of information are stored and various oPerations can be performed on them. It is the highest unit of file organization. These databases are manage: Management System (DBMS). 7.2 DATABASE MANAGEMENT SYSTEM (DBMS) Database Management Systems are Specially designed applications to create a connection Petween the user and the program, and to store dat in an organized manner. The purpose of DBMS software is to allow the user to create, access, modify and control a database. A DBMS stores data in such a manner that it becomes easier and highly efficient to retrieve, anipulate and produce information, Thus, a bee 's an electronic or computerized record: Keeping system, It maintains the various Pleces of inf ined formation i d summari form, instead of keeping them in separate Independent files, erated an Examples of database management system: Lite, Microsoft SQL Server, Oracle, AP dene he ate, MS Access, MySQL, Postgresal. $0! A few customized DBMSs are computerized lib; flight rary s es, reservation systems, computerized parts Inventory Yate titomated teller machin¢Database Management awn D o = a ™ Lx patabase al s ics acts | sat Fig. 7-4: Role of Database Management syste m ools to a structured way. base (that is, ask questions about the data), ulate data in the database. ives us 8 ie ata” 7 oy the data @ : 7 and manip! jidate the data entered and check for inconsistencies, vali i duce fl jnformatio} exible reports, both on screen and on paper, that make it > e ein the database asy to comprehend the stmaintains data consistency in case of multiple users, ss, ‘pans isa general-purpose software system that fciltates the proces of defining constructing sirpulsting databases for various applications, 724 Need for DBMS sedatabase system is used to eliminate the problems of data redundancy and data inconsistency ‘tat were found in flat file systems. The database system does not maintain separate files for «ferent applications. Rather, it works on the centrally maintained database which means that the iatais kept at one place and all the applications that require the data may refer to this database. \enever any file gets updated, the updated version of the file is available to all applications tsing the database system. So, data redundancy and data inconsistency are controlled to a large cent, 4owever, at times there might be data redundancy due to some technical requirements in .d to maintain the same data for different b ah i sey applications, In such cases, we are require’ t this is not recommended. Fig, 7.5: Purpose of a Centralized Database System .. Database Concepts Ay7.2.2 Components of a Database System™ «described in the figure given below, system ar The various components of a database SY° 2 User Fig. 7.6: Components of a Database System Let us discuss these components. 1, Users: Users can = of varied types—Database administrators DBAs (who desig, write and take care of computer database systems so that the right person can get ty needed information at the right time), System or Application developers, and End-users, DBMS provides the following critical services to the user: / (@) Database Creation: DBMS helps the user in creating and defining the required datag, in turn, a database. It manages and organizes the required data and databases, (b) Database Maintenance: A DBMS provides maintenance of data and database by addition, deletion, modification and regular updation of tables and their records, (©) Database Processing: DBMS performs one of the major tasks of query processing— it processes the queries or the information requirement of the users and retrieves necessary information from the database. 2. Database Application: Database applications may be Personal, Departmental, Commercial and Internal. It may be general purpose or customized as per the needs of a user. 3. DBMS: Software that allows users to define, create, ac as a DBMS, for example, MySQL, Oracle, etc. 4. Database: cess and manage database(s) is termed Patabase is an organized collection of logically related data. 7.2.3 Advantages of a DBMS nal data processing techniques, 3 Canto of Data Redundancy: Duplication of data leads to wastage of storage space. ADBMS eliminates data redundancy (duplication of d i i tiple copies of the same data are not st Sta) by integrating the files so that ml tored,, 2, Data Consistency: DBMS Provides data consi one place are reflected at stency to a large hhanges made#t all other places or "ge extent as the chang to all the users, to operate against the same stored date MEW wiormatics Practicas with dana. vsming Effort: A DBMS saves a lot of pr gram Programming effort since Ft ret avery procetng felving several tables oF files, report generation a ete, Thus, it provides eas : Mion MOU ces Standards: With centralized co wa he 908 fens En! se ensi Il applicabl can ensure that all app] e standards are folloy stato! Lr documentation standards and conventions " wees " ‘ty: Data integrity refers to th ; ta Integr! e validity and ed a he system itself checks for the correct mete sanastency of stored data se information to be enter vere format. However, it comes with various constraints, Gi te cor" : inthe © and Security: Data security refers to protection of data against accidental or rv gisclosure to unauthorized persons. Since there is a centralized control, the ine rotected: ; gata 6 Py: Combining all the organization's operational da conomic® ‘ications that works on this single source of et oF maintenance cost of data is reduced. a wv phe O" q Backup and Recovery System: A database sys re fi impr from hardware or software failure, er} recov ving Enterprise Requirement, not Individual Requirement: Since various types of som; with varying levels of technical knowledge use a database, a DBMS should provide a aay of user interfaces. “Gu fepattion (duplication) of the same data at multiple places in a database is known as data | redundancy: ! + ta into one database and creating data can result in cost saving. stem provides facilities for 12.40ata Abstraction Amajor purpose of a database system is to provide users with an abstract view of the data, ie., the system hides certain details of how the data is stored and maintained. Data abstraction is = process of hiding the implementation details (such as how the data is stored and maintained) and representing only the essential features to simplify users’ interaction with the system. The different levels of abstraction are: * Physical/Internal Level: It is the lowest level of abstraction that describes how the data is actually stored in memory. * logical/Conceptual Level: It is the next level of abstraction that describes what data is Stored and what relationships exist among those data. * View Level/External Level: It is the highest level of abstraction that describes only a part — entire database for a particular group of users. There are multiple views of the same ase. The users view data in the form of rows and columns. View/External Level View 1 View 2 Mew 3 ee Fig. 7.7: Levels of Data Abstraction7.2.5 Data Independence paMsis data indepen dence. A database een, a data, For example, it stores data about data jy y according to the user's requirement. So, i me adata itself follows a layered architecture so gus Ye ftect data at another level. A database system pen 5 One of the major advantages offered by contains a lot of data in addition to user: metadata, to locate and retrieve data easi is a need to change the existing data, met: we change data at one layer, it = oe both logical and physical data independence. “ ‘ > igieucantapen data independence is a kind of nea ue Independen, the actual data stored on the disk. If any changes are ma Mat, it dogg of the data also remains unafeg The user view change the data residing on the disk. ‘ iti h as adding or updating co; for example, changing the data definition suc] is & Constrang ¢ relationships in the database. of > Physical data independence refers to making changes in the internal structure or inter, memory storage formats, storage devices, etc, Withoue schema such as file organization, changing the conceptual schemas, ie., the format of the stored data. For example, replaci the storage device from hard disk to magnetic tape or any secondary storage device wil ng affect the data stored at logical level. 7.3 DBMS MODELS Data models define how the logical structure of a database is modelled. A data model is an integrated collection of conceptual tools that can be used to describe the structure of the database along with its appropriate data types, relationships and constraints that must be applied to the data. Data models are used to implement abstraction in a DBMS. They are communication tools and define how data is linked to each other and how it is processed and stored within the system Data models organize data for various users. A data model should be able to give the best data representation and should possess the following desirable characteristics: (a) Data models should be presented graphically using diagrams and symbols. (b) Data representation in a data model should have no data redundancy. (© It should be made available and shared by various applications. (4) Data represented should be consistent, stable and valid in all aspects. Data models are categorized into three different categories: DATA MODELS ) Fig. 7.8: Classification of Data Modelsrchical Model odel, records are organized as tre, ical es rather than be piracy of ae child date segments i inpnines Wasa re ical mo! ne-to- (rn maier® chical ™ aaa e-to-many (1:M) relationships between rae mt, Bae arent segl in have many child segments but it does not Ne t asida ned er na ea child segment has only one parent segment and is iotaceed asap ie Four inks. STUDENT (Parent) | Rot umber L Name | aa LIBRARY — _ | child) | e ‘ | ree | Book ued Roll number | Firsttarm | Mall yrty | Anmaat ae paca i FEES (child) Roll number | Tuition_fee | Dev_fee | Total_fees| Fig. 7.9: Hierarchical Model we can see that the student database contains various files related to a student. a file higher in hierarchy is known as parent of the files contained Fees and Exam files respectively. Thus, this model parent and children in the form of hierarchical prom Fig. 7-9, inthis kind of arrangement, inside it. Here, Student is a parent of Library, represents one-to-many relationships between a (upside down) tree. The advantages of this model are: 1. Itis simple in operation and concepts. 2, Itpromotes data sharing. 3, Parent-child relationship promotes data integrity. 1. Itisefficient and can be naturally mapped for one-to-many (1:M) relationships. The limitations of this model are as follows: 1 It is suitable for hierarchical relationships only as it is inflexible for non-hierarchical relationships mapping. 2. Itsimplementation is complex and difficult since i children which require extra memory space. s the root node, it is done using pointers from parent to their and its deletion can cause deletion of all 3, Dependency on parent node, which | child nodes and, in turn, the entire model. 4 Changes in the structure require changes In all Management System) and SYSTEM 2000 are examples of hieral system, the applications. IBM's IMS (Information rchical database management matahace Concepts: T.a fei way o preening atabase 1 cords connected to one another’ ety vetion of records a fa coli hcal model with a graph, thus allow mu ali of difference between hierarchical ang i tipi or otpe eapabiity of handling MAny-toxmany aM wre than one parent node. 7 ieee odel conceived as The network data model is ad and their relationships. It consists Links. The network model replaces the connections among the nodes, The mal models is that the network model Pro elationships. T 0 ve m' s, a child node can ha Jationships. Thus, a ¢ sia T = | poll number Ticket No. Book issueg STUDENT (are ae number Address b = = EXAM (Child) ~ Roll number First.term Half_yrly | Annual FEES (Child) | Links = | poll number |Tution fee Dev.fee | Total tet] 4 Jas é | Unks + Fig, 7.10: Network Model From the above figure, it is clear that this model represents many-to-many relationships. It can be observed that the file EXAM is associated with three tables, viz. LIBRARY, FEES and STUDENT. Thus, it shows that a child can have more than one parent. This model is an improved version of hierarchical model having records along with pointers. These pointers establish many- to-many relationships and are termed as Links. Each record has its respective pointer or link with which it is associated. The advantages of Network model are: 1. It can handle more relationship types, ie., many-to-many (M:M) multi-parent relationships. 2. Data access is easy and more flexible. 3. Itincludes DDL (Data Definition Language) as well as DML (Data Manipulation Language)- 4. It promotes better data integrity. The limitations of this model are: : i. oo complicated in structure since there are several links which also limit efficien . Structural cl apes amt require changes to be made in all application programs. : level ri pi a Interface Is required to interact with the database. . Extra ry Is consumed for holding links (pointers), UNIVAC’s DMS 1100 and IDMS (Integrat of Network database management system, ted Database Management System) are examples i 47 Informatics Practices with Python—x1; model paata mast 18 DBMS {s the Relational Mod, a ical vi mn model since there are a leis a more scientific jqult oF error, these pointers may req Pointers involved with and stable ata integrity. walt in inconsistency the records among the records y saved for future use, Thus, this mo quired to process data with storage eiclensy and has all the pr : cy. roperdies consists ofa collection of tables, each of which i atabase as a collection of relations or tables, is assigned a unique name, ie, @ Per ag re on papuities T° nd - model sents the d ratte ofretaconal database ae: tually represented as an order! is conceP rly arrangement of data i into rows and columns a : is relation. He rmed a5 2 gach relation is represented as a table. a columns described ina table are the attributes that belong to an entity which is modelled as "table. Every FO lithe values in a relation are scalar, ie., at any given row or column position there is one and only one value. All operations are performed on the entire relation and the result is an entire relation. w ina table represents a single entity. 6 advantages of relational model are as follows: elational model provides structural independence by using independent tables. . Changes made in the table structure do not affect data access or other application programs. itis represented in the form of tables; so, itis simple and easy to understand. , Tabular view also provides easier database design, use, implementation and management. . Built-in query support based on SQLis provided by RDBMS (Relational: Database Management System). 6 Data organization and manipulation is easy, flexible and faster. . Powerful structure designing and processing capabilities of RDBMS isolate the ene uant from Physical-level details and, thus, improve implementation and management simplicity. 8 ‘ Mathematical operations can be successfully carried out using RDBMS. Limi ations of relational model are: a The RDBMS incurs hardware and system software overheads. ” The Size of database becomes very large: Database Concepts NXid columns), The les (ie, rows an ese ed a ahp among 4S of values 7.4 RELATIONAL DATABASE erally referred to using the mathematica) atical te organi In relational data model, the data is © ie are called relations. A row in a3) TPE i table ia collection of such relationsHiPS Tos es name, : al relation, from which the relation attributes joe Tuples = —>| Relation Fig. 7.11: Tuples and Attributes Basic Terminologies related to a Relational Database and Tables (relation) 1. Entity: An entity is something that exists and about which we can a some information, Itis an object which can be distinctly identified, for example, student entity, employee entity, item entity, etc. Entity becomes the name of the table. Attributes/Data-Item (Field): In a relational table, an attribute is a set of values of a Particular type. The term attribute is also used to represent a column. A table consists of several records (row) and each record can be broken into several smaller entities known as fields or attributes or columns. A set of attributes defines the characteristics or properties of an entity. In the given table, Student relation consists of four fields or attributes—Roll number, Name, Address and Gender. 3. Tuple: Each row in a table is known as a tuple. Itis also called a row/record. A single entry in a table is called a Record or Row. A Record in a table represents a set of related data. For example, the Student table, shown in Fig. 7.12, has 10 records, + Table/Relation: A table is a collection of logically related records. In other words, we may say s 6. Degree of Relation: Number of columns i or att i ‘ Thus, the degree of Student relation is 4, hie ot eee een 7. De ie main of Relation: It defines the kind of data Tepresented by the attribute. It is the set f all possib) Possible values that an attribute may Contain. For example, in the given table Student domain for the field Gender is two si arate vals at 00 ince it can have either ‘Mt! or ‘F’ as the possible and 8 Body of the Relation: It consists ofan unordered set of 0 or more tupl ore tuples. Informatics Practices with Python-x| ie v7 a irDegree 7 Attributes ARQ () Domains er aS key aL ————_ por Rollnumber F M 4 Nikunj Sharma : 5 Pratibha Jain F ele? 6 Tiana Verma Ashok Vihar F 7 ia Sharma __| Shalimar Bagh | _F | |~ 8 Shourya Seth | KarolBagh_| _M 9 Shreyas Singh_| Shakti Nagar | _M 10 Purvikumari_ | Tilak Nagar_| Fig. 7.12: Relational Model (Student-Relation/Table) fore, with reference tO Fig. 7.12, in the given relation-Student, pefores i inal ‘he ware are 10 tuples (Ke cardinality=10) and 4 attributes (ie, degree=4). . goll number, Name, Address and Gender are the attribute names. "pe first tuple contains the values (1, “Rinku Sharma’, "Tilak Nagar). " the domain of the attribute Gender is (M, F). 7.5 DATABASE KEYS taysarea very important part of Relational database, They allow us to identify an atribute or 2 ver rattributes on the basis of which a table is identified. They are sed to establish and identify ‘eaion between two or more tables, They also ensure that each record within a table can be tniquely identified by a combination of one or more fields within a table. The different types of keys in an RDBMS are as follows: Pky | Pia Primary Key A primary key is an attribute or a group of attributes that can’ uniquely identify tuples within the relation. Gandidate Key A candidate key is one that is capable of becoming the pr for the primary key position). Atemate Key A candidate key that Is nat the primary keys called an alternate key, Ue 'sdiscuss these keys in detail. “ i | Primary Key: A primary key isa set of one or more attributes/felds which uniquely dentiies atuple/row in a table, The salient features of a primary Key are as follows: {It is always unique in nature, Le, non-redundant. It does not have duplicate values in a Telation, jimary key (i.e., candidate (b) 'tarranges the table in its own order: IG ) ) Weannot be re-declared or left null natahace Concepts RRBs.. ey; however, primary key ,, 4 nly one primary defined as a Composite pea be, ft eign m0 can Be cOMbINA together ky th tne context ofthe table Item given belay Make, lier id) is the primary Key in the table ¢, “ig PPlig, is the primary key a Table: Item era eon eo (d) One table can have nance combination of more that OT For example, roll nine Sea oa relation S y key in the relat 2 while Supp_id (suPP $01 jor Printer 400 2 102 co 200 Ro you sce (Ove. Ee 505 Punjabi Bagh }105 Mouse 300 1103 Keyboard 180 109 Cable 500 Candidate Keys t | Primary Key ‘Alternate Key Primary Key ‘Alternate Key | Fig. 7.13: Keys in a Database (Tables) 2. Candidate Key: All the attributes ina relation that are candidates or are capable of becoming a primary key are the candidate keys. ; In the given table Item, Itemid and Item_name are the candidate keys. Out of these keys, Item id is the primary key and Item name becomes the alternate key. Similarly, inthe case of Supplier relation, Supp id and Area are the candidate keys, Supp_id is the primary key and Area becomes the alternate key. Thus, the equation becomes Candidate Keys - Primary Key = Alternate Key 3. Alternate Key: A candidate key that is not the primary key is called an alternate key. In other words, any attribute that is a candidate for the primary key, ie., which is capable of becoming a primary key but is not a primary key, is an alternate key. For example, in a customer table, cust_name is the alternate key. Similarly, in the given table Item, Item name becomes the alternate key. {23 BYTES Basic/raw facts about something which are not organized are termed as Data, % Each piece of information about an entity, such as name of a perso / ct or the Snes Rune ae Person or address, age or name of a produ > Acollection of data is referred to as dat viieebadabiar ied. * database and a database (management) system is basicaly a computer-based > Database systems help reduce data redund: ia 6 dats ond data secur, “ndancy, data inconsistency and facilitate sharing of data, standardization A relational database organizes the data into tabl A group of rows and columns forms a Table, > les known as relations, » The horizontal subset of a Table is known as a > Row/Tuple, The vertical subset of a Table is known as a Column/Attribute. A candidate key is an attribute (ora set of attrib, i primary key in a table. tes) that uniquely identifies @ row and is capable of becoming? A i eekey is one of the candidate keys. rimar¥ 1 nsive softw: A pBMs is 2 comprehe : are that provides essential services to create, m ing Databases and Datal e, Manage and maintai stems comprising enh um base Management Systems are simply referred to as D: Uderteaiee mon Database Mana ed to as Datab examples of OM Bement Systems are: MySQL, INGRES, Postgresql, ORAC enn qi esql, LE, Db2, Microsoft access: 7 oma of an attribute refers to the set of all the possible values for that attrib: attribute. www 2 > : ae of attributes in a relation is the degree of that relation, > Number of tuples in a relation is the cardinality of that relation. » pata integrity means that the data contained in the database is both acc > There are three levels of abstraction in DBMS—physical level, logical mates a oa > Adatabase system provides facilities for recovery from hardware and soft ennai > Database Administrator (DBA) is the person who is responsible for the ey ihe ti if ictioning of the database. > Arelation is 2 two-dimensional table.