1

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.

  • 1
    I have grave difficulty of a reasonable way to construct a ParkingSpace table that stores information about 100 spaces using 20 rows of 5 columns each. That goal appears to violate every rule of table construction known to mankind. I assume, therefore, that this is not really what your instructor wants. Columns represent attributes. What attributes of a parking space do you want to capture? Rows represent instances of a thing. If there are 100 parking spaces, you could have 100 rows in your table. Beyond that, though, I'm not sure quite what you're asking. – Justin Cave Apr 25 '14 at 2:29
  • this is the requirements: parking lot with 100 spaces (5 columns x 20 rows) (no exceptions to this requirement). HR needs to assign the parking spaces. I can create the table with the column names but how does HR reference each individual space and assigns the it to the employee? – procommtech8128 Apr 25 '14 at 4:01
  • 1
    I can't find many ways of reading that statement in a way that creates a vaguely reasonable requirement in a class that is, presumably, trying to teach the rudiments of proper database design. Maybe if "100 spaces" is not intended to mean "100 parking spaces" but the intention is to create a table with 20 parking spaces where the parking_lot_id is one of 5 attributes the requirement might be plausible. But that's a pretty tortured reading of the text. Since this is a class, I'd strongly suggest seeking clarification from the professor. – Justin Cave Apr 25 '14 at 4:06
  • 3
    @JustinCave the other possibility that comes to my mind is perhaps the physical parking lot has 5 rows of 20 parking-spaces each. If so, the table doesn't need 5 columns and 20 rows, it simply needs 100 rows, with whatever columns are necessary to describe each parking spot. Agreed, clarification from the professor is advised! – Max Vernon Apr 25 '14 at 5:01
  • 1
    @MaxVernon - That's why I was thinking initially that a 100 space parking lot would involve 100 rows in the ParkingSpace table. If the text of the assignment is exactly what was entered here and there is no additional context a table with 20 rows each with 5 columns does not make sense for a parking lot with 100 spaces. – Justin Cave Apr 25 '14 at 5:04
2

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
;
  • excellent example. this is exactly what i was imagining in my head; using row and column grid, but had no idea how to form the logic and the statements. Awesome job!!!! – procommtech8128 Apr 28 '14 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.