CareerCup

DATABASE DESIGN

Forum Post

11 Answers DATABASE DESIGN

Suppose I have tables student(sid,name,cid) and course(cid,cname)
how do i make sure that a student can take more than one course without redundant rows (like
sid name cid
101 xyz 02,
101 xyz 03,
101 xyz 04...ie it would be redundant if each student takes a large number of courses).

- 4m7u1 August 29, 2013 | Flag |

Email me when people comment.

Email me when people comment.

Loading...

An error occurred in subscribing you.

Comment hidden because of low score. Click to expand.

2

of 2 vote

I believe that the most efficient solution (as far as redundancy is concerned) is to have 3 tables:

Student:
* sid
* name

Course
* cid
* cname

StudentCourses
* sid
* cid

This way you only store the Student name and the Course name (which is where the "heavy" data is) once.

Best,

Slavi
HiredInTech

- Anonymous September 06, 2013 | Flag Reply

Comment hidden because of low score. Click to expand.

0

of 0 votes

Yea.. that works... thank you :)

- 4m7u1 September 10, 2013 | Flag

Comment hidden because of low score. Click to expand.

0

of 0 vote

Use multivalued columns.

- Anonymous August 29, 2013 | Flag Reply

Comment hidden because of low score. Click to expand.

0

of 0 votes

But am not allowed to do that. It should be normalized, atleast in 1NF.

- 4m7u1 August 30, 2013 | Flag

Comment hidden because of low score. Click to expand.

0

of 0 votes

What else are you not allowed?

- Anonymous August 30, 2013 | Flag

Comment hidden because of low score. Click to expand.

0

of 0 votes

INF is sufficient.

- 4m7u1 August 31, 2013 | Flag

Comment hidden because of low score. Click to expand.

0

of 0 vote

use the name and the course as a dimension and then use another table to store the courses selected by a student as a transaction. so effectively you would use less space. and remove redundancy.

- rohit chati August 31, 2013 | Flag Reply

Comment hidden because of low score. Click to expand.

0

of 0 vote

do one thing remove cid from student table and create a new table studnt_course(sid,cid) then redundancy would get off....

- ashishmits24 September 01, 2013 | Flag Reply

Comment hidden because of low score. Click to expand.

0

of 0 votes

nice. thanks. but is there anyway we could further optimize it , i mean without having to store redundant data( like if a student takes 20 courses then we need to store the students name 20 times against the appropriate courses).
P.S: I was asked this in Capital IQ, when i gave answer same as yours he asked me remove redundancy.

- 4m7u1 September 03, 2013 | Flag

Comment hidden because of low score. Click to expand.

0

of 0 votes

didn't get it what r u saying please tell using any example.

- ashishmits24 September 03, 2013 | Flag

Comment hidden because of low score. Click to expand.

0

of 0 votes

surely, the new table stores sid,cid right.
suppose there is a student with sid-01 and he takes courses c01,c02,c03,c04,c05,c06,c07 and c08.
then we need to have rows and table as
sid cid
01-c01
01-c02
01-c03
.......
01-c08.
what i meant to ask is, can this redundancy be removed in any way?

- 4m7u1 September 03, 2013 | Flag

Email me when people comment.

Email me when people comment.

Loading...

An error occurred in subscribing you.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.