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
AccommodationType |
Name |
1 |
Hall Of Residence |
2 |
Student Apartment |
Table AccomodationHostels
Hostel |
AccommodationTypeId |
Name |
OfficerId |
1 |
1 |
Normandy |
1 |
2 |
2 |
West Front |
10 |
3 |
1 |
East Front |
2 |
4 |
2 |
Aberdeen |
4 |
5 |
1 |
Kelly |
6 |
6 |
1 |
Lincoln |
7 |
7 |
2 |
Margaret |
9 |
8 |
1 |
Thames |
3 |
9 |
1 |
Lowland |
8 |
10 |
2 |
Rhine |
5 |
Table Rooms
RoomId |
HostelId |
Placenumber |
1 |
1 |
1 |
2 |
2 |
10 |
3 |
1 |
2 |
4 |
2 |
4 |
5 |
1 |
6 |
6 |
1 |
7 |
7 |
2 |
9 |
8 |
1 |
3 |
9 |
1 |
8 |
10 |
2 |
5 |
Table AccomodationOfficer
OfficerId |
Name |
Salary |
Recruitdate |
1 |
James |
23,000 |
01/01/2010 |
2 |
Mwangi |
43,000 |
01/11/2010 |
3 |
John |
33,000 |
31/01/2010 |
4 |
James |
23,000 |
06/01/2009 |
5 |
Elsie |
73,000 |
09/10/2009 |
6 |
Isaac |
53,050 |
06/01/2010 |
7 |
Rebecca |
23,300 |
06/01/2009 |
8 |
James |
23,000 |
06/01/2009 |
9 |
Luke |
23,000 |
06/01/2008 |
10 |
James |
23,000 |
06/01/2009 |
Table StudentsAccomodation
Adm No. |
Name |
Phone |
|
Gender |
RoomId |
Startdate |
Enddate |
Amount |
Semeter |
1345 |
Lucy |
98735 |
wd@m.c |
F |
1 |
06/01/08 |
06/09/08 |
2000 |
2 |
9686 |
Luke |
8765 |
1w@m.c |
M |
2 |
06/01/08 |
06/09/08 |
2000 |
2 |
7588 |
Loise |
6437 |
1q@m.c |
F |
9 |
06/01/08 |
06/09/08 |
2000 |
2 |
7548 |
John |
9284 |
y@m.c |
M |
3 |
06/01/08 |
06/09/08 |
2000 |
2 |
0737 |
Joyce |
6538 |
r@m.c |
F |
4 |
06/01/08 |
06/09/08 |
2000 |
2 |
7463 |
Peter |
7584 |
y@m.c |
M |
5 |
06/01/08 |
06/09/08 |
2000 |
2 |
5463 |
Lydia |
4563 |
d@m.c |
F |
7 |
06/01/08 |
06/09/08 |
2000 |
2 |
5674 |
Andy |
93287 |
m@m.c |
M |
10 |
06/01/08 |
06/09/08 |
2000 |
2 |
2978 |
Mary |
6768 |
m1@m.c |
F |
6 |
06/01/08 |
06/09/08 |
2000 |
2 |
0375 |
Jonny |
09855 |
b1@m.c |
M |
8 |
06/01/08 |
06/09/08 |
2000 |
2 |