Database Assignment: Data Dictionary and SQL Tests

This document presents a database assignment, including a data dictionary that defines the structure of tables such as BUILDING, APARTMENT, TENANT, LEASE, RENTPAYMENT, and MAINTENANCEREQUEST. It also includes a database diagram and a series of SQL tests to validate the database's functionality. The tests cover SELECT statements with WHERE clauses, aggregate functions, JOIN operations, and INSERT and UPDATE commands. Each test includes the SQL query and the result status, indicating whether the test passed.

DATABASE ASSIGNMENT
STUDENT NAME:
12 November, 2016

Contents
DATA DICTIONARY.................................................................................................................................2
DATABASE DIAGRAM.............................................................................................................................4
DATABASE TEST.....................................................................................................................................4

DATA DICTIONARY
TABLE COLUMN DATA
TYPES
BUILDING BUILDING_KEY INT
BUILDING_NAME Nvarchar
(50)
BUILDING_ADDRESS Nvarchar
(50)
BUILDING_CITY Nvarchar
(50)
BUILDING_STATE Nvarchar
(50)
BUILDING_ZIP Nvarchar
(10)
BUILDINGMANAGERPHONE Nvarchar
(10)
APARTMENT APARTMENTKEY VARCHAR
(10)
APARTMENTROOM INT
BUILDINGKEY INT
TENANT TENANTKEY VARCHAR
(10)
TENANTFIRSTNAME VARCHAR
(30)
TENANTLASTNAME VARCHAR
(30)
TENANTPHONE VARCHAR
(10)
LEASE LEASEKEY VARCHAR
(10)
LEASEMONTHLYRENT NUMERIC
(10,2)
LEASEDEPOSIT NUMERIC
(10,2)
LEASESTARTDATE DATE
LEASEENDDATE DATE
APARTMENTKEY VARCHAR
(10)

TENANTKEY VARCHAR
(10)
RENTPAYMENT RENTPAYMENTKEY INT
RENTPAYMENTDATE DATE
RENTPAYMENTAMOUNT NUMERIC
(10,2)
LEASEKEY VARCHAR
(10)
MAINTENANCEREQUEST MAINTENANCEREQUESTKEY VARCHAR(5
)
MAINTENANCEREQUESTDATE DATE
MAINTENANCEREQUESTTYPE VARCHAR
(30)
MAINTENANCEREQUESTDESCRIPTION VARCHAR
(50)
LEASEKEY VARCHAR
(10)
MAINTENANCEREQUESTDETAIL
S
MAINTENANCEREQUESTDETAILSKEY INT
MAINTENANCEREQUESTKEY VARCHAR(5
)
MAINTENANCEREQUESTDETAILSACTION VARCHAR
(30)
MAINTENANCEREQUESTDETAILSCOST NUMERIC
(10,2)
MAINTENANCEREQUESTDETAILSBUILDINGEXPEN
SE
NUMERIC
(10,2)

DATABASE DIAGRAM
DATABASE TEST
RULE: To test the working of select statement using where clause
SQL: SELECT * FROM APARTMENT WHERE APARTMENTROOMS = 4;
RESULT
STATUS: PASS
RULE: To test the working of select statement using where clause
SQL: SELECT * FROM LEASE WHERE LEASEMONTHLYRENT < 5000;
RESULT:

STATUS: PASS
RULE: to test the use of aggregate function
SQL: SELECT AVG(LEASEMONTHLYRENT) AS AVERGAE_RENT FROM LEASE;
RESULT:
STATUS: PASS
RULE: to test the use of aggregate function
SQL: SELECT BUILDINGKEY, COUNT(APARTMENTKEY) AS NO_OF_APPARTMENTS FROM apartment
GROUP BY buildingkey;
RESULT:

STATUS: PASS
RULE: to test the working of joins
SQL: SELECT B.BUILDING_NAME, A.* FROM apartment A, building B WHERE B.building_key =
A.buildingkey;
RESULT:
STATUS: PASS
RULE: to test the working of joins
SQL: SELECT apartment.apartmentkey, apartment.apartmentrooms, lease.leasekey,
lease.leasestartdate, lease.leaseenddate, lease.leasedeposit FROM LEASE, apartment WHERE
lease.apartmentkey = apartment.apartmentkey;
RESULT:

STATUS: PASS
RULE: to test the working of insert command
SQLINSERT INTO BUILDING(BUILDING_KEY, BUILDING_NAME, BUILDING_ADDRESS, BUILDING_CITY,
BUILDING_STATE, BUILDING_ZIP, BUILDINGMANAGERPHONE) VALUES (4, 'DEF', '45 DARLEN ROAD',
'LA', 'CA', 97777, 5555555556);
RESULT: SELECT * FROM BUILDING;
STATUS: PASS
RULE: to test the working of insert command
SQL: INSERT INTO TENANT(tenantkey, tenantfirstname) VALUES ('T5', 'JIA');
RESULT:
STATUS: PASS

RULE: to test the working of update command
SQL: UPDATE TENANT SET TENANTPHONE = 1111111134 WHERE tenantkey = 'T5';
RESULT:
STATUS: PASS