Free Abby College Students Accommodation Essay Sample

Abby College Students Accommodation

Table 1. AccommodationType

  Keep the types of accommodation i.e. Hall of Residence and Apartment

AccommodationTypeId ,  Name

Table 2. AccommodationHostels

Keeps records of different types of accommodations

HostelId, AccommodationTypeId, Name ,Rent, OfficerId

Table3. Rooms

Keeps records of the room details

RoomId, HostelId , Placenumber

Table4. AccommodationOfficer

Keeps details of officers incharge of different accommodation

OfficerId, name, salary, recruitdate

Table5. StudentsAccommodation

Keeps record of students’ accommodation details

AdminNo, name, phone, email, gender, RoomId, startdate, enddate, AmountPaid, semester

ANSWERS

• Creates table named accommodationType with two fields AccommodationTypeID which takes smallint as the data type, and name which takes data of type string(varchar).

CREATE table AccommodationType

(AccommodationTypeId smallint identity(1,1) , name varchar(60))

• Alters table AccommodationType by setting AccommodationTypeId as the primary key

Alter table AccommodationType ADD PRIMARY KEY(‘ AccommodationTypeId’) clustered

• Creates table named AccommodationHostels with fields HostelId, name, AccommodationTypeID, rent money, OfficerID and start date

CREATE table AccommodationHostels

(HostelId smallint identity(1,1) ,

name varchar(60),

AccommodationTypeId bigint  not null 

rent money not null,

OfficerId bigint, startdate date);

• Alters table AccommodationHostels by setting HostelID as the primary key

ALTER table AccommodationHostels ADD PRIMARY KEY(‘HostelId) clustered;

• Alters table AccommodationHostels by adding AccommodationTypeId in AccommodationHostels as its foreign key

ALTER TABLE  abby. AccommodationHostels

ADD CONSTRAINT  ‘HostelId’ Foreign key(‘AccommodationHostels.AccommodationTypeId’)

REFERENCES AccommodationType. AccommodationTypeId

ON DELETE CASCADE

ON UPDATE CASCADE

ADD INDEX AccommodationTypeId’

• Creates a table named rooms with field roomed, HostelId and PlaceNumber

CREATE table Rooms

(RoomId smallint identity(1,1) ,

,HostelId bigint, Placenummber bigint   );

• Alters table rooms by setting RoomId as the primary key

ALTER table Rooms ADD PRIMARY KEY(RoomId) clustered;

• Adds HostelId in AccommodationHostel as Rooms Foreign Key

ALTER TABLE  abby Rooms

ADD CONSTRAINT  ‘HostelId’ Foreign key(Rooms. HostelId)

REFERENCES AccommodationHostel. HostelId

ON DELETE CASCADE

ON UPDATE CASCADE

ADD INDEX HostelId

• Creates table AccommodationOfficer with field OfficerId,name, salary, jobtype

CREATE table AccommodationOfficer

