Database Assignment

The assignment content is a database assignment that includes a data dictionary, database diagram, and database test. The data dictionary provides details about the tables, columns, and data types used in the database. The database diagram shows the relationships between the tables. The database test consists of various SQL queries to test the working of select statements using where clause, aggregate functions, joins, insert command, and update command.

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