Page 1 :
f, oF, , ee, Module - I /, , i, , }, , Database and SOl, , Questions and Answers available with scratch card provided in the book. Please, follow instructions provided for downloading. : :, , , , 4NTRODUCTION, , Data_: They are raw or known facts) ie. the data that has not been, , , , processed. For example, data of the ages of students in a class., , Information : Processed data is called information.) For example, we find, , from the data of the age of students ina class, how many students are of age 18., The result we obtain is information., , Practically everything we do generates data. Our ancestors also needed to, record such data. When there were floods, marks were made on walls.in different, places, to indicate the level to which the water had risen in that area. In today’s, complex world, with so much data being generated due to various type of, financial and non-financial transactions, we need means to record this, information in a meaningful manner, so that we can retrieve it later with ease., , Database, , (A database is a collection of data that is related in some way, A list of, students enrolled for the commerce stream, the list of names, addresses and, telephone numbers in a telephone directory etc are all examples of databases. A, database can be compared to a filing cabinet which contains many files. The files, are tables containing data. The size of a database can be very large and can have, varying levels of complexity. While today most databases are maintained on, computers, some of them may be manually maintained. ,, , , , HIERARCHY OF DATA 3, , Character : Consists of letters (A-Z), digits (0-9) and special characters, (+, ete)., , Field(Column) : Is a collection of related characters. e.g. name, age etc., , Record(Row) : Is a collection of related fields. e.g. all the information of a student, Le. his name, age, address; roll number etc. is a record., , File(Table ) : A collection of related records is called a file., Database : A collection of one or more related Files is called a Database., , , , Note : In MySQL it is more common to call a Field as a Column, a record as a, , , , Row and a File as a Table., , , , , , N@MsS: = Odlaloagze 1% ent Syston», ROBMS Relatinal locra ¢ Menceg earend Syston ,, , UY

Page 2 :
8y, , , , , , DBMS, , ‘A DBMS is a collection of programs (c ‘, user to create and maintain a database) It is ii, manipulate the data in the database for vario ppl 4, information storage, organization and retrieval capabilities. The DBMs ,|, , enforces necessary access restrictions and security — co cane . Proj,, the database. Various types of control systems within the D Ke sure ty, the database continues to function properly. They include - Integrity sy; Stey,, Security system - Concurrency control system - Recovery control system So,, DBMS enable us to define "views" of the database. A view 18 how the data, appears to the user. This enables us to show only the relevant information, different types of users and it increases security, as certain users will not be a, to see data which they are not meant to see., Advantages of DBMS, 1. Redundancy is controlled., 2. Unauthorized access is restricted., 3. Provides multiple user interfaces., 4. Enforces integrity constraints., 5. Provides backup and recovery of data., Limitations of DBMS, 1. Uses extra computer time and resources., 2. It is expensive to install new systems., “3. Requires well qualified and expert personnel to operate the system., 4. If it is not designed for concurrent access to many users it will be of lif}, use., , 5. Authority. of ownership rights over the data have to be fixed, so as, determine who is eligible for using the data., , [ABCORD RELATIONSHIPS a, Tpo', , To make it easy for the end user who use the database for various pu, the following record relationships are used., , One - One : Here one parent record is associated with only one child recol, e.g. one doctor in a hospital will take care of one patient. ], , , One - Many : Here one parent record is associated with m i d, ; I ; : any child reco!, e.g. one doctor in the hospital will take care of many patients. z, , Many - One : Here two or more parent records are i i, t associated with one c, record, e.g. many doctors will be attending to a single patient. —, mae - Many : Here two or more parent records are associated with two or, . records, €.g. two or more doctors will be attending to two or more pati, ence bills of different patients will be associated with different doctors fs, , DATABASE SYSTEM, |, , The database and DBMS software together is called as Database system., NON RELATIONAL DATABASE SYSTEMS Ta, , © i dit, hey are popularly categorized according to the data structures they pres?, , . the user. oI, te The Non- tional or re-ri tional models are Hierarchical, ‘Oo € Non-relation: of p: ela ion. a, , , , a,, omputer based system) that enable, |, d to define, construct i!, , applications. It Proyj!|

Page 3 :
Hierarchical model \.\.., , a), , b), c), , a), , In this model the different records are related through inverted tree like, structures. A parent record can have many child records, but a child, record can have only one parent record., , It uses the one : one and one : many relationships., , In a hierarchical database, records contain groups of parent/child, relationships. For example, under Production Department (parent) would, be stored the orders A and B received by the department (child), under, that would be the Skills required for the order A and the date of delivery, (child), and for order B the material required, machines required and the, , date of delivery (child), another child would be the employees assigned to, each order., , Although this model has been widely used, it is often considered not, suitable for many application areas as its structure is inflexible and is not, suitable for complex relationships., , Example of Hierarchical Database, , , , Production, Department, , ae Order B, , , , , , , , , , , , , , , , , , , , |, Fair | pr pea, , , , , , Skills Date of Material Machines Date of, Required delivery Required Required delivery, , , , , , , , , , , , , , , , , , , , , , , , , , , , Advantages, , 1, 25, , Disadvantages, , T:, 2., , 3., , 4., Netwo:, a), , b), ¢), , The structure is simple to construct and operate., As the batch operations like payroll are done at maximum speed they, permit day to day structured operations rapidly. :, , Many to many relationships cannot be handled in this model., , Its structure is quite rigid and hence adding a field is very complicated, often resulting in making a new database., Deleting a parent record is complex as the entire branch of child records, , associated with it has to be first deleted., Ad hoc queries are not supported., , tk model, In this model a parent record can have many child records and a child, , record can have many parent records., It uses the many : many relationships. ;, The records are physically linked through linked lists.

