July 26, 2015

Requirement Specifications

Z Corp

Z Corp grants key-card access to rooms based on groups that key-card holders belong to. Design a database that supports the key-card system.

Specifications:

  • REQ-1: There are six users, and four groups.
  • REQ-2: Modesto and Ayine are in group "I.T"
  • REQ-3: Christopher and Cheong Woo are in group "Sales".
  • REQ-4: There are four rooms: "101", "102", "Auditorium A", and "Auditorium B".
  • REQ-5: Saulat is in group "Administration".

Specifications (cont..)

  • REQ-6: Group "Operations" currently doesn't have any users assigned.
  • REQ-7: I.T should be able to access Rooms 101 and 102.
  • REQ-8: Sales should be able to access Rooms 102 and Auditorium A.
  • REQ-9: Administration does not have access to any rooms.
  • REQ-10: Heidy is a new employee, who has not yet been assigned to any group.

Assumptions:

  • Users belong to only one group.

Table Relationships

  • Groups and Users: One to Many relationship. One group can have One or multiple users
  • Groups and Rooms: Many to Many relationship. One group can have access to one or multiple rooms and vice versa
  • Users and Rooms: Many to Many relationship. One or multiple users can have access to one or multiple rooms and vice versa

Create tables and Insert data

DROP TABLE IF EXISTS si_users CASCADE;

DROP TABLE IF EXISTS si_groups CASCADE;

DROP TABLE IF EXISTS si_rooms CASCADE;

  • CREATE TABLE si_users ( u_id int PRIMARY KEY, u_name varchar NOT NULL );

  • INSERT INTO si_users ( u_id, u_name ) VALUES ( 1, 'Modesto'); INSERT INTO si_users ( u_id, u_name ) VALUES ( 2, 'Ayine'); INSERT INTO si_users ( u_id, u_name ) VALUES ( 3, 'Christopher'); INSERT INTO si_users ( u_id, u_name ) VALUES ( 4, 'Cheng Woo'); INSERT INTO si_users ( u_id, u_name ) VALUES ( 5, 'Saulat'); INSERT INTO si_users ( u_id, u_name ) VALUES ( 6, 'Heidy');

Create tables and Insert data (cont..)

  • CREATE TABLE si_groups ( g_id int PRIMARY KEY, g_name varchar NOT NULL, u_id int NULL REFERENCES si_users );
  • INSERT INTO si_groups ( g_id, g_name, u_id ) VALUES ( 1, 'I.T', 1); INSERT INTO si_groups ( g_id, g_name, u_id ) VALUES ( 2, 'I.T', 2); INSERT INTO si_groups ( g_id, g_name, u_id ) VALUES ( 3, 'Sales', 3); INSERT INTO si_groups ( g_id, g_name, u_id ) VALUES ( 4, 'Sales', 4); INSERT INTO si_groups ( g_id, g_name, u_id ) VALUES ( 5, 'Administration', 5); INSERT INTO si_groups ( g_id, g_name, u_id ) VALUES ( 6, 'Operations', NULL);

Create tables and Insert data (cont..)

  • CREATE TABLE si_rooms ( r_id int PRIMARY KEY, r_name varchar NOT NULL, g_id int NULL REFERENCES si_groups );
  • INSERT INTO si_rooms ( r_id, r_name, g_id ) VALUES ( 1, '101', 1); INSERT INTO si_rooms ( r_id, r_name, g_id ) VALUES ( 2, '102', 2); INSERT INTO si_rooms ( r_id, r_name, g_id ) VALUES ( 3, '102', 3); INSERT INTO si_rooms ( r_id, r_name, g_id ) VALUES ( 4, 'Auditorium A', 4); INSERT INTO si_rooms ( r_id, r_name, g_id ) VALUES ( 5, 'Auditorium B', NULL);

Select statement 1 with results

1. All groups, and the users in each group. A group should appear even if there are no users assigned to the group.

  • SELECT b.g_id, b.g_name, a.u_id, a.u_name FROM si_users a RIGHT JOIN si_groups b ON a.u_id = b.u_id;

Select statement 2 with results

2. All rooms, and the groups assigned to each room. The rooms should appear even if no groups have been assigned to them.

  • SELECT b.r_id, b.r_name, a.g_id, a.g_name FROM si_groups a RIGHT JOIN si_rooms b ON a.g_id = b.g_id;

Select statement 3 with results

3. A list of users, the groups that they belong to, and the rooms to which they are assigned. This should be sorted alphabetically by user, then by group, then by room.

  • SELECT a.u_id as userid, a.u_name as employee_name, b.g_name as group_name, c.r_name as room_name FROM si_users a LEFT JOIN si_groups b ON a.u_id = b.u_id FULL OUTER JOIN si_rooms c ON b.g_id = c.g_id ORDER BY a.u_name, b.g_name, c.r_name;