Home‎ > ‎CIS 54‎ > ‎Lab 11: Project Implementation‎ > ‎

Example Implementation

Schema Update

The image below shows my completed schema:

I made a few changes to my schema from the previous assignment:
  • I added a Room entity so that a section could be taught in a room 
  • I make the key of Grade a composite key of Assignment and Student to match the expected way to solve an MVD. 
  • Course has a surrogate key instead of using the course ID. That way if a course changes it can get a new surrogate key and students who've taken the previous version don't see the changes on their transcript. 
  • A grade applies to an Assigned, not an assignment. That way, if I assign the same assignment to multiple sections one student can turn it in multiple times. 
The MWB file for my schema is attached to the bottom of this page. 

Schema With Data 

I added data to my schema and exported the SQL. The exported SQL is attached to the bottom of the page. 

Procedures

Here's a description of CRUD operations on my entities: 
  1. When a student registers for classes they're created. They can update their name but students are never deleted. 
  2. When a course is created it gets a unique ID. Courses cannot be updated, new IDs will be assigned even if the course number is the same. 
  3. Rooms are created when buildings are. A room renovation may change the number of available seats. 
  4. I create assignments and make them due on a particular day. They can be copied but not reused. They are never deleted. 
Here's how the relationships are formed: 
  1. When a student enrolls in a class an enrollment is created. If they drop the class the enrollment is deleted. 
  2. When a course is taught a section is created and given a room. Sections are never deleted. 
  3. When an assignment is given to a class it is assigned (an assignment row is created). Assignments can be deleted (but not often). 
  4. When I grade an assignment a grade is created. Grades can be changed if I made an error. 

Summary Procedure

It's good to know how many students are enrolled in a class before the beginning of the semester and how much space there is in a section. The following query determines those quantities:
 
select 
Section.SectionNumber, count(*) as Enrolled, Room.Capacity
from 
Enrollment, Section, Room 
where 
Enrollment.SectionNumber = Section.SectionNumber and
    Section.RoomID = Room.RoomID
group by
Section.SectionNumber
;

Grouping Procedure

At the end of the semester I have to calculate everyone's grade. The grade is based on the percentage of points earned compared to the total points available. The following query calculates the final grades of every student in every section and prints an ordered list: 

select 
Student.Name, Assigned.SectionNumber, 100*(sum(Grade.Score) / sum(Assignment.Total)) as Percent
from 
Student, Grade, Assigned, Assignment 
where 
Student.StudentID = Grade.StudentID and
    Grade.AssignmentID = Assigned.AssignmentID and
    Grade.SectionNumber = Assigned.SectionNumber and 
Assigned.AssignmentID = Assignment.AssignmentID 
group by 
Student.StudentID, Assigned.SectionNumber
order by 
Assigned.SectionNumber, Student.Name
    ;

A Transaction

In order for a student to enroll there has to be enough space in the class. Therefore when performing an INSERT Into the enrollment table I must check to be sure that the limit is not exceeded. The transaction pseudo-code looks like this:

insert into Enrollment

Run the query:
select 
Section.SectionNumber, count(*) as Enrolled, Room.Capacity
from 
Enrollment, Section, Room 
where 
    Enrollment.SectionNumber = Section.SectionNumber and
    Section.RoomID = Room.RoomID
group by
Section.SectionNumber
having Enrolled > Capacity
;

If the above query returns any rows:
    ROLLBACK
otherwise 
    COMMIT

When ROLLBACK happens the enrollment is stopped. 
ċ
ExampleProjectModel.mwb
(11k)
Michael Matera,
May 3, 2017, 2:18 PM
ċ
Project.sql
(9k)
Michael Matera,
May 3, 2017, 2:29 PM
Comments