Page 4 :
54 ee Computer Systems and Applications (T.Y. B.Com.) (Sem, , d) Hence this model addresses some of the drawbacks of the Hierarchig, model. It supports more complex queries and relationships than, possible in the Hierarchical model., , e) However, the Network model has its limitations, as it is very comp}, , . While accessing the database the users have to be familiar with, structure and keep a track of where they have reached and how they, there., , f) If any change is to be made to the structure, it would affect, application that interacts with the database., , Example of Network Database, , , , , , , , Teacher 1 Teacher 2, , , , , , , , , , , , , , , , Student A Student B g Student C, , , , , , , , , , , , , , , , , , Advantages, 1. It has a higher level of flexibility as compared to Hierarchical Database., 2. They are ideal for handling many to many relationships., , 3. Complex data relationships can be represented more effectively., Disadvantages, , 1. The database structure is difficult to change. ‘, 2. Operation and maintenance of this structure is quite complicated., , 3. There is lack of structural independence, relationships cannot be adde, to the existing database easily., , RELATIONAL DATABASE MANAGEMENT SYSTEMS(RDBMS) :, 1. The Relational Database model was proposed by Mr. Codd of IBM/, 1970. However, the first commercial system appeared only in 1981-82., , 2. In this model, unlike the Hierarchical and Network models, there are?, physical links., , , , 3. All the data is kept in tables which contain rows and columns., , 4. The data in two tables are linked through the columns and not throug, , physical links. Hence there is no reason to traverse pointers as in a No, Relational Model., , Relational databases have become a predominant choice for the’ storage!, information in new databases used for financial records, manufacturil, and logistical information, personnel data and much more., , 6. Relational databases have often replaced hierarchical databases 4, , — databases because they are easier to understand and use, e¥!, ough they are much less efficient., , As computer processin;, relational databases, wh:, -been outweighed by thei, , § power has increased, the inefficiencies, , ich made them impractical in earlier times, b#, iT ease of use.

Page 5 :
ee =. a, , 8. However, relational databases have been challenged by Object Databases,, , which were introduced in an attempt to address the object-relational, impedance mismatch in relational database, and XML databases., , Although still used in some organizations the Hierarchical and Network, Databases are no longer in wide use. The Relational model is most widely, implemented model in modern business systems and it is the foundation for SQL., , Note : There are 12 rules Yor a Relational DBMS, also called as Codd’s Rule., A Relational Database Management System that satisfies at least six of these, rules is acknowledged as a fully operational RDBMS., , Consider a sample Inventory database given below, which consists of the, , tables of Stock and Supplier. As the supp_id column is the same in both tables, we can obtain information from both tables at, the same time. \, , , , , , , , , , , , , , , , , , , , , , , , ved | Stock Table - Ram /, stock_id | supp_id stock_name elstock ?, bo] 0+], 1138 001 Dove Soap 245, 1145 002 “Colgate 153, 1167 003 Nirma 110, win a, , - Supplier table, , , , , , , , , , , , , , , , , , , , , , , , supp_id | Supp_name: | Phone_no, 001 Manu & Co. | 9821066723, 002 Raju & Co. | 9912456023, 003 Hiral & Co. | 9983457120, KA, 4 |, PRIMARY KEY, , , , , , A column in the table that uniquely identifies each row in that table is called, , the primary key.| In the above example stock_id in the stock table can be the, , , , {CANDIDATE KEY ], In a table there could be more than one column that uniquely identifies a row, in ie table. Then such columns are called candidate keys, From among the candidate keys, we can choose one to be the primary key, or, a combination of them to be the primary key. In the above stock table stock_id, and supp_id are candidate keys. Any one of them can be selected as the primary, , x key. &, UHOREIGN KEY, , {it is a column in one table, whose value matches with the primary key in, another table/ A foreign key establishes a relationship, or constraint, between two, , tables., In the above tables, stock_id is the primary key in the stock table and, y in the supplier table. The supp_id column in the stock, , Supp_id is the primary ke: e ‘ ‘ the, table is the foreign key, as it is associated with the supp_id key which is the, , primary key in the supplier table.