Question

You have been given the following specifications for a simple database about the requests for software that staff members make for their units (note that primary keys are shown underlined, foreign keys in bold). You should run your SQL to demonstrate that it works correctly, and paste in the statements used plus the output from Oracle.

LAB (RoomNo, Capacity)

SOFTWARE (SoftwareID, SoftwareName, Version)

REQUEST (SoftwareID, RoomNo, RequestDate, TeachingPeriod, Progress)

Based on the table specifications provided, answer the following questions.

a. Give the SQL to create the LAB and SOFTWARE tables. Choose appropriate data types. None of the attributes should be allowed to be null. Include the primary key constraints.

b. Give the SQL to create the REQUEST table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that if a lab is deleted from the database, any requests that exist for that lab are also deleted.

c. Give the SQL to add a record to each of the tables: LAB, SOFTWARE and REQUEST. Make up your own data (you may wish to add lab 350.2.006 to the LAB table for testing in part (e) later).

d. Give the SQL to create a constraint to the REQUEST table to restrict the possible values of Progress to the following 5: Requested, Installed, Function Testing, User Acceptance Testing, and Deployed.

e. Give the SQL to record the fact that the capacity of lab 350.2.006 has increased by 5.

Answer 1

drop table request;
drop table lab;
drop table software;

create table lab
(
    roomNo varchar2(50),
    Capacity int,
    CONSTRAINT lab_pk PRIMARY KEY (roomNo)
);

insert into lab values ('500',50);
insert into lab values ('600',50);
insert into lab values ('700',50);
insert into lab values ('800',50);

create table software
(
    SoftwareId varchar2(50),
    SoftwareName varchar2(50),
    Version varchar2(50),
    CONSTRAINT software_pk PRIMARY KEY (SoftwareId)
);

insert into software values ('1','Oracle','5.2');
insert into software values ('2','SQL Server','5.2.3');
insert into software values ('3','MySQL','5.2.1.2');

create table request
(
    softwareId varchar2(50),
    roomNo varchar2(50),
    RequestDate date,
    TeachingPeriod varchar2(50),
    Progress varchar2(50),
    CONSTRAINT software_fk FOREIGN KEY (SoftwareId) REFERENCES software(SoftwareId),
    CONSTRAINT room_fk FOREIGN KEY (roomNo) REFERENCES lab(roomNo)
);

insert into request values ('1', '500', sysdate ,'102', 'None');
insert into request values ('2', '600', sysdate ,'102', 'None');

select * from lab;
select * from software;
select * from request;