0

I was given the following task:

Part1:

You are to design a schema (some tables) in which a student will have id, name; a course will have id, name; A schedule will have roomId, starting time; A room will have id, location, capacity.

I built the following: Students (varchar(10) PK id, first, last, courseId) --btw I think it's better to add an enumertion (identity) column and call this column + id column under PK - so there could be more than one row for each student (each for another course taken)

Courses (varchar(10) PK id, name)

Scheduler (FK to Courses CourseId, FK to Courses RoomId, date day, time(7) hour) -- What dataTypes are suitable here?

Rooms (PK id, location, capacity)

Do you think of a better design ?

Part2: You're to write the following queries a. return a list of all students and how many courses each of them took? order from highest to lowest.

select  s.id as STUDENT_ID,
        s.first as FIRST_NAME, 
        s.last as LAST_NAME,
        count (s.CourseId) as COURSE_COUNT
from    dbo.Students as s       
Group by s.id
order by count(s.CourseId) desc
--STUDENT_ID, FIRST_NAME, LAST_NAME, COURSE_COUNT

b. Which is the course with the highest number of students?

Create view q2 as

select top 1
        c.name as COURSE_NAME,
        count(s.id) as STUDENT_COUNT
from    dbo.Students as s   
        JOIN dbo.Courses as c
        ON (s.CourseId = c.id)  
Group by c.name
order by count(s.id) desc
--COURSE_NAME, STUDENT_COUNT

c. Which rooms has 2 or more courses overlapping?

Create view q3 as
select  sc.room as CLASSROOM_ID,
        r.location as LOC,
        COUNT(sc.courseId) as OVERLAP_COURSE_COUNT
from    dbo.Schedule as sc
        JOIN dbo.Rooms as r
        ON (sc.room = r.room)   
Group by sc.room, sc.day, sc.hour
HAVING COUNT(sc.courseId) > 1
--CLASSROOM_ID, LOC (Location), OVERLAP_COURSE_COUNT

d. Which are the courses with are not scheduled in the Schedule ?

Create view q4 as
select  c.id as COURSE_ID,
        c.name as NAME      
from    dbo.Courses as c
where c.id not in 
 (select sc.courseId
  from  dbo.Schedule as sc)

--  COURSE_ID, NAME
-- not in (course_id from dbo.Schedule)

Do you agree with these queries? Any better way to implement? I would try to execute them but would appreciate any comments.

Elad Benda
  • 35,076
  • 87
  • 265
  • 471

1 Answers1

1

Part1:

You are to design a schema (some tables) in which a student will have

id, name; a course will have id, name; A schedule will have roomId, starting time; A room will have id, location, capacity.

I built the following: Students (varchar(10) PK id, first, last, courseId) --btw I think it's better to add an enumertion (identity) column and call this column + id column under PK - so there could be more than one row for each student (each for another course taken)

Courses (varchar(10) PK id, name)

Scheduler (FK to Courses CourseId, FK to Courses RoomId, date day, time(7) hour) -- What dataTypes are suitable here?

Rooms (PK id, location, capacity)

Do you think of a better design ?

Your table design is flawed at the outset; there's a better way to set this up.

  1. In general, don't use varchar for primary keys unless you have to. (Note that, in most cases, you really won't have to. By most, I mean almost all. (By almost all, I mean all.))

  2. You stated that you would add an identity column, which is a good idea, but you said that you'd add it so that you could add a student more than once (for multiple classes), which is a bad idea. In this case, I would use a separate table to link your Students table and your Courses table - this way, you can avoid a duplication of data, which could lead to data anomalies.

  3. Following on #2 - the solution you created would have let you add more than one student to one course. However, can't students have more attend more than one course at a school? You should rethink your table relationships; you may realize that this would require a many-to-many table.

  4. If you're creating a link table between your Rooms and your Courses, and you're wondering what datatype you should use - you should use the datatype that your foreign key uses, or else you'll get an error when you try to create the foreign key. (This is, at least, the case for MySQL, although from your query syntax it looks as though you're writing for a Microsoft implementation.) As for the rest, I'd use the time datatype for the couse time, and some type of enumerated value for the day.