” 写作COMP7104程序、 辅导Database Systems程序、 辅导JavaCOMP7104 DASC7104 2019-2020 Advance Database SystemsHomework 2 Indexing and Relational AlgebraNote: the pictures are slightly simplified, they do no show any record pointerfor the leaf nodes.For drawing B+ trees you could use tools such as the following: httpss://www.cs.usfca.edu/~galles/visualization/BPlusTree.html httpss://projects.calebevans.me/b-sketcher/1. Assume we have the following B+-tree of order 1. Each node in the indexmust have either 1 or 2 keys (i.e, 2 or 3 pointers), and the leaves can holdup to 2 entries.a) What is the height of this tree after the following sequence of insertions:26, 27, 28, 29, 30? Note that the current tree height is 2.b) What is the maximum number of keys one can insert into the tree abovewithout changing its height?c) What is the minimum number of keys one can insert to change theheight of the tree above?d) Unrelated to the index tree above, assume you bulk-loaded the keys 1 to60 into a B+ tree with fill-factor 0.75 and order d = 2. How many leaf nodeswould there be?2. Suppose we have an Alternative 2 unclustered index on the attribute pair(assignment_id, student_id) with a height of 2 (one must traverse 3 indexpages to reach a leaf page).Here is the schema: 写作COMP7104作业、 辅导Database Systems作业CREATE TABLE Submissions (record_id integer UNIQUE,assignment_id integer,student_id integer,time_submitted integer,grade_Received byte,comment text,regrade_request text,PRIMARY KEY(assignment_id, student_id) );CREATE INDEX SubmissionLookupIndex ON Submissions (assignment_id,student_id);Assume the table and its associated data takes up 12 MB (note:1MB=1024KB) on disk and that page size is 64 KB. (This excludes the indexpages, but includes extra space allocated for future insertions, thereforepages are assumed 2/3 full).Assume also you know the size of each attribute type: integer 4B, text: 32B,byte: 1B. Page pointers (page Ids) are 2B long, row Ids are 4B long.a) How many records do we have in the Submissions table?b) We want to scan all the records in Submissions. How many I/Os will thisoperation take?c) The following instruction is executed:UPDATE SubmissionsSET grade_received=85WHERE Assignment_id=20 AND student_id=12345;How many I/Os will this operation take?d) In the worst case, how many I/Os does it take to perform an equalitysearch on attribute grade_received?3. Consider the B+ tree index of order d = 2 shown below.a) Show the index Resulting from inserting a data entry with key 9 into thisindex.b) Show the index resulting from inserting a data entry with key 3 into theoriginal index. How many page reads and page writes are necessary for thisinsertion?c) Show the index resulting from deleting the data entry with key 8 from theoriginal index, assuming that the left sibling is checked for possibleredistribution.d) Show the index resulting from deleting the data entry with key 8 from theoriginal index, assuming that the right sibling is checked for possibleredistribution.e) Show the index resulting from starting with the original index, inserting adata entry with key 46 and then deleting the data entry with key 52.f) Show the index resulting from deleting the data entry with key 91 from theoriginal index.4. Relational Algebra. Consider the following schema:Movie(movieId: integer , title: string , directorId:integer)MovieDirector(directorId:integer , dname:string)MovieClient(clientId:integer, cname:string)Seen(movieID: integer , clientID:integer)Liked(movieID: integer , clientID: integer)The primary key fields are underlined, and the domain of each field is listedafter the field name. For example, movieId is the primary key for the Movietable, movieId and clientId together form the primary key for Seen, etc.Describe in plain English (without using words such as join, project, select,etc) what the Following relation algebra expressions (execution plans)compute (if they compute something):The following relational operators are used: : natural join (with Equality condition on all common attributes) : projection : selection : set difference.a) title(DirectorId (dname=John WuMovieDirector) Movie)b) title,dname(DirectorId (dname=John WuMovieDirector) Movie)c) title (Seen Liked)d) ClientId(MovieClient) ClientId(MovieId (Movie) x ClientId (MovieClient) Seen)e) ClientId(Liked Seen)如有需要，请加QQ：99515681 或邮箱：99515681@qq.com
“
添加老师微信回复‘’官网 辅导‘’获取专业老师帮助，或点击联系老师1对1在线指导。
