Oracle Database – Designing a Parking Lot Table for Employees

oracle

I am having dificulty designing a table that stores the information about a parking lot with 100 spaces that are arranged by 5 columns x 20 rows for employees, guests, handicap, executives, contractors and using an outer join sql statement.

I am using an Oracle DBMS. I have a project to do for a Database Fundamentals course in college that requires the creation of several tables (employees, retirementplan, healthplan, healthcost, healthtype, ParkingSpace).

The requirement is that the HR personnel will assign the parking space. So I am assuming that it needs a create view table or some kind of scenario where there is a prompt for HR to assign the space to that person. I am also of the mindset that each space should be named so it can be referenced. How is this accomplished. Can someone please guide me or provide the basics, please. I am a newbie so please take it easy on me. Thanks a million.

Best Answer

I drew an ER diagram for the parkinplace model.

ER diagram created wit h the dia tool

(The dia file can be found here)

A parkingplace can be assigned to at most one employee. There may be parkingplaces that are not assigned to employes (e.g. parkingplaces for guests). One can store the column/row coordinate of each parking place. This may be expected for this homework problem. One may not do this in practice because these coordinates are not realy useful when on administrates the parking places. If there are parking places added that are not on this area then column and rows may not make sense. But if one has no column/row attribute a string attribute for specifying the location may be useful. This attribbute could hold the column/row information as string. So basically the parking place table containts 100 entries, one for each parking place. 


create table employee(employee_id number  primary key,
                      first_name varchar2(30),
                      last_name varchar2(30))
/
create table reservation(name varchar2(30) primary key)
/
create table parkingplace(parkingplace_id number
                            primary key,
                          reservation_name varchar2(30)
                            references reservation(name),
                          employee_id number
                            references employee(employee_id),
                          grid_column number not null,
                          grid_row number not null,
                          unique(grid_column,grid_row),
                         check(grid_column between 1 and 5),
                         check(grid_row between 1 and 20),
                         check(employee_id is null 
                               or reservation_name !='guest') )
/
create index idx_reservation_name on parkingplace(reservation_name)
/
create index idx_employee_id on parkingplace(employee_id)
/

The following script fills the tables with testdata



insert into reservation(name) values('employees')
/
insert into reservation(name) values('guests')
/
insert into reservation(name) values('handicap')
/
insert into reservation(name) values('executives')
/
insert into reservation(name) values('contractors')
/
declare
n number;
begin
n:=0;
for r in 1..20 loop 
  for c in 1..5 loop
    n:=n+1;
    insert into parkingplace(
      parkingplace_id,
      reservation_name,
      grid_column,
      grid_row) values(n,'employees',c,r);
    end loop;
  end loop;
end;
/

update parkingplace
 set reservation_name='handicap'
  where grid_column=1 or grid_column=5
/


update parkingplace
 set reservation_name='guests'
  where grid_row=1
/


insert into employee(employee_id, first_name, last_name)
            values(1,'Clark','Kent')
/
update parkingplace
  set employee_id=1
  where parkingplace_id=6
/
insert into employee(employee_id, first_name, last_name)
            values(2,'Loise','Lane')
/
update parkingplace
  set employee_id=2
  where parkingplace_id=7
/

insert into employee(employee_id, first_name, last_name)
            values(3,'Lana','Lang')
/
update parkingplace
  set employee_id=3
  where parkingplace_id=8
/

insert into employee(employee_id, first_name, last_name)
            values(4,'Bruce','Wayne')
/
update parkingplace
  set employee_id=4
  where parkingplace_id=9
/

If you want to display all 100 parkin places with the employee names if there is an employee assigned then you have to use a LEFT OUTER JOIN.

select 
grid_row,
grid_column,
reservation_name,
first_name,last_name 
from 
parkingplace left outer join 
employee on (parkingplace.employee_id=employee.employee_id);

Because we have stored the geometrical information (row and column number) we can output the row * column matrix with the appropriate query

select grid_row,c1,c2,c3,c4,c5
from (
  select 
grid_row,
grid_column,
first_name,
last_name
from 
parkingplace left outer join 
employee on (parkingplace.employee_id=employee.employee_id)
 )
pivot (max(first_name||' '||last_name) for grid_column in (
  1 as c1,
  2 as c2,
  3 as c3,
  4 as c4,
  5 as c5))
order by grid_row;

A demonstration can be found in sqlfiddle

This PIVOT extension of the select statement is implemented in Oracle since version 11. Prior version of Oracle have to use technics similar to that shown by @kkarns in a comment. 

select 
 grid_row,
max(decode(grid_column,1, first_name||' '||last_name,null)) c1,
max(decode(grid_column,2, first_name||' '||last_name,null)) c2,
max(decode(grid_column,3, first_name||' '||last_name,null)) c3,
max(decode(grid_column,4, first_name||' '||last_name,null)) c4,
max(decode(grid_column,5, first_name||' '||last_name,null)) c5
from 
parkingplace left outer join 
employee on (parkingplace.employee_id=employee.employee_id)
group by grid_row
order by grid_row
;
Related Question