all papers written from scratch

24/7/365 support

no plagiarism - GUARANTEED

Free Abby College Students Accommodation Essay Sample

← Summative Assesment: Principles and Practice Database System Phase 3DB2 →

Buy Cheap Abby College Students Accommodation Essay

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 [email protected]

 

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                                                     

Has

 

 

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                                                     

       
     
   
 

Has

 

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                                                     

       
     
   
 

Has

 

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

Email

Gender

RoomId

Startdate

Enddate

Amount

Semeter

1345

Lucy

98735

[email protected]    

F

1

06/01/08

06/09/08

2000

2

9686

Luke

8765

[email protected]

M

2

06/01/08

06/09/08

2000

2

7588

Loise

6437

[email protected]

F

9

06/01/08

06/09/08

2000

2

7548

John

9284

[email protected]

M

3

06/01/08

06/09/08

2000

2

0737

Joyce

6538

[email protected]

F

4

06/01/08

06/09/08

2000

2

7463

Peter

7584

[email protected]

M

5

06/01/08

06/09/08

2000

2

5463

Lydia

4563

[email protected]

F

7

06/01/08

06/09/08

2000

2

5674

Andy

93287

[email protected]

M

10

06/01/08

06/09/08

2000

2

2978

Mary

6768

[email protected]

F

6

06/01/08

06/09/08

2000

2

0375

Jonny

09855

[email protected]

M

8

06/01/08

06/09/08

2000

2

 

 

Related essays

  1. Database System Phase 3DB2
  2. Successful Students
  3. Summative Assesment: Principles and Practice
  4. Math
www.casinoworld123.info/pravila-igr/igrovye-avtomaty/

hbhostel.com.ua/

купить диплом специалиста
15% first order  Order now  close
Close