(OfficerId smallint identity(1,1) , 

name varchar(60),

salary Money  not null 
check (salary >= 0),

salary money  not null check (salary <= 75000),

 jobtype varchar(50) not null check(jobtype) in (“Manager”, “Supervisor” , “Clerk”,  “Care Taker”, “Supervisor” , “Secretary”  , “Accountant”, recruitdate date    );

• Alters table AccommodationOfficer by adding OfficerId as the primary key

ALTER table AccommodationOfficer

ADD PRIMARY KEY(OfficerId) clustered;

• Creates table named students accommodation with fields AdminNo, name,phone Gendeer, start date, endDate,roomed as the fields.

CREATE table StudentsAccommodation

(AdminNo  smallint identity(1,1) primary key clustered,

name varchar(70) notnull, phone numeric notnull,

gender varchar(50)t not null  check(gender) in (“M”, “F” ),

startdate DateTime  not null ,

enddate DateTime  not null check (enddate > startdate),

RoomId bigint notnull,

AmountPaid money,

semester varchar(20) notnull,

email varchar(100) check(email(“@”)));

• Alters table rooms by adding AdminNo as the primary key

ALTER table Rooms ADD PRIMARY KEY(AdminNo) clustered;

ALTER TABLE  abby StudentsAccommodation

ADD CONSTRAINT  RoomId Foreign key(StudentsAccommodation. RoomId)

REFERENCES Rooms. RoomId

ON DELETE CASCADE

ON UPDATE CASCADE

ADD INDEX RoomId

This query retrieves the  students’ details.

SELECT * FROM StudentsAccommodation

This is the query that displays all employees who were recruited during 2010, giving their name and hire-date.

SELECT name, recruitdate from AccommodationOfficer

WHERE recruitdate >=’01/01/2010’

 AND recruitdate <=’31/12/2010’

This query lists the employee’s name, job and salary who conduct the inspection of a specific apartment.

SELECT AccommodationOfficer .name, AccommodationOfficer. job, AccommodationOfficer .salary

FROM AccommodationOfficer , AccommodationHostels

WHERE AccommodationHostels. OfficerId = AccommodationOfficer. OfficerId

This query gives the number of student in a specific hall that started 1 October 2009.

SELECT COUNT(StudentsAccommodation. AdminNo)

 FROM AccommodationHostels,StudentsAccommodation

 WHERE  AccommodationHostels. Startdate>= ‘1 October 2009’

AND StudentsAccommodation. RoomId =Rooms. RoomId

AND Rooms. HostelId= AccommodationHostels. HostelId

This query lists all the different rooms and the hall they are located in order by hall name.

SELECT Rooms. Placenummber,  AccommodationHostels.name

FROM Rooms, AccommodationHostels

WHERE AccommodationHostels. HostelId= Rooms. HostelId

ORDERBY AccommodationHostels.name ASC

This lists the student name, room number and lease details.

SELECT name, Placenummber, startdate, enddate

FROM StudentsAccommodation

This query lists the employee names and salary. It also shows the increment of 10% and then retrieves the final figure.

DECLARE @Salary money

DECLARE @OfficerId Bigint

DECLARE @SalaryAftterIncrease money

DECLARE @SalaryIncrease float

SET @SalaryIncrease= (100+10)/100

Select AccommodationOfficer .name,@Salary=(  AccommodationOfficer .salary) , @OfficerId =(  AccommodationOfficer .OfficerId)

FROM AccommodationOfficer , AccommodationHostels

 WHERE AccommodationHostels. OfficerId = AccommodationOfficer. OfficerId

SET @SalaryAftterIncrease= round( @Salary*@SalaryIncrease)

UPDATE  AccommodationOfficer  SET Salary= @SalaryAftterIncrease

 WHERE OfficerId=@OfficerId

This lists the room number, the apartment it is in, and the students who are living in those rooms.

SELECT Rooms. Placenummber, AccommodationHostels.name, StudentsAccommodation.name FROM  Rooms, AccommodationHostels, StudentsAccommodation

 WHERE Rooms. HostelId= AccommodationHostels. HostelId

AND Rooms. RoomId= StudentsAccommodation.RoomId

E-R Diagrams

Entity sets

AccodationType (AccomodationTypeId(pk) ,  Name)

AccomodationHostel (HostelId(pk), AccomodationTypeId(fk), Name ,Rent, OfficerId)

 E-R Diagram                                                     

Entity sets

AccomodationHostel (HostelId(pk), AccomodationTypeId(fk), Name ,Rent, OfficerId)

Rooms (RoomId(pk), HostelId (fk), Placenummber(ck))

Relationships

AccomationHostel has more than one rooms            [M: N] [0: 0]

 E-R Diagram                                                     

Entity sets

Rooms (RoomId(pk), HostelId (fk), Placenummber(ck))

   StudentsAccomodation (AdminNo(pk), name, phone , email, gender , RoomId(fk), startdate, enddate, AmountPaid, semester)

Relationships

Rooms has more than one student     [M: N] [0: 0]

 E-R Diagram                                                     

Entity sets

AccomodationHostel (HostelId(pk), AccomodationTypeId(fk), Name ,Rent, OfficerId(fk))

      AccomodationOfficer (OfficerId(pk), name, salary, recruitdate)

Relationships

AccomationHostel has more than one rooms            [M: N] [0: 0]

Listing of records in each table

Table  AccomodationType

Table  AccomodationHostels

Table  Rooms

Table  AccomodationOfficer

Table  StudentsAccomodation