Accommodation Services for the Language Summer School - Essay Example

DATABASE SYSTEMS WORK CASE STUDY: Accommodation services for the language summer school Primary analysis of the case study (Identifying Entities and their attributes): After a detail analysis of the entire case study, I derive at the following possible entities and their attributes: ENTITIES ATTRIBUTES STAFF Name, Address, Number, Position, Gender, Contact_Info STUDENTS Number, Address, Date_of_Birth, Gender, Residence, Status HALL Number, Address, Telephone, Room_Type ROOM Id, Number, Weekly_Rent INVOICE Student_Number, Room_Id, PaymentDue, Date_of_Payment, Amount, Method_of_Payment The above table of entities and their attributes follows from the analysis of the case study. These entities form the master entities and others will follow from the logical and conceptual relationships between these entities. Assumptions from the case study: I have made the following assumptions: 1. The students residence, as maintained by the office, includes either Renting a room or Flat I have decided to take an attribute in the student entity to maintain that. It takes the value as either 'Room' or 'Flat'. The attribute is named as Residence. 2. The status of the student as to they are currently 'allocated' or in 'waiting list' is determined by the attribute called Status. 3. The room type in the Hall entity takes two values 'single' and 'double' rooms. Identifying relationships between entities: 1. Relationship between STAFF and STUDENT is depicted below: 2. Relationship between STUDENT and HALL is depicted below: 3. Relationship between STUDENT and ROOM is depicted below: 4. Relationship between STUDENT, ROOM and INVOICE is depicted below: Constructing Conceptual model: Assumptions: 1. The rectangle depicts an entity and the cone shaped depicts a relationship type. 2. The conceptual model depicts all the associated relationships between the entities. The descriptions and related associations are as follows: a. The relationship type 'responsible for' is linking the student and the staff. b. The relationship type 'rents' is linking the student and the room. The special assumption taken in this particular relationship type is that it has an attribute called 'lease' which is an attribute to a relationship type. c. The relationship type 'has_details' binds the student, invoice and room entities. As the invoice entity has details of both the student and the room and also its own properties needs to be linked with all the three to depict the relation. d. The relationship 'reside' links student and hall entities. As students reside in the hall in a particular room. This conceptual model depicts all the entities and their relations; it gives the overall picture and understanding of the system to a novice. This can also be called a logical model as it does not take into consideration any DBMS in mind and shows the logical association of the system. Next we are constructing the logical system which has the cardinality ratios. A cardinality ratio of a binary relation is the maximum number of instances an entity can participate in a given relationship type. Logical model The logical model is as follows: The difference here is that the entities here are associated with their relationships with the cardinality ratios respectively. The cardinality ratios are derived as follows: 1. Between student and staff (M:N). a. 1 student is responsible to 1 staff b. Many (M) students are responsible to 1 staff. c. 1 staff takes responsibility of 1 student d. Many (M) staff takes responsibility of many students. 2. Between student and hall (N:1). a. 1 student resides in 1 hall. b. Many (M) students reside in 1 hall. c. 1 hall accommodates 1 student d. Many (M) halls do not accommodate 1 student. 3. Between student, hall and invoice (1:1:1). a. 1 invoice has details of 1 student and his hall. b. 1 invoice does not have details of more than 1 student and their halls. c. Many invoices do not have details of many students and different halls. 4. Between student and room (1:1). a. 1 student rents 1 hall. b. Many (M) students cannot rent 1 hall. c. 1 hall is rented by 1 student d. Many (M) halls are not rented by 1 student. Producing tables form the logical model Special note: The attribute named 'lease' to the relationship named 'rent' has been shifted to the rent table as the cardinality ratio is 1:1 so any attribute of the relationship type must be shifted on either side. The arrows show the functional dependencies. The single arrow with no point head determines all the attributes of the entire entity. In the table 'Responsible_For' which was previously a relationship between student and staff had a cardinality ratio of M:N. The relationship type will form a separate table with the same name having the primary keys of both the aligned tables. Normalization of tables 1. As the first normal form says: All the attributes must be atomic and not have multi valued values. a. Only the table 'Room' has an attribute called 'Room Type' which can have both 'single' and 'double' rooms in a particular hall. Thus it needs to be decomposed into two separate attributes namely 'Single' and 'Double'. b. I have also assumed that in a hall there could be more than 1 single room and double room. So the separated attribute will be able to distinguish how many single and double rooms are present in a particular hall. 2. The second normal form says: There should be no non-key attribute which should be partially dependent on a key attribute. a. Here the key attributes are those which determine the other attributes in a particular relation or a table. They uniquely identify the tuple in a given table. b. We see here that there is no non key attribute which is partially dependent on a key attribute or a set of attributes. So all the tables are in second normal form. 3. The third normal form says that there must not exist transitive dependency between attributes. It precisely means that if one attribute, say A1, determines another attribute, say A2, and if A2 again determines a third attribute A3, then it precisely means that A1 determines A3 which in maximum cases is not true. a. In our case study, there is no occurrence of such an attribute which has the feature of transitive dependency. Query the database using SQL: 1. At the Office front: a. The details of the students Who rented a room or a flat or waiting list candidates. SELECT ALL FROM STUDENTS AS S WHERE S.RESIDENCE LIKE 'ROOM' OR S.RESIDENCE LIKE 'FLAT' OR S.STATUS LIKE 'WAITING'; b. The details of the students and their respective staffs. SELECT S.NUMBER, ST.NUMBER FROM STUDENTS AS S, STAFF AS ST, RESPONSIBLE_FOR AS RF WHERE S.NUMBER=RF.NUMBER AND ST.NUMBER=RF.NUMBER; c. The details of student, staff and their accommodations. SELECT ALL FROM STUDENTS AS S, STAFF AS ST, ROOM AS R, HALL AS H, RESPONSIBLE_FOR AS RF WHERE S.NUMBER=RF.NUMBER AND ST.NUMBER=RF.NUMBER AND S.NUMBER=R.STUDENT_NUMBER AND ST.NUMBER=H.STAFF_NUMBER; d. The details of students and their accommodation whose payment is due and method of payment is 'cash'. SELECT ALL FROM STUDENTS AS S, ROOM AS R, INVOICE AS INV WHERE S.NUMBER=INV.STUDENT_NUMBER AND R.ID=INV.ROOM_ID AND INV.PAYDUE LIKE 'YES' AND INV.METHOD_OF_PAY LIKE 'CASH'; e. The details of students with maximum lease and date of payment is within the last 3 months. SELECT ALL FROM STUDENTS AS S, ROOM AS R, INVOICE AS INV WHERE S.NUMBER=INV.STUDENT_NUMBER AND R.ID=INV.ROOM_ID GROUP BY S.NUMBER HAVING R.LEASE=MAX (R.LEASE) AND MAX (SYSDATE - INV.DATE_OF_PAYMENT) > 90; f. The details of female students with their staff details with unpaid invoices and minimum lease. SELECT ALL FROM STUDENTS AS S, STAFF AS ST, RESPONSIBLE_FOR AS RF, INVOICE AS INV WHERE S.NUMBER=INV.STUDENT_NUMBER AND RF.STAFF_NUMBER AND S.NUMBER=RF.STUDENT_NUMBER AND S.GENDER LIKE 'FEMALE' AND INV.PAYMENTDUE LIKE 'YES' GROUP BY S.NUMBER HAVING MIN (R.LEASE)=R.LEASE; 2. At the Student front: a. Invoice details, Staff details and if payment due date has passed. SELECT ALL FROM STUDENTS AS S, STAFF AS ST, ROOM AS R, RESPONSIBLE_FOR AS RF, INVOICE AS INV WHERE S.NUMBER=RF.NUMBER AND ST.NUMBER=RF.NUMBER AND S.NUMBER=R.STUDENT_NUMBER AND INV.DATE_OF_PAYMENT < SYSDATE AND INV.PAYMENTDUE LIKE 'YES' GROUP BY S.NUMBER; b. Details of their staff and weekly rent in either flat or room. SELECT ST.NAME, ST.ADDRESS, R.RENT_WEEKLY FROM STUDENTS AS S, STAFF AS ST, ROOM AS R, RESPONSIBLE_FOR AS RF WHERE S.NUMBER=RF.NUMBER AND ST.NUMBER=RF.NUMBER AND S.NUMBER=R.STUDENT_NUMBER AND S.STUDENT_RESIDENCE IN ('FLAT','ROOM'); 3. At the Staff front: a. The details of the students and their respective rooms and halls. SELECT ALL FROM STUDENTS AS S, STAFF AS ST, ROOM AS R, RESPONSIBLE_FOR AS RF, HALL AS H WHERE S.NUMBER=RF.NUMBER AND ST.NUMBER=RF.NUMBER AND S.NUMBER=R.STUDENT_NUMBER AND S.STUDENT_RESIDENCE IN ('FLAT','ROOM') AND H.STUDENTNUMBER=S.STUDENT_NUMBER; b. The details of the students who stay in double rooms and have maximum lease. SELECT ALL FROM STUDENTS AS S, STAFF AS ST, ROOM AS R, RESPONSIBLE_FOR AS RF AND HALL AS H WHERE S.NUMBER=RF.NUMBER AND ST.NUMBER=RF.NUMBER AND S.NUMBER=R.STUDENT_NUMBER AND S.STUDENT_RESIDENCE WITH 'ROOM' AND H.ROOMTYPE LIKE 'DOUBLE' GROUP BY S.NUMBER HAVING MAX (R.LEASE) =R.LEASE; BRIEF REPORT: 1. The attribute 'lease' was a little tricky to plot in to an entity. I chose to place it in Room entity as lease quite sounds similar to room type and it is also associated with it. 2. The strengths of the system is that it covers almost all details of the students living in the school. 3. The limitations are that a. It does not include their food details and the amenities provided to them at the school. b. The staff may also have shift duties as one staff cannot be responsible all the day round. c. There are no separate accommodations for males and females. The system design can be in any way a designer desires to develop but must be logically sound to avoid redundancy of information and reduce anomalies of addition, deletion and modification of data. Bibliography Navathe Elmasri. Fundamentals of Database systems. Read More