With proper assumptions and using ONE of the following relations to answer the q

Question

With proper assumptions and using ONE of the following relations to answer the question. You can refer to the schema of university database shown at the end of the question

student(ID, name, dept_name, tot_cred)

takes(ID, course_id, sec_id, semester, year, grade)

course(course_id, title, dept_name, credits)

Explain how to create different indices: primary index, secondary index, dense index, sparse index, respectively. Using any relation, you can either describe based on the definition of the indices or answer with the instance of the relation using some fake data/tuples.

University schema diagram is given below

classroom(building, room number, capacity)

department(dept name, building, budget)

course(course id, title, dept name, credits)

instructor(ID, name, dept name, salary)

section(course id, sec id, semester, year, building, room number, time slot id)

teaches(ID, course id, sec id, semester, year)

student(ID, name, dept name, tot cred)

takes(ID, course id, sec id, semester, year, grade)

advisor(s ID, i ID) time slot(time slot id, day, start time, end time)

prereq(course id, prereq id)

Explanation / Answer

Q1) Explain how to create different indices: primary index, secondary index, dense index, sparse index, respectively. Using any relation, you can either describe based on the definition of the indices or answer with the instance of the relation using some fake data/tuples.

Primary Index Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation.

Eg:- student(ID, name, dept_name, tot_cred). So here the Primary Key of the relation will be ID, which is also our Primary Index.

Secondary Index Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Eg:- student(ID, name, dept_name, tot_cred). So here the Secondary Index can be dept_name, because its a non key attribute in Student relation and it will contain duplicate values for many student records.

Dense Index:- In dense index, there is an index record for every search key value in the database. This makes searching faster but requires more space to store index records itself. Index records contain search key value and a pointer to the actual record on the disk.

student(ID, name, dept_name, tot_cred)

Sparse Index:- In sparse index, index records are not created for every search key. An index record here contains a search key and an actual pointer to the data on the disk. To search a record, we first proceed by index record and reach at the actual location of the data. If the data we are looking for is not where we directly reach by following the index, then the system starts sequential search until the desired data is found.

Eg: department(dept name, building, budget)

Please let me know in case of any clarifications required. Thanks!

1001 ------> Gopal Malaker CSE 7.89 1002 -------> Jack Sparrow ECE 8.90 1003 -------> Annie McFlaren CHE 7.65

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.