Database Systems Case Study by akm33296

VIEWS: 170 PAGES: 12

									                   Case Study:1




Database Systems
   Case Study
                                                                                              Case Study:2
                                A Universe of Discourse
The university provides a number of courses in areas such as information systems, software engineering
and computer science. Each course comprises a set of modules. Each module is delivered in one
semester by one or more lecturers. A lecturer may deliver more than one module in each semester. Some
modules have a set of pre-requisite modules specified. A module may act as a pre-requisite for more
than one module.
Each year students register against a course. A Student may register for only one course at one time.
However, the system needs to maintain a history of student course registrations over a number of years.
On each course, a student registers against each module chosen. A student may register against up to
five modules per semester. In each module a student is assessed. A student may be assessed a number of
times against each module.
                                                                      Case Study:3

              Conceptual Model: ER Approach

                                       Lecturer




           Course
                                       Teaches




                                  Module               Prerequisite


CourseRegistration


                     Assessment   ModuleRegistration




                      Student
                                                                      Case Study:4
    Conceptual Model: Including Optionality
                                      Lecturer




           Course                      Teaches




                                     Module            Prerequisite


CourseRegistration



                     Assessment   ModuleRegistration




                       Student
                                                 Case Study:5



      Generalisation



                Student
                          (disjoint, complete)


Undergraduate        Postgraduate
                                                                              Case Study:6

                        Part of a Relational Schema

Domains                               Relation Modules
moduleNames: character                Attributes
levels: {1,2,3}                       moduleName: moduleNames
courseCodes: character                level: levels
staffNos: integer                     courseCode: courseCodes
statuses: {L,SL,PL,Reader,Prof,HOD}   staffNo: staffNos
staffNames: character                 Primary Key moduleName
                                      Foreign Key staffNo references Lecturers Not Null
                                      Delete Restricted
                                      Update Cascades

                                      Relation Lecturers
                                      Attributes
                                      staffNo: staffNos
                                      staffName: staffNames
                                      status: statuses
                                      Primary Key staffNo
                                                                     Case Study:7

                Part of an Object-Oriented Schema
Create Class Student                      Create Class Assessment
Superclass: object                        Attributes
Attributes                                studentDetails: Student
studentName: character,                   moduleDetails: Module
termAddress: character,
dateOfBirth: date,                        Create Class Module
dateOfEnrolment: date                     Attributes
Methods                                   moduleName: STRING,
enrolStudent(courseCode: Course)          level: INTEGER,
                                          roll: INTEGER
Create Class Undergraduate                Relationships
Superclass: student                       givenOn Course,
Relationships                             taughtBy List(Lecturer)
memberOf Set(Course) Inverse enrols       Methods
                                          increaseRoll(amount: INTEGER)
Create Class Postgraduate
Superclass: student
Relationships
assignedTo Department Inverse registers
                                           Case Study:8

                                 Methods



body enrolStudent(courseCode: Course)
conditions
student exists
courseCode exists
roll of course not exceeded
actions
create new instance of Enrolment class
update Student dateOf Enrolment
                                                  Case Study:9
                    Part of a Deductive Schema

module(relationalDatabaseSystems).
module(relationalDatabaseDesign).
student(johnDavies).
student(prakeshPatel).
takes(johnDavies, relationalDatabaseSystems).
takes(prakeshPatel, relationalDatabaseDesign).
lecturer (hywelEvans).
lecturer(paulBeynonDavies).
teaches(hywelEvans, relationalDatabaseSystems).

assesses (Lecturer, Student) :-
          takes(Student, Module),
          teaches(Lecturer, Module).

enrolStudent(S, C):-
         student(S),
         module(C),
         assert(takes(S,C)).
                                                           Case Study:10

                                Part of an Oracle Schema

CREATE TABLE Lecturers
(staffNo NUMBER (3),
staffName CHAR(20),
status CHAR (3) DEFAULT(‘L’),
deptName CHAR(30));


 CREATE TABLE Modules
 (moduleCode CHAR(6),
 moduleName CHAR (15),
 mlevel NUMBER(1) DEFAULT(1),
 courseCode CHAR (5),
 staffNo NUMBER(3));
                                                                                       Case Study:11

                                  Oracle Constraints

ALTER TABLE Lecturers ADD CONSTRAINT L_pk PRIMARY KEY (staffNo);
ALTER TABLE Modules ADD CONSTRAINT M_pk PRIMARY KEY (moduleCode);
ALTER TABLE Modules ADD CONSTRAINT M_fk FOREIGN KEY (staffNo) references Lecturers(staffNo);
ALTER TABLE Modules ADD CONSTRAINT M_level_1 CHECK (mlevel BETWEEN 1 and 3);
ALTER TABLE lecturers ADD CONSTRAINT L_status_1 CHECK (status IN (‘L’,’SL’,’PL’));
ALTER TABLE Modules ADD CONSTRAINT L_staffNo_1 CHECK (staffNo BETWEEN 100 AND 999);
                                                                                         Case Study:12

                                         Oracle Triggers

CREATE TRIGGER lecturer_delete_trig
AFTER      DELETE
ON Lecturers
BEGIN
           IF TO_CHAR(sysdate, ‘HH24’) > 13 THEN
                    RAISE_APPLICATION_ERROR(-20010,’Not allowed to delete a lecturer now’);
           END IF;
END;
/


CREATE OR REPLACE TRIGGER student_update_row
BEFORE UPDATE
OF totalGrant
ON Students
FOR EACH ROW
BEGIN
            IF :new.totalGrant < :old.totalGrant THEN
             :new.feecompensation := :old.totalGrant – :new.totalGrant;
            END IF;
END;

								
To top