database query and index

Question

Given two relations:

      Students = (St-Id, Name, Address, CourseNo, Cgpa)
      Courses = (CourseN0, CourseName, Credits)

where primary keys are St-Id and CourseNo. CourseNo in Students relation is a foreign key references Courses relation.

Assume the following queries are frequent:

Question: What are the courses (CourseNo and CourseName) studied by each student?

SELECT Students.Name, Courses.CourseName, Course.CourseNO
FROM Students
INNER JOIN Courses
ON Students.CourseNo=Course.CourseNo;

Is that the right query by using join operation?

It's a a primary index because of course number. Can we consider it as rule to say courseNo is a primary index? It's also clustering? What is the difference between clustering and primary index?

Question: What is the Cgpa for each student?

Answer : Select Cgpa and name from students

Clustered index is explained here: dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html. It's usually the same as the primary key. — Barmar
– Barmar, Commented Apr 9, 2015 at 11:37

Ubother · Accepted Answer · 2015-04-09 11:54:13Z

2

I agree with simon at rcl, your design only allows one course per student. Try to put an intersection table between student and courses.

Students = (St-Id, Name, Address, Cgpa)
Courses = (CourseN0, CourseName, Credits)
StudentCourse = (St-Id, CourseN0)

answered Apr 9, 2015 at 11:54

Ubother

1731 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2015-04-09 11:40:32Z

0

If the table has a primary key, then the clustered index is the same as the primary key. If it doesn't have a primary key, InnoDB will create a clustered index on its own. The rules it uses to decide how to do this are described here.
Your syntax is not quite right. To select multiple columns from the table, you separate them with comma, not and. So it should be:
```
SELECT name, cpga FROM student
```

answered Apr 9, 2015 at 11:40

Barmar

789k57 gold badges555 silver badges669 bronze badges

4 Comments

user3624832 Over a year ago

Question: What is the Cgpa for each student? is a secondary index because cgpa is not aprimary index and it will frequently access.Right?

Barmar Over a year ago

The Cgpa query is just returning columns from every row. There's no need for an index, because you're not trying to match anything.

user3624832 Over a year ago

in case in future i match Cgpa with registering course for example , with address or with relation that does not have a primary key .In that case can we use secondary index for the Cgpa ? What the index will be ?

Barmar Over a year ago

I'm not sure what kind of query you're talking about. Why would the Cgpa match up with any other table? Cgpa is Combined Grade Point Average, right? It's a calculated value that wouldn't appear in any other table. But if you did need to match it frequently, there's no reason you couldn't add an index on it. You can add an index on any column you want.

simon at rcl · Accepted Answer · 2015-04-09 11:41:42Z

0

Also, if St-id is the primary key of Students then a student can only have one course. You design looks a bit lacking there.

answered Apr 9, 2015 at 11:41

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

Comments

VJP · Accepted Answer · 2015-04-09 11:42:30Z

0

SELECT 
     name
    ,cgpa
FROM
   students
WHERE 
   1=1

try this

answered Apr 9, 2015 at 11:42

VJP

11 bronze badge

Collectives™ on Stack Overflow

database query and index

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related