st" Unit-3 DBMS _ ap | Sasa? oe Nhot sn Dems a Or>., a ‘RDEMS = ae __ Qzy Ou)i + “an paimory Fea | OP TAF Doane database Sor? What is Foreign key? —— oe) eat Advent yen of DMs [expla] len Concepk of Ejoampler fd a Ape, Be fq pone 2) “Poh ha Concefh 7 ETA @ pample a) Beplan 1 difiiak gave UE Aci cb eee Bebrec, Tablen il Poplain Aepentl Drv Dnkeg ty witheg, Rr) ai field propoier Dams aye any wn "ete Stepp fa Creat’ a pesgnsitt 8 Dedifpa vies ra SQ LIEK plain ath Lrampe «Qi Concept of Database Database is a software system with storage backing that has ended up being really helpful for industrial needs. Think of the large number of web Pages on the Internet. How is information about them stored, By building data sets and storing them in databases. The Database along with the software system that enable the backing store to behave in a user-friendly way, is what a DBMS is. Among the services the software System of DBMS provide are: ask about the data, arrange the outp: ut with formatting, modify the data in the database easily, delete a table(s), etc,Q2 % \RDBMS store the data as collection of tables, which might be related by common fields (database table columns), RDBMS also provide relational operators to manipulate the data stored in the database tables. \Most RDBMS use SQL as database query~Q3> | [The former case is that of the field being called a primary key. It is the field that varies from row to row. The latter case is that of a composite primary key. In this case, not one but a combination of fields makes up the primary key.)Q4 A foreign key is a column or group of columns in a relational database “table that Provides a link between data in two tables. It acts as a Cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.i Q5 * Database servers are dedicated computers that store the actual databases and run only the DBMS software. They are referred to as back ends,‘Q.6 y _ ‘Ss of a Database DBMS is preferred due to the following advantages: Sharing of Data: Different users can use the same database to access the data according to their needs. Data Redundancy: Data redundancy means duplication of data. It avoids duplication of data and ensures that there is only one instance of certain data. Data Inconsistency: It helps to avoid data inconsistency. It means if a single database is used by multiple users then it also ensures that the same data is present for all the users. For example, if the two departments (HR and IT) access the data simultaneously then only one department will see the updates by the other department. DBMS solves this issue Confidentiality: The DBMS can ensure different views for the different users of the database. This keeps the confidentiality of the data safe. Highly Securable: Database can be secured by assigning a lock to it by using keys. In a database, data is stored in the form of tables. Each instance of data is known as tuple. The table stores the data based on some theme.Q7 Concept and Examples of Field, Record, Table, Database A database consists of fields, records and files. - Fields: A column within a table that contains only one type of information is called a field. For example, Name, Address, Date of Birth and Phone Number are different field names. Record: A set of various fields is called a record. All the information in the table in various columns represents a record. For example, the information entered under the fields, Name, Address, Date of Birth and Phone Number indicate the record of students. _ File: A file is a collection of related records. (Fics ) Records I Name FName City Age Slary Smith John 3 35 $280 Doe Jane 1 28 $325 Brown Scott 3 Al $265 Howard Shemp 4 48 $359 <— Taylor Tom 2 22 $250 FIG, 8.2: EXAMPLES OF PARTS OF TABLEQ8 “Concept and Examples of Relational Database © RDBMS stands for Relational Database Management System. This name is used for databases that are developed by using the relational database model as prescribed by EF. Codd, of IBM’s San Jose Research laboratory. Most popular database systems in use are RDBMS-es. > (RDBMS store the data as collection of tables, which might be related by common fields (database table columns). RDBMS also provide relational operators to manipulate the data stored in the database tables. Most RDBMS use SQL as database query language. The most popular RDBMS are MS SQL Server, DB2, Oracle, MS Access and MySQL. osQ9 Data Types used in LibreOffice Base Integers ‘Type Option | HSQLDB Range Storage space Tiny Integer | TINYINT | TINYINT | 25 = 256 | -128 to +127 I Byte Small Integer | SMALLINT | SMALLINT | 2! = 65536 | -32768 to 2 Byte +32767 Integer INTEGER | INTEGER | | 2® = 4294967296 | 4 Byte INT 2147483648 to +2147483647 Bigint BIGINT | BIGINT | 2 8 ByteFloating-point Numbers ‘Type Option Decimal DECIMAL Number | NUMERIC NUMERIC [Point perfect accuracy. in the GUI, fixed decimal Point, p Float FLOAT (Double used erfect accuracy, je Perfect accuracy, Unlimited, up to 30 places | a DB icine sca HSQL Range Storage space | DECIMAL Unlimited, upto 50 places | in the GUI, fixed decimal | Variable] instead) Real REAL REAL (Peccsaeerenaae ——____ | Double | DOUBLE ee Adjustable, not exact, 15/8 Byte | Lae. | decimal places maximum | | ! J Text Type Option HSQLDB Range Storage space Text DECIMAL | DECIMAL | Adjustable Variable Text NUMERIC | NUMERIC _| Adjustable, range affects sorting | Variable Text (fix) | CHAR CHAR | Adjustable, rest of actual text | Fixed CHARACTER | replaced with spaces Memo REAL. REAL Variable Time [ Type Option HSQLDB Range Storage space Date DATE DATE Unlimited, upto 50 places | 4 Byte in the GUI, fixed decimal point, perfect accuracy. Ti TIME Unlimited, up to 50 places | 4 Byte a a in the GUI, fixed decimal point, perfect accuracy. Date/Time | TIMESTAMP | TIMESTAMP | Adjustable (0.6 — 6 means | 8 Bytes | DATETIME | with milliseconds) Other Type Option HSQLDB Range Storage space YevNo | BOOLEAN BOOLEAN | BIT Binaryfield | BINARY BINARY Like Integer | Fixed (fix) Binary field | VARBINARY VARBINARY Like Integer | Variable Im yy | LONGVARBINARY | Like Integer | Variable, lage LONGVARBINAR’ intended for larger images. jee OTHER OTHER OTHER | OBJECT aQ.10 Relationships Between Tables Like in mathematics, there can be many kinds of relations. between attributes in tables. In fact, not all the attributes, taken pairwise need have a relationship among cach other. In general, three kinds of relationships exist between attributes of table(s). They are: 1. 1:1 2. In 3. nil An easy example of a 1:1 relationship, is a roll number, address. You would expect a given student, to be having a single address where he/she resides. For 1: n kind of relationship, student to subjects. A given student can have muultiple subjects that he/she is associated with. Moreover, different students will generally (even if in the same class) have different subjects. "« : For n: 1 relationship, you can think of the student, class teacher relationship. There will be many students who are under the guidance of a single class teacher. In fact, all students of a given class (and section) will be under the same teacher. This justifies the n:1 type of relationship between student and class teacher. On drawing figures of these database tables, you have examples for all types of telationships between attributes.”Q.11 Referential Integrity Suppose you have two tables. One is Employee v/s Café ID. So every employe does NOT necessarily have a Café ID, but if someone has Café ID, surely they ar an employee. Additionally , for every Café ID, there is a table Café ID v/s Balance This table stores the Café ID and the balance for each Café ID. So, the Café ID is the primary key of the second table. Also, the primary key (Café ID) is being referenced by the first table. So, there is a referential integrity. Further, the Café ID is known as the foreign key. See the figure, for foreign key examples. (Cy Firs Table - ty First Database -LibreOifice Baze: Table Date View File Edit View Insert Tools Window Help Employee iO | Employee Name avinriee fick oa [TJasczz”—Diyananoos MGR [Fler Kankumar ENGR Tata [TJraet Sedna Kumasi ENGR a2 [|men321 Kapil Sharma. MNGR [Cy Second Table - My Fist Database -Librefice Bose: Table Data View File Edt View Insert Tools window Help lpaiz rer2 Tie 917Q.12 | Field Properties In the Design view of a table, you may see the format of the table and individual properties for each of the fields of the table also. Following are the field properties available in Base. gy} Entry Required: The value of this property can be Yes or No. If entry required is Yes, the field cannot be absent i.e. should be necessarily present with a value. _\ 2. Length: It designates the length of the value of the field. It is the maximum length 7" © that the field can hold. ye A- Default value: It designates the assumed value for the field, if it is not provided by the user input. 4. Format example: To set the field format of that field. It is used to reset the category, format, language and alignment of that particular field.Q.13 Creation of Query with a Design View: It is possible to create a query ih a different way. To create query using the design viev follow the given steps: 1, Click on the Queries button on the pane of the opening screen. A screen with different options appears. . Click on Create Query in Design View option. . The Add Table or Query dialog box appears. . Select the 1* table and click on the Add button. . Click on Close button to close the Add Table or Query dialog box. . In the Design grid for query, choose the fields of the tables, whether the field . is to be visible or not and the search criteria for the query. Aw wn 7. Click on Run Query from the interface or press FS key. The output of the amery anneareQ.14 (Structured Query Language (SQL) While querying using the methods given in this session, are good enough for most Purposes, you can put the entire query in a single line. This line of instruction will be parsed and processed by the DBMS and the query will be output. For example: 2 SELECT * FROM Emp_Table WHERE Salary < 15000 - After execution of the above query, all the tuples from Emp_Table having salary less than 15000 will be displayed. Additionally, all the fields part of the tuple are output. The * in the above query can be meaningfully replaced by a field list i.e. the fields that are to be displayed (rather than all of them, as with * in the query). ‘You could leave out the WHERE clause entirely, if you want all the tuples of the table unconditionally, In the last section, we dealt with grouping. Well, grouping can be enforced in SQL too. For example: © SELECT * FROM Emp_table WHERE Salary < 20000 GROUP BY Salary The above is a valid SQL query to query and group the output by salary. For the SELECT queries to work, you must have read permissions to the table. If you need to UPDATE tables, you must have write permissions as well to the tables. © UPDATE Stud_table SET percentange = 89.8 WHERE StudName = “Karun Mehta” In this query, the percentage of Karun Mehta will be set as 89.8 in the table named as Stud_table. : s \ DELETE * FROM Stud_table After the execution of this query, all the records of the table named as Stud_ table will be deleted. ‘You could write a different DELETE command, like: ° DELETE * FROM Stud_table WHERE Percentage < 80 Here, all the records having percentage less than 80 will be deleted from the table named as Stud_table.A. Multiple choice questions. 1, What is the singular form for data? (a) Datan (b) Datas 6 datum 2. Acolumn within a table that contains only one type of information is called a: (a) Record (b) File : JerField 3. Acollection of related records is known as: (a) Database Sor (0) FieldA. Multiple choice questions. + 1, What storage space does the Tiny Integer data type occupy in a Base table? a) 1 byte (b) 2 bytes (c) 4 bytes 2. How much space does the TIME data type hold in Base? (a) 2 bytes Ab) 4 bytes (c) 8 bytes + 3. How much space does the Bigint data type hold in Base? (a) 4 bytes Ab78 bytes (c) 6 bytesA. Multiple choice questions. = 1, Inwhich view of the table can you view the format of the table? JS oata view (b) Form view (c) Design view 2. Which dialog box allows addition of tables to a query? (a) Add Data (b) Add Data tables (SOG Tables 3, Which of the following relations is most likely NOT 1:1? Aa) Student ~ Teacher (b) Student - Roll Number (c) Student - Rank in a given yearA, Multiple choice questions. ‘4. What is output by the SQL query: SELECT 2 * 4 fa)2 (by4 Ase 2. Ifa table contains only even numbers in the field UsedNo, what is output by the query: Select UsedNo%2 ? (a) Either 0 or 1 {b) Only 0 3. What is the first step in Use Wizard to Create Query? (a) Sorting Order (b) Search conditions (c) Only 1 geld SelectionA. Multiple choice questions. 1. are the ways to produce the data stored in databases and tables in a printed form. (a) Forms (b) Queries —t¢J Reports 2. By right-clicking on an empty area of the form, you can change the of the form. (a) Element lengths (b) Element heights ef Background 3, Can you enter a whole new record into the table using forms? artes (b) No (c) It dependsA. Multiple choice questions. 1, If the people posing for a photo are data, then the photo uploaded on Facebook with the name of people in it i (a) Processing (b) Backing store LF information —_— 2. Acollection of related records is also known as: 2) Field {b) Database Le) 2+ Which of the following is a composite key most closely related to? HeayPrimary key (b) Foreign key (c) None of these 4. Cana primary key ever be NULL? (a) Yes V(b) No 46) It depends 5. Beforg starting to enter data in a table, you must create the: jesign of the table 'x) Database from which to read the data (c) Empty the table 6. How would you sort data in a table? \\ (a) Choose a to z or z to a from data view of table AS Place the cursor on the column by which to sort and choose a to zor toa {c) None of theseA. Multiple choice questions. L 2 Which of the following does DDL stand for? (a) Data Depleting Language (b) Data Development Language tc) Data Definition Language What does the S in SQL stand for? (a) Specific (b) Standard Xo) Structured To modify the datatype of a column in table, which command and keyword is used? (a) CREATE, change Ab) ALTER, modify (c) DROP, change The DROP command is used to: (a) Drop only databases ‘p) Drop only tables _(c) Drop databases or tablesClass 10 Relational Database Management Systems eet) Olr-(a02) A. Fill in the blanks. 188 1. A database is an organized collection of data. 2. A database management system is a software creati fi on Coe Package that can be used for creating and 3. A RDBMS is a database management system that is based on the relational model, 4, Three popular DBMS software are Microsoft Access, OpenOffice.org & MySQL. 5. A Primary Key is a unique value that identifies a row in a table, 6. Composite Key is a combination of one or more columns. 7. A table is a set of data elements that is organized using a model of vertical columns and horizontal rows. 8. A column is a set of data values of a particular type, one for each row of the table. 9. A row represents a single, data item in a table. 10. Datatypes are used to identify which type of data we are going to store in the database. 11, Create table DDL command is used to create a table. 12. Common DDL statements are create, alter and drop. 13, The types of languages used for creating and manipulating the data in the Database are Data Definition Language (DDL) & Data Manipulation Language (DML). 14. A DDL is a standard for commands that define the different structures in a database. 15. A DML is a language that enables users to access and manipulate data in a database. 46. A query language is a part of DML involving information retrieval only. 17. A popular data manipulation language is SQL. 18. Relationships are the basic building blocks of a database. 19. There are three types of Relationships in a table. 20. A table helps the user to systematically store information in the database. 21. A form enables users to view, enter, and change data directly in database objects such as tables. 22. SELECT statement retrieves zero or more rows from one or more database tables or database views. 23. By default, data is arranged in ascending order using ORDER BY clause. 24. UPDATE statement is used for modifying records in a database. 25. DELETE statement is used to remove one or more records in a Database. Page 1 of 6Pr Class 10 28. To create a form you need to select the form option available under Database section. Relational Database Management Systems (Basic) © IT (402) 27. A query helps to collect specific information from the pool of data in the database. 28. Report is used to display the summary of data. 29. GUL are the interfaces with which the user interacts. 30, Data from muttiple tables can be stored in Jabels. B. Question Answer. 1. What does DBMS stand for? Ans. The full form of DBMS is Database Management System. 2. What does RDBMS stand for? Ans. The full form of RDBMS is Relational Database Management System. 3. How is data organized in a RDBMS? Ans. The Relational Database Management System (RDBMS) organizes the data into tables. RDBMS tables are organized like other tables that have rows and columns. Rows are called records and columns are called fields. 4, State the relationship and difference between a primary and foreign key? Ans. In relation, a table will have only “one primary key", but foreign keys can be multiple. Primary key uniquely identifies a record in the table, Foreign key is a field in the table that is the primary key in another table. 5. In how many ways tables can be created in Base? Ans. Users can create a Database table in two ways. 4. Top-to-bottom approach: In this approach, first fields are defined and later domains and data elements are defined. 2. Using the Wizard to create a table Step 1: Select fields. Step 2: Set field data types and formats. Step 3: Set primary key. Step 4: Create the table. 6. are data types used in DBMS /RDBMS? ‘Ans. Data types are classified into five categories listed below. Numeric Types Alphanumeric Types Binary Types Date time Other Variable types eee ee Page 2 of 6class 10. Relational Database Management Systems (Basic) (402) : soutt yrs S2id EP 7. List datatypes available in Numeric Datatype? Ans. The different types of numeric data types are: a Boolean an Tinyint if ‘Smallint Integer Bigint Numeric Decimal Real Float Double ee 8. List datatypes available in Alphanumeric Data Type? Ans. The different types of Alphanumeric Data Type are: Longvarchar Char Varchar Varchar_Ignore Case eeee 9. Define the structure of a table. Ans. A table is a set of data elements (values) that is organized using a model of vertical colurnns (which are identified by their name) and horizontal rows. A table has a defined number of columns, but can have any number of rows. Each row is identified by the values appearing in a particular column identified as a unique key index or the key field. 40. Differentiate between Tuples and Attributes of a table. ‘Ans. A row also called a Record or Tuple represents a single, data item in a table. Whereas ‘A column is a set of data values of a particular simple type, one for each row of the table. 44, Name different Binary data types. Ans. The different Binary data types are: ‘@ Longvarbinary © Binary © Varbinary 42. What is the file extension for databases created using OpenOffice.Org Base? ‘Ans. The file extension for databases created using OpenOffice.Org Base is *.odb Page 3 of 6——— ; oO Class 10 Relational Database Management. Systems (Basic) IT (402) 13. List any three file formats that can be managed using OpenOffice.OrgBase? Ans. Three file formats that can be managed using OpenOffice.org base are: *.odt ~ This file format is used by OpenOffice.org Whiter application for creating documents. This is the counterpart of .doc extension of MS Word, “ods ~ This file format is used by OpenOffice.org Cale application for creating spreadsheets. This is the counterpart of fs extension of MS Excel, “.odp — This file format is used by OpenOffice.org Presentation application for creating Presentations. This is the counterpart of .ppt extension of MS Powerpoint. 14, How many types of relationships can be created in Base? Explain each of them. Ans. There are three types of relationships which can be created in tables: 1. ONE to ONE: In this relationship, both the tables must have primary key columns. 2. ONE to MANY OR MANY to ONE: In this relationship, one of the tables must have a primary key column. It signifies that one column of the primary key table is associated with all the columns of the associated table. 3. MANY to MANY: In this relationship, no table has the primary key column. ¢ 15. What do you mean by Sorting? In how many ways it can be done? Ans. Data sorting is any process that involves arranging the data into some meaningful order to make it easier to understand, analyze or visualize. Sorting can be done in either ascending order of descending order. 16. Explain Referential Integrity with the help of an example. Ans, Referential integrity is used to maintain accuracy and consistency of data in a relationship. In Base, data can be linked between two or more tables with the help of primary key and foreign key constraints, Referential integrity helps to avoid: * Adding records to a related table if there is no associated record available in the primary key table. * Changing values in a primary if any dependent records are present in associated table(s). © Deleting records from a primary key table if there are any matching related records available in associated table(s). _ 17. Name DML commands. s Ans. The DML commands are: SELECT —retrieve data from a database, INSERT — insert data into a table. UPDATE — updates existing data within a table. DELETE — deletes all records from a table, the space for the records remain eee. No Page 4 of 6Class10 _Relational Database Management Systems (Basic) 9! 1°62) 18. What is the purpose of using queries? 104 noowied otsitnraTtid .2S Ans. A query can either be a request for data results from your database of for action Gilthe’ddla, oF for both. A query can give you an answer to a simple question, perform calculations; combine data from different tables, add, change, or delete data from a database. 19, Which clause of the Select statement helps to display specific data? Ans. The SELECT statement WHERE clause helps to display Specific data, 20. Differentiate between Where and Orderby clause of SQL statements. "Ans. The difference between Where and Orderby clause of SQL statements is: * WHERE specifies which rows to retrieve, * ORDER BY specifies an order in which to return the rows. 21. State the purpose of Update Command with the help of an example. Ans. Update statement is used for modifying records in a database. The general syntax of the update statement is as follows: UPDATE SET = value [, column_name = value ...] [WHERE ]; Example: Update SDetails set Location = “Bhubaneswar” where Rollno = 14; 22. Why is there a need to create Forms? Ans. A form provides the user a systematic way of storing information into the database. It is an interface in a user specified layout that lets users to view, enter, and change data directly in database objects such as tables. 23. What is the purpose of creating Reports? Ans. A report helps to display the data in a summarized manner. Itis used to generate the overall work outcome in a clear format. You can create reports in the database, 24, What are the prerequisites to create a Form and Reports? Ans. Forms allow you to both add data to tables and view data that already exists. ne present data from tables and also from queries, which then search for and analyze data withit these same tables. Page 5 of 6Class 10 Relational Database Management nae Bele] © 17 (402) 25. Differentiate between Forms and Reports. t The difference between forme, and Reports is: Form rsifo 0. i Report ] « A form provides an interface that:allows « «Reports are’ used to present data users to enter, change and view the from tables or queries in a format data in a database table.Forms’ are that can be printed. made up of elements such as textboxes We cannot make changes to the and labels. data in a report. © We can make changes to the data in a form. 26. Can a form display data from queries? Ans. Yes, a form can display data from queries. 27. In how many ways Forms and Reports can be created in a database? Ans. In three different ways we can create Forms and Reports in database: © Ablank form and report. © Create a form and report by using a template. © Create a form and report using wizard.