INF3703 Assignment 2 (COMPLETE ANSWERS) 2025 - Due 27 June 2025

INF3703
ASSIGNMENT 2 2025

UNIQUE NO.
DUE DATE: 27 JUNE 2025

,2nd EXAMPLE

-- INF3703 ASSIGNMENT 2 SOLUTION

-- Student Number: 99999999



-- ===================================

-- QUESTION 1: PHYSICAL DESIGN SETUP

-- ===================================



-- 1.4.2: Verify user tablespace assignment

SELECT username, default_tablespace

FROM dba_users

WHERE username = 'ASS2_99999999';



-- ===================================

-- QUESTION 2: ERD (Textual Description)

-- ===================================

-- Entities: POS_DEVICE_99999999, HOM_99999999, STAFF_99999999, SHIFT_99999999

-- Ternary relationship: CHECKOUT_99999999 (with FK references to all 3 entities and
multiplicities)

-- Multiplicities:

-- - HOM can check out many POS devices per shift.

-- - Each STAFF member gets 1 POS per shift.

-- - POS devices can be unassigned (optional).

, -- Use a ternary ERD tool (draw.io, Lucidchart) to visually model the above and include:

-- - PKs, FKs, entity names suffixed by _99999999

-- - Connectors labeled with cardinalities (e.g., 1:N, 0:1, etc.)



-- ===================================

-- QUESTION 3: MULTIVALUED ATTRIBUTE

-- ===================================

-- 3.1 Multivalued Attribute: STAFF_PHONE

-- Justification: A single staff member can have multiple phone numbers.



-- 3.2 Create new entity:

-- Entity: STAFF_PHONE_99999999

-- Columns:

-- PHONE_ID (PK),

-- STAFF_ID (FK),

-- PHONE_NUMBER



CREATE TABLE STAFF_PHONE_99999999 (

PHONE_ID VARCHAR2(10) PRIMARY KEY,

STAFF_ID VARCHAR2(10),

PHONE_NUMBER VARCHAR2(20),

CONSTRAINT FK_STAFF_PHONE FOREIGN KEY (STAFF_ID)

REFERENCES MAINTENANCE_STAFF_99999999(STAFF_ID)