Reference to the School_DB, consider (un-normalized) version that contains the f

Question

Reference to the School_DB, consider (un-normalized) version that contains the following relation:

Participate_In_Activity (std_id, name, address, school_id, act_id, act_date, ranking)

A student is identified with a unique std_id, and has only one address. students can have multiple activities, but they always have different activity dates. The student can have different rankings on different activities but only one ranking per activity.

Assume that the following four SQL commands are known to be frequent (with actual parameters substituted in for ?):

1. SELECT DISTINCT name, address FROM Participate_In_Activity WHERE std_id = ?;

2. SELECT * FROM Participate_In_Activity WHERE std _id = ? AND act_date > ?;

3. SELECT std_id, ranking FROM Participate_In_Activity WHERE ranking BETWEEN ? AND ?;

a) Suggest one or more indexes, taking into account of the above.

b) State the indexed attributes for each index, along with the index type (primary or secondary).

Please show detailed solution with explanations, thanks!

STUDENT frame lname address gender grade std id class president school id STAFF Sid name lname bdate gender Salary role School id SCHOOL school id school name school head id SCHOOL BOARD school id school board name ACTIVITY act id act name act date school id PARTICIPATE std id act id Ranking

Explanation / Answer

Solution:-

A) Index -

The given database is in unnormalized form and tables schemas are given. A relation Participate_In_Activity is given. Every Table has a primary key in the given schema. We need to introduce index in the database.

In this case of database the primary keys of the tables are used as the index. For example the std_id is primary key in the table Student. The primary key std_id can be used as the index for the relation. This is a general understanding of the index. The index has thew search key std_id by which any desired record can be fetched from the relation.

The school_id is also a primary key so it can be used to constitute a primary index.

B) Primary and Secondary Index -

Primary index - these indexex are constitutes by the primary keys. The key value of arelation makes an index called the primary key. For example in the above case the std_id is a primary key and constitutes an index for thr thr realtion. The sdt_id is a primary index.

Secondary index - The secondary index in database can be constitutes by the candidate keys and any non key values combination. For example the given database schema we consider the std_id as the primaruy index for the relations. Now if we want to constitute a secondary database then we can take the address and school_id as a candidate key. This candidate key can be used to constitute the secondary index.

The school_id and the address are attributes and none of them are primary keys of any relation. Both attributes make a candidate key together so it can be used to constitute a secondary index. We can create many other secondary indexes with the available candidate keys if required.

The primary indexes attributes based on the given database schema-

1) std_id

2) school_id

3) s_id

4) act_id

These are the primary keys of the different relations in the database. By these primary keys we can constitute the primary indexes as per rquirement.

The Secondary indexes attributes based on the given database schema-

1) fname and address

2) school_id and address

3) act_id and act_date

These are the attributes together behaves like candidate keys and these can be used to constitute the secondary indexes.

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

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