SQL
Project description
A basic database is in place. Look at the business rules and test them with some SQL. Look at the previous business rules developed and design some SQL queries to test
them. Set up a test plan. List the rule, the SQL that was written, and the results. The business rules are: Tenants arrange maintenance requests online and tenants can
pay rent online. Attached is an example of what the results should look like.
USE [Master]
GO
CREATE DATABASE [LEASE] ON PRIMARY
(NAME = N’Lease’, FILENAME = N’FSASQLDBlease.mdf’,
SIZE = 4GB, MAXSIZE = 10GB, FILEGROWTH = 2GB)
LOG ON
(NAME = N’Lease_log’, FILENAME = N’FSASQLDBlease_log.ldf’,
SIZE = 2GB, MAXSIZE = 3GB, FILEGROWTH = 10%)
GO
CREATE TABLE dbo.Tenant Lease Agreement (
TenantLeaseKey CHAR(10), Primary Key,
LeaseStartDate DATE, Not Null,
LeaseEnIDate DATE, Not Null,
Deposit DECIMAL(15, 2),
RentAmount DECIMAL(15, 2),
LateFees DECIMAL(15, 2),
ApartmentKey CHAR(15), Foreign Key,
TenantKey CHAR(20), Foreign Key,
);
CREATE TABLE dbo. Building(
BuildingKey CHAR(10), Primary Key,
BuildingName CHAR(30), Not Null
BuldingAIDress CHAR(30), Not Null
);
CREATE TABLE dbo. Manager(
ManagerKey CHAR(10),Primary Key
ManagerLastName CHAR(50), Not Null
ManagerFirstName CHAR(50), Not Null
BuildingKey CHAR(10), Foreign Key
);
CREATE TABLE dbo.Tenant(
TenantKey CHAR(20), Primary Key
TenantLastName CHAR(50),
TenantFirstName CHAR(50),
TenantEmail_AIDress CHAR(30),
TenantPhoneNumber NUMERIC (20),
);
CREATE TABLE dbo.Rent Revenue(
RentRevenueKey CHAR10), Primary Key
RentAmount DECIMAL(15, 2),
RentDueDate DATE,
LateFeeAmount DECIMAL (15, 2),
TenantLeaseKey CHAR(30), Foreign Key
);
CREATE TABLE dbo. Apartment Maintenance(
ApartmentMaintenanceKey CHAR(10), Primary Key
RequestDate DATE,
MaintenanceType CHAR(30),
Resolution CHAR(30),
Resolution Date DATE,
ApartmentKey CHAR(30), Foreign Key
);
CREATE TABLE dbo. Expense Type(
ExpenseTypeKey CHAR(30), Primary Key
Repair CHAR(50),
Maintenance CHAR(50),
Utilities CHAR(50),
Apartment Cleaning CHAR(50),
Insurance CHAR(50),
);
CREATE TABLE dbo. Expenses(
ExpenseCostKey CHAR(30), Primary Key
CostAmount DECIMAL(15, 2),
ApartmentMaintenanceKey CHAR(30), Foreign Key
ExpenseTypeKey CHAR(30), Foreign Key
);
ITC 4150, Database Design and Implementation
Unit VII Westlake Research Hospital
Sample Answers to Scenario Questions
1. Two or three simple SELECTs with various WHERE criteria Rule to Test List all the patients of a given doctor List all the PatientKeys of patients whose depression
level is severe Means of Testing
SELECT * FROM Patient WHERE DoctorKey=’j303² SELECT PatientKey FROM PatientMedicalHistory WHERE DepressionLevel=’Severe’
Expected Result 4 records 3 records
Result 4 3
2. Two or three queries using aggregate functions Rule to Test Count the number of patients in each drug group Count the number of patients per doctor Means of Testing
SELECT DrugKey, COUNT(PatientKey) FROM PatientGroup GROUP BY DrugKey SELECT DoctorKey, COUNT(PatientKey) FROM Patient GROUP BY DoctorKey
Expected Result 7 in group 12, 6 in group 2 2 or 3 per doctor
Result Success
Success
3. At least two queries that use joins Rule to Test Get the names of all patients that have diabetes Means of Testing
SELECT PatientLastName, Diabetes FROM Patient p INNER JOIN PatientMedicalHistory pmh ON p.PatientKey=pmh.PatientKey WHERE Diabetes = 1 SELECT PatientLastName,
AppointmentDate, BloodPressure, Pulse, DepressionStatus FROM Patient p INNER JOIN Appointment a ON p.PatientKey=a.PatientKey INNER JOIN AppointmentDetail
Expected Result 2
Result 2
Get patient name, appointment date, blood pressure , pulse and depression status for a particular patient
Johnson, 10/12/2010,110/70,7 7,Same
Success
ad ON a.AppointmentKey=ad.Appointme ntKey WHERE p.PatientKey=’JL200²
4. Two or three INSERT statements Rule to Test AID a new appointment and appointment details Means of Testing
INSERT INTO Appointment(AppointmentKey, AppointmentDate, AppointmentTime,PatientKey, DoctorKey) VALUES(‘10101²,’10/23/2010²,’ 2:30 PM’, JL200²,’PD33²) INSERT INTO
AppointmentDetail(Appointment DetailKey, AppointmentKey, Bloodpressure, Pulse, PatientWeight, DepressionLevel, DoctorsNotes) VALUES(‘22020²,’10101²,’114/7
5²,77,187,’Moderate’,’the patient seems to be making some progess’)
Expected Result Two new records
Result success
5. One or two UPDATEs and/or a DELETE Rule to Test Change a patient’s doctor Change a patient’s phone number Means of Testing
UPDATE Patient SET DoctorKey=’jj01² WHERE PatientKey=’JL200² UPDATE Patient SET PatientPhone=’2065551245² WHERE PatientKey=’JL200²
Expected Result 1 record changed 1 record changed
Result 1 record changed 1 record changed
General Guidelines for this Assignment
The queries should be a part of a testing scenario. Each query should validate that the database fulfills a requirement or business rule. The testing, however, is not
expected to be complete or comprehensive in any way. The following rubric for this unit assignment should also guide you on the level of detail and the content
expected. Contact your instructor if you have questions.
Poor Incomplete or malformed SQL. Queries don’t aIDress requirements or business rules. No testing format
OK Uses testing format. Queries mostly correct. Queries mostly aIDress requirements and business rules
Good Uses testing format. All queries well-formed and directed at requirements and business rules
