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. Each question is worth 3 marks.

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

a.

create table LAB
-> (RoomNo varchar(20) primary key , Capacity int not null);
Query OK, 0 rows affected (0.35 sec)

mysql> desc LAB;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| RoomNo | varchar(20) | NO | PRI | NULL | |
| Capacity | int(11) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table SOFTWARE
-> (SoftwareID int primary key , SoftwareName varchar(20) not null, Version int not null);
Query OK, 0 rows affected (0.30 sec)

mysql> desc SOFTWARE;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| SoftwareID | int(11) | NO | PRI | NULL | |
| SoftwareName | varchar(20) | NO | | NULL | |
| Version | int(11) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

b.

mysql> create table REQUEST (
-> SoftwareID int , RoomNo varchar(20),RequestDate date not null,TeachingPeriod int not null, Progress varchar(20) not null, primary key(SoftwareID,RoomNo), foreign key (SoftwareID) references SOFTWARE(SoftwareID) on delete cascade , foreign key (RoomNo) references LAB(RoomNo) on delete cascade);
Query OK, 0 rows affected (0.41 sec)

mysql> desc REQUEST
-> ;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SoftwareID | int(11) | NO | PRI | NULL | |
| RoomNo | varchar(20) | NO | PRI | NULL | |
| RequestDate | date | NO | | NULL | |
| TeachingPeriod | int(11) | NO | | NULL | |
| Progress | varchar(20) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

c.

mysql> insert into LAB
-> values ("350.2.006",100);
Query OK, 1 row affected (0.05 sec)

mysql> insert into SOFTWARE values ("101","VS code",2);
Query OK, 1 row affected (0.05 sec)

mysql> insert into REQUEST values (101,"350.2.006","2019-08-08",3,"Installed");
Query OK, 1 row affected (0.06 sec)

mysql> select * from LAB;
+-----------+----------+
| RoomNo | Capacity |
+-----------+----------+
| 350.2.006 | 100 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from SOFTWARE;
+------------+--------------+---------+
| SoftwareID | SoftwareName | Version |
+------------+--------------+---------+
| 101 | VS code | 2 |
+------------+--------------+---------+
1 row in set (0.00 sec)

mysql> select * from REQUEST;
+------------+-----------+-------------+----------------+-----------+
| SoftwareID | RoomNo | RequestDate | TeachingPeriod | Progress |
+------------+-----------+-------------+----------------+-----------+
| 101 | 350.2.006 | 2019-08-08 | 3 | Installed |
+------------+-----------+-------------+----------------+-----------+
1 row in set (0.00 sec)

d.

mysql> alter table REQUEST
-> add constraint cons CHECK(Progress IN("Requested", "Installed", "Function
on Testing", "User Acceptance Testing","Deployed"));
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc REQUEST;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SoftwareID | int(11) | NO | PRI | NULL | |
| RoomNo | varchar(20) | NO | PRI | NULL | |
| RequestDate | date | NO | | NULL | |
| TeachingPeriod | int(11) | NO | | NULL | |
| Progress | varchar(20) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

e.

mysql> update LAB
-> set Capacity =Capacity+5
-> where RoomNo="350.2.006";
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from LAB;
+-----------+----------+
| RoomNo | Capacity |
+-----------+----------+
| 350.2.006 | 105 |
+-----------+----------+
1 row in set (0.00 sec)