Employee so the second row from the table would be

Info icon This preview shows pages 35–38. Sign up to view the full content.

View Full Document Right Arrow Icon
(employee) So, the second row from the table would be selected. Now, we’ll try to get a relation where this selected row doesn’t exist. How? Very simple. Just use set difference: employee – σ employee_name = "B" (employee)
Image of page 35

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
32 The above set-difference operation will produce the following relation: employee_name branch_name branch_city salary A DU Dhaka 1000 C BUET Dhaka 3000 Now, we can just replace the original employee table with this new table using the assignment operation: employee employee – σ employee_name = "B" (employee) Voilá, it’s done! Update Update is used to change or modify values of one or more attributes of a tuple/tuples. The SQL syntax for updating is: update table set attribute_1 = value_1, ..., attribute_n = value_n where expression So how does the update operation work? Consider the above table again. Suppose we want to change employe B ’s city from Rajshahi to Comilla . To do this, we’ll first use projection to project the attributes of that row. But instead of projecting branch_city , we’ll directly project “ Comilla ”. This will forcibly place “Comilla” as the attribute’s value. The projection is as follows: П employee_name, branch_name, "Comilla" , salary (employee) We’ll get a relation containing the following tuple: employee_name branch_name branch_city salary B RU Comilla 2000 Now, we’ll delete the row to be updated (i.e., the row containing employee_name B ) from the original table: employee employee – σ employee_name = "B" (employee) The original table should look like the following: employee_name branch_name branch_city salary A DU Dhaka 1000 C BUET Dhaka 3000 Now, let’s add our previously projected record to this new table using union operation: employee employee ( П employee_name, branch_name, "Comilla" , salary (employee)) Thus, in the final employee table, the attribute will be updated. Therefore, the steps for updating the above attribute can be expressed as follows: t П employee_name, branch_name, "Comilla" , salary (employee) employee employee – σ employee_name = "B" (employee) employee employee (t)
Image of page 36
33 Indexing and Hashing The Problem Many queries reference only a small fraction of records in a file. For example, “find all accounts at Mirpur branch” only returns records from account file where branch_name = “Mirpur”. It is inefficient for the system to read every record and to check the branch_name field for the name “Mirpur”. We should be able to locate these records directly. To allow these forms of access, we design additional structures associated with files. These additional structures are called indices. Basic Concepts An index for a file in a database system works in much the same way as the index in this textbook. If we want to learn about a particular topic (specified by a word or a phrase) in this textbook, we can search for the topic in the index at the back of the book, find the pages where it occurs, and then read the pages to find the information we are looking for. The words in the index are in sorted order, making it easy to find the word we are looking for.
Image of page 37

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 38
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern